PI16983: BATCH EXPLAIN RUNS POORLY AFTER MIGRATION TO DB2 V10 NFM.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• After migration of DB2 to NFM, batch EXPLAIN started to run
  poorly. The batch process is doing enormous numbers of getpages
  in BP0. In v9 dgo@tpm3 index is used, but not in v10 dgo@tpm6
  

Local fix

• The default DB2 index for SYSPACKSTMT is SYSIBM.DSNKSX01 and
  has the columns LOCATION COLLID NAME CONTOKEN SEQNO,
  .
  As a workaround, Create a second index for the table SYSPACKSTMT
  CREATE INDEX DSNKSX03 ON SYSIBM.SYSPACKSTMT (COLLID, NAME,
  VERSION)
  
  The access path and the performance shoud be identical to v9
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: OMEGAMON XE for DB2 PE/DB2PM                 *
  *                 users of component                           *
  *                 - EXPLAIN                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION: 1. After migration to DB2 V10 NFM,      *
  *                         BATCH EXPLAIN runs poorly.           *
  *                      2. On the OLM EXPLAIN panel DGOMYWTS    *
  *                         (Table Space Info) some values are   *
  *                         displayed incorrectly as "???".      *
  ****************************************************************
  * RECOMMENDATION: Apply this PTF                               *
  ****************************************************************
  PROBLEM SUMMARY:
  1. After migration to DB2 V10 NFM, BATCH EXPLAIN runs poorly.
     The batch process is doing enormous numbers of getpages
     in BP0. In V9 DGO@TPM3 the index DSNKSX01 is used,
     but not in V10 DGO@TPM6.
  2. On the OLM EXPLAIN panel DGOMYWTS (Table Space Info) some
     values are displayed incorrectly as "???".
  
  PROBLEM CONCLUSION:
  1. The column name LOCATION is added to the WHERE clause for
     the table SYSPACKSTMT that the default index DSNKSX01
     (LOCATION COLLID NAME CONTOKEN SEQNO) can be used.
  2. On the OLM EXPLAIN panel DGOMYWTS (Table Space Info) the
     following possible values are displayed:
      - b - Normal
      - I - Normal MEMBER CLUSTER
      - G - Partitioned by growth
      - K - Large MEMBER CLUSTER
      - L - Large
      - O - LOB table space
      - P - Implicit for XML columns
      - R - Range partitioned
  
  KEYWORDS:
  PERFORMANCE / INDEX DSNKSX01
  DB2 V10 NFM / BATCH EXPLAIN / DBRM DGO@TPM6
  DGOMYWTS / SYSTABLESPACE.TYPE / TABLE SPACE INFO
  
  CIRCUMVENTION:
  1. Create a second index for the table SYSIBM.SYSPACKSTMT.
     CREATE INDEX DSNKSX03
     ON SYSIBM.SYSPACKSTMT (COLLID, NAME, VERSION)
  

Problem conclusion

• 1. The column name LOCATION is added to the WHERE clause for
     the table SYSPACKSTMT that the default index DSNKSX01
     (LOCATION COLLID NAME CONTOKEN SEQNO) can be used.
  2. On the OLM EXPLAIN panel DGOMYWTS (Table Space Info) the
     following possible values are displayed:
      - b - Normal
      - I - Normal MEMBER CLUSTER
      - G - Partitioned by growth
      - K - Large MEMBER CLUSTER
      - L - Large
      - O - LOB table space
      - P - Implicit for XML columns
      - R - Range partitioned
  
  KEYWORDS:
  PERFORMANCE / INDEX DSNKSX01
  DB2 V10 NFM / BATCH EXPLAIN / DBRM DGO@TPM6
  DGOMYWTS / SYSTABLESPACE.TYPE / TABLE SPACE INFO
  

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:

  UI18833 UI18834 UI18835

Modules/Macros

• DGO@TPM5 DGO@TPM6 DGOMTPOP DGOMYEDS DGOMYE01 DGOMYIE0 DGOMYOCD
  DGOMYOCI DGOMYOCM DGOMYODI DGOMYOII DGOMYOIM DGOMYOIP DGOMYOIS
  DGOMYOKI DGOMYOPI DGOMYOPT DGOMYOTI DGOMYOTM DGOMYOTP DGOMYOTS
  DGOYMAIN DGOYTPM5 DGOYTPM6 FPEFNENU FPEFNJPN
  

Fix information

• Fixed component name

  OM XE DB2PE/PM

• Fixed component ID

  5655OPE00

Applicable component levels

• R510 PSY UI18833

     UP14/06/19 P F406

• R511 PSY UI18834

     UP14/06/19 P F406

• R520 PSY UI18835

     UP14/06/19 P F406

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.