Skip navigation

Solutions for Db2

5 Posts authored by: Ramon Menendez Employee
Share This:

In a previous article, we introduced the repositories the BMC products use. In that article we spoke about the most important and used of those repositories: The BMC Common utility tables.


In this new article on the same topic we will briefly describe the BMC Common DB2 repository.


The BMC Common DB2 repository was added in December 2009 to the 9.2 version of the Backup & Recovery set of products, in order to support Dynamic Grouping.


In this version, a new way of creating groups of objects (called Object Sets) was introduced. An object set is a set of table spaces and indexes (usually belonging to the same application) that are grouped together in order to perform common backup and recovery procedures. Grouping simplifies the backup and recovery process because you can use the generated JCL to back up or recover the entire group at once. Grouping also simplifies the specification of backup and recovery options, because you can specify the options just once for the entire group.


These Objects sets can be build by using the Recovery Manager for DB2 online interface or by using the Recovery Manager for DB2 batch utilities. Using the batch program is faster for creating groups that contain more than a few hundred objects.


These objects sets could use several patterns to identify objects to INCLUDE or EXCLUDE from the group:


_ TS (table space name pattern)

_ IX (index name pattern)

_ TB (table name pattern)

_ IS (index space name pattern)

_ PL (plan name pattern)

_ PG (package name pattern)

_ SG (stogroup name pattern)

_ OS (object set name pattern)

_ SQ (dynamic SQL pattern)

_ RP (repository plan)



Probably the most powerful way of creating groups is by SQL pattern. With this option, you can include complex logic to select objects to process, that could include for example JOIN with user defined control tables to identify object that for example should never be processed.


In order to support the new object sets, new OBJECTSET syntax was added to Recover Plus for DB2, Copy Plus for DB2 and Log Master for DB2. This syntax allowed these products to read the new BMC Common DB2 repository and extract the list of objects to process. Of course, that made existing syntax like for example RMGROUP obsolete.


The new BMC Common DB2 repository included the following new tables:


_ object set (OBJSETS)

_ object set definition (OBJSET_DEF)

_ object set SQL (OBJSET_SQL)

_ group options (GRPOPTS)

_ product registration (PRODREG)

_ group authorizations (GROUPAUTH)


The repository is documented in detail in an Appendix in the supporting products User Guide.


These tables replaced the existing RECOVERY MANAGER tables used for group definition (In that first version, a utility was provided to migrate the content of the previous tables to the new ones).


Since them, several additional features or support have been added to the BMC Common DB2 repository. For example, in Version 10.1, RECOVERY MANAGER added OBJECTSET support for ARMBGEN, that allows creating Recovery groups using the OBJECTSET syntax. Also a new batch utility, ARMBSET, has been created to allow common DB2 commands or utilities, like STOP, to be executed at the OBJECTSET level.


But the main change occurred last June, when in version 11.1 a shared repository with DASD MANAGER PLUS was announced. From version 11.1, DASD MANAGER PLUS object sets are also stored in the BMC Common DB2 repository, enabling object sets to be shared across products. The BMC Common DB2 repository replaces the previous three tables used in the BMCASUnn database for Object sets definitions.


The repository is also documented in Chapter 2 of the DASD MANAGER PLUS for DB2 Reference, “DASD MANAGER PLUS database”. No data conversion is necessary when you upgrade from previous versions of DASD MANAGER PLUS to version 11.1.00 or later. The Installation System migrates the data for you.


This support adds also addition syntax and allows Actions defined in DASD MANAGER PLUS to be executed at the Object Set level.


I hope this article has been of interest, in future articles we will develop this topic and will include other BMC repositories.



Share This:

One of my favorite BMC Catalog Manager for DB2 commands is APPLY. The idea behind this command is to Apply a SQL statement model or template to a list of objects. With this command, you can generate and execute ad-hoc SQL statements without the need of editing or generating statements one by one.


This command will substitute the host variables in the SQL template for the corresponding columns in the list entries and create one statement for each object in the list. The host variables must be the DB2 catalog column names.


Let's do a step by step example:


Let's suppose I want to add a RESTRICT ON DROP attribute to all my tables.


First, I generate a list with all my tables, from the LIST panel or just from any panel with TB ISPRXM.%. Now, I could generate an ALTER statement for each of them, change this attribute and execute, but that could be really time consuming, depending on the number of tables.


I will do it in just one execution, using APPLY. From any row in the list, I use 's' to see the full catalog row information. There, I can see that the columns I would need for the statement template are named CREATOR and NAME in the catalog (SYSTABLES).


Then, I need to create the SQL template. I could do it by hearth or by checking the right sintax in the manual, but it is much easier if BMC Catalog Manager for DB2 do it for you. From any row in the list, I use ALTER to generate an ALTER statement, in the "Create/Alter Table" panel, change the WITH RESTRICT ON DROP attribute and generate the SQL:


     ALTER TABLE                      
          ADD RESTRICT ON DROP        


Then, I edit the SQL and change the column values for host variables with the column names:


     ALTER TABLE                      
          ADD RESTRICT ON DROP        

and save it in SQL Table as MYAPPLYSQL.


Then, I come back to my list and just use the command APPLY MYAPPLYSQL ALL. If I want to exclude any particular object in the list, I can use the X designator in the Cmd column. BMC Catalog Manager for DB2 will process the command against all objects except those excluded with the X designator.


In the "Confirm APPLY SQL MODEL" panel, I can review, edit and change the template, and select Apply to list objects 'Y'. In the "Confirm SQL" panel, I can see it has generated an ALTER statement for each table in the list, substituting the correct creator and name from the list. There, I can edit, save and execute the list of commands.


Of course, I could be done with SQL, using a combination of literals and column names from SYSIBM.SYSTABLES, but this method is much easier.


Another not well known BMC Catalog Manager for DB2 command that is related to this, is IMPORT (do not confuse with IMPORT function in BMC Change Manager for DB2). With this command, you can import SQL statements in a PDS dataset (for example, the PDS you use as input for SPUFI) to the BMC Catalog Manager for DB2 SQL table. From the SQL Table, it is very easy to browse, edit or execute SQL Statements or of course, use them as SQL templates for APPLY command.


Hope this entry can be useful.


Share This:

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:


                   COUNT(*) AS EJECUCIONES


  WHERE DATE > CURRENT DATE - 1 MONTH              



Executions and total elapsed by utility in the last month  


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

     SECOND(ELAPSED)) AS TOTAL                             


  WHERE DATE > CURRENT DATE - 1 MONTH                    



Executions with Elapsed time higher than 30min.              



   FROM BMCUTIL.CMN_BMCHIST                      




Executions with higher Elapsed time, by utility    





                               (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.

Share This:

One of the Best kept secrets of BMC CHANGE MANAGER for DB2 is the IMPORT function.


The idea behind IMPORT is that sometimes is more easy to specify changes with any of those languages than using the product panels. This function also helps you to create automated procedures to implement changes, for example coming from a design tool in DDL format or from a COMPARE process in CDL format. You can IMPORT into CHANGE MANAGER for DB2 text files containing change specifications written either in DDL, CDL or DML.


DDL (Data Definition Language) is the DDL standard DB2, that can be created manually, extracted from any design tool or any product generating DDL (for example, HDDL function from BMC Catalog Manager for DB2) or from a BASELINE Report created with BMC CHANGE MANAGER for DB2.


CDL (Change Definition Language) is a proprietary language for change specifications used by BMC CHANGE MANAGER for DB2. This language is the output from the COMPARE process, but you can also generate manually text files containing DDL, as in the following example:


-CDL  nnnn ALTER DATABASE old_name NAME new_name.


DML (Data Manipulation Language) is another proprietary language from BMC, used as change definition language within the CM/PILOT component of BMC CHANGE MANAGER for DB2.  You can also use two CM/PILOT scripts to help you create SQL-like Data Manipulation Language (DML) statements to update, delete, and migrate data structures. This language can be generated from the CM/PILOT panels or also manually and imported into a WORKID. An example could be:



       SET NAME = 'new_name'

       WHERE NAME=’old_name’


The content of these files is validated by BMC CHANGE MANAGER for DB2 and converted into an ALTER WORKID. After the statements are imported, BMC CHANGE MANAGER for DB2 creates entries in the CD tables as if they had been requested in the Specification component. This WORKID can be browsed, modified, analyzed and executed.


In the “ALTER and CHANGE MANAGER for DB2® User Guide” there is a full section dedicated to “Importing files”, in the "Altering data structures in a database environment” chapter.


In the “ALTER and CHANGE MANAGER for DB2® Reference Manual” there are several sections with examples, either of CDL or DML.

In the HLQ.DBCNTL data set there are several examples (ACMDMLxx) of pregenerated DML files that can be edited and adapted to the user needs.


For DDL or CDL the Import component can also use Inbound Migrate Profiles to make input from another system compatible with the receiving subsystem’s version of the application. Inbound migrate profiles contain only change rules that modify imported data structure definitions. The change rules are applied as the CDL file or DDL file is imported.


However, DML does not use Migrate Profiles and you will get a message BMC70372W - Migrate profile invalid for DML


The Import process can be run either in foreground or in batch, find attached examples of different IMPORT processes:


For DDL:


//IMPORTIN DD DISP=SHR,                               

//         DSN=ISPRXM.BMCPROD.JCL(DDL)               

//ALUIN    DD *                                       

  SSID DJJ1                                           

  WORKID ISPRXM.TESTDDL                                  

  SOURCETYPE DDL                                      



For CDL:


//IMPORTIN DD DISP=SHR,                                 

//         DSN=ISPRXM.BMCPROD.JCL(CDL)                  

//ALUIN    DD *                                         

  SSID DJJ1                                             

  WORKID ISPRXM.TESTCDL                                   

  SOURCETYPE CDL                                        



For DML:   


//AUCIN    DD DISP=SHR,                             

//         DSN=ISPRXM.BMCPROD.JCL(DML)              

//ALUIN    DD *                                     

  SSID DJJ1                                         

  CMPILOT REUSEWORKID                               

  WORKID ISPRXM.TESTDML                               




To facilitate the IMPORT process, there are additional syntax for managing the target WORKID:


NEWWORKID creates the WORKID as part of the IMPORT, you can use a template for generating the WORKID name.


REPLACEWORKID imports into an existing WORKID and replaces its previous content or creates a new one if it does not exists


WORKIDMERGE combines the imported specifications with any other specifications that could exist in the WORKID.


I hope you will find this function useful for your advanced change management needs in DB2.


Share This:

Are you in DB2 9 or beyond and do you still have a bunch of simple Tablespaces? Are you worried about accidentally dropping one of them and not being able to recreate it again?


Of course, the first thing to do is ALTER RESTRICT ON DROP of all Tables residing in Simple TS, to avoid an accidental Drop.


But, what happen if even thou, any Simple TS is dropped? Even if you got the DDL, you will not be able to recreate it. You could recreate it as segmented, but then any attempt to recover the data, will fail.


How could you do it? I have developed and tested the procedure below, using BMC Log Master for DB2 and BMC Unload Plus for DB2, but you can try to use similar products from another vendor with similar characteristics, you only need any Log analysis product capable of extracting all info from the dropped TS and an Unload utility capable of unloading from an unregistered copy.


First, use BMC Log Master for DB2 with the DROPRECOVERY syntax, to get the DDL and details from the last FIC and RBA's in the report:



     FILTERREL AND                                                     



     FILSZ NONE                                                        

     HISTORY WRITE                                                     





        CYLINDERS SPACE(1,1) UNIT(SYSDA) RELEASE                       


        CYLINDERS SPACE(1,1) UNIT(SYSDA) RELEASE                

     FROM  DATE(aaaa-mm-dd) TIME(               

     TO  DATE(aaaa-mm-dd) TIME(                 


Then, use BMC Log Master for DB2 to populate the repository with information about the old object, because it is not in the Catalog anymore, using OVERTIME MODE:




    EXECUTION MODE OVERTIME                 



    FILSZ NONE                                            

    HISTORY WRITE                                         



    REPOS UPDATE                                          

    INCLUDE DICTIONARY                                    

    DB2CATALOG YES                                        

    FROM  DATE(aaaa-mm-dd) TIME(          

    TO  DATE(aaaa-mm-dd) TIME(            



Then, use BMC Log Master for DB2 to extract any SQL activity after the copy, also using OVERTIME MODE. Include Image copy information extracted from the first step for completion and scan range from RBA information (between copy and drop point):



     FILTERREL AND                                      

     EXECUTION MODE OVERTIME                            

          ATTEMPT COMPLETION                            




       IMRBA X'C92C2B68DDAF'                            



     FILSZ NONE                                         

     HISTORY WRITE                                      




       TYPE SUMMARY                                             


          CLASS(*) NOHOLD                                       




          CYLINDERS SPACE(1,1) UNIT(SYSDA) RELEASE              


          CYLINDERS SPACE(1,1) UNIT(SYSDA) RELEASE              

       INCLUDE TRIGGER YES                                      

    DB2CATALOG NO                                               

    FROM RBA X'C92C2B68DDAF'                                    

    TO   RBA X'C92C2E51B5CF'                                                  



Then, using BMC UNLOAD PLUS for DB2, UNLOAD data from the unregistered copy:



         INFILE IN001 FULL        

         DDLDDN DDL0001


Then, you got all information needed. Recreate the object and its dependents, using the DDL extracted, but adding the SEGSIZE clause. Load data extracted in the previous step that will bring data back to the time of the last copy and apply generated MIGRATE SQL to include all activity after the copy.


Of course, this is an emergency process, I recommend you using BMC Recovery Management for DB2 solution and the TRANSFORM process to change all your existing TS to UTS without impact.


Good luck!

Filter Blog

By date:
By tag: