Resetting the Db2 SQL Performance Analyzer REGISTRY

Db2 SQL Performance Analyzer includes a cleanup mechanism that resets all the SQL PA entries in the REGISTRY to their original status.

About this task

Occasionally, an entry might have an invalid status of in use in the Db2 SQL Performance Analyzer REGISTRY. The Db2 SQL Performance Analyzer REGISTRY table can be reset if entries are incorrectly marked as in use.

The Db2 SQL Performance Analyzer REGISTRY table contains an entry for each Db2 SQL Performance Analyzer generic plan table, holding an Db2 SQL Performance Analyzer secondary authorization ID, a place for the primary authorization ID of the user (set to ANL99999), an in use flag (Y or N) to help avoid conflicts, and a timestamp indicating last use.

By registering a PLAN_TABLE as in use, Db2 SQL Performance Analyzer gives exclusive control of that table to a specific user, for a portion of the time Db2 SQL Performance Analyzer is processing. The table is released as soon as it is no longer required before the end of the Db2 SQL Performance Analyzer process.

Tip: To clean up any fragmented or frozen entries in the SQL PA REGISTRY, consider adding ssidRSET to your job scheduler so that it runs periodically. Rarely, users might receive an error message that about there being no unused secondary authorization IDs (ANL1008E).Db2 SQL Performance Analyzer contains code to detect when the REGISTRY is full, and automatically resets any entries that are more than 24 hours old.

Procedure

To run the cleanup mechanism:
  1. Locate and run hiqual.SANLSQL, member ssidRSET.
    ssidRSET is designed to run under TSO SPUFI, but you can also run it from a batch job.
  2. Add ssidRSET to your job scheduler so that it runs periodically.