IDENTITY_VAL_LOCAL()

[oana12]

Hi,
I have in a procedure
create procedure x
....
DECLARE temp_cursor CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
SELECT v_UNIQUEID
FROM SYSIBM.SYSDUMMY1;

insert into y
select from .......;

values IDENTITY_VAL_LOCAL() INTO v_UNIQUEID;
(IDENTITY_VAL_LOCAL() is the identity from y table)
...
open temp_cursor;
end;

the value for IDENTITY_VAL_LOCAL() is NULL.
Can you explain me why ?

In SQL Server you can use the print command to see the value for an parameter. What can be used in DB2?

Thanks

 

[MarcLodge]

Sorry Oana, but I've read this 5 times, and am still struggling to understand it. Could you possibly list the code in a code box (see the TGML bits for info)to make it slight easier to read exactly what you are doing.

Regards,
Marc

 

[sln007]

OANA12,

If you are INSERT-ing a row (or rows) into a table with
an IDENTITY Column, simply leave that column out of the
list of columns being inserted, and out of the 'values'
list. DB2 will automatically assign a unique number for
that column, for each insert performed.

This is the simplest method. I hope it satisfies your
coding issue.



Steve N.
State of Ohio, MIS

 

[juliane26]

In V8 there is the SELECT from INSERT command on AIX.
that can be used to return (and process) values from a generated identity column. That way you dont have to use sequences.

Generated columns are generally the better option than selecting from sysibm.sysdummy, but it has to be specified at the time of creating the table. (e.g. col1 integer not null primary key always generated as identity)

otherwise you can still use a sequence, that provides you a list of unique values.

Still I have to agree with Marc, your code is rather difficult to read ....





Juliane