SQL to get auto-generated key from last insert statement

[s1devwiz]

Greetings,

I am new to DB2 and am migrating an app from Oracle to DB2 UDB Workgroup 7.1. I am planning on using the GENERATED ALWAYS AS IDENTITY clause for my primary key field in each of my tables. (the Oracle app uses sequences)
My question is, after I perform an INSERT on a table, what is the SQL to get the value of the new records generated key value?

Thanks in advance,

John

 

[blom0344]

Hello John,

How would you tell which records were lastly inserted AFTER you have done the insert? If you specify as GENERATED ALWAYS instead of GENERATED BY DEFAULT the new numbers will be unique in the whole range , but where does the new range start? Can't imagine you can solve this with a SQL on the updated table. If you store a copy of the table before the insert you can run a simple EXCEPT query on the identity column. I can imagine writing an extra timestamp to a field as an alternative and afterwards using this in the where clause......... T. Blom
Information analyst
tbl@shimano-eu.com

 

[s1devwiz]

Maybe a little more clarification is needed. I need to save a record in a parent table and records in the child table. I need to retrieve the value of the auto generated key of the newly inserted parent record in order to perform inserts on the child table.
What are some techniques in DB2 to handle this common database situation?
For example in MS SQLServer there is an @@identity 'function' that you can select to get the value of the autogenerated key of the last insert statement you executed: something like:

insert into parenttbl (...) values (...)
select @@indentity as newkeyval
insert into childtbl (parentkey,...) values (newkeyval,...)

 

[Shaixpeare]

John, here are 2 ways:

1) Values IDENTITY_VAL_LOCAL()
2) SET :HOSTVAR = IDENTITY_VAL_LOCAL()

You can also, just use the IDENTITY_VAL_LOCAL() function on subsequent inserts to child tables if you need to propagate the value.

Hope this helps,
Todd

 

[legacy370]

I want to bring out a point that you should be aware of: presuming that you're using ROWID for your identity fields, DB2 changes the ROWID whenever the table is loaded. Consequently, if you've inserted those original ROWID's into a child table, you'll need to update the child table with the new values that DB2 has used for the parent table upon loading.

 

[blom0344]

I looked up upon this subject a bit further and I doubt it has anything to do with ROWID. This (ROWID) is a virtual column only created when you query a table. The identity column is generated uniquely with recoverable values for each row in a table. With recoverable I should imagine that DB2 will NOT change the values of existing identities.
There is however a distinction between GENERATED ALWAYS and GENERATED BY DEFAULT. The last option allows an application to provide explicit values for a column. DB2 then generates the values not explicitly set, but only provides uniqueness against the existing values in the table.
The value of an identity column can only be modified if it is defined as GENERATED BY DEFAULT.

Anyone for a decisive comment? T. Blom
Information analyst
tbl@shimano-eu.com

 

[s1devwiz]

Thanks for the replies.

First of all, my tables are defined with key fields defined with BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE). I will not be explicitly changing the indentity fields and want DB2 to generate values for me. That part I got.

My problem is propagating the identity value of a parent table into its child tables' records.

This is a client/server app and after the parent insert statement, I issue
select IDENTITY_VAL_LOCAL() as newkey from sysibm.sysdummy1
which is returning NULL.

Am I going about this all wrong?

Thanks in advance,

John

 

[blom0344]

Hello John,

You may want to look at:

http://www.idug.org/member/journal/may01/articl12.cfm

but I doubt it will make you very glad......... T. Blom
Information analyst
tbl@shimano-eu.com

 

[Shaixpeare]

John, you are making this more complicated than it is. Are you familiar with DB2's "VALUE" statement; it's kind of like a select statement. Now, I don't know what language your client/server app is written in so I can't show you the specific code, but the statement itself is EXACTLY like the one in my earlier reply to this thread. Forget doing the select on sysdummy and make sure you do the values statement immediately after your insert and before any commits, etc. It's a piece of cake. Cya

 

[s1devwiz]

SOLVED!

Ok, got it working. The problem was that I had the ODBC setting AUTOCOMMIT set to 'on' (the default) so that is why I was getting NULL values.

I did try the VALUE command without error but got NULL values also, so I stuck with the select statement since I wasn't that familiar with the VALUE command.

Thanks for your helpful input.

John