473,320 Members | 1,825 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
5 31953

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

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
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

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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Matt | last post by:
how to get the last record from database without looping? Whenever the user need to insert a new record to the database, it just increment the id field by one from the last record. I tried...
1
by: PT | last post by:
I got a problem. And thats..... First of all, I got these three tables. ------------------- ------------------ ---------------------- tblPerson tblPersonSoftware ...
5
by: newtophp2000 | last post by:
I have been using the following query to identify the IDENTITY columns in a given table. (The query is inside an application.) select column_name from information_schema.columns where...
12
by: francisds | last post by:
Hi, Can you guys see if there's a solution to this problem? I have a database from which I have to read each record and process that record. New records are being added all the time, so I...
2
by: db2group88 | last post by:
i would like to know when i create a table with identity column, should i used generated by default or generated as always. since when i create this table, i might copy some data from another table...
2
by: valerio | last post by:
Hi all, I've some problem to import data to DB2 using the db2move and db2look tools. Follow the problem : I have exported data from db2 v. 7 database on windows server, using the db2look and...
10
by: MaRCeLO PeReiRA | last post by:
Hi guys, I am in troubles with a SERIAL field. I have five tables. A parent table and four child tables. When I do the INSERT in the parent table, I have an ID (generated) by the sequence...
8
by: BigJohnson | last post by:
We're using ASPUpload as a tool to upload files to our server and save the details to SQLServer. However, I have an application where I need to return the pkID of the just saved file. I'm assuming...
1
by: Frank Swarbrick | last post by:
We're trying to take advantage of the new ROW CHANGE TIMESTAMP option. Here is a simple table: CREATE TABLE "ACCTASGN"."NUMBER_STATUS" ( "STATUS_CODE" CHAR(1) NOT NULL , "STATUS_DESCRIPTION"...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.