RE: generate next sequence number while inserting data

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.
>