Accessing transition tables in a user-defined function or stored procedure

If you want to refer to the entire set of rows that a triggering SQL statement modifies, rather than to individual rows, use a transition table. You can reference a transition table in user-defined functions and procedures that are invoked from a trigger.

About this task

This topic describes how to access transition variables in a user-defined function, but the same techniques apply to a stored procedure.

To access transition tables in a user-defined function, use table locators, which are pointers to the transition tables. You declare table locators as input parameters in the CREATE FUNCTION statement using the TABLE LIKE table-name AS LOCATOR clause.

Procedure

To access transition tables in a user-defined function or stored procedure:

Declare input parameters to receive table locators. You must define each parameter that receives a table locator as an unsigned 4-byte integer.
Declare table locators. You can declare table locators in assembler, C, C++, COBOL, PL/I, and in an SQL procedure compound statement.
Declare a cursor to access the rows in each transition table.
Assign the input parameter values to the table locators.
Access rows from the transition tables using the cursors that are declared for the transition tables.

Results

The following examples show how a user-defined function that is written in C, C++, COBOL, or PL/I accesses a transition table for a trigger. The transition table, NEWEMP, contains modified rows of the employee sample table. The trigger is defined like this:

CREATE TRIGGER EMPRAISE
  AFTER UPDATE ON EMP
  REFERENCING NEW TABLE AS NEWEMPS
  FOR EACH STATEMENT MODE DB2SQL
  BEGIN ATOMIC
    VALUES (CHECKEMP(TABLE NEWEMPS));
  END;

The user-defined function definition looks like this:

CREATE FUNCTION CHECKEMP(TABLE LIKE EMP AS LOCATOR)
  RETURNS INTEGER
  EXTERNAL NAME 'CHECKEMP'
  PARAMETER STYLE SQL
  LANGUAGE language;

Assembler: The following example shows how an assembler program accesses rows of transition table NEWEMPS.

CHECKEMP CSECT
         SAVE  (14,12)             ANY SAVE SEQUENCE
         LR    R12,R15             CODE ADDRESSABILITY
         USING CHECKEMP,R12        TELL THE ASSEMBLER
         LR    R7,R1               SAVE THE PARM POINTER
         USING PARMAREA,R7         SET ADDRESSABILITY FOR PARMS
         USING SQLDSECT,R8         ESTABLISH ADDRESSIBILITY TO SQLDSECT
         L     R6,PROGSIZE         GET SPACE FOR USER PROGRAM
         GETMAIN R,LV=(6)          GET STORAGE FOR PROGRAM VARIABLES
         LR    R10,R1              POINT TO THE ACQUIRED STORAGE
         LR    R2,R10              POINT TO THE FIELD
         LR    R3,R6               GET ITS LENGTH
         SR    R4,R4               CLEAR THE INPUT ADDRESS
         SR    R5,R5               CLEAR THE INPUT LENGTH
         MVCL  R2,R4               CLEAR OUT THE FIELD
         ST    R13,FOUR(R10)       CHAIN THE SAVEAREA PTRS
         ST    R10,EIGHT(R13)      CHAIN SAVEAREA FORWARD
         LR    R13,R10             POINT TO THE SAVEAREA
         USING PROGAREA,R13        SET ADDRESSABILITY
         ST    R6,GETLENTH         SAVE THE LENGTH OF THE GETMAIN
         ⋮
************************************************************
* Declare table locator host variable TRIGTBL              *
************************************************************
TRIGTBL SQL TYPE IS TABLE LIKE EMP AS LOCATOR
************************************************************
* Declare a cursor to retrieve rows from the transition    *
* table                                                    *
************************************************************
         EXEC SQL DECLARE C1 CURSOR FOR                                X
              SELECT LASTNAME FROM TABLE(:TRIGTBL LIKE EMP)            X
              WHERE SALARY > 100000
************************************************************
* Copy table locator for trigger transition table          *
************************************************************
         L     R2,TABLOC           GET ADDRESS OF LOCATOR
         L     R2,0(0,R2)          GET LOCATOR VALUE
         ST    R2,TRIGTBL
         EXEC SQL OPEN C1
         EXEC SQL FETCH C1 INTO :NAME
         ⋮
         EXEC SQL CLOSE C1
⋮PROGAREA DSECT                     WORKING STORAGE FOR THE PROGRAM
SAVEAREA DS    18F                 THIS ROUTINE'S SAVE AREA
GETLENTH DS    A                   GETMAIN LENGTH FOR THIS AREA
⋮
NAME     DS    CL24
⋮
         DS    0D
PROGSIZE EQU   *-PROGAREA          DYNAMIC WORKAREA SIZE
PARMAREA DSECT
TABLOC   DS    A                   INPUT PARAMETER FOR TABLE LOCATOR
⋮
         END   CHECKEMP

C or C++: The following example shows how a C or C++ program accesses rows of transition table NEWEMPS.

int CHECK_EMP(int trig_tbl_id)
{
  ⋮
  /**********************************************************/
  /* Declare table locator host variable trig_tbl_id  */
  /**********************************************************/
  EXEC SQL BEGIN DECLARE SECTION;
    SQL TYPE IS TABLE LIKE EMP AS LOCATOR trig_tbl_id;
    char name[25];
  EXEC SQL END DECLARE SECTION;
  ⋮
  /**********************************************************/
  /* Declare a cursor to retrieve rows from the transition  */
  /* table                                                  */
  /**********************************************************/
  EXEC SQL DECLARE C1 CURSOR FOR
    SELECT NAME FROM TABLE(:trig_tbl_id LIKE EMPLOYEE)
    WHERE SALARY > 100000;
  /**********************************************************/
  /* Fetch a row from transition table                      */
  /**********************************************************/
  EXEC SQL OPEN C1;
  EXEC SQL FETCH C1 INTO :name;
  ⋮
  EXEC SQL CLOSE C1;
  ⋮
}

COBOL: The following example shows how a COBOL program accesses rows of transition table NEWEMPS.

       IDENTIFICATION DIVISION.
       PROGRAM-ID. CHECKEMP.
       ENVIRONMENT DIVISION.
       INPUT-OUTPUT SECTION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01  NAME   PIC X(24).
       ⋮
       LINKAGE SECTION.
      *********************************************************
      * Declare table locator host variable TRIG-TBL-ID       *
      *********************************************************
       01  TRIG-TBL-ID SQL TYPE IS TABLE LIKE EMP AS LOCATOR.
       ⋮
       PROCEDURE DIVISION USING TRIG-TBL-ID.
       ⋮
      *********************************************************
      * Declare cursor to retrieve rows from transition table *
      *********************************************************
       EXEC SQL DECLARE C1 CURSOR FOR
         SELECT NAME FROM TABLE(:TRIG-TBL-ID LIKE EMP)
         WHERE SALARY > 100000 END-EXEC.
      *********************************************************
      * Fetch a row from transition table                     *
      *********************************************************
       EXEC SQL OPEN C1 END-EXEC.
       EXEC SQL FETCH C1 INTO :NAME END-EXEC.
       ⋮
       EXEC SQL CLOSE C1 END-EXEC.
       ⋮
       PROG-END.
       GOBACK.

PL/I: The following example shows how a PL/I program accesses rows of transition table NEWEMPS.

 CHECK_EMP: PROC(TRIG_TBL_ID) RETURNS(BIN FIXED(31))
            OPTIONS(MAIN NOEXECOPS REENTRANT);
 /****************************************************/
 /* Declare table locator host variable TRIG_TBL_ID  */
 /****************************************************/
 DECLARE TRIG_TBL_ID SQL TYPE IS TABLE LIKE EMP AS LOCATOR;
 DECLARE NAME CHAR(24);
 ⋮
 /****************************************************/
 /* Declare a cursor to retrieve rows from the       */
 /* transition table                                 */
 /****************************************************/
 EXEC SQL DECLARE C1 CURSOR FOR
   SELECT NAME FROM TABLE(:TRIG_TBL_ID LIKE EMP)
   WHERE SALARY > 100000;
 /****************************************************/
 /* Retrieve rows from the transition table          */
 /****************************************************/
 EXEC SQL OPEN C1;
 EXEC SQL FETCH C1 INTO :NAME;
 ⋮
 EXEC SQL CLOSE C1;
 ⋮
 END CHECK_EMP;