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

A fix is available

Subscribe

You can track all active APARs for this component.

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 number

    PI16442

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2014-04-21

  • Closed date

    2014-06-18

  • Last modified date

    2014-08-04

  • 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.



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for z/OS

Software version:

B10

Reference #:

PI16442

Modified date:

2014-08-04

Translate my page

Machine Translation

Content navigation