Release Notes

|10.5 DB2 Registry and Environment Variables

|10.5.1 Corrections to Performance Variables


|

|Table 5. Performance Variables

Variable Name Operating System Values
Description
DB2_BINSORT All Default=NO

Values: YES or NO

Enables a new sort algorithm that reduces the CPU time and elapsed time of sorts. This new algorithm extends the extremely efficient integer sorting technique of DB2 UDB to all sort datatypes such as BIGINT, CHAR, VARCHAR, FLOAT, and DECIMAL, as well as combinations of these datatypes. To enable this new algorithm, use the following command: 
db2set DB2_BINSORT = yes
DB2_BLOCK_BASED_BP
Solaris Operating Environment Default=None

Values: dependant on parameters

Specifies the values needed to create a block area within a buffer pool. The ID of the buffer pool is needed and can be seen in the BUFFERPOOLID column of the SYSCAT.BUFFERPOOLS system catalog view. The number of pages to be allocated in the buffer pool to block-based I/O must be given. The number of pages to include in a block is optional, with a default value of 32.

The format for the use of this registry variable is: 

DB2_BLOCK_BASED_BP=BUFFER POOL ID,BLOCK AREA SIZE,[BLOCK SIZE];...

Multiple buffer pools can be defined as block-based using the same variable with a semi-colon separating the entries.

The value for BLOCK SIZE can range from 2 to 256. If no BLOCK SIZE is given, the default used is 32.

If the BLOCK AREA SIZE specified is larger than 98% of the total buffer pool size, then the buffer pool will not be made block-based. It is a good idea to always have some portion of the buffer pool in the page-based area of the buffer pool because there is a possibility of individual pages being required even if the majority of the I/O on the system is sequential prefetching. If the value specified for BLOCK AREA SIZE is not a multiple of BLOCK SIZE, it is reduced to the nearest block size boundary. For more information on block-based I/O, see 10.2.1, Block- Based Buffer Pool.

DB2_NO_FORK_CHECK
UNIX Default=OFF

Values: ON or OFF

When this variable is "ON", the client process will not protect itself against an application making a copy of the process to be run (called forking). When forking occurs, the results are unpredictable. The results could range from no effect, to some bad results, to some error code being returned, to a trap in the application. If you are certain that your application does not fork and you want better performance, you should change the value of this variable to "ON".
DB2_MINIMIZE_LIST_PREFETCH All Default=NO

Values: YES or NO

List prefetch is a special table access method that involves retrieving the qualifying RIDs from the index, sorting them by page number and then prefetching the data pages.

Sometimes the optimizer does not have accurate information to determine if list prefetch is a good access method. This might occur when predicate selectivities contain parameter markers or host variables that prevent the optimizer from using catalog statistics to determine the selectivity.

This registry variable will prevent the optimizer from considering list prefetch in such situations.

DB2_INLIST_TO_NLJN All Default=NO

Values: YES or NO

In some situations, the SQL compiler can rewrite an IN list predicate to a join. For example, the following query: 
    SELECT *
     FROM EMPLOYEE
     WHERE DEPTNO IN ('D11', 'D21', 'E21')
could be written as: 
    SELECT *
     FROM EMPLOYEE, (VALUES 'D11', 'D21', 'E21) AS V(DNO)
     WHERE DEPTNO = V.DNO

This revision might provide better performance if there is an index on DEPTNO. The list of values would be accessed first and joined to EMPLOYEE with a nested loop join using the index to apply the join predicate.

Sometimes the optimizer does not have accurate information to determine the best join method for the rewritten version of the query. This can occur if the IN list contains parameter markers or host variables which prevent the optimizer from using catalog statistics to determine the selectivity. This registry variable will cause the optimizer to favor nested loop joins to join the list of values, using the table that contributes the IN list as the inner table in the join.

| |

|10.5.2 New Parameters for Registry Variable DB2BPVARS

|The registry variable DB2BPVARS supports two new parameters: |NUMPREFETCHQUEUES and PREFETCHQUEUESIZE. These parameters are |applicable to all platforms and can be used to improve buffer-pool data |prefetching. For example, consider sequential prefetching in which the |desired PREFETCHSIZE is divided into PREFETCHSIZE/EXTENTSIZE prefetch |requests. In this case, requests are placed on prefetch queues from |which I/O servers are dispatched to perform asynchronous I/O. By |default, DB2 maintains one queue of size max( 100 , 2*NUM_IOSERVERS ) for each |database partition. In some environments, performance improves with |either more queues, queues of a different size, or both. The number of |prefetch queues should be at most one half of the number of I/O |servers. When you set these parameters, consider other parameters such |as PREFETCHSIZE, EXTENTSIZE, NUM_IOSERVERS, buffer-pool size, and |DB2_BLOCK_BASED_BP, as well as workload characteristics such as the number of |current users.

|If you think the default values are too small for your environment, first |increase the values only slightly. For example, you might set |NUMPREFETCHQUEUES=4 and PREFETCHQUEUESIZE=200. Make changes to these |parameters in a controlled manner so that you can monitor and evaluate the |effects of the change.
|

|Table 6. Summary of New Parameters

Parameter name Default value Valid range
NUMPREFETCHQUEUES 1 1 to NUM_IOSERVERS

if set to less than 1, adjusted to 1

if set to greater than NUM_IOSERVERS, adjusted to NUM_IOSERVERS

PREFETCHQUEUESIZE max(100,2*NUM_IOSERVERS) 1 to 32767

if set to less than 1, adjusted to default

if set to greater than 32767, adjusted to 32767

|10.5.3 Corrections and Additions to Miscellaneous Registry Variables

| | |

|The DB2_NEWLOGPATH2 registry variable is available for all |operating systems. A new variable, DB2_ROLLFORWARD_NORETRIEVE, has |been introduced. The correct information for both variables appears |below.
|

|Table 7. Miscellaneous Variables

Variable Name Operating System Values
Description
DB2_NEWLOGPATH2 ALL Default=NO

Values: YES or NO

This parameter allows you to specify whether a secondary path should be used to implement dual logging. The path used is generated by appending a "2" to the current value of the logpath database configuration parameter.
DB2_ROLLFORWARD_NORETRIEVE
ALL Default=(not set)

Values: YES or NO

If the database configuration parameter USEREXIT is enabled, log files are automatically retrieved from the archive during rollforward operations. The DB2_ROLLFORWARD_NORETRIEVE variable lets you specify that rollforward operations should not retrieve log files from the archive. This variable is disabled by default. Set this variable to YES if you do not want rollforward to retrieve log files automatically. For example, set the variable to YES in a hot-standby setup when want to keep log records created by a bad application from corrupting the backup system.

|10.5.4 Corrections and Additions to General Registry Variables

| |

|A new variable, DB2_REDUCED_OPTIMIZATION, has been introduced.
|

|Table 8. General Registry Variable

Variable Name Operating System Values
Description
DB2_REDUCED_OPTIMIZATION ALL Default=NO

Values: YES, NO, or any integer

This registry variable lets you disable some of the optimization techniques used at specific optimization levels. If you reduce the number of optimization techniques used, you also reduce time and resource use during optimization.
Note:
Although optimization time and resource use might be reduced, the risk of producing a less-than-optimal data access plan is increased.
  • If set to NO

    The optimizer does not change its optimization techniques.

  • If set to YES

    If the optimization level is 5 (the default) or lower, the optimizer disables some optimization techniques that might consume significant prepare time and resources but that do not usually produce a better access plan.

    If the optimization level is exactly 5, the optimizer scales back or disables some additional techniques, which might further reduce optimization time and resource use, but also further increase the risk of a less-than-optimal access plan. For optimization levels lower than 5, some of these techniques might not be in effect in any case. If they are, however, they remain in effect.

  • If set to any integer

    The effect is the same as if the value is set to YES, with the following additional behavior for dynamically prepared queries optimized at level 5: If the total number of joins in any query block exceeds the setting, then the optimizer switches to greedy join enumeration instead of disabling additional optimization techniques as described above for optimization level 5, which implies that the query will be optimized at a level similar to optimization level 2.

    For information about greedy and dynamic join enumeration, see "Search Strategies for Selecting Optimal Join" in Administration Guide: Performance.

Note that the dynamic optimization reduction at optimization level 5, as described in "Adjusting the Optimization Class" in Administration Guide: Performance, takes precedence over the behavior described for optimization level of exactly 5 when DB2_REDUCED_OPTIMIZATION is set to YES as well as over the behavior described for the integer setting.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]