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 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 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.
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/
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
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/ This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
by: PT |
last post by:
I got a problem. And thats.....
First of all, I got these three tables.
------------------- ------------------ ----------------------
tblPerson tblPersonSoftware ...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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"...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
| |