November 13, 2012

DB2 Performance Stored Procedures

Last week I posted a lengthy list of stored procedures that can be used to help with administrative tasks. What follows is a much shorter list of some stored procedures that are used to manage performance. For more about these procedures and how to use them, see the IBM manual, "DB2 10 for z/OS: Managing Performance."

  • DSNACCOX (DB2 realtime statistics) issues recommendations for maintaining DB2 databases. These recommendations include when to reorganize, image copy, or update statistics for table or index spaces. SDNACCOX also indicates when a data set has exceeded a specified threshold for the number of extents it occupies and when an object is in a restricted state.
  • DSNACCOR has been deprecated and replaced by DSNACCOX.
  • DSNAEXP (DB2 EXPLAIN) allows database operators to issue EXPLAIN statements within certain simple SQL statements without the authorization that's typically required to execute such statements. DSNAEXP was deprecated with DB2 10. The EXPLAIN privilege or SQLADM authority should now be used instead.

These stored procedures, which are new with DB2 10, help with automating statistics maintenance (ADMIN_UTL_MONITOR, ADMIN_UTL_EXECUTE, ADMIN_UTL_MODIFY). The "Managing Performance" manual also has details on these procedures.

  • SYSPROC.ADMIN_UTL_MONITOR provides functions that enable analysis of database statistics. These functions include issuing alerts for out-of-date, missing or conflicting statistics as well as summary and detailed table-level reports that describe generated RUNSTATS statements.
  • SYSPROC.ADMIN_UTL_EXECUTE solves alerts stored in the SYSIBM.SYSAUTOALERTS catalog table. The maintenance windows for these alerts are defined by the SYSIBM.SYSAUTOTIMEWINDOWS catalog table.
  • SYSPROC.ADMIN_UTL_MODIFY maintains the SYSIBM.SYSAUTORUNS_HIST and SYSIBM.SYSAUTOALERTS catalog tables, removing all entries in these tables that are older than a configurable threshold. This procedure also removes all entries in SYSIBM.SYSAUTOALERTS that are in COMPLETE state.

Database administrators are always looking for ways to improve application performance. Do you use any of these stored procedures? Have you found them helpful? Please share your experiences in Comments.