Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Initializing the Identity Columns in db2

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
yacov

New User


Joined: 27 Jul 2007
Posts: 6
Location: Israel

PostPosted: Sun Aug 05, 2007 11:01 pm    Post subject: Initializing the Identity Columns in db2
Reply with quote

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.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Sun Aug 05, 2007 11:13 pm    Post subject:
Reply with quote

why not use a timestamp?
Back to top
View user's profile Send private message
yacov

New User


Joined: 27 Jul 2007
Posts: 6
Location: Israel

PostPosted: Sun Aug 05, 2007 11:25 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Sun Aug 05, 2007 11:31 pm    Post subject:
Reply with quote

Drop/create is what you want to do then.
Back to top
View user's profile Send private message
yacov

New User


Joined: 27 Jul 2007
Posts: 6
Location: Israel

PostPosted: Mon Aug 06, 2007 12:00 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Mon Aug 06, 2007 12:54 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Mon Aug 06, 2007 1:32 am    Post subject:
Reply with quote

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 icon_lol.gif
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Mon Aug 06, 2007 4:07 am    Post subject:
Reply with quote

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?
Back to top
View user's profile Send private message
yacov

New User


Joined: 27 Jul 2007
Posts: 6
Location: Israel

PostPosted: Mon Aug 06, 2007 9:36 am    Post subject:
Reply with quote

Quote:
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?


I didn't explained myself properly.We are going to procces the data from db2 table, by taking every time the rows we need.

dbzTHEdinosauer - thanks for your advices - I will consider to use timestamp.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Mon Aug 06, 2007 10:01 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
skkp2006

New User


Joined: 14 Jul 2006
Posts: 93
Location: Chennai,India

PostPosted: Mon Aug 06, 2007 10:15 am    Post subject: Re: Initializing the Identity Columns in db2
Reply with quote

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
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts unload data from table with lob columns farhad_evan DB2 1 Sat Apr 22, 2017 1:32 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
No new posts Combine columns without breaking words Indrajit_57 SYNCSORT 1 Sat Mar 19, 2016 9:33 am
No new posts 3 columns update Seppe DB2 2 Mon Jun 22, 2015 6:43 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us