|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. |
|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 |
|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
|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 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. |