The schema is SYSIBM.
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. 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.
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.
Examples:
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
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.
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.