Re: generate next sequence number while inserting data

   I have noticed that when an insert fails because of constraints, the
   number is skipped.

   Raul.
   
___________________________________________________________________________________
   CWilt@xxxxxxxxxxxx wrote:

 Ashish,

 Correct.  This was added at v5r2, you can't use it before that.

 Also, check out the ROWID.
 http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/sqlp/rbafymst80.htm


 Also an important note.  It is possible that the generated numbers are not
 sequential.  I haven't yet found the link with more info, but basically the
 sequence can skip values if multiple jobs are inserting into the same table.
 For performance reasons, the DB reserves a block of numbers for a given job
 doing inserts.

 HTH,
 Charles


  

 -----Original Message-----
 From: Ashish Kulkarni [mailto:kulkarni_ash1312@xxxxxxxxx]
 Sent: Tuesday, April 13, 2004 3:30 PM
 To: Java Programming on and around the iSeries / AS400
 Subject: RE: generate next sequence number while inserting data


 Hi
 Thanx for all the mails, i was able to do it
 CREATE TABLE CXASH/TEST2 (TEST1  SMALLINT NOT NULL
 GENERATED   
         ALWAYS AS IDENTITY(START WITH 1 INCREMENT  BY
 1 CYCLE),
 TEST2 VARCHAR (36) NOT NULL)                         
         
 Just to confirm, this will only work in V5R2 , and not
 before that
 ashish
 --- Rick.Chevalier@xxxxxxxxxxxxxxx wrote:
    

 Ashish,

 I believe what you want is an identity column.  It
 would be defined when the table is created on the
 AS/400.

 If the table already exists and you can't create it
 with an identity column try putting the sequence
 number logic into a user define function.

 HTH,

 Rick

 -----Original Message-----
 From: java400-l-bounces@xxxxxxxxxxxx
 [mailto:java400-l-bounces@xxxxxxxxxxxx]On Behalf Of
 Ashish Kulkarni
 Sent: Tuesday, April 13, 2004 1:26 PM
 To: java400-l@xxxxxxxxxxxx
 Subject: generate next sequence number while
 inserting data


 Hi
 I have to write a program which inserts data in
 AS400
 DB2 using SQL insert statement,
 One of the column is sequence number,
 When ever i insert a record i want to get the last
 sequence number add one to it and generate a next
 number and insert that number
 Do i have to do it programmatically or is there a
 way
 i can define the table so it will automatically
 generate the next sequence number and insert it
 automatically

 Ashish


 _______________________________________________
 This is the Java Programming on and around the
 iSeries / AS400 (JAVA400-L) mailing list
 To post a message email: JAVA400-L@xxxxxxxxxxxx
 To subscribe, unsubscribe, or change list options,
 visit:
 http://lists.midrange.com/mailman/listinfo/java400-l
 or email: JAVA400-L-request@xxxxxxxxxxxx
 Before posting, please take a moment to review the
 archives
 at http://archive.midrange.com/java400-l.

      


        
                
 __________________________________
 Do you Yahoo!?
 Yahoo! Tax Center - File online by April 15th
 http://taxes.yahoo.com/filing.html
 _______________________________________________
 This is the Java Programming on and around the iSeries /
 AS400 (JAVA400-L) mailing list
 To post a message email: JAVA400-L@xxxxxxxxxxxx
 To subscribe, unsubscribe, or change list options,
 visit: http://lists.midrange.com/mailman/listinfo/java400-l
 or email: JAVA400-L-request@xxxxxxxxxxxx
 Before posting, please take a moment to review the archives
 at http://archive.midrange.com/java400-l.

    

 _______________________________________________
 This is the Java Programming on and around the iSeries / AS400 (JAVA400-L) 
mailing list
 To post a message email: JAVA400-L@xxxxxxxxxxxx
 To subscribe, unsubscribe, or change list options,
 visit: http://lists.midrange.com/mailman/listinfo/java400-l
 or email: JAVA400-L-request@xxxxxxxxxxxx
 Before posting, please take a moment to review the archives
 at http://archive.midrange.com/java400-l.