DB2 10.5 for Linux, UNIX, and Windows

IDENTITY_VAL_LOCAL scalar function

The IDENTITY_VAL_LOCAL function is a non-deterministic function that returns the most recently assigned value for an identity column, where the assignment occurred as a result of a single INSERT statement using a VALUES clause.


>>-IDENTITY_VAL_LOCAL--(--)------------------------------------><

The schema is SYSIBM.

The function has no input parameters.

The result is a DECIMAL(31,0), regardless of the actual data type of the corresponding identity column.

The value returned by the function is the value assigned to the identity column of the table identified in the most recent single row insert operation. The INSERT statement must contain a VALUES clause on a table containing an identity column. The INSERT statement must also be issued at the same level; that is, the value must be available locally at the level it was assigned, until it is replaced by the next assigned value. (A new level is initiated each time a trigger or routine is invoked.)

The assigned value is either a value supplied by the user (if the identity column is defined as GENERATED BY DEFAULT), or an identity value generated by the database manager.

It is recommended that a SELECT FROM data-change-table-reference statement be used to obtain the assigned value for an identity column. See "table-reference" in "subselect" for more information.

The function returns a null value if a single row INSERT statement with a VALUES clause has not been issued at the current processing level against a table containing an identity column.

The result of the function is not affected by the following operations:

A single row INSERT statement with a VALUES clause for a table without an identity column
A multiple row INSERT statement with a VALUES clause
An INSERT statement with a fullselect
A ROLLBACK TO SAVEPOINT statement

Notes

Expressions in the VALUES clause of an INSERT statement are evaluated before the assignments for the target columns of the insert operation. Thus, an invocation of an IDENTITY_VAL_LOCAL function inside the VALUES clause of an INSERT statement will use the most recently assigned value for an identity column from a previous insert operation. The function returns the null value if no previous single row INSERT statement with a VALUES clause for a table containing an identity column has been executed within the same level as the IDENTITY_VAL_LOCAL function.
The identity column value of the table for which the trigger is defined can be determined within a trigger by referencing the trigger transition variable for the identity column.
The result of invoking the IDENTITY_VAL_LOCAL function from within the trigger condition of an insert trigger is a null value.
It is possible that multiple before or after insert triggers exist for a table. In this case, each trigger is processed separately, and identity values assigned by one triggered action are not available to other triggered actions using the IDENTITY_VAL_LOCAL function. This is true even though the multiple triggered actions are conceptually defined at the same level.
It is not generally recommended to use the IDENTITY_VAL_LOCAL function in the body of a before insert trigger. The result of invoking the IDENTITY_VAL_LOCAL function from within the triggered action of a before insert trigger is the null value. The value for the identity column of the table for which the trigger is defined cannot be obtained by invoking the IDENTITY_VAL_LOCAL function within the triggered action of a before insert trigger. However, the value for the identity column can be obtained in the triggered action by referencing the trigger transition variable for the identity column.
The result of invoking the IDENTITY_VAL_LOCAL function from within the triggered action of an after insert trigger is the value assigned to an identity column of the table identified in the most recent single row insert operation invoked in the same triggered action that had a VALUES clause for a table containing an identity column. (This applies to both FOR EACH ROW and FOR EACH STATEMENT after insert triggers.) If a single row INSERT statement with a VALUES clause for a table containing an identity column was not executed within the same triggered action, before the invocation of the IDENTITY_VAL_LOCAL function, the function returns a null value.
Because IDENTITY_VAL_LOCAL is a non-deterministic function, the result of invoking this function within the SELECT statement of a cursor can vary for each FETCH statement.
The assigned value is the value actually assigned to the identity column (that is, the value that would be returned on a subsequent SELECT statement). This value is not necessarily the value provided in the VALUES clause of the INSERT statement, or a value generated by the database manager. The assigned value could be a value specified in a SET transition variable statement, within the body of a before insert trigger, for a trigger transition variable associated with the identity column.
Scope of IDENTITY_VAL_LOCAL: The IDENTITY_VAL_LOCAL value persists until the next insert in the current session into a table that has an identity column defined on it, or the application session ends. The value is unaffected by COMMIT or ROLLBACK statements. The IDENTITY_VAL_LOCAL value cannot be directly set and is a result of inserting a row into a table.
A technique commonly used, especially for performance, is for an application or product to manage a set of connections and route transactions to an arbitrary connection. In these situations, the availability of the IDENTITY_VAL_LOCAL value should be relied on only until the end of the transaction. Examples of where this type of situation can occur include applications that use XA protocols, use connection pooling, use the connection concentrator, and use HADR to achieve failover.
The value returned by the function following a failed single row INSERT statement with a VALUES clause into a table with an identity column is unpredictable. It could be the value that would have been returned from the function had it been invoked before the failed insert operation, or it could be the value that would have been assigned had the insert operation succeeded. The actual value returned depends on the point of failure, and is therefore unpredictable.

Examples

Example 1: Create two tables, T1 and T2, each with an identity column named C1. Start the identity sequence for table T2 at 10. Insert some values for C2 into T1.

   CREATE TABLE T1
     (C1 INTEGER GENERATED ALWAYS AS IDENTITY,
      C2 INTEGER)

   CREATE TABLE T2
     (C1 DECIMAL(15,0) GENERATED BY DEFAULT AS IDENTITY (START WITH 10),
      C2 INTEGER)

   INSERT INTO T1 (C2) VALUES (5)

   INSERT INTO T1 (C2) VALUES (6)

   SELECT * FROM T1

This query returns:

C1          C2
----------- -----------
          1           5
          2           6

Insert a single row into table T2, where column C2 gets its value from the IDENTITY_VAL_LOCAL function.

   INSERT INTO T2 (C2) VALUES (IDENTITY_VAL_LOCAL())

   SELECT * FROM T2

This query returns:

C1                C2
----------------- -----------
              10.           2

Example 2: In a nested environment involving a trigger, use the IDENTITY_VAL_LOCAL function to retrieve the identity value assigned at a particular level, even though there might have been identity values assigned at lower levels. Assume that there are three tables, EMPLOYEE, EMP_ACT, and ACCT_LOG. There is an after insert trigger defined on EMPLOYEE that results in additional inserts into the EMP_ACT and ACCT_LOG tables.
```
   CREATE TABLE EMPLOYEE
     (EMPNO SMALLINT GENERATED ALWAYS AS IDENTITY (START WITH 1000),
      NAME CHAR(30),
      SALARY DECIMAL(5,2),
      DEPTNO SMALLINT)

   CREATE TABLE EMP_ACT
     (ACNT_NUM SMALLINT GENERATED ALWAYS AS IDENTITY (START WITH 1),
      EMPNO SMALLINT)

   CREATE TABLE ACCT_LOG
     (ID SMALLINT GENERATED ALWAYS AS IDENTITY (START WITH 100),
      ACNT_NUM SMALLINT,
      EMPNO SMALLINT)

   CREATE TRIGGER NEW_HIRE
     AFTER INSERT ON EMPLOYEE
     REFERENCING NEW AS NEW_EMP
     FOR EACH ROW
     BEGIN ATOMIC
       INSERT INTO EMP_ACT (EMPNO) VALUES (NEW_EMP.EMPNO);
       INSERT INTO ACCT_LOG (ACNT_NUM, EMPNO)
         VALUES (IDENTITY_VAL_LOCAL(), NEW_EMP.EMPNO);
     END
```
The first triggered insert operation inserts a row into the EMP_ACT table. The statement uses a trigger transition variable for the EMPNO column of the EMPLOYEE table to indicate that the identity value for the EMPNO column of the EMPLOYEE table is to be copied to the EMPNO column of the EMP_ACT table. The IDENTITY_VAL_LOCAL function could not be used to obtain the value assigned to the EMPNO column of the EMPLOYEE table, because an INSERT statement has not been issued at this level of the nesting. If the IDENTITY_VAL_LOCAL function were invoked in the VALUES clause of the INSERT statement for the EMP_ACT table, it would return a null value. The insert operation against the EMP_ACT table also results in the generation of a new identity value for the ACNT_NUM column.
The second triggered insert operation inserts a row into the ACCT_LOG table. The statement invokes the IDENTITY_VAL_LOCAL function to indicate that the identity value assigned to the ACNT_NUM column of the EMP_ACT table in the previous insert operation in the triggered action is to be copied to the ACNT_NUM column of the ACCT_LOG table. The EMPNO column is assigned the same value as the EMPNO column of the EMPLOYEE table.
After the following INSERT statement and all of the triggered actions have been processed:
```
   INSERT INTO EMPLOYEE (NAME, SALARY, DEPTNO)
     VALUES ('Rupert', 989.99, 50)
```
the contents of the three tables are as follows:
```
   SELECT EMPNO, SUBSTR(NAME,1,10) AS NAME, SALARY, DEPTNO
     FROM EMPLOYEE

EMPNO  NAME       SALARY  DEPTNO
------ ---------- ------- ------
  1000 Rupert      989.99     50

   SELECT ACNT_NUM, EMPNO
     FROM EMP_ACT

ACNT_NUM EMPNO
-------- ------
       1   1000

   SELECT * FROM ACCT_LOG

ID     ACNT_NUM EMPNO
------ -------- ------
   100        1   1000
```
The result of the IDENTITY_VAL_LOCAL function is the most recently assigned value for an identity column at the same nesting level. After processing the original INSERT statement and all of the triggered actions, the IDENTITY_VAL_LOCAL function returns a value of 1000, because this is the value that was assigned to the EMPNO column of the EMPLOYEE table.