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

A fix is available

Subscribe

You can track all active APARs for this component.

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 number

    PM75999

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-10-26

  • Closed date

    2013-01-23

  • Last modified date

    2013-03-04

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



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM75999

Modified date:

2013-03-04

Translate my page

Machine Translation

Content navigation