Registry Variable Tuning

The DB2 registry variables have an effect on the entire DB2 instance and all databases within the instance, as well as on all applications that access any of the databases. The following registry variables have the biggest effect on performance of the instance, databases, and applications:

• DB2_USE_PAGE_CONTAINER_TAG

• DB2_HASH_JOIN

• DB2_PARALLEL_IO

• DB2_NO_PKG_LOCK

• DB2_NT_NOCACHE

• DB2_SPIN_LATCHES

DB2_USE_PAGE_CONTAINER_TAG

Background

When a DMS table space is created, the file or raw device containers will be pre-allocated by DB2. Within the first extent of each container, DB2 will create the container tag as it does with SMS table spaces.

In previous versions of DB2 UDB, the container tag was stored in a single page to minimize the space requirements. Large Storage Area Networks (SANs) and disk arrays using Redundant Array of Independent Disks (RAID) technology have become more popular, and many databases are being created on RAID-protected disks. When using a one-page container, the beginning and end of an extent could not be made to line up with the beginning and end of a stripe on the disks and normally would cause suboptimal I/O because each I/O would need to access more than one disk. If the extent size is set to be equal to or an integer multiple of the RAID stripe size, by making the container a full extent in size, the I/Os would always line up with the underlying disk stripes . In DB2 UDB Version 7, the container tag could be made a full extent using the DB2_STRIPED_CONTAINERS registry variable.

Now, in DB2 UDB Version 8, the tag is created as a full extent in size by default for DMS table spaces. To force DB2 to create the tag on a single page, the new registry variable DB2_USE_PAGE_CONTAINER_TAG should be set to ON. To activate changes to this registry variable, the DB2 instance must be stopped and restarted.

Platforms

All

Default

On for DMS table spaces, off for SMS table spaces

Values

ON, OFF

DB2_HASH_JOIN

Background

In previous versions of DB2, the DB2 optimizer would normally choose between two different join methods : a nested loop join or a merge join. When the DB2_HASH_JOIN registry variable was set to YES, the optimizer was also able to consider using a hash join when optimizing the access plan if the optimization level was 5 or higher. Because hash joins can significantly improve the performance of certain queries, especially in DSS environments where the queries are normally quite large and complex, hash joins are always available in DB2 UDB Version 8 but are still considered only when the optimization level is 5 or higher. Because hash joins require more resources than a nested loop join or a merge scan join, they can be disabled if needed.

NOTE

To disable hash joins, set the DB2_HASH_JOIN registry variable to NO.

Hash joins are discussed in more detail in Chapter 6, The DB2 Optimizer.

Platforms

All

Default

On

Values

ON, OFF

DB2_ANTIJOIN

Background

DB2 can examine incoming SQL statements to determine whether a NOT EXISTS subquery can be converted to an anti-join that DB2 is able to process more efficiently . In a nonpartitioned database, the default is for DB2 to search for these opportunities. In a partitioned database, the DB2_ANTIJOIN registry variable must be set to YES before DB2 will attempt to replace NOT EXISTS subqueries with an anti-join .

Platforms

All

Default

Yes for nonpartitioned databases; No for partitioned databases

Values

Yes, No

DB2_INLIST_TO_NLJN

Background

Under certain conditions, the query rewrite facility of the optimizer can rewrite an IN list to a join, which can provide better performance if there is an index on the column with the IN list. In this case, the list of values would be accessed first and joined to the base table with a nested loop join, using the index to apply the join predicate.

If the IN list contains parameter markers or host variables, the optimizer does not have accurate information to determine the best join method for the rewritten version of the query. In this case, this registry variable causes 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.

For example, the following query:

  SELECT * FROM EMPLOYEE   WHERE DEPTNO IN ('D11 ','D21 ','E21 ')  

could be rewritten as:

  SELECT * FROM EMPLOYEE   (VALUES 'D11 ','D21 ','E21)AS V(DNO)   WHERE DEPTNO =V.DNO  

Platforms

All

Default

No

Values

Yes, No

DB2_CORRELATED_PREDICATES

Background

When this registry variable is set to YES, the optimizer uses the KEYCARD statistical information about the unique indexes in an attempt to detect cases of correlation between join predicates. If correlation is detected , the optimizer will dynamically adjust the combined selectiveness of the correlated predicates, obtaining a more accurate estimate of the join size and total query cost.

Adjustment can also be done for correlation of simple equality predicates if there is an index on the columns in the predicates. In this case, the index does not need to be unique, but the columns in the equality predicates of the SQL statements must include all columns in the index. In this example:

  WHERE C1=5 AND C2=10  

if there is an index on C1 and C2, the optimizer will look for correlation between the columns.

Platforms

All

Default

Yes

Values

Yes, No

DB2_REDUCED_OPTIMIZATION

Background

The DB2_REDUCED_OPTIMIZATION registry variable tells the DB2 UDB optimizer to reduce the available optimization features at the specified optimization level. By reducing the number of optimization techniques used, the time and resource use during optimization are also reduced.

NOTE

Although optimization time and resource use might be reduced, the risk of producing a less-than -optimal data access plan is increased.

When the registry variable is set to NO, the optimizer does not change its optimization techniques. However, when the registry variable is set to YES:

• If the optimization level is 5 (the default) or lower, the optimizer disables some optimization techniques that might consume significant preparation time and resources but 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 increases 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 the registry variable is set to any integer value, the effect is the same as though it were set to YES. However, in addition, when dynamically prepared queries are optimized at level 5:

• If the total number of joins in any query block exceeds the setting of the DB2_REDUCED_OPTIMIZATION registry variable, the optimizer will switch to greedy join enumeration instead of disabling the additional optimization techniques, as described above.

When the optimization level is set to 5, the optimizer can dynamically reduce the optimization for dynamically executed queries. If the DB2_REDUCED_OPTIMIZATION registry variable is set to DISABLE, the optimization level cannot be reduced at level 5, and the optimizer will perform full level 5 optimization.

NOTE

The dynamic optimization reduction at optimization level 5 takes precedence over the behavior described for optimization level of exactly 5 when DB2_REDUCED_OPTIMIZATION is set to YES, as well as the behavior described for the integer setting.

Platforms

All

Default

No

Values

Yes, No, Disable, any integer value

DB2_OVERRIDE_BPF

Background

When a database is activated or started (i.e., during the first connection to the database), DB2 automatically creates four hidden buffer pools for the database, in addition to the IBMDEFAULTBP and any user -created buffer pools. These buffer pools are hidden and do not have entries in the system catalog tables. In addition, these buffer pools cannot be used directly by assigning table spaces to them and cannot be altered .

There will be one hidden buffer pool per page size (i.e., 4 KB, 8 KB, 16 KB, and 32 KB) to ensure that there is a buffer pool available under all circumstances. DB2 UDB will use these buffer pools under the following conditions:

• When the CREATE BUFFERPOOL statement is executed and the IMMEDIATE option is specified but there is not enough memory available to allocate the buffer pool

  • If this occurs, a message is written to the administration notification log.

  • Any table spaces that are using the buffer pool will be remapped to the hidden buffer pool with the same page size.

• When the IBMDEFAULTBP and/or any of the user-created buffer pools cannot be allocated when the database is activated or started

  • If this occurs, a message is written to the administration notification log.

  • Any table space that is using a buffer pool that was not allocated will be remapped to the hidden buffer pool with the same page size.

  • DB2 will be fully functional because of the hidden buffer pools, but performance will be drastically reduced.

• When a table space is created and its page size does not correspond to the page size of any of the user-created buffer pools

• During a roll forward operation if a buffer pool is created and the DEFERRED option is specified

  • Any table spaces that are created and assigned to this buffer pool will be remapped to the hidden buffer pool with the same page size for the duration of the roll forward operation.

By default, the hidden buffer pools will be created with a size of 16 pages. This can be changed using the DB2_OVERRIDE_BPF registry variable.

Platforms

All

Default

Not set

Values

Any positive integer

DB2_PINNED_BP

Background

The buffer pools are the work area for the database, and all searching for and manipulation of the data and indexes must take place within the buffer pools. In order for DB2 to scan a table or an index, the pages of the table or index must be in the database's buffer pool (or buffer pools). In a constrained system, the operating system may potentially swap/page the buffer pool out of the server's real memory. If the buffer pool is swapped/paged out of real memory, it must be first read from disk before it can be manipulated. Keeping the buffer pool(s) in the server's real memory allows database performance to be more consistent.

The DB2_PINNED_BP registry variable is used to ensure that the buffer pools for all databases in the DB2 instance are kept in the server's real memory. This registry variable is used on both AIX and HP/UX; however, when used for a 64-bit DB2 instance in HP/UX, the DB2 instance group must also be given the MLOCK privilege.

To do this, a user with root access rights must perform the following actions:

1. Add the DB2 instance group to the / etc/privgroup file.

2. Issue the command setprivgrp -f /etc/privgroup.

For example, if the DB2 instance group belongs to db2inst1 group, the following line must be added to the /etc/privgroup file:

  db2inst1 MLOCK  

Platforms

AIX, HP/UX

Default

No

Values

Yes, No

DB2_FORCE_FCM_BP

Background

For a multi-partitioned database on AIX, when the DB2 instance is started, the FCM buffers used for inter-partition communication are allocated from either the database global memory or from a separate shared memory segment, if there is not enough global memory available. If the DB2_FORCE_FCM_BP registry variable is set to YES, the FCM buffers are created in a shared memory segment. Otherwise, the database partitions on the same server node will communicate through UNIX sockets.

Communicating through shared memory is faster than UNIX sockets. However, if the DB2 instance is created as a 32-bit instance, this will use a whole segment of shared memory and, therefore, reduce the number of shared memory segments available for other uses, particularly for database buffer pools.

Platforms

AIX

Default

No

Values

Yes, No

DB2_AWE

Background

Address Windowing Extensions (AWE), allows the allocation of up to 64 GB of shared memory on 32-bit Windows 2000 and Windows XP servers. To use this registry variable, Windows must be configured correctly to support AWE, and the DB2 instance owner must also be assigned the "lock pages in memory" right.

NOTE

If AWE support is enabled, extended storage cannot be used for any of the buffer pools in the database. Also, buffer pools referenced with this registry variable must already exist.

Platforms

Windows 2000, Windows XP

Default

NULL

Values

X,Y where X = buffer pool ID and Y = number of pages for the buffer pool

DB2_BINSORT

Background

The DB2_BINSORT registry variable enables a new binary sort algorithm that reduces the CPU usage and overall elapsed time of sorts. This new algorithm extends the extremely efficient binary sorting technique for integer data types to all data types, such as BIGINT, CHAR, VARCHAR, FLOAT, and DECIMAL, as well as combinations of these data types.

Platforms

All

Default

Yes

Values

Yes, No

DB2_AVOID_PREFETCH

Background

The DB2_AVOID_PREFETCH registry variable specifies whether prefetching should be used during restart recovery of a database. By default, prefetching will be used; however, if DB2_AVOID_PREFETCH is set to YES, DB2 will not perform prefetching during restart recovery.

Platforms

All

Default

Off

Values

On, Off

DB2TCPCONNMGRS

Background

TCP/IP connection managers are processes or threads that are running in a DB2 instance to accept incoming connection requests from clients using the TCP/IP protocol. If the DB2TCPCONNMGRS registry variable is not set, the default number of connection manager processes or threads is created. If the DB2TCPCONNMGRS registry variable is set to a value between 1 and 8, the specified number of connection manager processes or threads is created. If the DB2TCPCONNMGRS registry variable is set to a value less than 1, one connection manager process or thread is created.

NOTE

Having the number of connection managers set to 1 can limit performance for remote connections in databases with a lot of users, frequent connects and disconnects, or both.

Platforms

All

Default

The square root of the number of CPUs in the server, rounded up to a maximum of 8 on an SMP server.

Values

1, 2, 3, 4, 5, 6, 7, 8

DB2MAXFSCRSEARCH

Background

To insert a record, DB2 must find enough contiguous free space on a page for the record. Inserts can occur in three modes:

• Scan mode

• Append mode

• Base on clustering index

When the insert is operating in scan mode, DB2 must scan the free space maps in the object for a page with enough contiguous free space. To speed up the searching for free space, DB2 stores a free space control record (FSCR) on the first page of an object and on every 500th page of the object. Then, when DB2 is scanning for free space, it needs to read only every 500th page and examine the FSCR to determine whether any of the next 500 pages contains enough free space to hold the record.

The DB2MAXFSCRSEARCH registry variable applies to the scan mode only. When in scan mode, the DB2MAXFSCRSEARCH registry variable specifies how many FSCRs to search for free space before switching to append mode. To optimize insert performance, DB2 will switch to append mode after searching for free space in five FSCRs. Larger values for the DB2MAXFSCRSEARCH registry variable will optimize space reuse, and smaller values will optimize for insert speed. Setting the DB2MAXFSCRSEARCH registry variable to -1 forces DB2 to search all FSCRs before switching to append mode for the insert.

The first insert into a table after the database is activated will start at the beginning of the object. For example, when inserting a record into a table, DB2 will scan the FSCR on page 0; if space is available on pages 0499 for the record, it will be inserted. If no space is available, it will check the FSCR on page 500. If space is available on pages 500999 for the record, it will be inserted. If no space is available, it will check the FSCR on page 1000. If space is available on pages 10001499 for the record, it will be inserted. If no space is available, it will check the FSCR on page 1500.

In this case, the FSCR on page 1000 indicated there was enough contiguous free space on page 1002 for the record to be inserted, so DB2 will read page 1002 into the buffer pool and insert the record.

To optimize subsequent insert performance, DB2 will keep an FSPR pointer (FSCR Ptr) on page 1000 because DB2 knows there was insufficient space on any page prior to this, so there is no sense in rescanning these pages unless a record is deleted. If a record on pages 0 through 999 is deleted, the FSCR Ptr will be moved to the FSCR for the page where the record was deleted.

For example, if a record is deleted from page 833, the FSCR Ptr will be moved to the FSCR for page 833, page 500 in this case, as shown in Figure 8.16.

Figure 8.16. Free space control record pointer.

In this case, the next scan for free space will start at page 500, not at the beginning of the object. This is a relatively simple example, but if the FSCR Ptr is on page 25500 and a delete occurs on page 23328, the FSCR Ptr will be moved to page 23000, not back to the beginning of the table.

Platforms

All

Default

5

Values

1, 133554

DB2_SELECTIVITY

Background

The DB2 optimizer uses the catalog statistics to estimate the selectivity of the predicates in the SQL statement when determining the optimal access plan for the statement. In some cases, the estimates can be significantly different from the real data, especially if the SQL statement uses host variables and can, therefore, produce inefficient access plans. If the selectivity of a particular predicate is known, it can be specified in the SQL statement as follows :

  --+-----+--+-predicate--+-------------------------------+-+---->   '-NOT-'              '-SELECTIVITY--numeric-constant-'   '-(search-condition)---------------------------'   .------------------------------------------------------------------------.   V   >----+--------------------------------------------------------------------+-+--   '-+-AND-+--+-----+--+-predicate--+-------------------------------+-+-'   '-OR--'  '-NOT-'              '-SELECTIVITY--numeric-constant-'   '-(search-condition)---------------------------'  

The selectivity clause requires that the DB2_SELECTIVITY registry variable be set to YES and that:

• The predicate be a basic predicate where at least one expression contains host variables

• The predicate is a LIKE predicate where the match expression, predicate expression, or escape expression contain host variables

Platforms

All

Default

No

Values

Yes, No

DB2_PRED_FACTORIZE

Background

The DB2 optimizer can rewrite SQL queries to make them more efficient by adding additional predicates from disjuncts in the query. In some circumstances, these additional predicates can alter the estimated cardinality of the intermediate and final result sets and produce better access plans. The DB2_PRED_FACTORIZE registry variable specifies whether the query rewrite facility can look for opportunities to add these additional predicates. If the DB2_PRED_FACTORIZE registry variable is set to YES, the following query:

  SELECT n1.empno, n1.lastname   FROM employee n1, employee n2   WHERE   ((n1.lastname='SNOW '   AND n2.lastname='PHAN ')   OR (n1.lastname='PHAN '   AND n2.lastname='SNOW '))  

could be rewritten by the optimizer to include the following additional predicates:

  SELECT n1.empno, n1.lastname   FROM employee n1, employee n2   WHERE n1.lastname IN ('SNOW ','PHAN ')   AND n2.lastname IN ('SNOW ','PHAN ')   AND ((n1.lastname='SNOW '   AND n2.lastname='PHAN ')   OR (n1.lastname='PHAN '   AND n2.lastname='SNOW '))  

Platforms

All

Default

No

Values

Yes, No

DB2MEMDISCLAIM

Background

On AIX, memory used by DB2 agent processes may retain some associated paging space. This paging space may remain reserved, even when the associated memory is freed by the DB2 agent process. Whether this paging space is released depends on the AIX system's virtual memory management allocation policy. The DB2MEMDISCLAIM registry variable controls whether DB2 agents can explicitly request that AIX de-allocate the reserved paging space when the memory is freed.

The DB2MEMDISCLAIM registry variable, when set to YES, can result in smaller paging space requirements and possibly less disk activity from paging. On a DB2 server where paging space is plentiful and there is enough real memory so that paging never occurs, setting the DB2MEMDISCLAIM registry variable to NO can provide a minor performance improvement.

The DB2MEMDISCLAIM registry variable should be used in conjunction with the DB2MEMMAXFREE registry variable.

Platforms

AIX

Default

Yes

Values

Yes, No

DB2MEMMAXFREE

Background

This registry variable specifies the maximum number of bytes of unused private memory that is retained by DB2 agent processes before the unused paging space is returned to the operating system. The DB2MEMMAXFREE registry variable should be used in conjunction with the DB2MEMDISCLAIM registry variable.

Platforms

AIX

Default

8388608

Values

Any positive integer value

DB2_MMAP_READ

Background

DB2_MMAP_READ is used in conjunction with DB2_MMAP_WRITE to allow DB2 to use memory-mapped I/O, which can be more efficient. In most environments, memory-mapped I/O should be used to avoid operating system locks when multiple processes are reading from and writing to different sections of the same file. However, when memory-mapped I/O is enabled, DB2 cannot read from JFS file system cache.

Platforms

AIX

Default

On

Values

Off, On

DB2_MMAP_WRITE

Background

This registry variable is used in conjunction with DB2_MMAP_READ, above, to allow DB2 to use memory-mapped I/O, which can be more efficient. In most environments, memory-mapped I/O should be used to avoid operating system locks when multiple processes are reading from and writing to different sections of the same file. However, when memory-mapped I/O is enabled, DB2 cannot read from JFS file system cache.

Platforms

AIX

Default

On

Values

Off, On