Why does an RC/Migrator analysis use unqualified SQL to access the catalog
tables and how can the overall performance be enhanced?
Because 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(MIGRATORr) member 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 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) 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
Beginning with DB2 V4.1 additional indexes can be created on the catalog tables. Appendix A of the CA Solutions for OS/390 and Z/OS
Post-Installation and Customization Guide 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
parmlib(migrator). FULLTREE NO initiates CAF processing which ultimately minimizes SQL access. Note: FULLTREE NO does not
affect the a Compare analysis. FULLTREE NO improves analysis performance only.
- VSAM Reads
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
* NOTE: The VSAM analysis option is no longer a valid option when operating in a V8 subsystem.
- 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 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 using PDA).
Performance wise, IDCAMS is the least optimal choice because IDCAMS can be very slow as it involves accessing the ICF catalog
Tabletrieve 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 SIZE parm 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
creators, but have thousands of different tables names.
- It has been shown that a significant performance gain can be
realized if the tablespace PTITSMG2 is compressed and then reorged. The more rows present on the PTMG2_ALTER _0200 table the more significant
the elapsed time savings. Tablespace PTITSMG2 contains the table PTMG2_ALTER_0200 and index PTMG2_ALTERIX_0200.