DB2 Version 7 Load Utility and Identity Columns

Rob Badger

DB2 Version 7 Load Utility and Identity Columns
Our shop is new to DB2 version 7 and the utility suite. I have created a
table with an identity column. The users requested a change to a column in
that table, and the want to keep the data.

I have unloaded all of the columns of the table by listing all of the
columns in the table. I am now trying testing the load with an identity
column.

How do I get the load utility to work? I have listed all of the columns in
the load statement created from the SYSPUNCH statement.

Any assistance will be greatly appreciated.

Rob Badger
WPS Insurance



Steen Rasmussen

Re: DB2 Version 7 Load Utility and Identity Columns
(in response to Rob Badger)
If you want to MAINTAIN the existing values, you can't have the identity
column defined GENERATED ALWAYS. You will have to change the column to
GENERATED BY DEFAULT - in that way you can assign the value, and if no value
is assigned, DB2 will generate one for you. But you also need to be aware of
the START VALUE in order not to get dup's.

Kind regards
Steen Rasmussen



-----Original Message-----
From: Rob Badger [mailto:[login to unmask email]
Sent: 27. september 2002 00:03
To: [login to unmask email]
Subject: DB2 Version 7 Load Utility and Identity Columns


Our shop is new to DB2 version 7 and the utility suite. I have created a
table with an identity column. The users requested a change to a column in
that table, and the want to keep the data.

I have unloaded all of the columns of the table by listing all of the
columns in the table. I am now trying testing the load with an identity
column.

How do I get the load utility to work? I have listed all of the columns in
the load statement created from the SYSPUNCH statement.

Any assistance will be greatly appreciated.

Rob Badger
WPS Insurance





Bob Irving

Re: DB2 Version 7 Load Utility and Identity Columns
(in response to Steen Rasmussen)
Hi,

We have done the same at our shop with ni problems. We are on V6, which
might make a difference in the approach you take, as V7 I believe (?) will
allow you to alter the last seq number generated for a given identity column
in a table directly in the catalog
With that said, We always use GENERATED BY DEFAULT so we can put our own
values in the IDENTITY COLUMN. Unload your table, take note of the total
number of rows. we then drop, recreate the table (along with the other
usual rebinds stats stuff) and change the start number in the IDENTITY Column
definition to something greater than the total # rows unloaded. Laod the data
as usual and you are good to go.

I'd be curious if V7 will allow you to just alter the sequence number table
in the catalog and basically do the same - bump up the last number to your
rows unloaded total + 1.

Hope this helps
Regards,
Bob Irving
FMR Corp

David Seibert

Re: DB2 Version 7 Load Utility and Identity Columns
(in response to Bob Irving)
>V7 I believe (?) will allow you to alter the last seq number generated for
a given identity column in a table directly in the catalog...
Hello Bob.

Nope. V7 DB2 for z/OS does not.

But a little birdie at the Tech Conference in Anaheim a few weeks ago told
us it might be in Version Next coming some time next year, perhaps, maybe,
possibly.

David Seibert
Compuware Corporation Database Product Architect
[login to unmask email]



The contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose
it to anyone else. If you received it in error please notify us immediately
and then destroy it.



Bob Irving

Re: DB2 Version 7 Load Utility and Identity Columns
(in response to David Seibert)
thank you -

Bill Kebea

identity columns
(in response to Bob Irving)
I created a table with an Identity Columns defined as Generate Always
(Start at 1, Increment by 1). When I load a row into the table the column
was set to 1.
When I loaded another row the column was set to 1001. Then 2001, 3001 and
so on. The inserts work find, i.e 1,2,3,4,etc. but the load doesn't seem
to have any rhyme or reason. I did 4 inserts and got 1,2,3,4. I then
loaded a row and the next number was 5. I then loaded another row ant the
number jumped to 1021.

Anybody have any clue as to what's happening.

Bill Kebea
Database Analyst
Wakefern Food Corp.
Edison, N.J.
[login to unmask email]



Phil Jackson

Re: identity columns
(in response to Bill Kebea)

Sounds like perhaps you are having DB2 reserve X number of values - check
the cache value
in the control panel - I think if checked, it will cache or reserve (default
20) ID values.

Phil J.





I created a table with an Identity Columns defined as Generate Always
(Start at 1, Increment by 1). When I load a row into the table the column
was set to 1.
When I loaded another row the column was set to 1001. Then 2001, 3001 and
so on. The inserts work find, i.e 1,2,3,4,etc. but the load doesn't seem
to have any rhyme or reason. I did 4 inserts and got 1,2,3,4. I then
loaded a row and the next number was 5. I then loaded another row ant the
number jumped to 1021.

Anybody have any clue as to what's happening.

Bill Kebea
Database Analyst
Wakefern Food Corp.
Edison, N.J.
[login to unmask email]





Mel Collier

Re: identity columns
(in response to Phil Jackson)
Bill,

My first question would have to be. Do you want to load the current
identity column value along with your other data and keep that value or do
you want DB2 to generate a new identity column value for you? There are
two ways to setup the identity column definition: Generate Always and
Generate By Default.

And tell us what type of load you are performing (insert, replace, etc).

Thanks,
Mel

-----Original Message-----
From: Jackson, Phil [mailto:[login to unmask email]
Sent: Thursday, December 12, 2002 2:59 PM
To: [login to unmask email]
Subject: Re: identity columns




Sounds like perhaps you are having DB2 reserve X number of values - check
the cache value
in the control panel - I think if checked, it will cache or reserve (default
20) ID values.

Phil J.





I created a table with an Identity Columns defined as Generate Always
(Start at 1, Increment by 1). When I load a row into the table the column
was set to 1.
When I loaded another row the column was set to 1001. Then 2001, 3001 and
so on. The inserts work find, i.e 1,2,3,4,etc. but the load doesn't seem
to have any rhyme or reason. I did 4 inserts and got 1,2,3,4. I then
loaded a row and the next number was 5. I then loaded another row ant the
number jumped to 1021.

Anybody have any clue as to what's happening.

Bill Kebea
Database Analyst
Wakefern Food Corp.
Edison, N.J.
[login to unmask email]



DB2-L webpage at http://listserv.ylassoc.com < http://listserv.ylassoc.com > .
The owners of the list can

Randy Bright

Re: identity columns
(in response to Mel Collier)
If you are using the IBM Load Utility it always uses a cache of 1000
regardless of the value specified at CREATE time.

Also, the remaining values in the cache at utility execution time and at DB2
shut-down are lost. For example, if you CREATE a new table with an IDENTITY
column specifying a cache of 20 (the default) and insert one row you get a
value of 1. If you then shut down DB2 and re-start it, then do another
insert, you get a value of 21 (you lost the 20 values cached by the first
start of DB2). If you then do a load, the first value generated by the load
utility will be 41 (you lost the 20 values cached and un-used by DB2). If
you loaded less than 1000 rows, then do a second load utility, the first
value generated by the second utility will be 1041 (you lost the 1000 cached
and un-used by the first load utility). And so on and so forth.

Keep in mind that IDENTITY values are only guaranteed to be generated
uniquely, not consecutively. You can specify a cache of 1 at CREATE time to
attempt to keep the values consecutive, but utilities will still generate
non-consecutive values. And if you have high insert activity the constant
updating of SYSIBM.SYSSEQUENCES at every insert is probably not worth the
performance hit.

Randy Bright
Architect, DB2 Utilities
BMC Software, Inc.

-----Original Message-----
From: Jackson, Phil [mailto:[login to unmask email]
Sent: Thursday, December 12, 2002 1:59 PM
To: [login to unmask email]
Subject: Re: identity columns




Sounds like perhaps you are having DB2 reserve X number of values - check
the cache value
in the control panel - I think if checked, it will cache or reserve (default
20) ID values.

Phil J.





I created a table with an Identity Columns defined as Generate Always
(Start at 1, Increment by 1). When I load a row into the table the column
was set to 1.
When I loaded another row the column was set to 1001. Then 2001, 3001 and
so on. The inserts work find, i.e 1,2,3,4,etc. but the load doesn't seem
to have any rhyme or reason. I did 4 inserts and got 1,2,3,4. I then
loaded a row and the next number was 5. I then loaded another row ant the
number jumped to 1021.

Anybody have any clue as to what's happening.

Bill Kebea
Database Analyst
Wakefern Food Corp.
Edison, N.J.
[login to unmask email]



DB2-L webpage at http://listserv.ylassoc.com < http://listserv.ylassoc.com > .
The owners of the list can