Enabling parallel processing

Queries cannot take advantage of parallelism unless you enable parallel processing.

Before you begin

Db2 does not consider the use of CP parallelism if you declare a cursor as WITH HOLD and bind the application with RR or RS isolation.

Procedure

To enable parallel processing:

  • For static SQL, specify DEGREE(ANY) on BIND or REBIND.
    This bind option affects static SQL only and does not enable parallelism for dynamic statements.
  • For dynamic SQL, set the CURRENT DEGREE special register to 'ANY'.
    • Begin general-use programming interface information.You can set the special register with the following SQL statement:
      SET CURRENT DEGREE='ANY';
      End general-use programming interface information.
    • You can also change the special register default from 1 to ANY for the entire Db2 subsystem by modifying the value of the CDSSRDEF subsystem parameter.
    Setting the special register affects dynamic statements only. It has no effect on your static SQL statements. You must also make sure that parallelism is not disabled for your plan, package, or authorization ID in the RLST.
  • If you bind with isolation CS, choose also the option CURRENTDATA(NO), if possible.
    This option can improve performance in general, but it also ensures that Db2 considers parallelism for ambiguous cursors. If you bind with CURRENTDATA(YES) and Db2 cannot tell if the cursor is read-only, Db2 does not consider parallelism. When a cursor is read-only, it is best to explicitly specify that the cursor is read-only. You can use the FOR FETCH ONLY or FOR READ ONLY clause on the DECLARE CURSOR statement.
  • Specify a virtual buffer pool parallel sequential threshold (VPPSEQT) value that is large enough to provide adequate buffer pool space for parallel processing.
    If you enable parallel processing, multiple parallel tasks can be activated if Db2 estimates that high elapsed times can be reduced.
  • For parallel sorts, allocate sufficient work files to maintain performance.
    Db2 also considers only parallel I/O operations if you declare a cursor WITH HOLD and bind with isolation RR or RS.
  • For complex queries, run the query in parallel within a member of a data sharing group.
  • Limit the degree of parallelism.
    To limit the maximum number of parallel tasks that Db2 generates, you can set the value of the PARAMDEG subsystem parameter. If system resources are limited, the best value of MAX DEGREE is 1 - 2 times the number of online CPUs. However, do not change the value PARAMDEG subsystem parameter value to disable parallelism. Instead, use the DEGREE bind parameter or CURRENT DEGREE special register to disable parallelism.