How to reset/restart/modify the IDENTITY counter?

[Who.Really.Really.Cares]

Hi!

I guess this must be a FAQ but I'll give it a try.
I've searched the web and usenet archive and found only negative
answers. But most of them were dated like 3-4 years back. Hasn't
anything changed since then? DB2 V7.2.
The problem:
I have a table with a primary key GENERATED BY DEFAULT AS IDENTITY.
Then I INSERT several rows and force their PK's value. Then I see that
my INSERTs haven't modified the value of DB2's internal IDENTITY
counter so subsequent INSERTs (this time with generated PK value) will
eventually conflict with existing rows resulting in 803 error.
So how can I load my table (from a script) with some initial data
before I launch my system?

Best regards

WhoReallyCares

[klh]

> So how can I load my table (from a script) with some initial data

before I launch my system?

If this DB2 on NT after you load the table run an SQL command to find
the maximum key value.

select max(key_value) from tblname

Then alter the table like:
alter table tblnm alter column key_nm restart with max_key_value + 1

Where max_key_value + 1 is whatever your previous select statement
showed.

If this is DB2 on os/390 or z/0S then create the table initially with
the generated ID starting at a value higher than the highest value
you'll be loading. Then load the table.

HTH,
klh

[Who.Really.Really.Cares]

Hi!

While browsing the internet I've found an outline of a solution to
simulate "ALTER TABLE ... RESTART WITH ..." on DB2 v7. It went like
this:

1. Instead of having IDENTITY column in your table, create another
table (counter) with a single column and define it as IDENTITY
2. Create a trigger in your table and have it: a) insert a record into
"counter" table, b) obtain the IDENTITY value, c) use this value in
your table, d) delete the record from the "counter" table
This is transparent to the application and the advantage is that you
can always drop and re-create the "counter" table with required initial
value of its IDENTITY counter.

So this is what I did:

1.
Instead of having normal table with IDENTITY column:

CREATE TABLE TAB (
ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT PK
PRIMARY KEY,
PAYLOAD INTEGER)

I had two tables:

CREATE TABLE TAB (
ID INTEGER WITH DEFAULT NOT NULL CONSTRAINT PK PRIMARY KEY,
PAYLOAD INTEGER)
CREATE TABLE CNTR (
ID INTEGER GENERATED ALWAYS AS IDENTITY)

2.
Trigger:

CREATE TRIGGER TRG
NO CASCADE BEFORE
INSERT ON TAB
REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO CNTR VALUES (DEFAULT);
DELETE FROM CNTR;
UPDATE TAB
SET ID = IDENTITY_VAL_LOCAL();
END

Unfortunately this doesn't work because INSERTs are illegal in BEFORE
triggers. Rats! Why???

I could imagine that I can't do INSERTs to the same table but why not
some other table??? DB2 sucks! :-)

Ok, so another try:

CREATE TRIGGER TRG1
NO CASCADE BEFORE
INSERT ON TAB
REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
SET NEW.ID = -1; --Mark the record somehow
END
CREATE TRIGGER TRG2
AFTER
INSERT ON TAB
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO CNTR VALUES (DEFAULT);
DELETE FROM CNTR;
UPDATE TAB
SET ID = IDENTITY_VAL_LOCAL()
WHERE ID = -1; --Find the marked record
END

Now it's starting to be really ugly and clumsy but works:

INSERT INTO TAB (PAYLOAD) VALUES (100)

SELECT * FROM TAB

ID PAYLOAD
-- -------
1 100

Unfortunately the application can't obtain the IDENTITY value of the
inserted record:

SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1 yields NULL.

I found the explanation in the documentation of IDENTITY_VAL_LOCAL():
"The INSERT statement must also be issued at the same level; that is,
the value must available locally at the level it was assigned, until it
is replaced by the next assigned value. (A new level is initiated each
time a trigger or routine is invoked.)"

So I tried in vain.

Now the question: How do you think, did the original inventor of the
solution mentioned in the beginning of my post really had some working
solution or only THOUGHT they had a solution. (This resembles the known
case with Fermat's last theorem haha).

Is there any way to cross those "levels" and pass the IDENTITY value
from within my trigger to the application???

I'll appreciate any comments. Thanks.

WhoReallyCares

P.S.
By the way. If I post this from Google Gropus, will it appear on Usenet
servers worldwide??

[Serge Rielau]

*sigh* This works on DB2 V7.2 for LUW which is what
you're running right? If you are on DB2 V7.1 for LUW it's about time you
catch up with 3 years worth of fixpack....

SET SCHEMA = MYSCHEMA
$

DROP PROCEDURE GOODSTUFF.SYNCIDENTITY
$
DROP TABLE T
$

CREATE PROCEDURE GOODSTUFF.SYNCIDENTITY(IN schemaname VARCHAR(128),
IN tablename VARCHAR(128))
BEGIN
DECLARE sqlcode INTEGER;
DECLARE maxid BIGINT;
DECLARE idcolname VARCHAR(128);
DECLARE stmttxt VARCHAR(1000);
DECLARE s STATEMENT;
DECLARE cur CURSOR FOR s;

SELECT colname INTO idcolname
FROM SYSCAT.COLUMNS
WHERE tabname = tablename
AND tabschema = schemaname
AND identity = 'Y';
IF SQLCODE = 100 THEN
SIGNAL SQLSTATE '78000'
SET MESSAGE_TEXT = 'can''t find identity column';
END IF;
SET stmttxt = 'SELECT MAX("' || idcolname || '") FROM "' ||
schemaname || '"."' || tablename || '"';
PREPARE s FROM stmttxt;
SET maxid = 0;
OPEN cur;
FETCH cur INTO maxid;
CLOSE cur;
SET stmttxt = 'ALTER TABLE "' || schemaname || '"."' || tablename ||
'" ALTER COLUMN "' || idcolname ||
'" RESTART WITH ' || CHAR(maxid + 1);
EXECUTE IMMEDIATE stmttxt;
END
$

SET PATH = PATH, GOODSTUFF
$

CREATE TABLE T(pk INT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
c1 INT)
$
INSERT INTO T(c1) VALUES 1, 2, 3,4 ,5, 6
$

SELECT * FROM T ORDER BY pk
$

INSERT INTO T(pk, c1) VALUES (12, 7), (13, 8)
$

CALL SYNCIDENTITY('MYSCHEMA', 'T')
$

INSERT INTO T(c1) VALUES 9, 10, 11
$

SELECT * FROM T ORDER BY pk
$

PK C1
----------- -----------
1 1
2 2
3 3
4 4
5 5
6 6
12 7
13 8
14 9
15 10
16 11

11 record(s) selected.
Cheers
Serge

PS: Makes me think just how much money I could make as a consultant
instead of a salary-man

PPS: Shall I submit a developerWorks DB2 article on this?

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[peteh]

We're not worthy, we're not worthy :-)

Seriously, if you began charging for the frequent and high quality
advice you provide here, we (the customers) and IBM would be deprived
of a very valuable service. Besides, I couldn't possibly afford you
even WITH the US/Canadian exchange rate.

Pete H

Serge Rielau wrote:

*sigh* This works on DB2 V7.2 for LUW which is what
you're running right? If you are on DB2 V7.1 for LUW it's about time you catch up with 3 years worth of fixpack....

SET SCHEMA = MYSCHEMA
$
<more excellent code snipped>
Cheers
Serge

PS: Makes me think just how much money I could make as a consultant
instead of a salary-man

PPS: Shall I submit a developerWorks DB2 article on this?

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Who.Really.Really.Cares]

Serge Rielau wrote:

*sigh* This works on DB2 V7.2 for LUW which is what
you're running right? If you are on DB2 V7.1 for LUW it's about time you catch up with 3 years worth of fixpack....
I'm running DB2 V7.1 for z/OS. (in original post I wrote 7.2 but it
proved to be a mistake).
PS: Makes me think just how much money I could make as a consultant
instead of a salary-man

I'm not sure what you're trying to say. Your smart procedure uses
"ALTER TABLE ... ALTER COLUMN ... RESTART WITH ..." syntax, which is
not available to me. So your reply doesn't fit to my problem, I guess.
What I was trying to achieve was to simulate "RESTART WITH"
functionality.

WhoReallyCares

[Serge Rielau]

Wh*********************@gmail.com wrote:

Serge Rielau wrote:

*sigh* This works on DB2 V7.2 for LUW which is what
you're running right? If you are on DB2 V7.1 for LUW it's about time

you

catch up with 3 years worth of fixpack....

I'm running DB2 V7.1 for z/OS. (in original post I wrote 7.2 but it
proved to be a mistake).

PS: Makes me think just how much money I could make as a consultant
instead of a salary-man

I'm not sure what you're trying to say. Your smart procedure uses
"ALTER TABLE ... ALTER COLUMN ... RESTART WITH ..." syntax, which is
not available to me. So your reply doesn't fit to my problem, I guess.
What I was trying to achieve was to simulate "RESTART WITH"
functionality.

WhoReallyCares

Ah.. now that starts to make sense.
I just checked DB2 V8 for zOS and RETART is supported.
You may want to:
* bug your boss when you can get DB2 V8
* bug your IBM Rep and ask whether the feature can be made abvailable
on DB2 V7. I can't imagine you being the first to need it.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab