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

How to modify CA Db2 for z/OS suite indexes on System Catalog to be STORTYPE = I for Implicit?

book

Article ID: 192283

calendar_today

Updated On:

Products

RC Compare for DB2 for z/OS Database Management for DB2 for z/OS - Administration Suite RC/Extract for DB2 for z/OS RC/Migrator for DB2 for z/OS RC/Merger for DB2 for z/OS RC/Query for DB2 for z/OS RC/Secure for DB2 for z/OS RC/Update for DB2 for z/OS

Issue/Introduction

Have some CA 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 00000001) and replaced (USING STOGROUP
DTSSMS, PRIQTY 2880, SECQTY 720) but it was created again with STORTYPE = 'E'
and USING VCAT 00000001.

Have the following list of CA Db2 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_| DTS        | CAIXP1               | SYSPLAN                        | 
 2_| DTS        | CAIXTP1              | SYSTABLEPART                   | 
 3_| DTS        | CATACX03             | SYSCOLAUTH                     | 
 4_| DTS        | CATATX02             | SYSTABAUTH                     | 
 5_| DTS        | CATDCX01             | SYSCOLUMNS                     | 
 6_| DTS        | CATDPX03             | SYSTABLEPART                   | 
 7_| DTS        | CATDRH03             | SYSFOREIGNKEYS                 | 
 8_| DTS        | CATDSX04             | SYSTABLESPACE                  |   
   +--------------------------------------------------------------------+   
                                                               

Environment

Release : 20.0

Component : General Services for DB2

Resolution

Site has created some optional Db2 catalog indexes to improve SQL performance within
the CA Db2 tools for z/OS. In the example you provided, creating index DTS.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 CA Db2 tools for z/OS.