Initializing the Identity Columns in db2

yacov · New User Joined: 27 Jul 2007 Posts: 6 Location: Israel

Hi,

I'm pretty new to db2 world (mainly worked with VSAMs and ADABAS before), so here my question.

In our application we write transactions into db2 table.After the finishing the on-line operations, we uload the table to the seq. file and do our proccesing.
Identity column is not used for the key. We use it to know ,after table unload, the order of transactions, it is important for us.

The question is how after uloading the data , we initilize the Identity column starting value? Every day in our system is a new day and we want to start from the begining.

I hope that the drop and create table is not the only solution.

Thanks.

dbzTHEdinosauer · Posted: Sun Aug 05, 2007 11:13 pm

why not use a timestamp?

yacov · New User Joined: 27 Jul 2007 Posts: 6 Location: Israel

This is the solution, but we prefer to deal with real number(s) of transactions, it is much more comfortable for us, and not with meaningless timestamp.
We occasionly look directly to the table to find reasons for the errors.

stodolas · Posted: Sun Aug 05, 2007 11:31 pm

Drop/create is what you want to do then.

yacov · New User Joined: 27 Jul 2007 Posts: 6 Location: Israel

Ok, I understand.Thanks,

Just one another question. What the meaning of order in definition of Identity column ?
If I specify No Order I can get 5 value and after that 4? It doesn't makes sense.

dbzTHEdinosauer · Posted: Mon Aug 06, 2007 12:54 am

To use an identity column as a non-key field is dumb, and you are miss-using the row-id feature of DB2. Since a row-id will eventually roll (start at the begining), it will be meaningless.

timestamp-insert would provide an ordering (sequence of what went first, etc.) Timestamps don't start over, they just keep going....

Since timestamp is unique, it could be your referal (key) back to the db2 table; timestamp will be ordered and using a timestamp range you know what was posted on what day.

as you said, no experience with db2. it is not vsam, should not be processed as vsam, and you should learn something new.

But, it is your system, do what you want.

dbzTHEdinosauer · Posted: Mon Aug 06, 2007 1:32 am

besides, to go thru the trouble of defining a column as identity, and defining a unique index - to insure that it is unique, seems sort of wasteful, when you are not going to take advantage of the select options (since you are processing the data as qsam - still walking with mud on your boots).

and since you are starting fresh each day, you could save the drop and create (if you used timestamp) by deleting all the rows during the unload.

and i image the insert would be faster with a timestamp as apposed to an identity column, since there is no need to check for uniqueness - but then again, doing anything efficiently or quickly during the online does not seem to be you goal - just make things a little easier during a batch cycle - that could be written to use a cursor select (for update - actually delete) directly from the DB2 table thus providing yourself/project an easy restart capability - as apposed to reading thru a qsam file to the restart point.

yeah, i am just needling you, db2 rookie

stodolas · Posted: Mon Aug 06, 2007 4:07 am

What is the rational for unloading the table to a file just to process it? Why not process it right from the DB with an ORDER BY clause on your cursor?

yacov · New User Joined: 27 Jul 2007 Posts: 6 Location: Israel

dbzTHEdinosauer · Posted: Mon Aug 06, 2007 10:01 am

If this is indeed a quick and dirty conversion/implementation, dropping the table and then create with the identity column defined as

GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1)

would be very quick and easy.

The 'next identity column value' is maintained in one of the sysibm.?? tables. I can get back to you with the exact table, or someone else can provide the info in the meantime.

If you don't drop and create, the 'next value' must be manipulated in the 'sysibm.???table' and that is not advised.

The concept of identity column was to provide a methodology whereby you could have a unique key for a row without having to define a multi-multi-column primary key or where a timestamp would be meaningless or as unique would be difficult to create - i.e. mass insert.

skkp2006 · Posted: Mon Aug 06, 2007 10:15 am

I would suggest loading an empty file into the table rather than a drop and define statement. If the volume of data is not so high getting the required data using the timestamp is the best possible solution by which the old data may be for a week can be retained in the table and can be flushed by some weekend jobs.

Syam