PI86450: REMOVE DDL FOR QUERY OPTIMIZATION DATABASE AND PLAN TABLES IN DSNTIJSG

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• This apar will remove the DDLs in DSNTIJSG for the Query
  Optimization database and plan tables.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All users of DB2 11 for z/OS and                             *
  * DB2 12 for z/OS installation job                             *
  * DSNTIJSG are affected by this                                *
  * change.                                                      *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Job DSNTIJSG creates explain tables                          *
  * in the SYSIBM schema that are no                             *
  * longer used by the query                                     *
  * optimization tools that they were                            *
  * created for.                                                 *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * APPLY PTF                                                    *
  ****************************************************************
  Job DSNTIJSG is used to bind the packages and plans and create
  user-maintained databases for various DB2-supplied facilities.
  
  Job step DSNTIJQ creates the DB2 query optimization database,
  a collection of system profile and explain tables in the
  SYSIBM schema. Currently, these explain tables created in the
  SYSIBM schema are no longer used by the query optimization
  tools they were originally created for.
  

Problem conclusion

• Currently, DDLs to create explain tables are found in jobs
  DSNTIJSG and DSNTESC. To avoid repeating the same DDLs to
  create the explain tables in both jobs, explain tables that
  are created in the SYSIBM schema and which are no longer
  used by the query optimization tools are no longer created
  by job DSNTIJSG.
  
  DDLs for creating the following explain tables, their
  containing table spaces (where applicable), indexes,
  auxiliary tables and containing LOB tablespaces are
  removed from the installation job DSNTIJSG, job step
  DSNTIJQ:
  
      TABLESPACE DSNOPTT8
      LOB TABLESPACE DSNMQLTS
      LOB TABLESPACE DSNOQIL1
      LOB TABLESPACE DSNOQIL2
  
      TABLE SYSIBM.PLAN_TABLE
      INDEX SYSIBM.PLAN_TABLE_IDX1
  
      TABLE SYSIBM.DSN_STATEMNT_TABLE
      INDEX SYSIBM.DSN_STATEMNT_TABLE_IDX1
  
      TABLE SYSIBM.DSN_FUNCTION_TABLE
      INDEX SYSIBM.FUNC_EXPIDX1
  
      TABLE SYSIBM.DSN_PREDICAT_TABLE
      INDEX SYSIBM.DSN_PREDICAT_TABLE_IDX1
      INDEX SYSIBM.DSN_PREDICAT_TABLE_IDX2
  
      TABLE SYSIBM.DSN_STRUCT_TABLE
      INDEX SYSIBM.DSN_STRUCT_TABLE_IDX1
  
      TABLE SYSIBM.DSN_PGROUP_TABLE
      INDEX SYSIBM.DSN_PGROUP_TABLE_IDX1
  
      TABLE SYSIBM.DSN_PTASK_TABLE
      INDEX SYSIBM.DSN_PTASK_TABLE_IDX1
  
      TABLE SYSIBM.DSN_FILTER_TABLE
      INDEX SYSIBM.DSN_FILTER_TABLE_IDX1
  
      TABLE SYSIBM.DSN_DETCOST_TABLE
      INDEX SYSIBM.DSN_DETCOST_TABLE_IDX1
  
      TABLE SYSIBM.DSN_SORT_TABLE
      INDEX SYSIBM.DSN_SORT_TABLE_IDX1
  
      TABLE SYSIBM.DSN_SORTKEY_TABLE
      INDEX SYSIBM.DSN_SORTKEY_TABLE_IDX1
  
      TABLE SYSIBM.DSN_PGRANGE_TABLE
      INDEX SYSIBM.DSN_PGRANGE_TABLE_IDX1
  
      TABLE SYSIBM.DSN_VIEWREF_TABLE
      INDEX SYSIBM.DSN_VIEWREF_TABLE_IDX1
  
      TABLE SYSIBM.DSN_QUERY_TABLE
      INDEX SYSIBM.DSN_QUERY_TABLE_IDX1
      INDEX SYSIBM.DSN_QUERY_TABLE_IDX2
  
      AUX TABLE SYSIBM.DSN_QUERY_AUX
      INDEX SYSIBM.DSN_QUERY_AUXINX
  
      TABLE SYSIBM.DSN_QUERYINFO_TABLE
      AUX TABLE SYSIBM.DSN_QUERYINFO_AUX
      INDEX SYSIBM.DSN_QUERYINFO_AUXINX
      AUX TABLE SYSIBM.DSN_QUERYINFO_AUX2
      INDEX SYSIBM.DSN_QUERYINFO_AUXINX2
  
      TABLE SYSIBM.DSN_COLDIST_TABLE
  
      TABLE SYSIBM.DSN_KEYTGTDIST_TABLE
  
      TABLE SYSIBM.DSN_STAT_FEEDBACK
  
      TABLE SYSIBM.DSN_PREDICATE_SELECTIVITY
      INDEX SYSIBM.DSN_PREDSEL_IX1
  
  DDLs for creating the following system profile tables,
  their containing database and table space, index, and
  storage group remain in the installation job DSNTIJSG:
  
      STOGROUP DSNOPTSG
      DATABASE DSNOPTDB
      TABLESPACE DSNOPTTS
  
      TABLE SYSIBM.DSN_PROFILE_TABLE
      UNIQUE INDEX SYSIBM.DSN_PROFILE_TABLE_IX_ALL
      INDEX SYSIBM.DSN_PROFILE_TABLE_IX2_ALL
  
      TABLE SYSIBM.DSN_PROFILE_HISTORY
  
      TABLE SYSIBM.DSN_PROFILE_ATTRIBUTES
      UNIQUE INDEX SYSIBM.DSN_PROFILE_ATTRIBUTES_IX_ALL
  
      TABLE SYSIBM.DSN_PROFILE_ATTRIBUTES_HISTORY
  

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:

  UI52051 UI52052

Modules/Macros

• DSNTIJSG
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI52051

     UP17/12/06 P F712

• RB10 PSY UI52052

     UP17/12/06 P F712

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.