Tags: asidentity, column, database, db2, default, generated, identity, mysql, oracle, sql, table, theidentity, value

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

On Database » DB2

14,457 words with 5 Comments; publish: Thu, 03 Jan 2008 13:05:00 GMT; (250171.88, « »)

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

All Comments

Leave a comment...

  • 5 Comments
    • Veeru71 schrieb:

      Quote:
      === Original Words ===

      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

      #1; Thu, 03 Jan 2008 13:07:00 GMT
    • j_ammann.db2.todaysummary.com.yahoo.com wrote:
      Quote:
      === Original Words ===

      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.

#2; Thu, 03 Jan 2008 13:08:00 GMT
  • >

    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/on...iness/conf2006/

    #3; Thu, 03 Jan 2008 13:08:00 GMT