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.
Article ID: 54547
Updated On: 05-30-2024
Products
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:
Additional Information
Feedback
