Next Sequence Number

[dhanuxp]

Hi..
I have a simple program which returns next sequence number & calling from java program.
test with 6 concurrent calling & it returns same sequence number to the 2 users. the rest are getting next incremented number...
do I need to call CL program to lock the file ?
any alternative idea to do this please....

     H DEBUG
     H OPTION(*SRCSTMT : *NODEBUGIO)
     H DFTACTGRP(*NO)
     H THREAD(*SERIALIZE)

      * Program entry point..
     d ULQT000R        PR
     d  ULQUTNUM                     10
      *
     d ULQT000R        PI
     d  ULQUTNUM                     10
      *
      * Data Structure for SQL Cursor
     d  c_ULQUTNUM     S             10    Inz
      *
      * Local Variables
      *
     d  t_CODE         s              2    Inz
     d  t_SEQU         s              8  0 Inz
      *
      *
      /free
         *Inlr = *On;

         Exec SQL SET OPTION
                     Naming    = *Sys,
                     Commit    = *None,
                     UsrPrf    = *User,
                     DynUsrPrf = *User,
                     Datfmt    = *iso,
                     CloSqlCsr = *EndMod;

         Exec SQL SELECT F00001 into : c_ULQUTNUM
                  FROM ULPARA WHERE K00001 ='QUTNUM';

         t_code     = %subst(c_ULQUTNUM:1:2);
         t_sequ     = %int(%subst(c_ULQUTNUM:3:8));
         t_sequ    += 1;
         ULQUTNUM   = t_code + %EDITC(  t_sequ : 'X' );

         Exec SQL UPDATE ULPARA SET F00001 =: ULQUTNUM
                  WHERE K00001 = 'QUTNUM';

         // sequence number to requester

         ULQUTNUM = c_ULQUTNUM;

         Return;
      /end-free

Thanks

[B.Hauser]

Re: Next Sequence Number

I'd use an SQL Sequence Object to determine the next number.:

1. Create sequence Object

CREATE SEQUENCE MySchema/MySeq 
as Dec(5, 0) start with LastULQUTNUM;

2. Determine next sequence:

/Free
   Exec SQL Set :t_sequ = Next Value for MySeq;
/End-Free

Using a sequence object will always determine the NEXT sequence even for concurrently running jobs.

Birgitta

[jamief]

Re: Next Sequence Number

Birgitta

what happens when it reaches 99999? Nevermind
You know so much stuff....
Your brain must weight 2500 grams. (twice the average)

CREATE SEQUENCE jamie/ORD_SEQ
     START WITH 1            
     INCREMENT BY 1          
     NO MAXVALUE             
     CYCLE

d MySequence      s              5  0                        
d count           s              5  0                        
                                                             
 /Free                                                       
     for count = 1 to 10000;                                 
      exec sql Set :MySequence = Next Value for ORD_SEQ;     
     endfor;                                                 
     *inlr = *on;                                            
                                                             
 /End-Free

CREATE SEQUENCE ORD_SEQ as Dec(2, 0) 
     START WITH 1                    
     INCREMENT BY 1                  
                                     
      no CYCLE

when you hit 99 in this loop it stays @ 99
then when called again seq remains at *zero....

d MySequence      s              2  0                     
d count           s              5  0                     
                                                          
 /Free                                                    
     for count = 1 to 100;                                
      exec sql Set :MySequence = Next Value for ORD_SEQ;  
     endfor;                                              
     *inlr = *on;                                         
                                                          
 /End-Free

I cycled this thing 10000 times it was slow!
I think I might stick with a dataarea and *LOCK.
But very interesting.

CREATE SEQUENCE statement


The CREATE SEQUENCE statement defines a sequence at the application server.

InvocationThis statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

AuthorizationThe privileges held by the authorization ID of the statement must include at least one of the following:

• IMPLICIT_SCHEMA privilege on the database, if the implicit or 2explicit schema name of the sequence does not exist2
• CREATEIN privilege on the schema, if the schema name of the 2sequence refers to an existing schema2
• SYSADM or DBADM authority

Syntax





Description

sequence-name

Names the sequence. The combination of name, and the implicit or explicit schema name must not identify an existing sequence at the current server (SQLSTATE 42710).The unqualified form of sequence-name is an SQL identifier. The qualified form is a qualifier followed by a period and an SQL identifier. The qualifier is a schema name.
If the sequence name is explicitly qualified with a schema name, the schema name cannot begin with 'SYS' or an error (SQLSTATE 42939) is raised.

AS data-type

Specifies the data type to be used for the sequence value. The data type can be any exact numeric type (SMALLINT, INTEGER, BIGINT or DECIMAL) with a scale of zero, or a user-defined distinct type or reference type for which the source type is an exact numeric type with a scale of zero (SQLSTATE 42815). The default is INTEGER.

START WITH numeric-constant

Specifies the first value for the sequence. This value can be any positive or negative value that could be assigned to a column of the data type associated with the sequence (SQLSTATE 42815), without non-zero digits existing to the right of the decimal point (SQLSTATE 428FA). The default is MINVALUE for ascending sequences and MAXVALUE for descending sequences.This value is not necessarily the value that a sequence would cycle to after reaching the maximum or minimum value of the sequence. The START WITH clause can be used to start a sequence outside the range that is used for cycles. The range used for cycles is defined by MINVALUE and MAXVALUE.

INCREMENT BY numeric-constant

Specifies the interval between consecutive values of the sequence. This value can be any positive or negative value that could be assigned to a column of the data type associated with the sequence (SQLSTATE 42815), and does not exceed the value of a large integer constant (SQLSTATE 42820), without non-zero digits existing to the right of the decimal point (SQLSTATE 428FA).If this value is negative, this is a descending sequence. If this value is 0 or positive, this is an ascending sequence. The default is 1.

MINVALUE or NO MINVALUE

Specifies the minimum value at which a descending sequence either cycles or stops generating values, or an ascending sequence cycles to after reaching the maximum value.

MINVALUE numeric-constant

Specifies the numeric constant that is the minimum value. This value can be any positive or negative value that could be assigned to a column of the data type associated with the sequence (SQLSTATE 42815), without non-zero digits existing to the right of the decimal point (SQLSTATE 428FA), but the value must be less than or equal to the maximum value (SQLSTATE 42815).

NO MINVALUE

For an ascending sequence, the value is the START WITH value, or 1 if START WITH is not specified. For a descending sequence, the value is the minimum value of the data type associated with the sequence. This is the default.

MAXVALUE or NO MAXVALUE

Specifies the maximum value at which an ascending sequence either cycles or stops generating values, or a descending sequence cycles to after reaching the minimum value.

MAXVALUE numeric-constant

Specifies the numeric constant that is the maximum value. This value can be any positive or negative value that could be assigned to a column of the data type associated with the sequence (SQLSTATE 42815), without non-zero digits existing to the right of the decimal point (SQLSTATE 428FA), but the value must be greater than or equal to the minimum value (SQLSTATE 42815).

NO MAXVALUE

For an ascending sequence, the value is the maximum value of the data type associated with the sequence. For a descending sequence, the value is the START WITH value, or -1 if START WITH is not specified.

CYCLE or NO CYCLE

Specifies whether the sequence should continue to generate values after reaching either its maximum or minimum value. The boundary of the sequence can be reached either with the next value landing exactly on the boundary condition, or by overshooting it.

CYCLE

Specifies that values continue to be generated for this sequence after the maximum or minimum value has been reached. If this option is used, after an ascending sequence reaches its maximum value it generates its minimum value; after a descending sequence reaches its minimum value it generates its maximum value. The maximum and minimum values for the sequence determine the range that is used for cycling.When CYCLE is in effect, then duplicate values can be generated for the sequence.

NO CYCLE

Specifies that values will not be generated for the sequence once the maximum or minimum value for the sequence has been reached. This is the default.

CACHE or NO CACHE

Specifies whether to keep some preallocated values in memory for faster access. This is a performance and tuning option.

CACHE integer-constant

Specifies the maximum number of sequence values that are preallocated and kept in memory. Preallocating and storing values in the cache reduces synchronous I/O to the log when values are generated for the sequence.In the event of a system failure, all cached sequence values that have not been used in committed statements are lost (that is, they will never be used). The value specified for the CACHE option is the maximum number of sequence values that could be lost in case of system failure.
The minimum value is 2 (SQLSTATE 42815). The default value is CACHE 20.

NO CACHE

Specifies that values of the sequence are not to be preallocated. It ensures that there is not a loss of values in the case of a system failure, shutdown or database deactivation. When this option is specified, the values of the sequence are not stored in the cache. In this case, every request for a new value for the sequence results in synchronous I/O to the log.

NO ORDER or ORDER

Specifies whether the sequence numbers must be generated in order of request.

ORDER

Specifies that the sequence numbers are generated in order of request.

NO ORDER

Specifies that the sequence numbers do not need to be generated in order of request. This is the default.

Notes

• 1It is possible to define a constant sequence, that is, 1one that would always return a constant value. 1This could be done by specifying an INCREMENT value of zero and a 1START WITH value that does not exceed MAXVALUE, or by specifying the 1same value for START WITH, MINVALUE and MAXVALUE. 1For a constant sequence, each time NEXT VALUE is invoked for the1sequence, the same value is returned. 1A constant sequence can be used as a numeric global variable. 1ALTER SEQUENCE can be used to adjust the values that will be 1generated for a constant sequence.
• A sequence can be cycled manually by using the ALTER SEQUENCE statement. If NO CYCLE is implicitly or explicitly specified, the sequence can be restarted or extended using the ALTER SEQUENCE statement to cause values to continue to be generated once the maximum or minimum value for the sequence has been reached.
• A sequence can be explicitly defined to cycle by specifying the CYCLE keyword. Use the CYCLE option when defining a sequence to indicate that the generated values should cycle once the boundary is reached. When a sequence is defined to automatically cycle (that is, CYCLE was explicitly specified), the maximum or minimum value generated for a sequence might not be the actual MAXVALUE or MINVALUE specified, if the increment is a value other than 1 or -1. For example, the sequence defined with START WITH=1, INCREMENT=2, MAXVALUE=10 will generate a maximum value of 9, and will not generate the value 10. When defining a sequence with CYCLE, carefully consider the impact of the values for MINVALUE, MAXVALUE and START WITH.
• Caching sequence numbers implies that a range of sequence numbers can be kept in memory for fast access. When an application accesses a sequence that can allocate the next sequence number from the cache, the sequence number allocation can happen quickly. However, if an application accesses a sequence that cannot allocate the next sequence number from the cache, the sequence number allocation may require having to wait for I/O operations to persistent storage. The choice of the value for CACHE should be done keeping in mind the performance and application requirements tradeoffs.
• The definer of a sequences is granted ALTER and USAGE privileges with the grant option. The definer can also drop the sequence.
• Compatibilities
  • For compatibility with previous versions of DB2:
    • A comma can be used to separate multiple sequence options
  • The following syntax is also supported:
    • NOMINVALUE, NOMAXVALUE, NOCYCLE, NOCACHE, and NOORDER.

ExamplesExample 1: Create a sequence called ORG_SEQ that starts at 1, increments by 1, does not cycle, and caches 24 values at a time:

  [COLOR=#000000][FONT=monospace][B]CREATE SEQUENCE[/B][/FONT][/COLOR][COLOR=#000000][FONT=monospace] ORG_SEQ[/FONT][/COLOR]
     [B]START WITH[/B] 1     [B]INCREMENT BY[/B] 1     [B]NO MAXVALUE[/B]     [B]NO CYCLE [/B][COLOR=#000000][FONT=monospace][B]CACHE[/B][/FONT][/COLOR][COLOR=#000000][FONT=monospace] 24 
[/FONT][/COLOR]

jamie

[Mercury]

Re: Next Sequence Number

This is how I shoud do to increment and update the seq number from a file.

    /free
     
        exec sql declare csr cursor for
              Select F00001
              From   ULPARA
              Where  K00001 ='QUTNUM'
              For    Update  of  F00001;
         
         exec sql open csr;

          exec sql fetch csr into :c_ULQUTNUM;

        t_code     = %subst(c_ULQUTNUM:1:2);         .
         .
         .
         .
        ULQUTNUM   = t_code + %EDITC(  t_sequ : 'X' );

         exec sql Update ULPARA
            Set   F00001 =: ULQUTNUM
            Where Current of csr;
         .
         .
         . 

Using the 'Current of csr' clause should run better and faster since it locks then updates the row where the cursor is pointing to.

[jamief]

Re: Next Sequence Number

Morning Philippe....How is your retirement treating you?

Take care
Jamie

[B.Hauser]

Re: Next Sequence Number

Why using a cursor at all and not updating the record directly?

/Free
   Exec SQL  Update ULPARA
                Set F00001 = Left(F00001, 2) concat Right(Digits(Next Value For MySeq), 8)
             Where K00001 = 'QUTNUM';
/End-Free

The solution may not work if you have to return the last value

Birgitta

[jamief]

Re: Next Sequence Number

This thread is LONG!

here is some stuff on a dataarea

this is the hunk of code that loops till locking dataarea
this will ensure no one gets duplicate #.

             *in99 = *on;                                                                           
             dow *in99 = *on;                                                                       
             in(e) *lock MyTwenty1DS;                                                               
              *in99 = %error;                                                                       
             enddo;

I?ve started a tutorial/sample reservoir on data area?s and their uses....


The example that I'm including here shows a couple examples.
It may have been better to split them up.
If this causes any issues please don't hesitate to ask questions.



DDS for file used in example

      * file                                                                                        
     A          R ##FILER                                                                           
     A            F1TEXT1       10          TEXT('Text-1')                                          
     A            F1TEXT2       10          TEXT('Text-2')                                          
     A            F1TEXT3       10          TEXT('Text-3')                                          
     A            F1NUM1         5  0       TEXT('Number-1')                                        
     A            F1DATE          L         DATFMT(*USA)                                            
     A          K F1DATE

Source for the program

     F##FILE    uf a e           k disk                                                             
      //-------------------------- ##FILE -------------------------                                 
      // A          R ##FILER                                                                       
      // A            F1TEXT1       10          TEXT('Text-1')                                      
      // A            F1TEXT2       10          TEXT('Text-2')                                      
      // A            F1TEXT3       10          TEXT('Text-3')                                      
      // A            F1NUM1         5  0       TEXT('Number-1')                                    
      // A            F1DATE          L         DATFMT(*USA)                                        
      // A          K F1DATE                                                                        
      //-----------------------------------------------------------                                 
      *                                                                                             
      * Variable Definition                                                                         
      *                                                                                             
     d CmdLength       s             15  5 inz(0)                                                   
     d CmdString       s            256    inz(*blanks)                                             
     d foundrecord     s               n                                                            
     d indataarea      s             20                                                             
     d  inDtaAra       s             10                                                             
     d  inLibrary      s             10                                                             
     d reply           s              1                                                             
                                                                                                    
     d MyTwenty1DS     ds            21    dtaara('MYTWENTY1')                                      
     d Program21                     21                                                             
                                                                                                    
     d DtaAraRcv       ds                                                                           
     d  AraBytes                     10i 0                                                          
     d  AraBytesOut                  10i 0                                                          
     d  AraDtaType                   10a                                                            
     d  AraLibrary                   10a                                                            
     d  AraLength                    10i 0                                                          
     d  AraDecimals                  10i 0                                                          
     d  AraValue                   2000a                                                            
                                                                                                    
     d APIError        ds                  Qualified                                                
     d  BytesP                       10i 0 inz(%size(apiError))                                     
     d  BytesA                       10i 0 inz(0)                                                   
     d  Messageid                     7                                                             
     d  Reserved                      1                                                             
     d  messagedta                  240                                                             
                                                                                                    
       //                                                                                           
       //  external calls                                                                           
       //                                                                                           
                                                                                                    
     d $command        pr                  extpgm('QCMDEXC')                                        
     d   command                   5000    options(*varsize)                                        
     d   Length                      15  5                                                          
                                                                                                    
     d $GetData        pr                  extpgm('QWCRDTAA')                                       
     d   thedata                           like(DtaAraRcv)                                          
     d   thedatasize                 10i 0 const                                                    
     d   libDtaara                   20    const                                                    
     d   start                       10i 0 const                                                    
     d   length                      10i 0 const                                                    
     d   Error                             Like(ApiError)                                           
                                                                                                    
       // automatically qualified by datastructure name                                             
     d beforeDS        ds                  LIKEREC(##FILER : *INPUT)                                
     d afterDS         ds                  LIKEREC(##FILER : *OUTPUT)                               
     d KeyDS           ds                  LIKEREC(##FILER : *KEY)                                  
                                                                                                    
      /free                                                                                         
                                                                                                    
        //--------------------------------------------------------                                  
        // MAIN PROGRAM                                                                             
        //--------------------------------------------------------                                  
                                                                                                    
              // this allows me to read from a file                                                 
              // keep a copy of the before record in datastructure BeforeDS                         
              // keep a copy of the after record in datastructure AfterDS                           
              // and update the table using the datastructure.                                      
              // only field changed was Text2...                                                    
                                                                                                    
              read ##FILE beforeDS;                                                                 
              //move the fields                                                                     
              afterDS = beforeDS;                                                                   
                                                                                                    
              // increment the number field by 1                                                    
              AfterDS.F1Num1 += 1;                                                                  
              // update the first record                                                            
              update ##FILER afterDS;                                                               
                                                                                                    
               // text2 update with dec field                                                       
              AfterDS.F1text2 = 'Counter:' + %char(AfterDS.F1Num1);                                 
              // write  a new record using datastructure                                            
              write ##FILER afterDS;                                                                
                                                                                                    
              reset foundrecord;                                                                    
              keyds.F1date = %date();                                                               
              setll %kds(KEYDS) ##FILER;                                                            
              if %equal;                                                                            
               foundrecord = *on;                                                                   
              endif;                                                                                
                                                                                                    
            // more dataarea code                                                                   
                                                                                                    
             cmdstring = 'CRTDTAARA DTAARA(QTEMP/MYTWENTY1) TYPE(*CHAR)' +                          
                         ' LEN(21)';                                                                
             cmdlength = %len(%trim(cmdstring));                                                    
             monitor;                                                                               
             $command (cmdstring:cmdlength);                                                        
             on-error;                                                                              
             endmon;                                                                                
                                                                                                    
           // populate it  ....                                                                     
                                                                                                    
             *in99 = *on;                                                                           
             dow *in99 = *on;                                                                       
             in(e) *lock MyTwenty1DS;                                                               
              *in99 = %error;                                                                       
             enddo;                                                                                 
                                                                                                    
             Program21 = 'Holy Crap Batman!';                                                       
             out MyTwenty1DS;                                                                       
             Unlock MyTwenty1DS;                                                                    
                                                                                                    
             in  MyTwenty1DS;         // no lock                                                    
             dsply program21 reply;                                                                 
                                                                                                    
           // now clear it  ....                                                                    
                                                                                                    
             *in99 = *on;                                                                           
             dow *in99 = *on;                                                                       
             in(e) *lock MyTwenty1DS ;                                                              
              *in99 = %error;                                                                       
             enddo;                                                                                 
             clear Program21;                                                                       
             out  MyTwenty1DS;                                                                      
                                                                                                    
             in  MyTwenty1DS;                                                                       
                                                                                                    
             dsply program21 reply ;                                                                
                                                                                                    
           // re-populate it  ....                                                                  
                                                                                                    
             *in99 = *on;                                                                           
             dow *in99 = *on;                                                                       
             in(e) *lock MyTwenty1DS;                                                               
              *in99 = %error;                                                                       
             enddo;                                                                                 
                                                                                                    
             Program21 = 'Lets try this again!';                                                    
             out MyTwenty1DS;                                                                       
             in MyTwenty1DS;                                                                        
             dsply program21 reply;                                                                 
                                                                                                    
             // use API QWCRDTAA to retrieve data from dataarea                                     
             exsr $QWCRDTAA;                                                                        
                                                                                                    
                                                                                                    
              *inlr = *on;                                                                          
                                                                                                    
        //--------------------------------------------------------                                  
        // $QWCRDTAA - read dataarea with API                                                       
        //--------------------------------------------------------                                  
                                                                                                    
             begsr $QWCRDTAA;                                                                       
                                                                                                    
              // these could be parameters nice if you need to access multiple                      
              // dataareas in multiple libraries.                                                   
              inDtaAra  = 'MYTWENTY1';                                                              
              inLibrary = 'QTEMP';                                                                  
                                                                                                    
              if InDtaAra = '*GDA' or                                                               
                 InDtaAra = '*LDA' or                                                               
                 InDtaAra = '*PDA';                                                                 
               %subst(InDataArea:1:10)  = InDtaARa;                                                 
               %subst(indataarea:11:10) = *blanks;                                                  
              else;                                                                                 
               indataarea =  inDtaAra + inlibrary;                                                  
              endif;                                                                                
                                                                                                    
              $getData(DtaAraRcv       :                                                            
                       %Size(DtaAraRcv):                                                            
                       InDataArea      :                                                            
                       -1              :                                                            
                       512             :                                                            
                       ApiError        );                                                           
                                                                                                    
              dsply %subst(AraValue:1:25) reply;                                                    
                                                                                                    
                                                                                                    
             endsr;                                                                                 
        //--------------------------------------------------------                                  
                                                                                                    
      /end-free

[Mercury]

Re: Next Sequence Number

Thanks ! Not so bad Jamie. You know retirement is a time when you never get around to doing all those things you intended to do when you were still working.

[jamief]

Re: Next Sequence Number

Enjoy young man! thanks for coming "out" to post...

Take care of yourself
Jamie

[Mercury]

Re: Next Sequence Number

Why using a cursor at all and not updating the record directly?

I can be mistaken but a cursor would lock the record from the fetch to the end of the update process so preventing any other update in between ??

[MichaelCatalani]

Re: Next Sequence Number

A feature I wish SQL would include is the ability of adding an "Indentity" type field based upon another field.

Like in the original scenario in the first post. Code - Sequence
Where the Sequence field is an identity field which starts at 1 and increments for each unique code field in the file.

Identity columns are really sweet, but at this point they cant handle incrementing a sequence field based upon another field.

Otherwise, updating a sequence like what is shown above is actually easier and cleaner, and likely much faster, with native IO. Plant the native IO portion in a service program so that it can be invoked from anywhere like the original post had it, except with native IO versus SQL.