isn't there anyone having any idea about this???
Unless your are using a DB with a similar function to the MS Access "AutoNumber " you will need to generate each unique serial number before you add the new row to your main table.
One way to solve this problem is to create a new table that just contains (as a minimum) two columns, for example REF_ID and NEXT_REF_NUMBER . The RE_ID could be a text field which contains a unique name for the reference, say "SERIALNO" and the NEXT_REF_NUMBER is an integer with a starting value of 1, or if you already have data in your main table then use the last serial number issued.
Before you add a new row to the main table you read the "SERIALNO" record from the new table, increment the NEXT_REF_NUMBER by 1 and then update the table immediately. keep the previous value of the NEXT_REF_NUMBER in a working storage fields as this is the serial number you need for your new row.
Depending on how you application works, and whether you have got any other outstanding updates to other tables pending at this point, you could commit the change to the new table. If your system can be accessed by multiple users at the same time this allows another user access to the new table to get the next available serial number.
If the addition of the row to the main table is in the middle of a number of other updates I would advice that you get the next serial number at the start of this process, before any other DB2 changes are made and commit this change to the database.
This helps to ensure that you do not issue the same serial number twice. Even if a later activity fails and the other Db2 changes are rolled back the update to the new table is already committed. It also ensure that other users can get to the new table as soon as possible to get the next serial number and that "roll backs" due to other errors do not again result in duplicate references being created.
Either way you can then add the new record to you main table with the serial number you stored. If a record gets deleted from the main table this will not affect the next serial number to be issued as this is being stored indepedantly on the other table and the next available serial number is not affected by the number of rows on the main table.
You can then use this new table to store as many references as you like, each one just needs it's own name that is unique.
There is a further downside to this process - each serial number produced follows on immediately from the next which means that subsequent inserts will add rows immediately after the last addition. This does not give DB2 a far chance to distribute the additions across the table as you are basically inserting one row after another.
If the system is not multi-user then this is not really a problem, it just results in your data not be very well organised on the table and you may need to re-organise the table more frequently because of this.
The way around this problem is to "cycle" the reference number. This requires an additional column on the new talble, REFLENGTH. This is again an integer and defines how long the generate reference is. So for example the row for "SERIALNO" could have a REFLENGTH of 8. So instead of creating "1" as the first reference you would create "00000001". You then bascially reverse this number so it becomes "10000000". This is then the value used on your main table as SERIALNO. The NEXT_REF_NUMMBE R on the main table is still incremented by 1, so in this example goes to 2 and this is then updated back to the table. When the next reference is created and reversed this gives a value of "20000000". This means that the two reference numbers generated do not follow sequencelly and so DB2 inserts these at difference points on the table and index - resulting in the data inserts being completed with a better distribution.
If you already have data on the table this is not a problem as you just set the NEXT_REF_NUMBER field to be the maximum value of the current rows on the table, but reversed e.g. current max serial number is 12345678, so set the NEXT_REF_NUMBER to 87654321. If you add one to this you get 87654322 which results in a serial number of 22345678 so a duplicate number is never generated.
Hope this helps, possibly a "little" more than you asked for!
202
