Improvement of the performance of a comparison
If the scope of an application contains about 20,000 or more objects, performing a comparison can consume a considerable amount of CPU time, memory, and disk space.
To minimize the amount of resources used for the comparison, perform the following steps:
Create or verify indexes on the Db2 catalog tables.
Table
Index
SYSIBM.SYSFIELDS
TBCREATOR, TBNAME, NAME
SYSIBM.SYSFOREIGNKEYS
CREATOR, TBNAME
SYSIBM.SYSRELS
CREATOR, TBNAME
SYSIBM.SYSSYNONYMS
TBCREATOR, TBNAME
SYSIBM.SYSTABLES
DBNAME, TSNAME, TYPE
Creating these indexes enables Compare to use indexes to retrieve specific objects. If the indexes do not exist, Compare might need to perform table space scans, which can consume a great deal of CPU time. For more information about creating indexes, see the Installation System and Customizing BMC products and solutions for Db2.
- Consider excluding any Db2 object types that are not necessary for the comparison. For example, on the Compare CDL Options panel, you can type N for VW to exclude views from the comparison.
Avoid the use of automatic change rules in a Db2 catalog to Db2 catalog comparison. Instead, specify change rules in an outbound migrate profile that is applied before the comparison (with the CHANGERULESIN2 keyword) or specify CHANGERULESIN2 NULL.
- If the database, creator, owner, and schema names are different for Compare1 and Compare2, specify an existing outbound migrate profile with change rules (CHANGERULESIN2) to be applied to Compare2.
- If the database, creator, owner, and schema names are the same for Compare1 and Compare2, specify CHANGERULESIN2 NULL.
When BMC AMI Change Manager for Db2 creates automatic change rules, it generates at least one change rule for each object in the comparison. Message BMC56388I in the diagnostic log provides you with the number of change rules that are generated. While the memory that is used to store these change rules is somewhat significant, the CPU time that is expended to apply these rules is considerable.
Modify the value of the THRESHOLD keyword in the ALUIN input stream to increase the threshold for the number of objects that are pulled from the Db2 catalog.
The THRESHOLD keyword specifies the integer that limits the number of objects that are pulled from the Db2 catalog using the nonunique catalog indexes. If the number of objects that are requested exceeds the limit, the nonunique catalog indexes are ignored. The relevant table space in the catalog is then scanned for the requested objects. For example, if the THRESHOLD is set to 175, and you request 200 objects, the product fully scans the table space instead of opening 200 Db2 cursors.
In general, the THRESHOLD keyword can be set to a very high value. The value is set to 100,069 by default.
- If possible, use the fastest input source that is available for your comparisons. The following sources of input are available (shown in order of speed, with the fastest listed first):
- DDL file or migrate-type worklist
- Baseline
- Db2 catalog
Related topic