PH21170: SQLCODE723 SQLCODE910 FROM DSNXERT ON A SET TRANSITION VARIABLE IN A BEFORE TRIGGER

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE723 SQLCODE910 from DSNXERT at location -244
  on a SET transition variable in a before trigger.
  The trigger package was bound with release deallocate and
  the triggering statement's package was bound with
  release commit .
  Additional keywords: SQLTRIGGER
  

Local fix

• Rebind the trigger package with release commit to match
  the triggering statement's package .
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 12 for z/OS users of                                 *
  * BEFORE TRIGGERs.                                             *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Db2 issues an SQLCODE -723(SQLCODE723)                       *
  * for a triggering SQL statement that                          *
  * returns an SQLCODE -910(SQLCODE910)                          *
  * when attempting to set a transition                          *
  * variable in a BEFORE TRIGGER with                            *
  * different package bind options for the                       *
  * trigger package and triggering                               *
  * statement package.                                           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  The problem can occur in more than one way.  The following
  example helps to demonstrate the reported problem using
  application DSNTEP3 with the following steps.
  1. Declare bind options for the DSNTEP3 package and plan with
     RELEASE(COMMIT).
     BIND PACKAGE(DSNTEP3) ACTION(REP) -
     RELEASE(COMMIT) -
     MEMBER(DSNTEP3) ENCODING(EBCDIC) -
     BIND PLAN(DSNTEP3) ACTION(REP) RELEASE(COMMIT)
     BIND PLAN(DSNTEP3) ACTION(REP) RELEASE(COMMIT) -
     PKLIST(*.DSNTEP3.DSNTEP3) ENCODING(EBCDIC) EXPLAIN(YES)
  2. Create a Table and a Trigger on the Table.
     CREATE TABLE MYTB (C1 INTEGER,
                        C2 TIMESTAMP);
    CREATE TRIGGER MYTRIGGER
     NO CASCADE BEFORE UPDATE ON MYTB
     REFERENCING NEW AS N
     FOR EACH ROW
     MODE DB2SQL
     WHEN (1=1)
       BEGIN ATOMIC
       SET N.C2 = CURRENT TIMESTAMP;
    END?
  3. Rebind the Trigger package with bind option
     RELEASE(DEALLOCATE).
     REBIND TRIGGER PACKAGE(ADMF001.MYTRIGGER) RELEASE(DEALLOCATE
  )
  4. Perform this order: UPDATE Table MYTB, alter Table MYTB,
     commit, UPDATE Table MYTB.
    UPDATE MYTB     <== causes trigger to fire
    SET C1 = 0;     <== set transition variable
    ALTER TABLE MYTB
    ADD COLUMN C3 INT;
    COMMIT;
    UPDATE MYTB     <== causes trigger to fire
    SET C1 = 1;     <== set transition variable
  Please note the different RELEASE bind options in the
  SQL statement and Trigger package.
  The second SET transition variable doesn't complete cleanly
  leaving an internal pointer present which leads to the
  aforementioned failure, an SQLCODE910 followed by an
  SQLCODE723.
  Note, the fix for Db2 11 for z/OS was shipped via APAR PH11855.
  

Problem conclusion

• The code in Db2 is modified to clear the residual pointer
  before exit to prevent the unexpected error completion of the
  UPDATE trigger when it is triggered the second time.
  Note, the fix for Db2 11 for z/OS was shipped via APAR PH11855.
  Additional Keywords: SQLCODE723 SQLCODE910 SQLTRIGGER
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UI68733

Modules/Macros

• DSNXRUTT
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI68733

     UP20/04/08 P F004 ¢

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.