Why does an RC/Migrator analysis use unqualified SQL to access the catalog tables and how can the overall performance be enhanced?
RC/Migrator was designed for medium to large migrations, the design utilizes just ONE unqualified cursor to access catalog data as opposed to opening a qualified cursor many times. If you are experiencing performance problems, there are many ways to potentially boost Migrator performance.
These performance-boosting techniques include:
- Limit the Number of Objects
The simplest way to boost performance is to limit the number of objects selected into the strategy. Breaking up your strategy into multiple smaller strategies is a sure-fire way to increase performance.
- Max Threads
The MAXTHREADS parameter in PARMLIB(MIGRATOR) limits the number of available threads per analysis. Since a batch analysis can use up to 12 threads, increasing MAXTHREADS to 12 could help the analysis run faster, provided your IDFORE (online maximum connections) or IDBACK (batch maximum connections) DSNZPARM parameter is 12 or more.
Alternate Catalog Mapping (ACM) improves performance by reducing contention on the catalog tablespaces. Additionally, isolating the ACM tables into separate tablespaces, (as opposed to the catalog which has several tables in the same tablespace) could further improve performance benefits.
When shadow tables are accessed, only the tablespace containing the shadow table is locked, reducing system catalog contention. Performance can further be improved by defining additional indexes on the shadow tables.
See d) Additional Indexes below
Benchmark testing has shown that the use of views with a where clause in ACM mappings can greatly reduce GETPAGE access and CPU utilization. When using views with a where clause in ACM be careful that all related objects can be accessed since only the objects qualifying the filtering in the views are recognized. For example if your ACMID SYSTABLES setting links to a view having clause 'WHERE CREATOR = CREATORA', and if a strategy table named CREATORA.TABLEA contained a view called CREATORB.VIEWB then this view could not be migrated in the strategy because only SYSTABLES rows having CREATOR = 'CREATORA' are available.
- Additional Indexes
From DB2 V4.1 additional indexes can be created on the catalog tables. Appendix B CA Database Management Solutions for DB2 for z/OS Implementation Guide, Additional Index Creation , contains our recommendations for creating additional indexes on catalog tables, with the goal of improving product performance. Additional indexes are also beneficial to ACM tables.
During a migration or alter strategy analysis, unqualified calls to the catalog tables can be avoided by setting the FULLTREE to 'NO' in the MIGRATOR member of PARMLIB. FULLTREE NO initiates CAF processing which ultimately minimizes SQL access.
Note: FULLTREE NO does not affect the a Compare analysis. FULLTREE NO improves Migration and Alteration analysis performance only.
- VSAM Reads (Invalid from DB2V8 onwards)
The use of VSAM READS speeds up processing by avoiding SQL access when creating, updating and analyzing RC/Migrator strategies. VSAM Reads is also useful is speeding up the initial object tree build process. When VSAM READS is activated catalog information is accessed by reading underlying VSAM datasets. VSAM READS can be activated by setting the VSAMID parm to (*) or (userid) in parmlib(migrator). If (*) is used, the primary logon ID is used for authority. If (userid) is used, then the authority of the (userid) will always be used. The ID(s) used for VSAM READS authority must have Read Control Interval Access to the DB2 catalog underlying VSAM datasets.
Note: VSAM Reads cannot be used in conjunction with ACM
- SIZE Parameter in Model Services
If you're encountering analysis performance problems, the problem could be caused by obsolete or missing statistical information. Whenever RCM builds utility statements, it needs to determine the size of the unload or temporary work datasets.
This is done 1 of 3 ways:
- Using RUNSTATS information from the DB2 catalog
- Using PDA (CA Database Analyzer for DB2 for Z/OS) stats from PDA tables.
- Using IDCAMS information from the VSAM files of the underlying object.
The 'SIZE' parameter in Model Services is used to determine where to retrieve this information from.
So, RPI means RUNSTATS first (if available), then PDA (if available) and then finally IDCAMS.
Similarly, PRI means RCM uses PDA statistics first (if available), then RUNSTATS (if available), and then IDCAMS.
(We recommend PRI if CA Database Analyzer is utilized).
Performance wise, IDCAMS is the least optimal choice because IDCAMS can be very slow as it involves accessing the ICF catalog Table to retrieve dataset information.
Note that although the size setting is PRI or RPI, if the data is not available in the catalog or in PDA, RCM will then use IDCAMS to retrieve size information
In one case study, analysis time dropped from 55 to 1 minute when RUNSTATS were used instead of IDCAMS.
Remember IDCAMS is slow, so keep RUNSTATS or PDA STATS current and use RPI or PRI in the 'SIZE' parameter to ensure optimal performance
- Use of UNDERSCORES in object name
If you use underscores (_) in view or table names and experience poor response time when listing views/tables in either RC/Migrator, RC/Query or RC/Update, try adding a new index to SYSVIEWS and/or SYSTABLES using columns (Name, Creator) followed by a REBIND.
This should improve performance significantly. In general - there is a MUCH better filter factor on NAME compared to CREATOR particularly in ERP environments, where it is common to use a limited number of creators, but have thousands of different tables names.
- PTITSMG2 compressed and then REORGed.
It has been shown that a significant performance gain can be realized if the tablespace PTITSMG2 is compressed and then reorged. Tablespace PTITSMG2 contains the table PTMG2_ALTER_0200 and index PTMG2_ALTERIX_0200.
The more rows present on the PTMG2_ALTER _0200 table the more significant the elapsed time savings.