Administration: Performance

8 8 8

New system environment variables (Linux)

The DB2_MAPPED_BASE and DB2DBMSADDR system environment variables have been 8added at FixPak 8.

Use of these registry variables is only recommended for advanced users.

8DB2_MAPPED_BASE 8

Variable name

88DB2_MAPPED_BASE 8

Values

880 OR (hex) virtual address in the 31-bit and 32-bit address range OR 8NULL (not set) 8

Operating systems

88Linux on x86 and Linux on zSeries (31-bit) 8

Description

88The DB2_MAPPED_BASE registry variable can be used to increase the amount 8of contiguous virtual address space available to a DB2 Universal Database (UDB) process by relocating the attachment address of the shared libraries 8for the specific process. The contiguous virtual address space is important 8to maximize the amount of database shared memory available to DB2 UDB. This variable 8is only effective on distributions that include the mapped_base file in the 8process identification directory in the proc file system. 8

DB2 UDB will attempt 8to relocate the shared libraries to the virtual address 0x20000000 if this 8variable is not set.

The registry variable can also be set to any virtual 8address (in hex) in the range of the 31 and 32-bit address space if the new 8address places the shared libraries lower in the address space.

8 8

Note:

An incorrect address can cause severe issues with DB2 UDB, ranging from an inability to start DB2 UDB 8to an inability to connect to the database. An incorrect address is one that 8collides with an area in memory that is already in use or is predestined to 8be used for something else. To address this problem, reset the DB2_MAPPED_BASE 8variable to NULL by using the following command: 8

db2set DB2_MAPPED_BASE=

The following message may appear multiple 8times in the db2diag.log file because this change is required once per logical 8node:

ADM0506I  DB2 has automatically updated the "mapped_base" 
8kernel parameter from "0x40000000(hex) 1073741824(dec)" to 
8the recommended value "0x20000000(hex) 536870912(dec)".

This message 8will only appear if setting of the registry variable successful, and it will 8include the address that the shared libraries are relocated to.

8DB2DBMSADDR 8

Variable name

88DB2DBMSADDR 8

Values

88Virtual addresses in the range 0x09000000 to 0xB0000000 in increments 8of 0x10000 8

Operating systems

88Linux on x86 and Linux on zSeries (31-bit) 8

Description

88Specifies the default database shared memory address in hexadecimal 8format. 8 8

Note:

An incorrect address can cause severe issues with DB2 UDB, 8ranging from an inability to start DB2 UDB, to an inability to connect to the 8database. An example of an incorrect address is one that collides with an 8area in memory that is already in use or predestined to be used for something 8else. To address this problem, reset the DB2DBMSADDR variable to NULL by using 8the following command: 8

db2set DB2DBMSADDR=

This variable can be set in conjunction with DB2_MAPPED_BASE or alone to 8fine tune the address space layout of DB2 UDB processes. This variable changes 8the location of the instance shared memory from it's current location at virtual 8address 0x10000000 to the new value given.

7 7 7

New communication registry variable

The DB2TCP_CLIENT_RCVTIMEOUT registry variable has been added in Version 78.2.

7 77777777777777777777777

Table 11. Communications variables
Variable name	Operating systems	Values
Description
DB2TCP_CLIENT_RCVTIMEOUT	All	7 Default=0 (not set) 7 Values: 0 to 732767 seconds
7 Specifies 7the number of seconds a client waits for data on a TCP/IP receive. 7 There is no timeout if the registry variable is not set or is set to 0. If 7the TCP/IP receive returns with data before the timeout value has expired, 7the application proceeds as usual. If the timeout value expires before data 7is returned, the connection closes. 7 7 7 Note: 7 This registry variable 7is applicable to the DB2 Client and the client side of the DB2 Gateway only. 7 It is not applicable to the DB2 Server.

7 7 7

New performance variable

The DB2_LARGE_PAGE_MEM performance variable has been added in Version 78.2.

7 77777777777777777777777

Table 12. Performance variables
Variable name	Operating systems	Values
Description
DB2_LARGE_PAGE_MEM	7 AIX 5.x 64-bit only 7 Linux	Default=NULL 7 Use * to denote that all applicable 7memory regions should use large page memory, or a comma-separated list of 7specific memory regions that should use large page memory. Available regions 7vary by operating system. On AIX 5.x 64-bit, the following regions can 7be specified: DB, DBMS, or PRIVATE. On Linux, the following region can be specified: 7DB.
7 Large page memory is supported only 7for DB2 Universal Database (UDB) for AIX 5L, 64-bit Edition, 7and DB2 UDB for Linux. 7 The DB2_LARGE_PAGE_MEM registry variable 7is used to enable large page support when running on AIX 5.x or any Linux architecture with the appropriate 7kernel support. This registry variable deprecates the DB2_LGPAGE_BP 7registry variable, which can only be used to enable large-page memory for 7the database shared memory region. This can now be enabled by setting DB2_LARGE_PAGE_MEM=DB. 7 Any documentation that mentions enabling large pages with the DB2_LGPAGE_BP 7registry variable can be treated as synonymous with setting DB2_LARGE_PAGE_MEM=DB. 7 Large page usage is primarily intended to provide performance improvements 7to high performance computing applications. Memory access intensive applications 7that use large amounts of virtual memory may obtain performance improvements 7by using large pages. To enable DB2 UDB to use large pages, you must first 7configure the operating system to use large pages. 7 Enabling large private 7pages will increase DB2 UDB memory usage by a significant amount, as each DB2 UDB 7agent will consume at least 1 large page (16MB) of physical memory. To enable 7large pages for agent private memory on 64-bit DB2 UDB for AIX (the DB2_LARGE_PAGE_MEM=PRIVATE setting), 7the following conditions must be met, in addition to configuring large pages 7on the operating system: 7 7 The instance owner must possess the CAP_BYPASS_RAC_VMM and CAP_PROPOGATE 7capabilities. 7 The kernel must support interfaces that allow a process to modify its 7page size at run time. . 7 On 64-bit DB2 UDB for AIX, enabling this variable reduces the size 7of the shared memory segment backing database memory to the minimum requirement. 7 The default is to create a 64 GB segment: see the database shared memory 7size (database_memory) database configuration parameter 7for more details. This avoids pinning more shared memory in RAM than is likely 7to be used. 7 By setting this variable, the ability to dynamically increase 7the overall database shared memory configuration (for example, to increase 7the size of buffer pools) will be limited. 7 On Linux, there is 7an additional requirement for the availability of the libcap.so library. This library must be installed for this option to work. If 7this option is turned on, and the library is not on the system, DB2 UDB will disable 7the large kernel pages and continue to function as it would previously. 7 On Linux, to verify that large kernel pages are available, issue the following 7command: 7 cat /proc/meminfo 7 If it is available, the following 7three lines should appear (with different numbers depending on the amount 7of memory configured on your machine): 7 HugePages_Total: 200 7 HugePages_Free: 200 7 Hugepagesize: 16384 KB 7 If you do not see these lines, or 7if the HugePages_Total is 0, configuration of the operating system or kernel 7is required.

8 8 8

SQL compiler variables

The following update applies to the topic "SQL compiler variables" in 8Appendix A "DB2 registry and environment variables" of the Administration Guide: Performance:

When either or both of the DB2 compiler variables DB2_MINIMIZE_LISTPREFETCH 8and DB2_INLIST_TO_NLJN, are set to ON, they remain active even if 8REOPT(ONCE) is specified.

8 8 8

Configuration parameter updates

Following are the updates to the configuration parameter documentation:

8 8

authentication - Authentication type

The Authentication type (authentication) database manager configuration 8parameter also accepts the following values:

DATA_ENCRYPT 8
The server accepts encrypted SERVER authentication 8schemes and the encryption of user data. The authentication works exactly 8the same way as SERVER_ENCRYPT.
8
The following user data are encrypted 8when using this authentication type:
8
- SQL statements.
- SQL program variable data.
- Output data from the server processing an SQL statement and including 8a description of the data.
- Some or all of the answer set data resulting from a query.
- Large object (LOB) data streaming.
- SQLDA descriptors.

DATA_ENCRYPT_CMP 8
The server accepts encrypted SERVER authentication 8schemes and the encryption of user data. In addition, this authentication 8type allows compatibility with earlier products that do not support DATA_ENCRYPT 8authentication type. These products are permitted to connect with the SERVER_ENCRYPT 8authentication type and without encrypting user data. Products supporting 8the new authentication type must use it. This authentication type is only 8valid in the server's database manager configuration file and is not valid 8when used on the CATALOG DATABASE command.

8 8

util_impact_lim - Instance impact policy

Starting with DB2 Universal Database Version 8.2, the default 8value of the Instance impact policy (util_impact_lim) database manager configuration parameter changes 8from 100 to 10.

8 8

sysadm_group, sysmaint_group, sysctrl_group, sysmon_group

The following database manager configuration parameters can all accept 8group names of 30 bytes (or less) on all platforms:

System administration authority group name (sysadm_group)

System maintenance authority group name (sysmaint_group)

System control group authority name (sysctrl_group)

System monitor authority group name (sysmon_group)

The table in the topic "Database manager configuration parameter summary" 8contains incorrect data types for these database manager configuration parameters. 8The correct value in all cases is char(30).

8 8

estore_seg_sz - Extended storage memory segment size

The maximum size for the Extended storage memory 8segment size database (estore_seg_size) 8configuration parameter on Windows based platforms is 16 777 216.

8 8

hadr_timeout - HADR timeout value

The correct upper limit of the HADR timeout value (hadr_timeout) database configuration parameter 8is 4 294 967 295.

8 8

locklist - Maximum storage for lock list

The documentation for the Maximum storage for locklist (locklist) database configuration parameter 8states that the maximum value for Windows 64-bit and 32-bit servers that 8service only local clients is 60 000. This value is incorrect, and 8should be 524 288.

8 8

num_db_backups - Number of database backups

The range of values for the Number of database backups (num_db_backups database configuration 8parameter is incorrect. The correct range is 0 - 32 767.

8 8 8

SQLDBCONF database configuration parameter file

After migrating to DB2 Universal Database (UDB) Version 8.2 from 8Version 8.1, DB2 UDB uses a new 16 KB database configuration parameter file named 8SQLDBCONF. (In Version 8.1, the database configuration parameter file was 8only 4 KB and named SQLDBCON).

8 8 8

Change to the DB2_HASH_JOIN default value

As of Version 8.1 the registry variable DB2_HASH_JOIN is set to ON by default.

The hash-join variable should be used, but it needs to be tuned to get 8the best performance.

Hash-join performance is best if you can avoid hash loops and overflow 8to disk. To tune hash-join performance, estimate the maximum amount of memory 8available for the sheapthres parameter , then 8tune the sortheap parameter. Increase its value 8until you avoid as many hash loops and disk overflows as possible, but do 8not reach the limit specified by the sheapthres 8parameter.

For more information, see the "Join methods" topic in the Administration Guide: Performance manual.

7 7 7

DB2NTNOCACHE registry variable is deprecated

Functionality previously achieved through DB2NTNOCACHE can be achieved 7at the table space level by specifying the NO FILE SYSTEM CACHING clause on 7the CREATE TABLESPACE or the ALTER TABLESPACE statement. Refer to SQL Reference for details on usage. The DB2NTNOCACHE registry variable 7will be removed in a future release.

7 7 7

Explain tables and organization of explain information

Explain tables might be common to more than one user. However, the explain 7tables can be defined for one user, and aliases can be defined for each additional 7user using the same name to point to the defined tables. Alternatively, the 7explain tables can be defined under the SYSTOOLS schema. The Explain facility 7will default to the SYSTOOLS schema if no other explain tables or aliases 7are found under the user's session ID for dynamic SQL, or the statement authorization 7ID for static SQL. Each user sharing the common explain tables must have insert 7permission on those tables. Read permission for the common explain tables 7should also be limited, typically to users who analyze the explain information.

7 7 7

Guidelines for capturing explain information

Explain data is captured if you request it when an SQL statement is compiled. 7Consider how you expect to use the captured information when you request explain 7data.

7Capturing information in the explain tables 7

Dynamic SQL statements: 7
Explain table information is captured in any 7of the following cases:
7
- The CURRENT EXPLAIN MODE special register is set to: 7
  - YES: The SQL compiler captures explain data and executes the SQL statement.
  - EXPLAIN: The SQL compiler captures explain data but does not execute the 7SQL statement.
  - RECOMMEND INDEXES: The SQL compiler captures explain data and the recommended 7indexes are placed in the ADVISE_INDEX table, but the SQL statement is not 7executed.
  - EVALUATE INDEXES: The SQL compiler uses indexes placed by the user in 7the ADVISE_INDEX table for evaluation. In EVALUATE INDEXES mode, all dynamic 7statements are explained as if these virtual indexes were available. The SQL 7compiler then chooses to use the virtual indexes if they improve the performance 7of the statements. Otherwise, the indexes are ignored. To find out if proposed 7indexes are useful, review the EXPLAIN results.
  - REOPT: The SQL compiler captures Explain data for static or dynamic SQL 7statements during statement reoptimization at execution time, when actual 7values for the host variables, special registers, or parameter markers are 7available.
- The EXPLAIN ALL option has been specified on the BIND or PREP command. 7The SQL compiler captures explain data for dynamic SQL at run time, even if 7the CURRENT EXPLAIN MODE special register is set to NO. The SQL statement 7also executes and returns the results of the query.

7 7 7

Additional return codes from db2CfgGet API, collate_info parameter

The collating information parameter can only be displayed using the db2CfgGet 7API. It cannot be displayed through the command line processor or the Control Center.

Configuration Type: Database 7
Parameter Type: Informational 7

This parameter provides 260 bytes of database collating information. The 7first 256 bytes specify the database collating sequence, where byte "n" contains 7the sort weight of the code point whose underlying decimal representation 7is "n" in the code page of the database.

The last 4 bytes contain internal information about the type of the collating 7sequence. The last 4 bytes of collate_info is an integer. The integer is sensitive 7to the endian order of the platform. The possible values are:

0 - The sequence contains non-unique weights

1 - The sequence contains all unique weights

2 - The sequence is the identity sequence, 7for which strings are compared byte for byte.

73 - The sequence is NLSCHAR, used 7for sorting characters in a TIS620-1 (code page 874) Thai database.7

74 - The sequence is IDENTITY_16BIT, 7which implements the "CESU-8 Compatibility Encoding Scheme for UTF-16: 78-Bit" algorithm as specified in the Unicode Technical Report #26 available 7at the Unicode Technical Consortium Web site at http://www.unicode.org.7

7X'8001' - The sequence 7is UCA400_NO, which implements the UCA (Unicode Collation Algorithm) based 7on the Unicode Standard version 4.00, with normalization implicitly set to 7ON.7

7X'8002' - The sequence 7is UCA400_LTH, which implements the UCA (Unicode Collation Algorithm) based 7on the Unicode Standard version 4.00, and sorts all Thai characters as per 7the Royal Thai Dictionary order.7

7X'8003' - The sequence 7is UCA400_LSK. which implements the UCA (Unicode Collation Algorithm) based 7on the Unicode Standard version 4.00, and sorts all Slovakian characters properly.7

If you use this internal type information, you need to consider byte reversal 7when retrieving information for a database on a different platform.

You can specify the collating sequence at database creation time.

8 8 8

Automatic setting of default prefetch size and update defaults

Starting with DB2 Universal Database (UDB) Version 8.2, you 8can use AUTOMATIC prefetch size for a table space. DB2 UDB automatically updates the prefetch 8size when the number of containers changes for the table space.

The syntax of the DB2_PARALLEL_IO registry variable is expanded to recognize 8containers with different I/O parallelism characteristics. Through the expanded 8syntax, containers for different table spaces can have different I/O parallelism 8characteristics. The I/O parallelism characteristic of each table space is 8used when a prefetch size of AUTOMATIC is specified for the table space. 8If the DB2_PARALLEL_IO registry variable is enabled but the expanded syntax 8identifying specific I/O parallelism characteristics for table spaces is not 8used, a default level of parallelism is assumed. The default level is RAID 85 (6+1).

The prefetch size information used by the optimizer is refreshed only when 8an ALTER TABLESPACE statement that changes the prefetch size of a table space 8or changes the number of containers (using ADD/DROP/BEGIN NEW STRIPE SET/ADD 8TO NEW STRIPE SET) is issued. If the number of physical disks per container 8registry settings changes, an ALTER TABLESPACE <table 8space name> PREFETCHSIZE AUTOMATIC statement should be issued to refresh 8the optimizer information (unless an ALTER TABLESPACE statement that refreshes 8the optimizer information is already issued).

If a table space is redirected or restored to use a different number of 8containers, refresh the optimizer information by issuing an ALTER TABLESPACE <table space name> PREFETCHSIZE AUTOMATIC statement. 8If there are multiple stripe sets within a table space, the maximum number 8of containers among the stripe sets is used to calculated the prefetch size. 8If the calculated prefetch size exceeds the maximum size (32 767 pages), the 8largest multiple of the number of containers that is smaller than the maximum 8is used as the prefetch size.

In a DB2 UDB Enterprise Server Edition environment, if a table space uses 8an AUTOMATIC prefetch size, the prefetch size might be different on different 8database partitions. This situation can exist because different database partitions 8can have different numbers of containers used for calculating the prefetch 8size. To generate the query access plan, the optimizer uses the prefetch 8size from the first partition in a database partition group.

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