When you enable the DB2_FORCE_FCM_BP registry variable there is one fewer |shared memory segments available for other uses, particularly for database |buffer pools. Enabling the DB2_FORCE_FCM_BP registry variable thus reduces |the maximum size of database buffer pools. Note that because of the large |number of available shared memory segments in a 64-bit environment, this reduction |in the number of shared memory segments should be an issue only in 32-bit |environments.
| | |When a table is first created, system catalog statistics are set to -1 |to indicate that the table has no statistics. Until statistics are gathered, |DB2 UDB uses default values for SQL statement compilation and optimization. | Updating the table or index statistics might fail if the new values are inconsistent |with the default values. Therefore, run the runstats command on a table or index before manually updating statistics for |either.
| | |SQL error message SQL1169N has a new reason code 5 to indicate that a column |of an explain table is too small.
|The following text is an update to Administration |Guide: Performance, Chapter 6. Understanding the SQL compiler.
|MDC roll out may be used even if a RID index is part of the optimization |plan regardless of the presence of a WHERE clause in the DELETE statement. |As a result, when listing the conditions that must be met to allow roll out |and the use of a more efficient way to delete rows, the condition that a "RID index was not chosen by the optimizer to find the rows to be deleted, |unless there is no WHERE clause in the DELETE statement" should be removed.
|Further, you are able to tell if MDC roll out is in effect because db2expln output shows the phrase "Cell Delete". | Note that db2exfmt does not show this information.
|The following text is an update to Appendix A. |DB2 Registry and Environment Variables:
|The description of DB2_MDC_ROLLOUT should be changed such that the condition |that a "RID index was not chosen by the optimizer to find the rows to be |deleted, unless there is no WHERE clause in the DELETE statement" should |be removed from the list.
| | |If you update the newlogpath, mirrorpath, or overflowlogpath configuration parameter values in a DB2 UDB Enterprise Server Edition |environment, the node number will be appended to the path name regardless |of the number of nodes on the system. This applies to both single-partition |and multi-partition systems in a DB2 UDB Enterprise Server Edition environment.
| | |The default value for DB2_COLLECT_TS_REC_INFO is ON. In DB2 UDB V 8.1 FixPak 7, the default value for the DB2_COLLECT_TS_REC_INFO |registry variable was changed to ON. The current |documentation incorrectly specifies the default for this variable as OFF.
A governor instance consists of a front-end utility and one or more daemons. Each instance of the governor that you start is specific to an instance of the database manager. By default, when you start the governor a governor daemon starts on each partition of a partitioned database. However, you can specify that a daemon be started on a single partition that you want to monitor.
Each governor daemon collects information about the applications that run against the database. The governor daemon then checks this information against the rules that you specify in the governor configuration file for this database.
When considering in-place table reorganization (instead of classic table reorganization), be aware that in-place table reorganization requires more log space.
Because in-place table reorganization logs its activities so that recovery is possible after an unexpected failure, it requires more log space than classic reorganization.
It is possible that in-place reorganization will require log space equal to several times the size of the reorganized table. The amount of required space depends on the number of rows that are moved and the number and size of the indexes on the table.
Recommendation: Choose in-place table reorganization for 24x7 operations with minimal maintenance windows.
An online table reorganization of a DMS table allows the starting of an online backup operation of a table space in which the table resides while the reorganization is happening. There may be lock waits of the reorganization operation during the truncate phase.
Refer to the REORG TABLE syntax descriptions for detailed information about executing these table reorganization methods.
On AIX(R) 5L 64-bit, the DB2_LARGE_PAGE_MEM registry variable now supports the keyword FCM.
By default, on AIX(R) 5L(TM) 64-bit, FCM memory is in the DBMS memory set. However, when the registry variable DB2_FORCE_FCM_BP is enabled, FCM memory is in its own memory set. On AIX 5L(TM) 64-bit, DB2_LARGE_PAGE_MEM supports the specification of the DBMS memory set. When FCM memory is in the DBMS memory set, and large page support is enabled for that memory set, FCM memory will be in large pages. When FCM memory is in its own memory set, the FCM keyword must be added to the value of the DB2_LARGE_PAGE_MEM registry variable to enable large pages for FCM memory.
Starting with DB2 Universal Database(TM) (UDB) Version 8.2.2 (equivalent to Version 8.1 FixPak 9), the configuration file specified by the DB2_RESOURCE_POLICY registry variable accepts a SCHEDULING_POLICY element. The SCHEDULING_POLICY element can be used on some platforms to select:
The registry variables DB2PRIORITIES and DB2NTPRICLASS can be used separately to control the operating system scheduling policy and set DB2 agent priorities.
However, the specification of a SCHEDULING_POLICY element in the resource policy configuration file provides a single place to specify both the scheduling policy and the associated agent priorities.
Selection of the AIX SCHED_FIFO2 scheduling policy with a priority boost for the db2 log writer and reader processes:
<RESOURCE_POLICY>
<SCHEDULING_POLICY>
<POLICY_TYPE>SCHED_FIFO2</POLICY_TYPE>
<PRIORITY_VALUE>60</PRIORITY_VALUE>
<EDU_PRIORITY>
<EDU_NAME>db2loggr</EDU_NAME>
<PRIORITY_VALUE>56</PRIORITY_VALUE>
</EDU_PRIORITY>
<EDU_PRIORITY>
<EDU_NAME>db2loggw</EDU_NAME>
<PRIORITY_VALUE>56</PRIORITY_VALUE>
</EDU_PRIORITY>
</SCHEDULING_POLICY>
</RESOURCE_POLICY>Replacement for DB2NTPRICLASS=H on Windows.
<RESOURCE_POLICY>
<SCHEDULING_POLICY>
<POLICY_TYPE>HIGH_PRIORITY_CLASS</POLICY_TYPE>
</SCHEDULING_POLICY>
</RESOURCE_POLICY>The DB2_MAPPED_BASE and DB2DBMSADDR system environment variables have been added at FixPak 8.
Use of these registry variables is only recommended for advanced users.
DB2 UDB will attempt to relocate the shared libraries to the virtual address 0x10000000 if this variable is not set.
The registry variable can also be set to any virtual address (in hex) in the range of the 31 and 32-bit address space.
db2set DB2_MAPPED_BASE=
The following message may appear multiple times in the db2diag.log file because this change is required once per logical node:
ADM0506I DB2 has automatically updated the "mapped_base" kernel parameter from "0x40000000(hex) 1073741824(dec)" to the recommended value "0x10000000(hex) 268435456(dec)".
This message will only appear if setting of the registry variable successful, and it will include the address that the shared libraries are relocated to.
db2set DB2DBMSADDR=
This variable can be set in conjunction with DB2_MAPPED_BASE or alone to fine tune the address space layout of DB2 UDB processes. This variable changes the location of the instance shared memory from it's current location at virtual address 0x20000000 to the new value given.
The DB2TCP_CLIENT_RCVTIMEOUT registry variable has been added in Version 8.2.
The DB2_LARGE_PAGE_MEM performance variable has been added in Version 8.2.
| Variable name | Operating systems | Values |
|---|---|---|
| Description | ||
| DB2_LARGE_PAGE_MEM |
AIX(R) 5.x 64-bit only Linux |
Default=NULL
Use * to denote that all applicable memory regions should use large page memory, or a comma-separated list of specific memory regions that should use large page memory. Available regions vary by operating system. On AIX 5.x 64-bit, the following regions can be specified: DB, DBMS, or PRIVATE. On Linux, the following region can be specified: DB. |
|
Large page memory is supported only for DB2 Universal Database (UDB) for AIX 5L(TM), 64-bit Edition, and DB2 UDB for Linux. The DB2_LARGE_PAGE_MEM registry variable is used to enable large page support when running on AIX 5.x or any Linux architecture with the appropriate kernel support. This registry variable deprecates the DB2_LGPAGE_BP registry variable, which can only be used to enable large-page memory for the database shared memory region. This can now be enabled by setting DB2_LARGE_PAGE_MEM=DB. Any documentation that mentions enabling large pages with the DB2_LGPAGE_BP registry variable can be treated as synonymous with setting DB2_LARGE_PAGE_MEM=DB. Large page usage is primarily intended to provide performance improvements to high performance computing applications. Memory access intensive applications that use large amounts of virtual memory may obtain performance improvements by using large pages. To enable DB2 UDB to use large pages, you must first configure the operating system to use large pages. Enabling large private pages will increase DB2 UDB memory usage by a significant amount, as each DB2 UDB agent will consume at least 1 large page (16MB) of physical memory. To enable large pages for agent private memory on 64-bit DB2 UDB for AIX (the DB2_LARGE_PAGE_MEM=PRIVATE setting), the following conditions must be met, in addition to configuring large pages on the operating system:
On 64-bit DB2 UDB for AIX, enabling this variable reduces the size of the shared memory segment backing database memory to the minimum requirement. The default is to create a 64 GB segment: see the database shared memory size (database_memory) database configuration parameter for more details. This avoids pinning more shared memory in RAM than is likely to be used. By setting this variable, the ability to dynamically increase the overall database shared memory configuration (for example, to increase the size of buffer pools) will be limited. On Linux, there is an additional requirement for the availability of the libcap.so library. This library must be installed for this option to work. If this option is turned on, and the library is not on the system, DB2 UDB will disable the large kernel pages and continue to function as it would previously. On Linux, to verify that large kernel pages are available, issue the following command: cat /proc/meminfo If it is available, the following three lines should appear (with different numbers depending on the amount of memory configured on your machine): HugePages_Total: 200
HugePages_Free: 200
Hugepagesize: 16384 KB
If you do not see these lines, or if the HugePages_Total is 0, configuration of the operating system or kernel is required. |
||
The following update applies to the topic "SQL compiler variables" in Appendix A "DB2 registry and environment variables" of the Administration Guide: Performance:
When either or both of the DB2 compiler variables DB2_MINIMIZE_LISTPREFETCH and DB2_INLIST_TO_NLJN, are set to ON, they remain active even if REOPT(ONCE) is specified.
Following are the updates to the configuration parameter documentation:
The Authentication type (authentication) database manager configuration parameter also accepts the following values:
The server accepts encrypted SERVER authentication schemes and the encryption of user data. The authentication works exactly the same way as SERVER_ENCRYPT.
The following user data are encrypted when using this authentication type:
The server accepts encrypted SERVER authentication schemes and the encryption of user data. In addition, this authentication type allows compatibility with earlier products that do not support DATA_ENCRYPT authentication type. These products are permitted to connect with the SERVER_ENCRYPT authentication type and without encrypting user data. Products supporting the new authentication type must use it. This authentication type is only valid in the server's database manager configuration file and is not valid when used on the CATALOG DATABASE command.
Starting with DB2 Universal Database Version 8.2, the default value of the Instance impact policy (util_impact_lim) database manager configuration parameter changes from 100 to 10.
The following database manager configuration parameters can all accept group names of 30 bytes (or less) on all platforms:
The table in the topic "Database manager configuration parameter summary" contains incorrect data types for these database manager configuration parameters. The correct value in all cases is char(30).
The maximum size for the Extended storage memory segment size database (estore_seg_size) configuration parameter on Windows(R) based platforms is 16 777 216.
The correct upper limit of the HADR timeout value (hadr_timeout) database configuration parameter is 4 294 967 295.
The documentation for the Maximum storage for locklist (locklist) database configuration parameter states that the maximum value for Windows 64-bit and 32-bit servers that service only local clients is 60 000. This value is incorrect, and should be 524 288.
The range of values for the Number of database backups (num_db_backups database configuration parameter is incorrect. The correct range is 0 - 32 767.
After migrating to DB2 Universal Database (UDB) Version 8.2 from Version 8.1, DB2 UDB uses a new 16 KB database configuration parameter file named SQLDBCONF. (In Version 8.1, the database configuration parameter file was only 4 KB and named SQLDBCON).
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 the best performance.
Hash-join performance is best if you can avoid hash loops and overflow to disk. To tune hash-join performance, estimate the maximum amount of memory available for the sheapthres parameter , then tune the sortheap parameter. Increase its value until you avoid as many hash loops and disk overflows as possible, but do not reach the limit specified by the sheapthres parameter.
For more information, see the "Join methods" topic in the Administration Guide: Performance manual.
Functionality previously achieved through DB2NTNOCACHE can be achieved at the table space level by specifying the NO FILE SYSTEM CACHING clause on the CREATE TABLESPACE or the ALTER TABLESPACE statement. Refer to SQL Reference for details on usage. The DB2NTNOCACHE registry variable will be removed in a future release.
Explain tables might be common to more than one user. However, the explain tables can be defined for one user, and aliases can be defined for each additional user using the same name to point to the defined tables. Alternatively, the explain tables can be defined under the SYSTOOLS schema. The Explain facility will default to the SYSTOOLS schema if no other explain tables or aliases are found under the user's session ID for dynamic SQL, or the statement authorization ID for static SQL. Each user sharing the common explain tables must have insert permission on those tables. Read permission for the common explain tables should also be limited, typically to users who analyze the explain information.
Explain data is captured if you request it when an SQL statement is compiled. Consider how you expect to use the captured information when you request explain data.
Explain table information is captured in any of the following cases:
The collating information parameter can only be displayed using the db2CfgGet API. It cannot be displayed through the command line processor or the Control Center.
This parameter provides 260 bytes of database collating information. The first 256 bytes specify the database collating sequence, where byte "n" contains the sort weight of the code point whose underlying decimal representation is "n" in the code page of the database.
The last 4 bytes contain internal information about the type of the collating sequence. The last 4 bytes of collate_info is an integer. The integer is sensitive to the endian order of the platform. The possible values are:
If you use this internal type information, you need to consider byte reversal when retrieving information for a database on a different platform.
You can specify the collating sequence at database creation time.
Starting with DB2 Universal Database (UDB) Version 8.2, you can use AUTOMATIC prefetch size for a table space. DB2 UDB automatically updates the prefetch size when the number of containers changes for the table space.
The syntax of the DB2_PARALLEL_IO registry variable is expanded to recognize containers with different I/O parallelism characteristics. Through the expanded syntax, containers for different table spaces can have different I/O parallelism characteristics. The I/O parallelism characteristic of each table space is used when a prefetch size of AUTOMATIC is specified for the table space. If the DB2_PARALLEL_IO registry variable is enabled but the expanded syntax identifying specific I/O parallelism characteristics for table spaces is not used, a default level of parallelism is assumed. The default level is RAID 5 (6+1).
The prefetch size information used by the optimizer is refreshed only when an ALTER TABLESPACE statement that changes the prefetch size of a table space or changes the number of containers (using ADD/DROP/BEGIN NEW STRIPE SET/ADD TO NEW STRIPE SET) is issued. If the number of physical disks per container registry settings changes, an ALTER TABLESPACE <table space name> PREFETCHSIZE AUTOMATIC statement should be issued to refresh the optimizer information (unless an ALTER TABLESPACE statement that refreshes the optimizer information is already issued).
If a table space is redirected or restored to use a different number of containers, refresh the optimizer information by issuing an ALTER TABLESPACE <table space name> PREFETCHSIZE AUTOMATIC statement. If there are multiple stripe sets within a table space, the maximum number of containers among the stripe sets is used to calculated the prefetch size. If the calculated prefetch size exceeds the maximum size (32 767 pages), the largest multiple of the number of containers that is smaller than the maximum is used as the prefetch size.
In a DB2 UDB Enterprise Server Edition environment, if a table space uses an AUTOMATIC prefetch size, the prefetch size might be different on different database partitions. This situation can exist because different database partitions can have different numbers of containers used for calculating the prefetch size. To generate the query access plan, the optimizer uses the prefetch size from the first partition in a database partition group.
[ Top of Page |Previous Page | Next Page | Contents ]