Comparing Db2 objects in batch
jthyssen 50A79HCRE7 Visits (1144)
Comparing Db2 objects in batch
The IBM Db2 Object Comparison Tool helps you compare source and target(s) Db2 environments to report on any differences and produce apply jobs to bring the target environment into the same state as the source environment.
For example, you can promote changes by running a catalog comparison of Db2 objects in your development environment with the equivalent Db2 catalog objects in your test environment. Another example, might involve comparing a DDL script, provided by a DBA or developer, against the Db2 catalog.
There are two ways of using the Db2 Object Comparison Tool:
With the second option, is it is easier to automate and/or integrate the job into your own tools and/or processes. Using z/OS Management Facility workflows, you can provide a REST front end to CM Batch using supplied workflows.
The Change Management feature was introduced in an earlier version of the product. It keeps track of all changes to Db2 objects; detect and manage overlapping changes; and adds the ability to automatically back out changes. It also supports multi-target changes, where one change can be applied to multiple targets.
Originally the Change Management feature was driven through ISPF panels. Batch capabilities referred to as Change Management Batch (CM batch) was subsequently added and has been enhanced over time. Today. it is possible to run a compare using CM Batch on its own, without performing Change Management. This is the topic of this blog.
Change Management is an optional feature that can be selected or deselected during customization.
Change Management database . . . . . . : YES
Regenerate the TCz jobs and submit the relevant customization jobs. This will generate the Db2 objects needed for Change Management and also create the CM Batch JCL procedure (hlq
Compare requires the following input:
Example 1 – Run Compare between DDL and catalog
The complexity of CM Batch has been wrapped into a JCL procedure, GOCCM, with the benefit that the JCL is easy to read and understand:
PARMS DD is the CM Batch parameters. A concatenation of parameters are provided inline and stored in a dataset. The former are the parameters that will likely change between each run, whereas the latter are parameters that remain unchanged. This set of parameters should be shared with the DBA team to ensure consistent parameters across changes.
The following parameters are used:
Explanations of some of the parameters are listed in the following table:
IGNORES DD contains the catalog attributes to ignore. OC ignores several catalog attributes that have no meaning, such as when an object was created, You mayalso want to ignore additional fields such as the OWNER of an object. The OWNER has implicit rights over an object, however, you do not necessarily want to recreate an object. For example an object may have beencreated by DBA#1 in development and also created by DBA#2 in test. You may also want to ignore OWNERTYPE and CREATORTYPE if some objects are created under a trusted context. Additionally, you may want to ignore differences in PARTITIONING and/or SPACE.
MASKS DD contains the masks for translation, overwrite, verification, and rename. In this scenario, we are comparing input DDL against the development system using the same schema names, databases names, etc., Ther
However, we do have some overwrite masks and a single verification mask
PRIQTY, SECQTY, SEGSIZE and DEFINE are overwritten for all tables (*.*) in the input to ensure site standards are applied to the input DDL.
A REXX exec TSVER, is called for verification of table spaces. This specific REXX checks that the table space is a universal table space. This is to avoid DBAs or developers creating segmented or classic partitioned tables spaces that deprecated for use and cannot be created in Db2 V12 FL504.
Db2 TEMPLATE statements are provided in ADBTEMPL DD to be used for image copies.
Submitting the job, creates the report and the work statement list – the change will not be run.
The Compare Report from REPORT DD describes differences in the following format:
A Comparison Summary report is available
as well as a Comparison Counts report
In addition to the report, a work statement list (WSL) is also generated:
The WSL can be viewed and edited through AOC’s WSL editor (primary command WSL in AOC ISPF). Additionally, you can convert the WSL from the internal format to a readable text format using CM B
Example 2 – run WSL
The WSL generated in example 1 can be executed in multiple ways, including:
Again, with CM Batch, the JCL is very simple:
The parameters hlq.
Submitting the JCL will apply the changes required to deploy the DDL into the target Db2 subsystem.
In our example, overwrite masks were used. Therefor, the DDL that was executed may be different from the DDL originally received by the developer. It is recommended to run DDL reverse engineering on the final objects from the Db2 catalog and provide the final DDL to the developer for an update in his/her Source Control Management (SCM).