Determining newly inserted identity value in DB2 v6

Determining newly inserted identity value in DB2 v6

Post by Dan Thomps » Sun, 16 Dec 2001 00:58:22



In DB2 v6, I have a db2 column that is defined with the identity clause.
Following insert, I need a way to determine the identity value assigned
by DB2.  The SQL Reference book gives the following example, using
VALUES and IDENTITY_VAL_LOCAL:

#1 CREATE TABLE EMPLOYEE
#1 (EMPNO INTEGER GENERATED ALWAYS AS IDENTITY,
#1 NAME CHAR(3 ),
#1 SALARY DECIMAL(5,2),
#1 DEPTNO SMALLINT);

#2 INSERT INTO EMPLOYEE
#2 (NAME,SALARY,DEPTNO)
#2 VALUES ('Rupert',989.99,5 );

#3 VALUES IDENTITY_VAL_LOCAL()INTO :IVAR;

Now my question is:  
If my batch process is running and has inserted a row (step 2),
then uses the VALUES statement (step 3) to get the value of the
inserted row, however during this time, an online user has
inserted a new row (between step 2 and 3 above), what value will
I get? the value from my insert, or from the online user insert?  
Is this the best way to determine the identity of my inserted row?

Thanks, Dan T

 
 
 

Determining newly inserted identity value in DB2 v6

Post by Serge Riela » Sat, 22 Dec 2001 00:01:00


Hi,

That's OK. Think of identity_val_local() as a special register. It is
related to your context only. There is no concurrency problem.

Cheers
Serge
--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada

 
 
 

1. Identity cannot be determined for newly inserted rows

Hi,

I'm trying to run some old code that was originally written in VJ++.  My
code is attempting to update a recordset and I'm receiveing an exception
whose message states, "Identity cannot be determined for newly inserted
rows".

This code used to work on another system, of course my present systen
has .net installed and lots of other new bits and pieces.
Any idea what might be causing this exception?  I've searched the MS
Knowledgebase and MSDN Libraries.

Thanks
Phil

2. Q:Paradox export to Quattro Pro

3. Error - The provider is unable to determine identity for newly inserted rows (0x80040E1B)

4. ABF 6.4 Programming Style /Standards

5. Identity cannot be determined for newly inserted rows

6. speed of cursor operations

7. Error - The provider is unable to determine identity for newly inserted rows (0x80040E1B)

8. Heterogeneous datab

9. The provider is unable to determine identity for newly inserted rows

10. Get the value of a newly inserted identity-int in a stored procedure

11. Get the newly inserted identity-value in a stored procedure

12. How to get Identity column values for newly inserted row

13. Getting IDENTITY value of newly inserted row