maintaining sequence no of rows.

[sumittyagi]

Hi! to all!

I have got an issue.
* I maintain serial no. of rows tables in a column serial_no(which is set as primary key).

* while inserting i count the no of rows, "select count(*) from tbl", and increment one to it, and assign that as serial no to new row to be inserted.

is it the right way to achieve this. or is there any other better way to do it.

thanks in advance for your help.

[sumittyagi]

isn't there anyone having any idea about this???

[bartonc]

Hi! to all!

I have got an issue.
* I maintain serial no. of rows tables in a column serial_no(which is set as primary key).

* while inserting i count the no of rows, "select count(*) from tbl", and increment one to it, and assign that as serial no to new row to be inserted.

is it the right way to achieve this. or is there any other better way to do it.

thanks in advance for your help.

This is not safe at all. If you delete a row from anywhere but the end, you'll duplicate a sernum. I don't know if DB2 has a field type of AUTOINCREMENT, but it should. Somehow you need to let the DBMS figure out the new ID and then you should be able to query it. In MySQL it's

 SELECT last_insert_id()

Hope that gets you on the right track.

[Snib]

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!

[sumittyagi]

Thanks bartonc and snib.
I got the idea!

Special thanks to snib for such a descriptive explaination.

[Snib]

I found something else that you may want to look into, Not sure exactly what version of DB2 this came in with, also not sure of performance overhead etc.

http://publib.boulder. ibm.com/infocenter/db2luw/v8/index.jsp?topic =/com.ibm.db2.udb .doc/admin/t0004990.htm

I would definitely advice using the "GENERATED ALWAYS" option to ensure that the applications cannot set the values.

Apparently once you have completed an insert you can then call and SQL SET with the INDENTIT_VAL_LO CAL() function to return the number generated by the last INSERT command.

Only problem I can see if that you cannot set the initial value - apparently this may have been resolved with a change to this in DB2 V8 or V9 but I could not find anything on a quick Google search. Maybe one of the DBAs that looks after this site would know a reference you could use.

Regards

Snib

[Snib]

I just found a reference for the DB2 SEQUENCE type, the next thing in UDB after "IDENTIY COLUMNS"! Have a look at this URL for further details:

http://www-128.ibm.com/developerworks/db2/library/techarticle/0205pilaka/0205pilaka2.htm l

Seems you have a few options if you have DB2 Version 9 - you learn something new every day!

Regards

Snib