How to reduce contention on the DB2 catalog and avoid granting SELECT authorization on all DB2 catalog tables to all users using the CA Database Management Solutions for DB2 for z/OS products.

Document ID : KB000054547
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

The Alternate Catalog Mapping (ACM) facility allows you to redirect 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.

Solution:

A large number of the CA 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.

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

 SYSIBM  -  SYSCOLAUTH          DB2ADMIN  -  SYSCOLAUTH
 SYSIBM  -  SYSCOLUMNS          DB2ADMIN  -  SYSCOLUMNS
 SYSIBM  -  SYSCOPY             DB2ADMIN  -  SYSCOPY
 SYSIBM  -  SYSDATABASE         DB2ADMIN  -  SYSDATABASE
 SYSIBM  -  SYSDBAUTH           DB2ADMIN  -  SYSDBAUTH
...

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

Main menu.BMP

 

Command ACM will display the status of ACM:

 
 RCQ Main menu.BMP

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

For details of how to create and maintain ACMIDs please refer to the CA 'DB2 DB Management Common Functions' online manual.

You will see that this is easy to implement including creating shadow objects (database, tables, and indexes) and loading (initializing or updating) the data.

If you run the refresh of ACM tables every night, the ACM tables will be up-to-date and you can avoid contention on the DB2 catalog during normal working hours.