PM14223: SQLCODE = -545 WHEN USING PREFIX.DSN_VIRTUAL_INDEXES TO CREATE VIRTUAL INDEXES ON AN EMPTY TABLE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE -545 (THE REQUESTED OPERATION IS NOT ALLOWED BECAUSE A
  ROW DOES NOT SATISFY THE CHECK CONSTRAINT) occurs when using
  prefix.DSN_VIRTUAL_INDEXES to create virtual indexes on an
  empty table.
  KEYWORDS:  DB2MIGV9/K  DB2MIGV8/K  DB2INCORR/K
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All users of the DSN_VIRTUAL_INDEXES explain *
  *                 table in DB2 UDB for z/OS Version 8 and DB2  *
  *                 9.1 for z/OS are affected by this change.    *
  ****************************************************************
  * PROBLEM DESCRIPTION: DB2 returns SQLCODE -545 (THE REQUESTED *
  *                      OPERATION IS NOT ALLOWED BECAUSE A ROW  *
  *                      DOES NOT SATISFY THE CHECK CONSTRAINT)  *
  *                      when creating a virtual index on an     *
  *                      empty table.                            *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Virtual indexes allow optimization tools to test the effect of
  creating and dropping indexes on the performance of particular
  queries.  Virtual indexes use an EXPLAIN table called
  DSN_VIRTUAL_INDEXES.  Prior to DB2 V8 new-function mode, this
  table is named DSN_VIRTUAL_INDEX.
  
  The current implementation of virtual indexes does not support
  index evaluation of empty tables because the following columns
  in DSN_VIRTUAL_INDEXES are defined with CHECK constraints that
  do not allow a value of zero:
  
    NLEAF
    NLEVELS
    FIRSTKEYCARDF
    FULLKEYCARDF
  
  An empty table may have a value of zero for some or all of
  these columns. As a result, the create request for the virtual
  index fails with SQLCODE -545.
  
  In addition, the CLUSTERRATIOF column is currently defined as
  NOT NULL WITH DEFAULT instead of NOT NULL WITH DEFAULT -1.
  

Problem conclusion

• This change relaxes the constraints to allow a virtual index to
  be used on an empty table.
  
  It also modifies the CLUSTERRATIOF column as follows:
  - The default setting is now NOT NULL WITH DEFAULT -1
  - The CHECK constraint now specifies
    CHECK("CLUSTERRATIOF" >= -1)
  
  The following DB2 parts are affected:
  - DSNTXTB:  REXX exec for migrating an EBCDIC explain table to
              Unicode
  - DSNTESC : DDL for creating sample explain tables
  - DSNTIJOS: Job for installing the DB2 Optimization Service
              Center
  
  If you maintain private copies of these members, you need to
  update them after applying this PTF.  You also need to update
  any existing DSN_VIRTUAL_INDEXES tables.  See the ++HOLD actions
  of this APAR for guidance.
  
  Additional keywords:
    SQLCODE545
    DSN_VIRTUAL_INDEX
    DSN_VIRTUAL_INDEXES
  

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:

  UK60838 UK60839

Modules/Macros

• DSNTESC  DSNTIJOS DSNTXTB
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R810 PSY UK60838

     UP10/10/14 P F010

• R910 PSY UK60839

     UP10/10/14 P F010

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.