By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,625 Members | 1,283 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,625 IT Pros & Developers. It's quick & easy.

how to get the last generated value of an identity column for a given table ?

P: n/a
Given a table with an identity column (GENERATED BY DEFAULT AS
IDENTITY),
is there any way to get the last generated value by DB2 for the
identity column?

I can't use identity_val_local() as the INSERTS are happening in a
different session.

Eg, We have the following table....

CREATE TABLE TEST
( A INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
B CHAR(30)
)

Also, we have created a unique index on column - A.

Somewhere in our application, the value for the identity column is
being explicity specified on the INSERT stmt.

Eg. INSERT INTO TEST (A, B) values (100, 'XYZ')

This is making some of the INSERT stmts fail (when DB2's internal
counter for the identity field reaches 100) with "unique constraint
violation" error.

Eg. INSERT INTO TEST (A,B) values (default, 'ABC')

I know DB2 would internally create a sequence object for identity
fields but is there any way to find out the corresponding Sequnce name
? If so, can we fetch "prevval" & "nextval" for that sequence ?

Thanks
-Murty

Sep 21 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a

Veeru71 schrieb:
Given a table with an identity column (GENERATED BY DEFAULT AS
IDENTITY),
is there any way to get the last generated value by DB2 for the
identity column?

I can't use identity_val_local() as the INSERTS are happening in a
different session.

Eg, We have the following table....

CREATE TABLE TEST
( A INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
B CHAR(30)
)

Also, we have created a unique index on column - A.

Somewhere in our application, the value for the identity column is
being explicity specified on the INSERT stmt.

Eg. INSERT INTO TEST (A, B) values (100, 'XYZ')

This is making some of the INSERT stmts fail (when DB2's internal
counter for the identity field reaches 100) with "unique constraint
violation" error.

Eg. INSERT INTO TEST (A,B) values (default, 'ABC')

I know DB2 would internally create a sequence object for identity
fields but is there any way to find out the corresponding Sequnce name
? If so, can we fetch "prevval" & "nextval" for that sequence ?

Thanks
-Murty
select t.tabschema,t.tabname,c.colname
from syscat.sequences s join syscat.tables t on
(s.seqschema=t.tabschema and s.create_time=t.create_time)
join syscat.columns c on
(t.tabschema=c.tabschema and t.tabname=c.tabname)
where s.seqname like 'SQL%'
and c.identity='Y'

joerg

Sep 21 '06 #2

P: n/a

j_******@yahoo.com wrote:
Veeru71 schrieb:
Given a table with an identity column (GENERATED BY DEFAULT AS
IDENTITY),
is there any way to get the last generated value by DB2 for the
identity column?

I can't use identity_val_local() as the INSERTS are happening in a
different session.

Eg, We have the following table....

CREATE TABLE TEST
( A INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
B CHAR(30)
)

Also, we have created a unique index on column - A.

Somewhere in our application, the value for the identity column is
being explicity specified on the INSERT stmt.

Eg. INSERT INTO TEST (A, B) values (100, 'XYZ')

This is making some of the INSERT stmts fail (when DB2's internal
counter for the identity field reaches 100) with "unique constraint
violation" error.

Eg. INSERT INTO TEST (A,B) values (default, 'ABC')

I know DB2 would internally create a sequence object for identity
fields but is there any way to find out the corresponding Sequnce name
? If so, can we fetch "prevval" & "nextval" for that sequence ?

Thanks
-Murty

select t.tabschema,t.tabname,c.colname
from syscat.sequences s join syscat.tables t on
(s.seqschema=t.tabschema and s.create_time=t.create_time)
join syscat.columns c on
(t.tabschema=c.tabschema and t.tabname=c.tabname)
where s.seqname like 'SQL%'
and c.identity='Y'

joerg
Thanks a lot. But I am unable to fetch prevval / nextval on these
system generated sequences....

db2 =values nextval for SQL060921094317700
SQL20142N Sequence "FTS.SQL060921094317700" cannot be used as specified.

Sep 21 '06 #3

P: n/a
Veeru71 wrote:
j_******@yahoo.com wrote:
>Veeru71 schrieb:
>>Given a table with an identity column (GENERATED BY DEFAULT AS
IDENTITY),
is there any way to get the last generated value by DB2 for the
identity column?

I can't use identity_val_local() as the INSERTS are happening in a
different session.

Eg, We have the following table....

CREATE TABLE TEST
( A INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
B CHAR(30)
)

Also, we have created a unique index on column - A.

Somewhere in our application, the value for the identity column is
being explicity specified on the INSERT stmt.

Eg. INSERT INTO TEST (A, B) values (100, 'XYZ')

This is making some of the INSERT stmts fail (when DB2's internal
counter for the identity field reaches 100) with "unique constraint
violation" error.

Eg. INSERT INTO TEST (A,B) values (default, 'ABC')

I know DB2 would internally create a sequence object for identity
fields but is there any way to find out the corresponding Sequnce name
? If so, can we fetch "prevval" & "nextval" for that sequence ?

Thanks
-Murty
select t.tabschema,t.tabname,c.colname
from syscat.sequences s join syscat.tables t on
(s.seqschema=t.tabschema and s.create_time=t.create_time)
join syscat.columns c on
(t.tabschema=c.tabschema and t.tabname=c.tabname)
where s.seqname like 'SQL%'
and c.identity='Y'

joerg

Thanks a lot. But I am unable to fetch prevval / nextval on these
system generated sequences....

db2 =values nextval for SQL060921094317700
SQL20142N Sequence "FTS.SQL060921094317700" cannot be used as specified.
That's by design. Anyway. Do you want teh last value generated by YOU?
or by ANYONE?
By anyone you can get the _high_watermark_ out of SYSIBM.SYSSEQUENCES,
but that simply tells you the next batch of values to be issued.
Also while you are fetching it the rest of teh system can move on. So
it's a rather fussy value.
Mind to tell us what you are trying to do?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 21 '06 #4

P: n/a

Serge Rielau wrote:
Veeru71 wrote:
j_******@yahoo.com wrote:
Veeru71 schrieb:

Given a table with an identity column (GENERATED BY DEFAULT AS
IDENTITY),
is there any way to get the last generated value by DB2 for the
identity column?

I can't use identity_val_local() as the INSERTS are happening in a
different session.

Eg, We have the following table....

CREATE TABLE TEST
( A INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
B CHAR(30)
)

Also, we have created a unique index on column - A.

Somewhere in our application, the value for the identity column is
being explicity specified on the INSERT stmt.

Eg. INSERT INTO TEST (A, B) values (100, 'XYZ')

This is making some of the INSERT stmts fail (when DB2's internal
counter for the identity field reaches 100) with "unique constraint
violation" error.

Eg. INSERT INTO TEST (A,B) values (default, 'ABC')

I know DB2 would internally create a sequence object for identity
fields but is there any way to find out the corresponding Sequnce name
? If so, can we fetch "prevval" & "nextval" for that sequence ?

Thanks
-Murty
select t.tabschema,t.tabname,c.colname
from syscat.sequences s join syscat.tables t on
(s.seqschema=t.tabschema and s.create_time=t.create_time)
join syscat.columns c on
(t.tabschema=c.tabschema and t.tabname=c.tabname)
where s.seqname like 'SQL%'
and c.identity='Y'

joerg
Thanks a lot. But I am unable to fetch prevval / nextval on these
system generated sequences....

db2 =values nextval for SQL060921094317700
SQL20142N Sequence "FTS.SQL060921094317700" cannot be used as specified.
That's by design. Anyway. Do you want teh last value generated by YOU?
or by ANYONE?
By anyone you can get the _high_watermark_ out of SYSIBM.SYSSEQUENCES,
but that simply tells you the next batch of values to be issued.
Also while you are fetching it the rest of teh system can move on. So
it's a rather fussy value.
Mind to tell us what you are trying to do?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Thanks Serge.
When you said get the _high_watermark_, I guess you mean get the
LASTASSIGNEDVAL from SYSIBM.SYSSEQUENCES.

We are actually trying to trouble-shoot a production issue wherein some
of the INSERTs (identity values are DB2 generated, not user-supplied)
are erroring out, with "unique constraint violation" message on the
identity field. I am guessing somewhere in the app, the rows are
getting inserted with user-supplied values to the identity field and
whenever DB2 counter hits these numbers, the INSERTs are failing.

I am just curious as to how to get the last assigned value (by DB2 in
any session, not user-supplied value) of the Identity field. Based on
your explanation, we can only get a rough estimate (high water mark) I
guess.

- Murty

Sep 22 '06 #5

P: n/a
Veeru71 wrote:
I am just curious as to how to get the last assigned value (by DB2 in
any session, not user-supplied value) of the Identity field. Based on
your explanation, we can only get a rough estimate (high water mark) I
guess.
The LASTASSIGNEDVALUE gives you an indicator of the last batch of values
doled out to DB2. So with default CACHE 20 and a non DPF system you get
a pretty good idea of where you are.
Why don't you just run a MAX() against the identity column? Compare that
with the last assigned value and you see the wall before it hits you.
Are you using with GENERATED BY DFEAULT? Did you use the CYCLE option or
change the sign of the INCREMENT.
Lastly take a look at you LOAD scripts. Do they OVERRIDE IDENTITY?
Are you using replication?

Note that in DB2 V8.2 you can alter the identity options from ALWAYS to
DEFAULT online. Given that there is very little reason to use GENERATED
BY DEFAULT during normal operation.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 22 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.