| 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 | 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 版本 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. |
|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 6. Miscellaneous Variables
|A new variable, DB2_REDUCED_OPTIMIZATION, has been introduced.
|
| Table 7. 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 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. |