Sequence alteration in db2

[manoj raut]

how to alter the paticular column in table for cerating sequence as auto generated.

[Snib]

Manoj,

The problem I can see with attempting to get DB2 to auto-generate a sequence number using a stored procedure or trigger is the issue of rollback in case of failure. Generally you set up another table to hold tha last sequence number issued . At the start of the unit of work you get the next sequence number from the sequence store table, increment the value and store this new value on the sequence store table. You then commit this change to the database. This ensure, even if the following unit of work fails, that the sequence number is not issued again. This is very important in multi-user type applications were more than one user could be adding rows to the same tables at the same time so they all need to generate new references to be stored which have to be unique.

Having read the details on stored procedures or triggers the problem seems to be that the generation of the next sequence number would be in the same unit of work as the add to the main table. If this unit of work was then rolled back the change to the sequence table would also be rolled back. In addition to this the update to the sequence store table would lock that row on the table for the duration of the current unit of work. The lock would be in place until the current unit of work failed or was committed. While the current unit of work is not yet commited to the tables the row on the sequence store table is locked so other users cannot create the next sequence number to use. This would potentially hold up these others users from progressing until the lock was released.

Have a look at my reply in the following thread as you could use a similar solution for you problem:

http://www.thescripts.com/forum/thread615216.html

Regards

Snib