Loading Identity Column

sue hsu

Loading Identity Column

Hi List,

We are using DB2 7.1 on z/os 1.2. We encounter a problem with a DB2 identity column. Will appreciate if anyone can shed some light on it.

We have a table that contains a column defined as:
integer not null generated always as identity
(start with 1, increment by 1, no cache, no cycle).
We defined a primary key (and, of course, a unique index) on this column.

We inserted 3 rows and then read the table without any problem.
We unloaded the data using DB2 UNLOAD utility, and tried to load it to
the same table under another qualifier. We used the DSN_IDENTITY along with
IGNOREFIELDS YES in the load control statement. The load job passed the RELOAD phase but failed at the build of the unique index(primary key), sending out a 'duplicates' error message.

How could, and why would, DB2 generate duplicate values for an identity column which is 'generated always'? Is there a PTF we have to apply, or did we miss something in our load job? Thanks for any help or information.

Sue Hsu





---------------------------------
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!

HEPP SHERY C

Re: Loading Identity Column
(in response to sue hsu)
On Thu, 20 Mar 2003 13:28:45 -0800, sue hsu <[login to unmask email]> wrote:

>
>Hi List,
>
>We are using DB2 7.1 on z/os 1.2. We encounter a problem with a DB2
identity column. Will appreciate if anyone can shed some light on it.
>
>We have a table that contains a column defined as:
> integer not null generated always as identity
> (start with 1, increment by 1, no cache, no cycle).
>We defined a primary key (and, of course, a unique index) on this column.
>
>We inserted 3 rows and then read the table without any problem.
>We unloaded the data using DB2 UNLOAD utility, and tried to load it to
>the same table under another qualifier. We used the DSN_IDENTITY along
with
>IGNOREFIELDS YES in the load control statement. The load job passed the
RELOAD phase but failed at the build of the unique index(primary key),
sending out a 'duplicates' error message.
>
>How could, and why would, DB2 generate duplicate values for an identity
column which is 'generated always'? Is there a PTF we have to apply, or
did we miss something in our load job? Thanks for any help or information.
>
>Sue Hsu
>
>
>
>
>
>---------------------------------
>Do you Yahoo!?
>Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!

Did anyone respond to your question regarding the duplicate identity
columns from the load? We recently encountered this as well. When I do a
count on the duplicate rows it looks like the majority of rows have the
same value-
SELECT HRCHY_NODE_map_K, COUNT(*)
FROM tfmr_HRCHYNODE_map
GROUP BY HRCHY_NODE_map_K
HAVING COUNT(*) > 1;
------------------------------------------------------------------------

HRCHY_NODE_MAP_K
---------------- -----------
-2079588352 907215

I have successfully loaded this table in the past- we introduced a bunch
of maintenance recently- I'm not sure if that caused this or not. I'm
unable to logon to IBM link to research if there is a PTF for this.

I was wondering how you resolved this?

Regards, Shery

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Kiran Gunnam

Re: Loading Identity Column
(in response to HEPP SHERY C)
I think it will restart the key . You need to alter sequnce and restart
with the high number + 1 or next val.



Kiran
--- Shery Hepp <[login to unmask email]> wrote:
> On Thu, 20 Mar 2003 13:28:45 -0800, sue hsu <[login to unmask email]>
> wrote:
>
> >
> >Hi List,
> >
> >We are using DB2 7.1 on z/os 1.2. We encounter a problem with a DB2
> identity column. Will appreciate if anyone can shed some light on it.
> >
> >We have a table that contains a column defined as:
> > integer not null generated always as identity
> > (start with 1, increment by 1, no cache, no cycle).
> >We defined a primary key (and, of course, a unique index) on this
> column.
> >
> >We inserted 3 rows and then read the table without any problem.
> >We unloaded the data using DB2 UNLOAD utility, and tried to load it
> to
> >the same table under another qualifier. We used the DSN_IDENTITY
> along
> with
> >IGNOREFIELDS YES in the load control statement. The load job passed
> the
> RELOAD phase but failed at the build of the unique index(primary
> key),
> sending out a 'duplicates' error message.
> >
> >How could, and why would, DB2 generate duplicate values for an
> identity
> column which is 'generated always'? Is there a PTF we have to apply,
> or
> did we miss something in our load job? Thanks for any help or
> information.
> >
> >Sue Hsu
> >
> >
> >
> >
> >
> >---------------------------------
> >Do you Yahoo!?
> >Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your
> desktop!
>
> Did anyone respond to your question regarding the duplicate identity
> columns from the load? We recently encountered this as well. When I
> do a
> count on the duplicate rows it looks like the majority of rows have
> the
> same value-
> SELECT HRCHY_NODE_map_K, COUNT(*)
> FROM tfmr_HRCHYNODE_map
> GROUP BY HRCHY_NODE_map_K
> HAVING COUNT(*) > 1;
>
------------------------------------------------------------------------
>
> HRCHY_NODE_MAP_K
> ---------------- -----------
> -2079588352 907215
>
> I have successfully loaded this table in the past- we introduced a
> bunch
> of maintenance recently- I'm not sure if that caused this or not. I'm
> unable to logon to IBM link to research if there is a PTF for this.
>
> I was wondering how you resolved this?
>
> Regards, Shery
>
>
---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives
> and home page at http://www.idugdb2-l.org/archives/db2-l.html. From
> that page select "Join or Leave the list". If you will be out of the
> office, send the SET DB2-L NO MAIL command to
> [login to unmask email] The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG
> conferences at http://conferences.idug.org/index.cfm


__________________________________
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

HEPP SHERY C

Re: Loading Identity Column
(in response to Kiran Gunnam)
Once I was able to get into IBMlink I found the following APAR- PQ76971

LOAD with IGNOREFIELDS YES into a table with an identity column,
for example:

LOAD DATA LOG YES REPLACE
INTO TABLE EIM_CONTACT_XM IGNOREFIELDS YES
( DSN_IDENTITY POSITION( 1: 8) DECIMAL)

caused bad values to be assigned to the identity column. The
IGNOREFIELDS specification was not being checked correctly,
causing LOAD to fail to generate identity column values.

Which we don't have applied- but I have requested our sysprog to apply this hiper.

Regards, Shery

-----Original Message-----
From: Kiran P Gunnam [mailto:[login to unmask email]
Sent: Tuesday, December 09, 2003 2:24 PM
To: [login to unmask email]
Subject: Re: Loading Identity Column

I think it will restart the key . You need to alter sequnce and restart
with the high number + 1 or next val.



Kiran
--- Shery Hepp <[login to unmask email]> wrote:
> On Thu, 20 Mar 2003 13:28:45 -0800, sue hsu <[login to unmask email]>
> wrote:
>
> Did anyone respond to your question regarding the duplicate identity
> columns from the load? We recently encountered this as well. When I
> do a
> count on the duplicate rows it looks like the majority of rows have
> the
> same value-
> SELECT HRCHY_NODE_map_K, COUNT(*)
> FROM tfmr_HRCHYNODE_map
> GROUP BY HRCHY_NODE_map_K
> HAVING COUNT(*) > 1;
>
------------------------------------------------------------------------
>
> HRCHY_NODE_MAP_K
> ---------------- -----------
> -2079588352 907215
>
> I have successfully loaded this table in the past- we introduced a
> bunch
> of maintenance recently- I'm not sure if that caused this or not. I'm
> unable to logon to IBM link to research if there is a PTF for this.
>
> I was wondering how you resolved this?
>
> Regards, Shery
>
>
---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives
> and home page at http://www.idugdb2-l.org/archives/db2-l.html. From
> that page select "Join or Leave the list". If you will be out of the
> office, send the SET DB2-L NO MAIL command to
> [login to unmask email] The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG
> conferences at http://conferences.idug.org/index.cfm


__________________________________
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm