RE: Performance question about "next number" methods

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

I haven't done any benchmarking and am not sure if IBM has either.

I have switched to using identity column attribute due to ease of use and
design concept that pushing function into low level database code is a good
thing from maintenance, portability and performance standpoint (usually :)).

Elvis

Celebrating 10-Years of SQL Performance Excellence
http://centerfieldtechnology.com/training.asp

-----Original Message-----
Subject: Performance question about "next number" methods

Can anyone comment on the performance of the following alternative "next 
number" techniques:

    1) increment a value stored in a data area  (e.g. in RPG, something 
like this):

          *LOCK  IN       DATAAREA
                        ADD  1                   NEXTNBR
                        OUT   DATAAREA

    2) store a record in a keyed "next numbers" file that stores the 
current value for each file
        (where the "key" is the name of the file that this next number 
pertains to)

          pseudo-code below:
             read record with key, with a record lock
             increment next number value
             update record, releasing the record lock
             return new value to caller
 
    3) using newer features in DB2 UDB, e.g. a sequence or identity field

These techniques could be implemented in any ILE language, RPG IV, 
COBOL, C, etc.

I am interested in the relative performance of one technique versus another.

Thanks.

Mark S. Waterbury