SQL IDENTITY_VAL_LOCAL scalar function in DB2 for i SQL

SQL IDENTITY_VAL_LOCAL scalar function in DB2 for i SQL

IDENTITY_VAL_LOCAL

The IDENTITY_VAL_LOCAL function returns the most recently assigned value for an identity column. It is not deterministic and this has no input parameters. The result is decimal(31,0)

Example #1

-- Create table

CREATE TABLE TABLE1
  (ROLLNO         INTEGER GENERATED ALWAYS AS IDENTITY,
   NAME          CHAR(30)); 

-- check identity value
select IDENTITY_VAL_LOCAL() from sysibm.sysdummy1

Output

                       IDENTITY_VAL_LOCAL 
                                         -

Currently its null as no value assigned.

--Insert value
INSERT INTO TABLE1
  (NAME)
  VALUES ('AMAR');

-- check identity value
select IDENTITY_VAL_LOCAL() from sysibm.sysdummy1

Output

                       IDENTITY_VAL_LOCAL 
                                         1

Example #2

-- Create table

CREATE TABLE TABLE2
  (ROLLNO         INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 25),
   NAME          CHAR(30)); 

-- check identity value
select IDENTITY_VAL_LOCAL() from sysibm.sysdummy1

Output

                       IDENTITY_VAL_LOCAL 
                                         -

Currently its null as no value assigned.

--Insert value
INSERT INTO TABLE2
  (NAME)
  VALUES ('AMAR');

-- check identity value
select IDENTITY_VAL_LOCAL() from sysibm.sysdummy1

Output

                       IDENTITY_VAL_LOCAL
                                       25

Scope of IDENTITY_VAL_LOCAL

The IDENTITY_VAL_LOCAL value exist only until the next insert operation done in the current session into a table that has identity column present or until the current session ends.