Identity Column, -803

Steve Grimes

Identity Column, -803
Hello, V7.1, OS/390 V2R10 here...!

Table A has a single key primary key, called PRIMARY_KEY which is an
INTEGER GENERATED BY DEFAULT AS IDENTITY

We're in a situation (in test) where the MAXASSIGNEDVAL for this
table/column is less than the highest value currently in the table. (We
copy data from PROD to TEST using DSN1COPY)

So, we now get -803 abends when we run the program which inserts rows into
this table.

From the DB2-L archives in 5/2001 I read:

The problem with IDENTITY columns is not that DB2 "does not automatically
update the MAXASSIGNEDVAL" but rather that DB2 provides absolutely _no_
mechanism, (apart from dropping and recreating the table with updated
DDL)for modifying MAXASSIGNEDVAL.




Is this (still) true?

What if we wrote a routine that added and deleted in a loop a million times
or so. Wouldn't that get us a high enough MAXASSIGNEDVAL that we wouldn't
bump into the problem any more (on this small table?)

Thanks in advance for suggestions and advice.

Stg



Ashish Mohan

Re: Identity Column, -803
(in response to Steve Grimes)
You could try this as a workaround...

1. Find the maximum value of PRIMARY_KEY in the source subsystem (SELECT
MAX(PRIMARY_KEY) FROM Table A).
2. Find MAXASSIGNEDVAL on the target subsystem (by querying SYSSEQUENCES).
3. Calculate N = MAX(PRIMARY_KEY) - MAXASSIGNEDVAL
4. Perform DSN1COPY (as you usually do).
5. Define a trigger on the source subsystem:
CREATE TRIGGER TRIG1
NO CASCADE BEFORE
INSERT ON Table A
REFERENCING NEW AS NEWROW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
SET NEWROW.PRIMARY_KEY = NEWROW.PRIMARY_KEY + N;
END
In the above trigger definition, substitute 'N' with the value of N
calculated in step 3.

Any further insert in Table A would have it's value incremented before the
insert (due to the trigger) and avoid -803.

Thanks.
Ashish.

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Friday, December 27, 2002 12:28 PM
To: [login to unmask email]
Subject: Identity Column, -803

Hello, V7.1, OS/390 V2R10 here...!

Table A has a single key primary key, called PRIMARY_KEY which is an
INTEGER GENERATED BY DEFAULT AS IDENTITY

We're in a situation (in test) where the MAXASSIGNEDVAL for this
table/column is less than the highest value currently in the table. (We
copy data from PROD to TEST using DSN1COPY)

So, we now get -803 abends when we run the program which inserts rows into
this table.

From the DB2-L archives in 5/2001 I read:

The problem with IDENTITY columns is not that DB2 "does not automatically
update the MAXASSIGNEDVAL" but rather that DB2 provides absolutely _no_
mechanism, (apart from dropping and recreating the table with updated
DDL)for modifying MAXASSIGNEDVAL.




Is this (still) true?

What if we wrote a routine that added and deleted in a loop a million times
or so. Wouldn't that get us a high enough MAXASSIGNEDVAL that we wouldn't
bump into the problem any more (on this small table?)

Thanks in advance for suggestions and advice.

Stg








Glenn Mackey

Re: Identity Column, -803
(in response to Ashish Mohan)
On a similar vane. Perhaps multiply all values by -1 which are greater than
MAXASSIGNEDVAL. Then execute your insert 1 million times to get the
MAXASSIGNEDVAL number high. After that, multiply the negative values by -1
again. Assume all values gtr -1.

-----Original Message-----
From: Mohan, Ashish [mailto:[login to unmask email]
Sent: Friday, December 27, 2002 7:01 PM
To: [login to unmask email]
Subject: Re: Identity Column, -803


You could try this as a workaround...

1. Find the maximum value of PRIMARY_KEY in the source subsystem (SELECT
MAX(PRIMARY_KEY) FROM Table A).
2. Find MAXASSIGNEDVAL on the target subsystem (by querying SYSSEQUENCES).
3. Calculate N = MAX(PRIMARY_KEY) - MAXASSIGNEDVAL
4. Perform DSN1COPY (as you usually do).
5. Define a trigger on the source subsystem:
CREATE TRIGGER TRIG1
NO CASCADE BEFORE
INSERT ON Table A
REFERENCING NEW AS NEWROW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
SET NEWROW.PRIMARY_KEY = NEWROW.PRIMARY_KEY + N;
END
In the above trigger definition, substitute 'N' with the value of N
calculated in step 3.

Any further insert in Table A would have it's value incremented before the
insert (due to the trigger) and avoid -803.

Thanks.
Ashish.

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Friday, December 27, 2002 12:28 PM
To: [login to unmask email]
Subject: Identity Column, -803

Hello, V7.1, OS/390 V2R10 here...!

Table A has a single key primary key, called PRIMARY_KEY which is an
INTEGER GENERATED BY DEFAULT AS IDENTITY

We're in a situation (in test) where the MAXASSIGNEDVAL for this
table/column is less than the highest value currently in the table. (We
copy data from PROD to TEST using DSN1COPY)

So, we now get -803 abends when we run the program which inserts rows into
this table.

From the DB2-L archives in 5/2001 I read:

The problem with IDENTITY columns is not that DB2 "does not automatically
update the MAXASSIGNEDVAL" but rather that DB2 provides absolutely _no_
mechanism, (apart from dropping and recreating the table with updated
DDL)for modifying MAXASSIGNEDVAL.




Is this (still) true?

What if we wrote a routine that added and deleted in a loop a million times
or so. Wouldn't that get us a high enough MAXASSIGNEDVAL that we wouldn't
bump into the problem any more (on this small table?)

Thanks in advance for suggestions and advice.

Stg













Randy Bright

Re: Identity Column, -803
(in response to Glenn Mackey)
I can't pass up this opportunity for a product plug.

With the latest release of LoadPlus for DB2 from BMC Software, Inc., you
have the option on a LOAD REPLACE or RESUME YES run to update the
MAXASSIGNEDVAL with an appropriate value based on the data being loaded.
This option eliminates all the problems you are experiencing when moving
data from production to development.

If you want more information, contact me off-line.

Randy Bright
Architect, DB2 Utilities
BMC Software, Inc.
[login to unmask email]



-----Original Message-----
From: Mackey, Glenn [mailto:[login to unmask email]
Sent: Saturday, December 28, 2002 8:21 AM
To: [login to unmask email]
Subject: Re: Identity Column, -803


On a similar vane. Perhaps multiply all values by -1 which are greater than
MAXASSIGNEDVAL. Then execute your insert 1 million times to get the
MAXASSIGNEDVAL number high. After that, multiply the negative values by -1
again. Assume all values gtr -1.

-----Original Message-----
From: Mohan, Ashish [mailto:[login to unmask email]
Sent: Friday, December 27, 2002 7:01 PM
To: [login to unmask email]
Subject: Re: Identity Column, -803


You could try this as a workaround...

1. Find the maximum value of PRIMARY_KEY in the source subsystem (SELECT
MAX(PRIMARY_KEY) FROM Table A).
2. Find MAXASSIGNEDVAL on the target subsystem (by querying SYSSEQUENCES).
3. Calculate N = MAX(PRIMARY_KEY) - MAXASSIGNEDVAL
4. Perform DSN1COPY (as you usually do).
5. Define a trigger on the source subsystem:
CREATE TRIGGER TRIG1
NO CASCADE BEFORE
INSERT ON Table A
REFERENCING NEW AS NEWROW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
SET NEWROW.PRIMARY_KEY = NEWROW.PRIMARY_KEY + N;
END
In the above trigger definition, substitute 'N' with the value of N
calculated in step 3.

Any further insert in Table A would have it's value incremented before the
insert (due to the trigger) and avoid -803.

Thanks.
Ashish.

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Friday, December 27, 2002 12:28 PM
To: [login to unmask email]
Subject: Identity Column, -803

Hello, V7.1, OS/390 V2R10 here...!

Table A has a single key primary key, called PRIMARY_KEY which is an
INTEGER GENERATED BY DEFAULT AS IDENTITY

We're in a situation (in test) where the MAXASSIGNEDVAL for this
table/column is less than the highest value currently in the table. (We
copy data from PROD to TEST using DSN1COPY)

So, we now get -803 abends when we run the program which inserts rows into
this table.

From the DB2-L archives in 5/2001 I read:

The problem with IDENTITY columns is not that DB2 "does not automatically
update the MAXASSIGNEDVAL" but rather that DB2 provides absolutely _no_
mechanism, (apart from dropping and recreating the table with updated
DDL)for modifying MAXASSIGNEDVAL.




Is this (still) true?

What if we wrote a routine that added and deleted in a loop a million times
or so. Wouldn't that get us a high enough MAXASSIGNEDVAL that we wouldn't
bump into the problem any more (on this small table?)

Thanks in advance for suggestions and advice.

Stg


















Carol Hynes

Re: Identity Column, -803
(in response to Randy Bright)
Hi Steve,
Here's how we solved this problem. We defined the production tables using the default for the START WITH parm. We defined the development tables using START WITH 1000000 or some number millions higher than we expect production to be. That way we can load production data to the development environment because the numbers are lower. When the numbers start to get close, you just drop and recreate your development tables using a higher START WITH number.

-----Original Message-----
From: [login to unmask email]
[mailto:[login to unmask email]
Sent: Friday, December 27, 2002 2:28 PM
To: [login to unmask email]
Subject: Identity Column, -803


Hello, V7.1, OS/390 V2R10 here...!

Table A has a single key primary key, called PRIMARY_KEY which is an
INTEGER GENERATED BY DEFAULT AS IDENTITY

We're in a situation (in test) where the MAXASSIGNEDVAL for this
table/column is less than the highest value currently in the table. (We
copy data from PROD to TEST using DSN1COPY)

So, we now get -803 abends when we run the program which inserts rows into
this table.

From the DB2-L archives in 5/2001 I read:

The problem with IDENTITY columns is not that DB2 "does not automatically
update the MAXASSIGNEDVAL" but rather that DB2 provides absolutely _no_
mechanism, (apart from dropping and recreating the table with updated
DDL)for modifying MAXASSIGNEDVAL.




Is this (still) true?

What if we wrote a routine that added and deleted in a loop a million times
or so. Wouldn't that get us a high enough MAXASSIGNEDVAL that we wouldn't
bump into the problem any more (on this small table?)

Thanks in advance for suggestions and advice.

Stg



Dave Nance

Re: Identity Column, -803
(in response to Carol Hynes)
Steve,
Another way you can get around this is by recreating your table and specifying what number to start with. This is a simpler solution than running a million inserts and defining a trigger on this table in a development region that now doesn't match your production region. Here's an example from the SQL reference.

CREATE TABLE T2 (C1 DECIMAL(15,0) GENERATED BY DEFAULT AS IDENTITY
(START WITH 10),
C2 SMALLINT );


Dave Nance
First Health Services, Corp.
(804)527-6841

>>> [login to unmask email] 12/27/02 3:28:21 PM >>>
Hello, V7.1, OS/390 V2R10 here...!

Table A has a single key primary key, called PRIMARY_KEY which is an
INTEGER GENERATED BY DEFAULT AS IDENTITY

We're in a situation (in test) where the MAXASSIGNEDVAL for this
table/column is less than the highest value currently in the table. (We
copy data from PROD to TEST using DSN1COPY)

So, we now get -803 abends when we run the program which inserts rows into
this table.

From the DB2-L archives in 5/2001 I read:

The problem with IDENTITY columns is not that DB2 "does not automatically
update the MAXASSIGNEDVAL" but rather that DB2 provides absolutely _no_
mechanism, (apart from dropping and recreating the table with updated
DDL)for modifying MAXASSIGNEDVAL.




Is this (still) true?

What if we wrote a routine that added and deleted in a loop a million times
or so. Wouldn't that get us a high enough MAXASSIGNEDVAL that we wouldn't
bump into the problem any more (on this small table?)

Thanks in advance for suggestions and advice.

Stg




This message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.

Steve Grimes

Re: Identity Column, -803
(in response to Dave Nance)
Thanks all -- I think the "Start With" option is just what we need for this
case!

Stg