How to modify Database Management Solutions for Db2 for z/OS suite indexes on System Catalog to be STORTYPE = I for Implicit
search cancel

How to modify Database Management Solutions for Db2 for z/OS suite indexes on System Catalog to be STORTYPE = I for Implicit

book

Article ID: 192283

calendar_today

Updated On: 10-27-2023

Products

Database Management for DB2 for z/OS - Administration Suite Database Management for DB2 for z/OS - Performance Suite Database Management for DB2 for z/OS - Recovery Suite Database Management for DB2 for z/OS - SQL Performance Suite Database Management for DB2 for z/OS - Utilities Suite DATABASE MANAGEMENT SOLUTIONS FOR DB2 FOR Z/OS

Issue/Introduction

Have some Database Management for Db2 for z/OS tool indexes defined on User Managed Spaces (STORTYPE = 'E' ).

Attempted to move one of them into Db2 managed space by dropping and recreating it
(Index=CAIXP1 on table=SYSPLAN).

During recreation removed (USING VCAT xxxxxxxx) and replaced (USING STOGROUP
xxxxxx, PRIQTY 2880, SECQTY 720) but it was created again with STORTYPE = 'E'
and USING VCAT xxxxxxxx.

Have the following list of Database Management tool indexes on user defined spaces (STORTYPE = 'E').
Please advise how to move these indexes to Db2 for z/OS managed storage. 

   +--------------------------------------------------------------------+ 
   |  CREATOR   |         NAME         |             TBNAME             | 
   +--------------------------------------------------------------------+ 
 1_| xxx        | CAIXP1               | SYSPLAN                        | 
 2_| xxx        | CAIXTP1              | SYSTABLEPART                   | 
 3_| xxx        | CATACX03             | SYSCOLAUTH                     | 
 4_| xxx        | CATATX02             | SYSTABAUTH                     | 
 5_| xxx        | CATDCX01             | SYSCOLUMNS                     | 
 6_| xxx        | CATDPX03             | SYSTABLEPART                   | 
 7_| xxx        | CATDRH03             | SYSFOREIGNKEYS                 | 
 8_| xxx        | CATDSX04             | SYSTABLESPACE                  |   
   +--------------------------------------------------------------------+                                                  

Resolution

Site created some optional Db2 catalog indexes to improve SQL performance within
the Database Management tools for z/OS. In the example you provided, creating index xxx.CAIXP2
against SYSIBM.SYSPLAN.  As these indexes are defined against the actual Db2 catalog you do not
have control over the STORTYPE.

Located the following Db2 for z/OS documentation link that should assist:
https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/sqlref/src/tpc/db2z_sql_createindex.html

In the above link it mentions:
User-defined indexes on catalog tables:
If you issue CREATE INDEX for an index on a catalog table, and you specify the USING clause,
Db2 ignores that clause. Instead, Db2 defines and manages the index data sets. The data sets
are defined in the same SMS environment that is used for the catalog data sets with default
space attributes.

This appears to be working as designed within Db2 for z/OS and not a function
of the Database Management tools for z/OS.