PH58409: UNNECESSARY ENTRY IN SYSPACKDEP FOR INDEX ON EXPRESSION

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Catalog Table SYSIBM.SYSPACKDEP is showing a dependency of a
  package on a unique index that should not be used during
  execution of the given package.
  In this scenario here, the index is an Index in expression /
  expression-based index.
  However, when the index is dropped, the package will become
  invalid.
  
  Additional Keyword and Symptoms:
  SQLINDEXONEXP
  INCORROUT    SQLINCORR     SQLINCORROUT
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 12 and 13 for z/OS users of a                        *
  * unique expression-based index.                               *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Incorrect dependency on a unique                             *
  * expression-based index could happen                          *
  * for a package or cached dynamic SQL                          *
  * statement, and dropping the unique                           *
  * expression-based index could                                 *
  * invalidate the package or cached                             *
  * dynamic SQL statement.                                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  An example is shown below:
  CREATE TABLE T1 (
    C1 CHAR(1)
  ,C2 CHAR(1)
  );
  CREATE UNIQUE INDEX IX1
    ON T1 ((C1 || C2) ASC)
  ;
  --#SET TERMINATOR #
  CREATE PROCEDURE PROC1(
  IN P1 INTEGER,
   OUT P2 CHAR(1) )
  LANGUAGE SQL
  BEGIN
    UPDATE T1 SET C2 = 'X' WHERE C1 = '1';
  END#
  --#SET TERMINATOR ;
  SELECT * FROM SYSIBM.SYSPACKDEP
  WHERE DNAME = 'PROC1'
        AND BNAME = 'IX1'
  ;
  The above query returned one row, which is not correct. Db2 did
  not correctly process the above SQL statements when there is a
  unique expression-based index on the table referenced.
  

Problem conclusion

• Db2 has been modified so the incorrect dependency on an
  expression-based index will not happen.
  Additional keywords: SQLINDEXONEXP
  

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:

  UI95900 UI95901

Modules/Macros

• DSNXOB2
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI95900

     UP24/03/12 P F403

• RD10 PSY UI95901

     UP24/03/12 P F403

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.