PM17660: INCORRECT SQLCODE204 DURING DROP TRIGGER. SQLCODE904 ON BINDLOCK WHILE CREATE TRIGGER DSNXICTR 00E30083 00000802

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• During CREATE TRIGGER when deadlock occurs on BIND lock
  BINDLOCK, a row is inserted into SYSTRIGGERS, and not rolled
  back. That causes that there is inconsistency between
  SYSPACKAGE and SYSTRIGGERS and there is no way eg, to drop the
  trigger with DROP trigger. SQLCODE204 is issued indicating that
  trigger does not exist (exist only
  
  Typical scenario is:
  
       CREATE TRIGGER TRIG1
  ...
   DSNT408I SQLCODE = -904, ERROR:  UNSUCCESSFUL EXECUTION CAUSED
  BY AN UNAVAILABLE RESOURCE. REASON 00E30083, TYPE OF.
            RESOURCE 00000802, AND RESOURCE NAME BINDLOCK.
   DSNT418I SQLSTATE   = 57011 SQLSTATE RETURN CODE.
   DSNT415I SQLERRP    = DSNXICTR SQL PROCEDURE DETECTING ERROR.
   DSNT416I SQLERRD    = 500  0  0  -1  0  0 SQL DIAGNOSTIC
  INFORMATION.
   DSNT416I SQLERRD    = X'000001F4'  X'00000000'  X'00000000'
  X'FFFFFFFF'  X'00000000'  X'00000000' SQL DIAGNOSTIC.
            INFORMATION.
  
  
  
      DROP TRIGGER  TRIG1;
  
  
   DSNT408I SQLCODE = -204, ERROR: TRIG1 IS AN UNDEFINED NAME
   DSNT418I SQLSTATE   = 42704 SQLSTATE RETURN  CODE
   DSNT415I SQLERRP    = DSNTBFR2 SQL PROCEDURE DETECTING  ERROR
   DSNT416I SQLERRD    = 238 0  0  -1  0  0 SQL DIAGNOSTIC
   INFORMATION
   DSNT416I SQLERRD    = X'000000EE'  X'00000000'  X'00000000'
   X'FFFFFFFF'
            X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION
  
  
  
  Trigger entry exists in SYSTRIGGERS, with timestamp of failed
  create trigger (when DEADLOCK BINDLOCK occured)
  
  To identify bad entries in SYSTRIGGERS run this query:
  
  SELECT CREATEDTS,NAME FROM SYSIBM.SYSTRIGGERS
  WHERE NAME NOT IN (SELECT NAME FROM SYSIBM.SYSPACKAGE);
  
  
  Keywords:
  SQLTRIGGER SQLCODE904 SQLCODE204 BINDLOCK RC00E30083 RS00000802
  

Local fix

• workaround:
  Manual catalog update is required, to delete bad SYSTRIGGERS row
  Then REPAIR DBD REBUILD dbname.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: DB2 for z/OS users who are creating          *
  *                 triggers.                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION: THE PROBLEM THIS FIX ADDRESSES HAS THE  *
  *                      FOLLOWING SYMPTOMS:                     *
  *                                                              *
  *                       A record is created in the             *
  *                       SYSIBM.SYSTRIGGERS table even though   *
  *                       a -904 SQLCODE was issued.             *
  *                                                              *
  *                      PROBLEM SCENARIO:                       *
  *                                                              *
  *                      The reported problem can happen when    *
  *                      executing a CREATE TRIGGER. If a        *
  *                      BINDLOCK deadlock occurs, the package   *
  *                      is never created but an entry is        *
  *                      created in the SYSIBM.SYSTRIGGERS       *
  *                      table resulting in inconsistency        *
  *                      between tables SYSIBM.SYSPACKAGE and    *
  *                      SYSIBM.SYSTRIGGERS.                     *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  SUMMARY OF DB2 EVENTS:
  
  DB2 did not correctly handle the return code from a deadlock
  during the execution of a CREATE TRIGGER, so program execution
  continued when it should have stopped. The result is an entry
  in the SYSIBM.SYSTRIGGERS table but the package was not
  created.
  

Problem conclusion

• DB2 will now correctly handle the return code as a result of
  a deadlock during a CREATE TRIGGER. A -904 will be issued and
  neither SYSIBM.SYSTRIGGERS nor SYSIBM.SYSPACKAGE will be
  updated.
  
  NOTE: If you have experienced this problem before
  applying this fix, there is a possibility that you have
  records in SYSIBM.SYSTRIGGERS without corresponding
  records in SYSIBM.SYSPACKAGE. These orphaned records could
  cause errors if you attempt to delete or re-create the trigger
  with same name. See HOLD information to eliminate them.
  
  
  ADDITIONAL KEYWORDS:
  SQLTRIGGER BIND SQLCODE904
  

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:

  UK59556 UK59557

Modules/Macros

• DSNTBTRR DSNXICTR
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R810 PSY UK59556

     UP10/08/25 P F008

• R910 PSY UK59557

     UP10/08/25 P F008

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.