PI16442: UNPREDICTABLE RESULTS CAN OCCUR IF THE WHEN CLAUSE OF A BEFORE OR AFTER TRIGGER REFERENCES THE TRIGGERING TABLE COLUMN NAME

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Unpredictable results can occur if the WHEN clause of a BEFORE
  or AFTER trigger references the triggering table column name
  and one of the following is true:
  a. The referenced name is qualified by the OLD or NEW
     correlation name and the OLD or NEW correlation name
     is the same as the triggering table name.
  
  b. The referenced name is not qualified by the OLD or NEW
     correlation name and only the OLD or NEW correlation
     name is specified, but not both.
  
  c. The referenced name is qualified by the triggering table
     name where the name is different from the OLD or NEW
     correlation name.
  
  d. The referenced name is not qualified by the OLD or NEW
     correlation name and both OLD and NEW correlation names
     are specified.
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 and 11 for z/OS users who use     *
  *                 the WHEN clause in a BEFORE or AFTER         *
  *                 trigger where the WHEN clause references     *
  *                 the name of a column of the triggering       *
  *                 table                                        *
  ****************************************************************
  * PROBLEM DESCRIPTION: The result of the trigger can be        *
  *                      unpredictable if the WHEN clause in     *
  *                      a BEFORE or AFTER trigger references    *
  *                      the name of a column of the triggering  *
  *                      table and one of the following          *
  *                      conditions is true:                     *
  *                                                              *
  *                        1. The referenced name is qualified   *
  *                           by the OLD or NEW correlation      *
  *                           name and the OLD or NEW            *
  *                           correlation name is the same as    *
  *                           the triggering table name.         *
  *                                                              *
  *                        2. The referenced name is qualified   *
  *                           by the triggering table name       *
  *                           where the name is different from   *
  *                           the OLD or NEW correlation name.   *
  *                                                              *
  *                        3. The referenced name is not         *
  *                           qualified by the OLD or NEW        *
  *                           correlation name and the OLD or    *
  *                           NEW correlation name is defined.   *
  *                                                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The result of the trigger can be unpredictable if the WHEN
  clause in a BEFORE or AFTER trigger references the name of
  a column of the triggering table and one of the following
  conditions is true:
  
    1. The referenced name is qualified by the OLD or NEW
       correlation name and the OLD or NEW correlation name is
       the same as the triggering table name.
  
    2. The referenced name is qualified by the triggering table
       name where the name is different from the OLD or NEW
       correlation name.
  
    3. The referenced name is not qualified by the OLD or NEW
       correlation name and the OLD or NEW correlation name is
       defined.
  
    Examples: (corresponding to the three cases just mentioned)
  
      CREATE TABLE T1(C1 INT, C2 INT);
  
        1. CREATE TRIGGER TRIG1
             NO CASCADE BEFORE UPDATE OF C1 ON T1
             REFERENCING OLD AS OLD_ROW
                         NEW AS T1
             FOR EACH ROW
             MODE DB2SQL
             WHEN(OLD_ROW.C1 = T1.C2)
             ...
  
           CREATE TRIGGER TRIG2
             NO CASCADE BEFORE UPDATE OF C1 ON T1
             REFERENCING OLD AS OLD_ROW
                         NEW AS T1
             FOR EACH ROW
             MODE DB2SQL
             WHEN(OLD_ROW.C1=(SELECT T1.C2 FROM SYSIBM.SYSDUMMY1))
             ...
  
           Currently T1.C2 should have resolved to a new
           transition variable but it's left unresolved.
           The result of each trigger is unpredictable.
  
        2. CREATE TRIGGER TRIG3
             NO CASCADE BEFORE UPDATE OF C1 ON T1
             REFERENCING OLD AS OLD_ROW
                         NEW AS NEW_ROW
             FOR EACH ROW
             MODE DB2SQL
             WHEN(NEW_ROW.C1=(SELECT T1.C2 FROM SYSIBM.SYSDUMMY1))
             ...
  
           Currently T1.C2 should have received SQLCODE -206, or
           T1.C2 could have resolved to a global variable if
           APPLCOMPAT in effect is 'V11R1', but it is left
           unresolved. The result of the trigger is unpredictable.
  
        3. CREATE TRIGGER TRIG4
             NO CASCADE BEFORE UPDATE OF C1 ON T1
             REFERENCING OLD AS OLD_ROW
                         NEW AS NEW_ROW
             FOR EACH ROW
             MODE DB2SQL
             WHEN(NEW_ROW.C1 = (SELECT C2 FROM SYSIBM.SYSDUMMY1))
             ...
  
           Currently C2 should have received SQLCODE -203, or
           C2 could have resolved to a global variable if
           APPLCOMPAT in effect is 'V11R1', but it is left
           unresolved. The result of the trigger is unpredictable.
  

Problem conclusion

• DB2 has been modified to ensure the above examples are
  processed correctly.
  
  Additional keywords: SQLTRIGGER SQLWHEN SQLBEFORE SQLAFTER
                       SQLUNPREDICTABLE SQLTRANSITION
                       SQLVARIABLE SQLCODE206 SQLGLOBALVAR
                       SQLGLOBAL SQLQUALIFIER
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

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

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

  UI18956 UI18957

Modules/Macros

• DSNXORSO
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI18957

     UP14/07/03 P F407

• RB10 PSY UI18956

     UP14/07/04 P F407

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.