473,695 Members | 2,782 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_lo cal() 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 32024

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_lo cal() 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.t abname,c.colnam e
from syscat.sequence s 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.tab name)
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_lo cal() 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.t abname,c.colnam e
from syscat.sequence s 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.tab name)
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 SQL060921094317 700
SQL20142N Sequence "FTS.SQL0609210 94317700" 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_lo cal() 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.t abname,c.colnam e
from syscat.sequence s 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.tab name)
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 SQL060921094317 700
SQL20142N Sequence "FTS.SQL0609210 94317700" 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.SYSSEQUE NCES,
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_lo cal() 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.t abname,c.colnam e
from syscat.sequence s 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.tab name)
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 SQL060921094317 700
SQL20142N Sequence "FTS.SQL0609210 94317700" 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.SYSSEQUE NCES,
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.SYSSEQUE NCES.

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 LASTASSIGNEDVAL UE 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
11745
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 objRS.MoveLast, but it wont work.
1
8741
by: PT | last post by:
I got a problem. And thats..... First of all, I got these three tables. ------------------- ------------------ ---------------------- tblPerson tblPersonSoftware tblSoftware ------------------- ------------------ ---------------------- PID PName PID* SID* SID SWName --- ----- --- --- --- ------ 1 Thomas 1 1 ...
5
3498
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 table_schema = 'user_a' and table_name = 'tab_a' and columnproperty(object_id(table_name), column_name, 'IsIdentity') = 1 This works. When "user_a" performs the query, everything is OK.
12
2067
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 need to go back and check for new records and process them. However:
2
11551
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 with identity column also, (so if i use generated as always, i can't do insert into new_tables select * from old_table), then i might also insert new data into this new table (if i do generated by default, then the new table could have the same...
2
17340
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 db2move tools. Then I moved the *.SQL and *.IXF files to the linux server, where is installed DB2 v.8 fp 6.
10
6417
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 (SERIAL field), and I have to use this ID to reference all child tables.
8
2719
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 that I could use the @@Identity command but cannot get this to function. Has anyone used this command with ASPUpload with an success, or any other methods that could be used? Thanks.
1
11897
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" VARCHAR(40) NOT NULL , "LAST_UPDATE" TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
1
8845
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8825
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7660
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6491
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5839
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4579
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3003
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2272
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1976
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.