Determine if a query problem is related to parallelism

The first question to ask when you encounter a problem in a parallel query is whether that problem is related to parallelism. To answer that question, disable all parallelism, then try the query again.

If the problem occurs whether or not you have parallelism enabled, you can assume that it is unrelated to parallelism. If the problem occurs only with parallelism enabled, follow these steps to determine which type of parallelism is causing the problem:

Disable all parallelism. Run the problem query again.
If the problem still occurs, enable CP parallelism. Run the query again.

When you run the query with CP parallelism, issue the following commands and collect the output:

DISPLAY BUFFERPOOL: The output shows the buffer pool thresholds for parallelism. Issue this command for all constrained buffer pools.
DISPLAY THREAD: Issue this command if you suspect a hang problem. The output shows PT in the status column for parallel threads. It also indicates which task is the originating task.

Disabling all parallelism

To disable parallelism, take one of the following actions:

For queries that use static SQL statements, bind the plan or package that contains the queries with the option DEGREE(1).
For queries that use dynamic SQL statements, run the following SQL statement before you run the queries:
```
SET CURRENT DEGREE='1'
```

Disabling CP Parallelism

To disable CP parallelism and leave I/O parallelism enabled

Determine which processors are online by issuing the z/OS® command:
```
D M=CPU
```
Vary all but one processor offline by issuing the z/OS command:
```
CF CPU(n),OFFLINE
```

Disabling parallelism by using the resource limit facility

If you use the resource limit facility (RLF), you can disable CP parallelism for static or dynamic SQL statements by specifying the RLST values shown in the following table. Use RLF to disable parallelism for static statements only for diagnosis.

Table 1. RLST values for disabling parallelism
RLFFUNC	RLFBIND	Effect
4	blank	Disable CP parallelism for dynamic statements
4	S	Disable CP parallelism for static plans or packages
4	A	Disable CP parallelism for static and dynamic statements

These RLST values do not affect the way RLF governs the processor time for dynamic statements. To use RLF to change the parallelism mode for a plan or package, you must rebind the plan or package while RLF is active. For data sharing, RLF must be active on the Db2 member where the rebind occurs.