Share:|

Every time you install a BMC product for DB2, it creates a set of DB2 tables, usually referred as “repository”. Most of those tables are for internal use, but sometimes the information contained in those tables can be quite useful.

 

Also, even if those tables are not designated to be maintained from outside the product, sometimes knowing its structure can be very useful for batch generation, exporting definitions, cloning, etc.

 

This article describes in detail some of the repositories the DB2 products create, the information contained there, their usage, maintenance needed, recommendations, tips, techniques, and several customer examples of exploiting the info contained  there (for example, queries to the tables). 

 

These repositories are created during installation, and I strongly recommend do not change the names by default.  If you are changing the version of any product, the installation will take care of creating a new repository and migrating previous data if needed, or reusing the existing tables. If new tables are created, $CxxMIG jobs will be created during installation, if the tables are reused but structure has changed, $CxxALTR jobs with the needed ALTER statements will be generated. For some products, it is optional to reuse the repository or create a new one, but that could change in future versions.

 

The repositories are documented in detail in the corresponding manuals, either in the Reference Guides or in the Customization Guide.

 

Some of those repositories are used by several products, for example the Common utility tables or the BMC Common DB2 repository.

 

In this article we will briefly describe the Common utility tables : Those tables are used mainly by the BMC DB2 utilities .

 

The most important tables are BMCUTIL and BMCSYNC. These are functionally equivalents to SYSUTIL table in DB2. They contain one row per running Utility in BMCUTIL, and one row per utility and object in BMCSYNC. These tables control execution and restarting of BMC utilities (including BMCSTATS that is considered another utility, as RUNSTATS). These tables control also concurrency between BMC utilities.

 

There are functions equivalent to the corresponding IBM commands of TERM and DISPLAY, which can be issued for example from BMC Catalog Manager for DB2 or from a REXX sample program provided by BMC.

 

Another important table is BMCHIST. This is a Historical of BMC utility executions. Some utilities write in this table unconditionally, while others can control writing or not with a parameter (HISTORY = Y/N in COPY PLUS, Unload Plus and RECOVER PLUS, BMCHIST = Y/N in Reorg Plus).

 

This table can be browsed with BMC Catalog Manager for DB2 or with SQL queries.

 

Several interesting queries to this tables can be, for example:

 

Executions by day and utility in the last month:

  SELECT CHAR(DATE,EUR), UTILNAME,

                   COUNT(*) AS EJECUCIONES

  FROM BMCUTIL.CMN_BMCHIST  

  WHERE DATE > CURRENT DATE - 1 MONTH              

  GROUP BY DATE, UTILNAME;

 

Executions and total elapsed by utility in the last month  

   SELECT UTILNAME, COUNT(*) AS EJECUCIONES,                  

     SUM(HOUR(ELAPSED)*3600 + MINUTE(ELAPSED)* 60 +       

     SECOND(ELAPSED)) AS TOTAL                             

  FROM BMCUTIL.CMN_BMCHIST        

  WHERE DATE > CURRENT DATE - 1 MONTH                    

  GROUP BY UTILNAME;

 

Executions with Elapsed time higher than 30min.              

   SELECT DBNAME,SPNAME,UTILNAME,               

      CHAR(ELAPSED,ISO) AS DURACION, CHAR(DATE,ISO) AS FECHA                      

   FROM BMCUTIL.CMN_BMCHIST                      

   WHERE HOUR(ELAPSED)* 60 + MINUTE(ELAPSED) > 30

   ORDER BY ELAPSED DESC;

 

Executions with higher Elapsed time, by utility    

    SELECT DBNAME,SPNAME,UTILNAME,

                     CHAR(ELAPSED,ISO) AS DURACION, CHAR(DATE,ISO) AS FECHA

    FROM BMCUTIL.CMN_BMCHIST         

    WHERE UTILNAME CONCAT CHAR(ELAPSED,ISO) IN                   

                               (SELECT UTILNAME CONCAT CHAR(MAX(ELAPSED),ISO)              

                              FROM BMCUTIL.CMN_BMCHIST         

                              GROUP BY UTILNAME);

 

Other tables in this Database are:

 

BMCLGRNX is a shadow of SYSLGRNGX.

 

BMCXCOPY is equivalent to SYSCOPY, when a BMC event or copy cannot be registered in SYSCOPY. For example, Nonstandard copies as Instant Snapshot, Cabinet, Encrypted, Compressed, etc.; Indexes with COPY NO, copied or reorganized by BMC; Partial Recover/Rebuild of indexes with BMC; etc.

 

This table is used by BMC products to identify and exploit nonstandard copies, for example UNLOAD PLUS uses this table for unloading from an Instant Copy.

 

The MODIFY command from COPY PLUS will delete from this table as the IBM MODIFY utility does from SYSCOPY.

 

BMCTRANS: Used by RECOVERY MANAGER and Log Master for Transaction Recovery

 

BMCDICT: Temporary table used for storing compression dictionaries for LOADPLUS and REORG PLUS.

 

Any other tables that could still reside in this database were created for supporting other functions that the evolution of the products has made them obsolete.

 

In future articles we will develop this topic, including other BMC repositories.

 

Many Thanks.