Subsection snapshots

On systems that use interpartition parallelism, the SQL compiler partitions the access plan for an SQL statement into subsections. Each subsection is executed by a different database agent (or agents for SMP).

The access plan for an SQL statement generated by the database code generator during compilation can be obtained using the db2expln command. As an example, selecting all the rows from a table that is partitioned across several partitions might result in an access plan having two subsections:
  1. Subsection 0, the coordinator subsection, whose role is to collect rows fetched by the other database agents (subagents) and return them to the application.
  2. Subsection 1, whose role is to perform a table scan and return the rows to the coordinating agent.

In this simple example, subsection 1 would be distributed across all the database partitions. There would be a subagent executing this subsection on each physical partition of the database partition group to which this table belongs.

The database system monitor allows you to correlate runtime information with the access plan, which is compile time information. With interpartition parallelism, the monitor breaks information down to the subsection level. For example, when the statement monitor switch is ON, a GET SNAPSHOT FOR APPLICATION will return information for each subsection executing on this partition, as well as totals for the statement.

The subsection information returned for an application snapshot includes:
  • the number of table rows read/written
  • CPU consumption
  • elapsed time
  • the number of table queue rows sent and received from other agents working on this statement. This allows you to track the execution of a long running query by taking a series of snapshots.
  • subsection status. If the subsection is in a WAIT state, because it is waiting for another agent to send or receive data, then the information also identifies the partition or partitions preventing the subsection from progressing in its execution. You may then take a snapshot on these partitions to investigate the situation.

The information logged by a statement event monitor for each subsection after it has finished executing includes: CPU consumption, total execution, time, and several other counters.