August 25, 2009

DB2 Commands to Manage Profile Tables

A few weeks ago in "A Morning Jolt for Response Times" I wrote about access database, the new DB2 command that's used to force open datasets containing the table or index space. DB2 9 for z/OS also contains a set of commands for managing PROFILE tables used by optimization tools such as IBM Optimization Service Center.

The start profile command loads or reloads profile tables into a data structure in memory. (Warning: Having IFCID trace 318 running while the profiling facility is active is NOT recommended. IFCID trace 318 inhibits the profiling facility's capability to report data collection and statistics. Run the stop profile command to disable the profiling facility.)

True to its name, the display profile command displays the status of the profiling facility. DSNT753I is the standard message you'll see:


status:    The profiling status can be either ON, OFF, SUSPENDED, STARTING,
            or STOPPING
     ON – Profiling is active
     OFF  – Profiling is inactive.
     SUSPENDED – Profiling was active, but is suspended now due to some error. Some error conditions might be the maximum push-out limit has been reached, the table space is full, and other similar.
        STARTING - Profiling is being started, but has not yet completed.
     STOPPING - Profiling is being stopped, but has not yet completed.

If the status is Off, Starting or Stopping, the Timestamp and Pushout lines aren't reported.

These DB2 tables are associated with profiles:

  • The virtual index table (SYSIBM.DSN_VIRTUAL_INDEXES) enables optimization tools to test the effect of creating and dropping indexes on the performance of particular queries.
  • Each row in the profile table (SYSIBM.DSN_PROFILE_TABLE) defines a profile. A profile is a set of criteria that identifies a particular query or set of queries.
  • The profile history table (SYSIBM.DSN_PROFILE_HISTORY) contains all of the profiles that were in effect at some point in time. 
  • The profile attributes table (SYSIBM.DSN_PROFILE_ATTRIBUTES) defines the attributes that are associated with a given profile.
  • The profile attributes history table (SYSIBM.DSN_PROFILE_ATTRIBUTES_HISTORY) contains the attributes that were in effect at some point in time.
  • The statement runtime information table (User-ID.DSN_STATEMENT_RUNTIME_INFO) contains runtime information for statements.
  •  The object runtime information table (SYSIBM.DSN_OBJECT_RUNTIME_INFO) contains runtime information about tables and indexes that DB2 accesses when it processes queries.

IBM recommends that you do not manually insert, update or delete data in these tables--they are to be managed by optimization tools.

For a detailed discussion of these tables and the profiling facility, see the "DB2 9 for z/OS Performance Monitoring and Tuning Guide."