Types of parallelism problems
Parallel queries can experience several types of problems.
- Hangs
The diagnostic procedures for hangs during parallel processing are similar to those for hangs during other types of Db2 processing.
One useful source of diagnostic information is the output that you receive when you run DSNWDMP with option DS=3 on dumps of the Db2 master address spaces. With DS=3, you can see what parallel tasks were running when the hang occurred.
- Incorrect output
Diagnosing incorrect output problems for query parallelism is similar to diagnosing problems when there is no parallelism.
If you collected trace data for IFCID 0221, you can check that data for parallel processing errors. Examine the page range or key range values in the QW0221D sections. There is a QW0221D section for each parallel task. Ensure that the ranges in any two QW0221D sections do not overlap.
If the query produces output, you can obtain further diagnostic information by forcing a dump in CSECT DSNXROUA while the query runs. To do that, run utility DSN1SDMP with these control statements:
* START A GLOBAL TRACE TO TRACE MODULE ENTRIES AND EXITS START TRACE=GLOBAL CLASS(3) DEST(OPX) FOR(1) * ABEND WHEN THE SELECT CRITERIA ARE MET ACTION(ABENDTER(00E60100)) SELECT * FIND THE LOCATION OF THE EVENT CODE P2,06 * ABEND WHEN THE EVENT CODE IS DSNXROUA ENTRY DR,00,X'023A'Next, run the query and collect the resulting dump data.
- No parallelism
The access path that Db2 chooses for a query is one factor that determines whether the query uses parallelism. For example, in a correlated subquery, the inner table access (single row retrieval) is not a good candidate for parallelism because the overhead of parallelism is too high. Db2 does not support parallel access for certain other access paths, such as an IN-list index scan.
Even when the access path is right for parallelism, Db2 might choose sequential access instead. Some reasons for this are:
- Host variable values limit the query to a single partition.
- ESA sort facility hardware is not available.
- Buffer pool resources are constrained.
If you think a query that is not being processed in parallel should be, collect the following information:
- A trace for IFCID 0221. This trace indicates whether buffer pools are constrained. IFCID 0221 records are written when performance trace class 8 is on.
- An SVC dump of the ssnmDBM1 address space.
- Execute the EXPLAIN statement for the problem queries and select all columns from the PLAN_TABLE. For column IBM_SERVICE_DATA, select HEX(IBM_SERVICE_DATA). The information in the PLAN_TABLE indicates whether the access path for the statement is right for parallelism.
- ABENDs
- When a parallel task abnormally terminates, the originating task returns SQLCODE -904 with reason code X'00E30101'. Db2 generates no dump for the originating task. Obtain the dump that is generated for the parallel task and analyze it as you would any other dump that is produced during a query.