IBM 4.6 5.6 Reorg and Runstats

Page 14 IBM Tivoli Identity Manager Performance Tuning Guide
5.6 Reorg and Runstats
Statistics on the number of rows in the tables and what indexes are available are required for IBM DB2 to
efficiently fulfill queries. It is important to update these table and index statistics after large Directory
Server Markup Language (DSML) loads, HR feeds, and reconciliations.
IBM Tivoli Identity manager ships with five JCL to execute reorg and runstats against the IBM DB2
databases.
In addition to running runstats on all tables within the database, we also manually update the statistics
table for the ACTIVITY, PROCESS, PROCESSDATA, and SCHEDULED_MESSAGE tables to ensure a
minimum cardinality. Setting a minimum cardinality on these tables helps the IBM DB2 query optimizer
and can decrease locking issues in the database.
Determining the values
JCL location Description
hlg.SAERSAMP(AERROITM) REORG for ITIM database
hlg.SAERSAMP(AERROLDP) REORG for LDAP database
hlg.SAERSAMP(AERROLDC) REORG for LDAP changelog database
hlg.SAERSAMP(AERRSITM) RUNSTATS for ITIM database
hlg.SAERSAMP(AERRSLDP) RUNSTATS for LDAP database
The REORG scripts above will reorganize both table spaces and indexes, improving data access
performance and reclaiming fragmented space. In addition, the REORG scripts will execute a RUNSTAT
for each table space after the REORG.
Caution: The REORG scripts should only be executed when the table spaces are not in use by stopping
the WebSphere Application Server and LDAP first. Failure to do this will result in the table spaces in an
intermediate state and will require additional attention.
Use RUNSTATS scripts on idle or lightly-used databases because it requires update locking on the
system statistics table to update the database statistics. A database with a heavy load might experience
transaction roll backs due to the system acquiring locks on the tables that are used by the database
optimizer to fulfill queries.
Note: The LDAP REORG and RUNSTATS JCL scripts rely on the DIR_DESCX2 index. See the Indexing
section for more information.
Setting the values
1) Edit the JCL to conform to your installation’s standards.
2) Update DB2 schema names, such as the database name, to match your environment.
3) Submit the JCL for execution.
4) Confirm the job’s completion code for success.
After running RUNSTATS on the ITIM database, the following should be run against the ITIM database to
update the statistics for the workflow tables. The following SQL statements require DB2 system
administrator privileges to perform.
UPDATE SYSIBM.SYSTABLES SET CARD = 50000
WHERE CARD < 50000 AND CREATOR = 'ENROLE' AND NAME = 'ACTIVITY';
UPDATE SYSIBM.SYSTABLES SET CARD = 50000
WHERE CARD < 50000 AND CREATOR = 'ENROLE' AND NAME = 'PROCESS';
UPDATE SYSIBM.SYSTABLES SET CARD = 50000
WHERE CARD < 50000 AND CREATOR = 'ENROLE' AND NAME = 'PROCESSDATA';
UPDATE SYSIBM.SYSTABLES SET CARD = 50000
WHERE CARD < 50000 AND CREATOR = 'ENROLE' AND NAME = 'SCHEDULED_MESSAGE';