PM75999: THE -START PROFILE COMMAND RESULTS IN NO SYSTEM PROFILE MONITORING EVEN THOUGH ROWS EXIST IN SYSIBM.DSN_PROFILE_TABLE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The -START PROFILE cmd results in no System Profile
  Monitoring even though rows exist in SYSIBM.DSN_PROFILE_TABLE
  ZPARM PADIX=YES - This can be recreated by :
  
  1) Delete & insert rows to the following PROFILE tables & check
     the rows inserted by SELECT *
  SYSIBM.DSN_PROFILE_TABLE
  SYSIBM.DSN_PROFILE_ATTRIBUTES
  
  2) Issue STA PROFILE
     -> command complete messages (DSNT741I and DSN9022I) are
        issued
  
  3) SELECT * FROM SYSIBM.DSN_PROFILE_HISTORY
     -> 0 ROW(S) displayed.
  

Local fix

• Set ZPARM PADIX=NO or CREATE INDEX as NOT PADDED
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 and DB2 9 for z/OS users of       *
  *                 PADDED indexes that include one or more      *
  *                 nullable VARCHAR columns                     *
  ****************************************************************
  * PROBLEM DESCRIPTION: In the reported case in DB2 10 (which   *
  *                      can happen in either DB2 10 CM or DB2   *
  *                      NFM but not in DB2 9), -START PROFILE   *
  *                      command fails to insert into table      *
  *                      SYSIBM.DSN_PROFILE_HISTORY if index     *
  *                      SYSIBM.DSN_PROFILE_TABLE_IX2_ALL on     *
  *                      table SYSIBM.DSN_PROFILE_TABLE is       *
  *                      padded, and the row value of indexed    *
  *                      column GROUP_MEMBER VARCHAR(24) for     *
  *                      SYSIBM.DSN_PROFILE_TABLE happens to be  *
  *                      null                                    *
  *                                                              *
  *                      In DB2 10 or DB2 9, incorrout is        *
  *                      possible on any index scan on a padded  *
  *                      index where the predicate on a VARCHAR  *
  *                      column specifies NULL                   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An index scan driven internally by the -START PROFILE command
  against padded index SYSIBM.DSN_PROFILE_TABLE_IX2_ALL (index on
  SYSIBM.DSN_PROFILE_TABLE) failed to find a row that was present,
  which in turn resulted in the command's lack of insert into
  table SYSIBM.DSN_PROFILE_HISTORY. The affected skipped row had a
  null value for the indexed column GROUP_MEMBER and the scan was
  checking for NULL in the predicate.
  
  The reason for the unsuccessful index scan was an incorrect
  comparison code in the index selection expression code in the
  data manager subcomponent, which used a wrong comparison length
  for null-value VARCHARs.
  
  With regard to -START PROFILE, this problem can only happen in
  DB2 V10 CM and above and not in DB2 9. The reason for this is
  that the definition of index SYSIBM.DSN_PROFILE_TABLE_IX2_ALL
  is different in DB2 9 such that column GROUP_MEMBER is not
  part of the index.
  
  Although this problem was exposed by DB2 10 -START PROFILE, the
  problem can theoretically exist in any DB2 10 or DB2 9 selection
  expression-based fetch on a padded index when the scan checks
  for NULL against a null-value VARCHAR column (e.g. SELECT...
  WHERE C1 IS NULL and value of C1 in the row (index) is null).
  
  The reported DB2 10 -START PROFILE problem can be recreated as
  follows, assuming the index SYSIBM.DSN_PROFILE_TABLE_IX2_ALL
  is padded:
  
  ----------------------------------------------------------------
  INSERT INTO SYSIBM.DSN_PROFILE_TABLE
   (
    ROLE,
    AUTHID,
    LOCATION,
    PRDID,
    COLLID,
    PKGNAME,
    PROFILEID,
    PROFILE_ENABLED
   )
   VALUES
   (
    NULL,
    NULL,
    '*',
    NULL,
    NULL,
    NULL,
    1,
    'Y'
   )
  
  
  INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES
   (
    PROFILEID,
    KEYWORDS,
    ATTRIBUTE1,
    ATTRIBUTE2,
    ATTRIBUTE3
   )
   VALUES
   (
    1,
    'MONITOR CONNECTIONS',
    'EXCEPTION',
    0,
    NULL
   )
  
  
  -START PROFILE
  
  
  Given that -START PROFILE did not insert into table
  SYSIBM.DSN_PROFILE_HISTORY when it should have, a
  SELECT * FROM SYSIBM.DSN_PROFILE_HISTORY will not show
  any rows representing the SYSIBM.DSN_PROFILE_TABLE row
  we inserted above.
  ----------------------------------------------------------------
  

Problem conclusion

• Code has been changed to correctly evaluate null-value VARCHAR
  columns during selection expression on a padded index.
  
  Additional keywords: DB2INCORR/K SQLINCORR SQLINCORROUT
  

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:

  UK91142 UK91143

Modules/Macros

•    DSNIONX2 DSNIOST2 DSNIRIDL DSNISFX2 DSNIXLDI
  DSNKFLRR DSNKFLST DSNKFPRV DSNKFTCH DSNKFTRR DSNKNXT2 DSNOLDEL
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK91142

     UP13/02/07 P F302

• R910 PSY UK91143

     UP13/02/07 P F302

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.