Improving Db2 performance

For DB2 sites, HCL Z Asset Optimizer operational jobs are usually run during off-peak so it is important that all IQ Import and Usage Import jobs must complete during the batch window.

Symptom

Operational jobs such as IQ Import and Usage Import seem to be taking longer to run.
Reports retrieved via the Analyzer also seem to take longer to finish.

Causes

This issue can occur for the following reasons:

Retaining too much usage data for long periods and not performing regular housekeeping tasks.
Importing all usage data into a single repository instead of spreading the usage data across multiple repositories.

Diagnosing the problem

Identify operational jobs that are now taking longer to run.
Identify Analyzer reports that are now taking longer to finish.

In the Db2 MSTR address space/system console, check for any DSNJ031I or DSNR035I messages, indicating that jobs may not be committing at regular intervals as expected.
In the Db2 MSTR address space/system console, check for problems related to the Db2 work file database (this has been covered in a previous problem/solution).
Run SQL command to determine size and periods of the largest table – TUSEMTD
Run SQL command to list libraries that are marked deleted (obsolete).
IBM Support may request you to run special diagnostics/traces.

Run SQL command to determine size and periods of the largest table – TUSEMTD
```
SELECT FPERIOD, COUNT(*) FROM &REREPZSCHM.TUSEMTD
GROUP BY FPERIOD ; 
```
Next review comments in job HZASLDEL and decide on range of periods to select for deletion of usage records.
Run SQL command to list libraries that are marked deleted (obsolete).
```
SELECT DISTINCT CAST(YEAR(FOBSERVEDELETED)AS CHAR(4))||    
'-'||CASE WHEN MONTH(FOBSERVEDELETED)< 10 
THEN '0' 
ELSE '' END || CAST(MONTH(FOBSERVEDELETED)
AS CHAR(2))
FROM &REPZSCHM.TLIBRARY
WHERE FOBSERVEDELETED IS NOT NULL;
```
The above SQL command is found in job, HZASPDEL. Review comments in the job and decide on range of periods to select for deletion of obsolete records from the TLIBRARY, TLIBSYS, TMODULE, TUSEPOV, and TCSECT tables.
Run job HZASLDEL to delete obsolete LPARs (LPARs that have been decommissioned at your site).
For sites that have an extremely large TUSEMTD table (50m to 100m rows per period), consider defining the table space as a partition-by-range (PBR) UTS. Use column FPERIOD in TUSEMTD table as the key for the range. As this is not a trivial task, the DBA needs to prepare an implementation plan for this exercise to convert from a PBG to a PBR.
On a regular basis, run REORG for all repositories. RUNSTATS is automatically run as part of IQ Import and Usage Import jobs.

Resolving the problem

For Analyzer reports, especially from the Discovery Tab, do not use an ‘*’ as the first character as a wild card. For example, in report ‘Search by Modules, restrict your search to a more definitive range.

As part of improving the run times, you may be advised by IBM Support to change the value for the COMMIT parameter required in the IQ Import and Usage Import jobs.