Database Management for Db2 for Z/OS : Reduce contention on the DB2 catalog and avoid granting SELECT authorization on DB2 catalog tables to all users.
search cancel

Database Management for Db2 for Z/OS : Reduce contention on the DB2 catalog and avoid granting SELECT authorization on DB2 catalog tables to all users.

book

Article ID: 54547

calendar_today

Updated On:

Products

Database Management for DB2 for z/OS - Administration Suite

Issue/Introduction

The Alternate Catalog Mapping (ACM) facility allows for redirection of DB2 catalog queries to shadow tables or views defined over the DB2 system catalog tables. This can greatly improve performance and limit access to the actual DB2 catalog tables.

 

Environment

Release: R20
Component: Database Management for Db2 for Z/OS 

Resolution

A number of the Database Management Solutions for DB2 for z/OS products can easily utilize ACM.

To list, create and maintain ACM select option M - Alt. Catalog Mapping from the CA DB2 Products Main Menu.

Value Pack
_ B  Batch Processor
_ C  DB2 Command Processor
_ I  Interactive SQL
_ M  Alt. Catalog Mapping <--------------------
_ TT Thread Term/Dynam DSNZPARM
_ Y  Utility Manager
_ 6  RI Manager


_ X  Exit CA/DB2 Products

Each ACMID is a definition of all catalog tables and their shadow table or views, like these examples:

----------------- Alternate Catalog Mapping ----------------- yyyy/mm/dd hh:mm
Command ===>                                                  SCROLL ===> CSR

Define ID   ==> ACMAL              Copy from ID ==>
Description ==> ACM TO D12APTIB

FROM: All: Define ID. tablename        TO:  All -->          .
To copy Define ID or standard NAME, type an '=' in the respective area above.
---------------------------------------------------------------------- authid
       SYSIBM   . SYSCOLAUTH                 ACMAL    . SYSCOLAUTH
       SYSIBM   . SYSCOLUMNS                 ACMAL    . SYSCOLUMNS
       SYSIBM   . SYSCOPY                    ACMAL    . SYSCOPY
       SYSIBM   . SYSDATABASE                ACMAL    . SYSDATABASE
       SYSIBM   . SYSDBAUTH                  ACMAL    . SYSDBAUTH
       SYSIBM   . SYSDBRM                    ACMAL    . SYSDBRM
       SYSIBM   . SYSFIELDS                  ACMAL    . SYSFIELDS
       SYSIBM   . SYSFOREIGNKEYS             ACMAL    . SYSFOREIGNKEYS
       SYSIBM   . SYSINDEXES                 ACMAL    . SYSINDEXES
.
.
.
SYSIBM   . SYSTABAUTH                 ACMAL    . SYSTABAUTH
SYSIBM   . SYSTABLEPART               ACMAL    . SYSTABLEPART
SYSIBM   . SYSTABLES                  ACMAL    . SYSTABLES

To use ACM, specify ACM ON and the ACMID on the DB2 Products Main Menu:

20.0.03   Database Management Solutions for DB2 for z/OS    yyyy/mm/dd hh:mm
OPTION ===>                                                   SCROLL ===> CSR
PT367 PT367I: PLEASE CHOOSE AN OPTION FROM THE LIST SHOWN.
DB2 SSID ==> ssid LOCATION ==> LOCAL                   DB2 VERSION: V12R1M500
ACM      ==> ON   ACMID    ==> ACMAL   SQLID ==> authid

 

Command ACM will display the status of ACM:

20.0.03   Database Management Solutions for DB2 for z/OS    yyyy/mm/dd hh:mm
OPTION ===> ACM                                               SCROLL ===> CSR

.

.

.

20.0.03   Database Management Solutions for DB2 for z/OS    yyyy/mm/dd hh:mm
OPTION ===>                                                   SCROLL ===> CSR
PT393 PT393I: ACM: ON     ACMID: ACMNEW
DB2 SSID ==> ssid LOCATION ==> LOCAL                   DB2 VERSION: V12R1M500
ACM      ==> ON   ACMID    ==> ACMNEW   SQLID ==> authid

Many products also support command ACMON / ACMOFF to toggle between ACM modes.

COMMAND     : ACMON

DESCRIPTION : The ACMON command allows you to enable RC/Query to utilize
              CA's Alternate Catalog Mapping (ACM) feature.  This feature
              allows you to access a "shadow" DB2 system catalog to reduce
              contention to your "live" DB2 system catalog, or to use views
              into the DB2 System catalog to help control access to it.

SYNTAX      : ACMON

ABBREVIATION: ACMON
-----------------------------------------------------------------------------
COMMAND     : ACMOFF

DESCRIPTION : The ACMOFF command disables RC/Query use of CA's
              Alternate Catalog Mapping (ACM) feature.  This feature
              allows you to access a "shadow" DB2 system catalog to reduce
              contention to your "live" DB2 system catalog, or to use views
              into the DB2 System catalog to help control access to it.  Not
              using ACM will mean all users will be accessing the "live" DB2
              system catalog when using RCQ functions.

SYNTAX      : ACMOFF

ABBREVIATION: ACMOFF
-------------------------------------------------------------------------------

For details of how to create and maintain ACMIDs please refer to the Database Management for DB2 for Z/OS , Use Alternate Catalog Mapping (ACM).

ACM reduces Db2 catalog contention and eases security concerns by redirecting catalog queries to views defined over the system catalog tables or shadow tables. ACM allows  Db2 catalog information to be viewed without having the SELECT privilege on the actual catalog tables and improves performance

If ACM tables are refreshed every night, the ACM tables will be up-to-date and will help to avoid contention on the DB2 catalog during normal working hours.  If VIEWS's of the catalog tables are used then there is no need to refresh the ACM.

As an example, when using RC/Query for Db2 for z/os,  when creating a report of tables.....

 

RQTL   20.0   --------------- RC/Q Table List --------------- yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

 DB2 Object ===> T                       Option  ===> L    Where => N
Table Name ===> *                     > Creator ===> authid1                >
  Qualifier ===> *                     > N/A     ===> *                      >
Loc: LOCAL ---------- SSID: ssid ----------authid1 -          LINE 1 OF 3    >
CMD      TABLE NAME         CREATOR  DATABASE TBLSPACE COL_CNT  NUMBER OF ROWS
________ ACCT_ACT_14566     authid1  CPS0004A A14566TS      16             N/A
________ ACCT_ACT_C14566   authid1  CPS0004A A14566TS      16             N/A
________ TFT_CURRENCY       authid1  DVFTSLEB SFTCURR0      47             N/A

 

When the SQL command is issued the SQL that is generated can be seen. The query is accessing the shadow table ACMAL.SYSTABLES instead of the main catalog table SYSIBM.SYSTABLES.

SELECT  A.NAME , A.CREATOR , A.DBNAME , A.TSNAME , CASE WHEN A.CARDF >
        9223372036854775807 THEN 9223372036854775807 ELSE BIGINT ( A.CARDF )
        END , A.COLCOUNT , A.TYPE , A.DBID , A.OBID , A.CLUSTERTYPE , A.EDPROC
        , A.VALPROC , A.PCTPAGES , A.PARENTS , A.CHILDREN , A.KEYCOLUMNS ,
        A.RECLENGTH , A.STATUS , A.KEYOBID , A.CHECKFLAG , A.AUDITING ,
        A.CREATEDBY , A.LOCATION , A.TBCREATOR , A.TBNAME , A.CREATEDTS ,
        A.ALTEREDTS , A.DATACAPTURE , A.RBA1 , A.RBA2 , A.PCTROWCOMP ,
        A.STATSTIME , A.CHECKS , A.CHECKRID5B , A.ENCODING_SCHEME ,
        A.TABLESTATUS , A.NPAGESF , A.SPACEF , A.AVGROWLEN , A.RELCREATED ,
        A.NUM_DEP_MQTS , A.VERSION , A.PARTKEYCOLNUM , A.SPLIT_ROWS ,
        A.SECURITY_LABEL , A.OWNER , A.APPEND , A.OWNERTYPE , A.CONTROL ,
        A.VERSIONING_SCHEMA , A.VERSIONING_TABLE , A.HASHKEYCOLUMNS , A.LABEL
        , A.REMARKS , A.CHECKRID , A.CLUSTERRID , A.ARCHIVING_SCHEMA ,
        A.ARCHIVING_TABLE , A.STATS_FEEDBACK , A.REGENERATETS
   FROM ACMAL.SYSTABLES A
  WHERE A.TYPE IN ( 'T' , 'G' , 'X' , 'M' , 'C' , 'P' , 'H' , 'R' , 'D' )
  AND A.CREATOR = 'authid1'
  ORDER BY A.NAME , A.CREATOR WITH UR

 

As well as actual tables and VIEWS of table, Aliases can also be used in an ACM. An alias that might be pointing to a remote location.

The table above, ACMAL.SYSTABLES is actually an Alias.

    CREATE ALIAS ACMAL.SYSTABLES
               FOR D12APTIB."SYSIBM  ".SYSTABLES;

So, in this case the RC/Query package would normally be selecting from SYSIBM.SYSTABLES but with the ACM on it uses ACMAL.SYSTABLES which itself is directing the query at a remote catalog table...D12APTIB."SYSIBM  ".SYSTABLES.

These are the list of tables that are supported in an ACM: 

ACM SUPPORTED TABLES

 

 

Additional Information