DB2 Version 9.7 for Linux, UNIX, and Windows
DB2 Version 9.7 for Linux, UNIX, and Windows
Changes to DB2® registry variables, configuration parameters, database physical design characteristics, and database authorities and privileges can result in DB2 server behavior changes that might impact your upgrade.
As a general rule, instance profile variables that you set in your DB2 profile registry or your system environment retain their values after an instance upgrade. Some global profile registry variables, such as DB2SYSTEM and DB2PATH, are set by the DB2 installation procedure or instance upgrade. However, the global profile registry variables that you set by running the db2set command with the -g option are not upgraded. Therefore, you must define them after upgrade.
Existing database and database manager configuration parameters also, as a general rule, retain their values after upgrade. However, the default values assigned to new parameters or the new default values assigned to existing parameters could impact the behavior or performance of your applications.
| Name | Upgrade impact |
|---|---|
| DB2_DDL_SOFT_INVAL | By default, this new registry
variable is set to ON to enable soft invalidation
support on certain DDL statements. See Automatic invalidation and revalidation
of database objects for
a list of the DDL statements. In Version 9.1 Fix Pack 2 or later, if the DB2_WORKLOAD registry variable was set to SAP, soft invalidation support was provided for certain types of views that had no object dependencies. After instance upgrade, if you need to disable soft invalidation, set DB2_DDL_SOFT_INVAL to OFF. However, if the DB2_WORKLOAD registry variable is set to SAP, soft invalidation is still supported for certain types of views regardless of the DB2_DDL_SOFT_INVAL setting. |
| DB2_FORCE_OFFLINE_ADD_ PARTITION | This new registry variable
indicates whether the add partition operation using the START
DATABASE MANAGER command with the ADD DBPARTITIONNUM parameter
is performed online or offline. The default value (FALSE)
indicates online mode. After instance upgrade, if you need to enforce the same behavior as in previous releases, set this registry variable to TRUE. This setting allows you to add partitions only in offline mode when you start the instance. |
| DB2_DEFERRED_PREPARE_ SEMANTICS | This registry variable allows dynamic statements containing untyped parameter markers to use deferred prepare semantics in CLI and JDBC applications. When set to YES, any untyped parameter markers derive their data types and length attributes based on the input descriptor from subsequent OPEN or EXECUTE statements. In previous releases, or when set to NO (the default) compilation of such dynamic statements fails. |
| DB2_LIMIT_FENCED_GROUP | By default, this registry
variable is set to OFF to maintain the same behavior as in previous
releases so that there is no upgrade impact. However, you should consider
setting this registry variable to ON after the upgrade to improve
the security for external routines. See Restricting operating system permission
for db2fmp process (Windows) in Installing DB2 Servers for
details. On Windows operating systems, ensure that the DB2 service account for new and existing instances belongs to the DBUSERS group. |
| DB2_PMAP_COMPATIBILITY | By default, this registry variable is set to ON so that the distribution map maintains its size of 4 096 entries. If you set this registry variable to OFF before upgrading a database, the distribution map size is increased to 32 768 entries and you need to use the new db2GetDistMap and db2GetRowPartNum APIs. See Upgrade impact from DB2 API changes for details. |
| DB2_RESTORE_GRANT_ADMIN_AUTHORITIES | Starting in DB2 Version 9.7 Fix Pack 2, If DB2_RESTORE_GRANT_ADMIN_AUTHORITIES is set to ON, and you are restoring to a new database, then SECADM, DBADM, DATAACCESS, and ACCESSCTRL authorities are granted to the user that issues the restore operation. |
| DB2_SMS_TRUNC_TMPTABLE_THRESH | Starting in Version 9.7 Fix Pack 2, the default for this variable is -2, which means that there will not be any unnecessary file system access for any spilled SMS temporary objects whose size is less than or equal to 1 extent * number of containers. Temporary objects that are larger than this are truncated to 0 extent. If you need to revert to the behavior in previous releases, set this variable to 0. |
| Name | Upgrade impact |
|---|---|
| DB2_EVALUNCOMMITTED and DB2_SKIPDELETED | For statements operating
under cursor stability isolation level with currently committed behavior
enabled using the cur_commit database configuration
parameter, these registry variables are in effect only when currently
committed cannot be applied to a scan. Otherwise, the evaluation of
predicates is performed on data retrieved by currently committed scans. If currently committed behavior was enabled using the BIND command or the PREPARE statement, these registry variables have no effect. See the row for the cur_commit configuration parameter in Table 5 for details. |
| DB2_GRP_LOOKUP | The setting for DB2_GRP_LOOKUP is not modified by instance upgrade. Due to the changes to the security model in Version 9.7, if this registry variable is not set, ensure that domain users are granted the database authorities and privileges that they required after the upgrade. See Windows platform security considerations for users for details about recommended settings for the DB2_GRP_LOOKUP registry variable and how to verify the authorities and privileges are granted to domain users. |
| DB2_LOGGER_NON_ BUFFERED_IO | Starting with Version 9.7, the default value for this
variable is AUTOMATIC, which means that log files
in the active log path might be accessed using non-buffered I/O. The
database manager determines which log files benefit from using non-buffered
I/O. In Version 9.5 Fix Pack 1 or later, the default was OFF and log
files were accessed using only buffered I/O. After upgrading your instance, leaving this variable unset, it has the same effect as the AUTOMATIC setting and you might experience this change in I/O access. The benefits of using non-buffered I/O are reduced memory requirements and more efficient I/O access for log files. Therefore, carefully consider the impact before you decide to disable this feature by setting this variable to OFF. |
| DB2RESILIENCE | Starting with Version 9.7 Fix Pack 3, the trap resilience enabled by this variable is extended to the load utility. In previous releases, load EDUs would crash the instance, but now they are suspended. |
| DB2_SKIPINSERTED | For statements operating under cursor stability isolation level with currently committed behavior enabled, this registry variable has no effect. Read the row for the cur_commit configuration parameter in Table 5 for details. |
| DB2_WORKLOAD | Starting in DB2 Version 9.7 Fix Pack 2, if DB2_WORKLOAD is set to SAP, DB2_EXTENDED_OPTIMIZATION is set to IXOR to improve performance for queries generated by SAP application. Starting in DB2 Version 9.7 Fix Pack 4, automatic reorganization supports reorganizing indexes in volatile tables. After upgrading your databases, if you have enabled automatic index reorganization in volatile tables, index reorganization will be performed periodically on volatile tables. Fore more information, see Enabling automatic index reorganization in volatile tables. |
You should remove the use of registry variables that are deprecated because the functionality associated with the variable is obsolete or has been replaced by new functionality. See Deprecated registry variables to determine the upgrade impact of deprecated registry variables.
If you are upgrading from DB2 Version 9.1 or earlier, consider removing deprecated registry variables in pre-Version 9.7 releases because the functionality associated with the variable is obsolete or has been replaced by new functionality. Also, remove the use of discontinued registry variables in pre-Version 9.7 releases as they do not have the intended effect. See Changes that impact Version 9.1 or earlier releases for details.
The following table describes the upgrade impact of the default values of new database manager configuration parameters:
| Name | Upgrade impact |
|---|---|
| alternate_auth_enc | This parameter enables
AES 256-bit encryption of user IDs and passwords. By default, this
parameter is not set which means that the server accepts the encryption
algorithm the client proposes so that there is no upgrade impact. During instance upgrade, if the DB2_SERVER_ENCALG registry variable is set, the alternate_auth_enc configuration parameter is set to AES_ONLY or AES_CMP depending on the setting of DB2_SERVER_ENCALG so that your pre-upgrade setting is preserved. After the upgrade, if you want to change how AES 256-bit encryption is used, update the setting of the alternate_auth_enc configuration parameter because the setting of DB2_SERVER_ENCALG is ignored. |
| diagsize | This parameter enables the DB2 diagnostic rotating logs functionality. During instance upgrade, the diagsize is set to 0 to maintain the same behavior as in previous releases. With this setting, one single diagnostic log file (db2diag.log) and one administration notification log (instance_name.nfy) are used for error and notification logging and these two files grow indefinitely. See Adopting new Version 9.7 functionality in upgraded databases for details on enabling rotating logs functionality. |
| ssl_svr_keydb, ssl_svr_stash, ssl_svr_label, ssl_svcename |
To configure SSL support in a DB2 instance in previous releases, you set values for SSL parameters in the SSLconfig.ini file. If this file exists when you upgrade your instances, these new database manager parameters are set to the corresponding SSL parameter value in SSLconfig.ini. If this file does not exist, these database manager parameters are set to null value (default) which means that the instance is started without SSL protocol support. See SSL client support expanded and configuration simplified for more details. |
| ssl_clnt_keydb, ssl_clnt_stash |
To configure Secure Sockets Layer (SSL) support in a client in previous releases, you used to set values for SSL parameters in the SSLClientconfig.ini file. If this file exists when you upgrade your instances, these new database manager parameters are set to the corresponding SSL parameter value in SSLClientconfig.ini. If this file does not exist, these database manager parameters are set to null value (default). You must set these database manager parameters to enable SSL protocol support in a client. |
The following table describes the upgrade impact of changes to database manager configuration parameters:
| Name | Upgrade impact |
|---|---|
| authentication, srvcon_auth |
If you enabled 256-bit AES encryption for user IDs and passwords, check the alternate_auth_enc parameter which allows you to specify an alternate encryption algorithm for user names and passwords. See the row for the alternate_auth_enc parameter in Table 3 for more details. |
| instance_memory | Starting with Version 9.7 Fix Pack1, the AUTOMATIC setting does not enforce a limit on memory allocated across the instance for DB2 database products without memory usage restrictions. If you are upgrading from Version 8, Version 9.1, or Version 9.5 Fix Pack 4 or earlier and you want to enforce a limit, set instance_memory to a specific value . See instance_memory for details. |
No database manager configuration parameters have been deprecated or discontinued in this release. However, if you are upgrading from DB2 Version 9.1 or earlier, consider removing deprecated database manager configuration parameters in pre-Version 9.7 releases because the functionality associated with the parameters is obsolete or has been replaced by new functionality. Also, remove the use of discontinued database manager configuration parameters in pre-Version 9.7 releases as they do not have the intended effect. See Changes that impact Version 9.1 or earlier releases for details.
The following table describes the upgrade impact of the default values of new database configuration parameters:
| Name | Upgrade impact |
|---|---|
| auto_reval | During database upgrade,
this configuration parameter is set to DISABLED to maintain the same
invalidation and revalidation behavior for database objects as in
previous releases so that there is no upgrade impact. If you create new databases in DB2 Version 9.7, the auto_reval configuration parameter is set to DEFERRED by default so that revalidation deferred semantics are enabled. This setting impacts certain DDL statements and allows you to create views, functions, stored procedures, triggers, and global variables even if they reference objects that do not exist or are invalid. See Automatic invalidation and revalidation of database objects for details about these new semantics. |
| cur_commit | During database upgrade,
this configuration parameter is set to DISABLED to maintain the same
behavior as in previous releases so that there is no upgrade impact.
If you want to enable currently committed behavior on cursor stability
scans, you need to set the cur_commit configuration
parameter to ON after the upgrade. See Adopting new DB2 Version 9.7 functionality in database applications and routines for details on enabling currently committed
behavior. For new databases created in DB2 Version 9.7, the cur_commit configuration parameter is set to ON so that currently committed behavior is enabled on cursor stability scans. See Currently committed semantics allow for more concurrency for details. |
| dec_to_char_fmt | This configuration parameter
controls the character string returned by the CHAR(decimal-expresion)
scalar function and the CAST specification from decimal to character.
If dec_to_char_fmt is set to NEW,
the CHAR function returns a fixed-length character string representation
of a decimal number without leading zeros and without a decimal separator
when the decimal part is zero. If dec_to_char_fmt is
set to V95, the character string included leading
zeros and a decimal separator when the decimal part was zero. During
database upgrade, this configuration parameter is set to V95 so
that the function returns the same character string format as in previous
releases. For new databases created in DB2 Version 9.7, dec_to_char_fmt is set to NEW. You need to set this parameter to V95 for compatibility with existing applications. |
| mon_deadlock, mon_locktimeout, mon_lockwait, mon_lw_thresh, mon_act_metrics, mon_obj_metrics, mon_req_metrics, mon_uow_data |
These parameters allow
you to control the collection of metrics and event monitor data at
the database level including the new lock event monitor. During database
upgrade, mon_deadlock is set to WITHOUT_HIST and mon_lw_thresh is
set to 5 000 000 while the remaining parameters are set
to NONE so that there is no change in behavior from
previous releases. For new databases created in DB2 Version 9.7, mon_req_metrics, mon_act_metrics, mon_obj_metrics are set to BASE, mon_lw_thresh is set to 5 000 000, and mon_deadlock is set to WITHOUT_HIST. The mon_locktimeout, mon_lockwait, and mon_uow_data parameters are set to NONE. You need to review the setting of these parameters to ensure that you have the intended settings. |
The following table describes the upgrade impact of changes to existing database configuration parameters:
| Name | Upgrade impact |
|---|---|
| applheapsz | In databases upgraded from DB2 Version 9.1 or DB2 UDB Version 8, the applheapsz configuration
parameter is set to AUTOMATIC to account for changes
to the DB2 memory model. In
releases prior to DB2 Version
9.5, this parameter indicated the amount of memory for each database
agent. Starting with DB2 Version
9.5, this parameter indicates the total amount of memory for an application. Due to optimization enhancements to match MQTs, the requirement for application heap has increased. If this parameter is set to AUTOMATIC, this setting accounts for the new requirements. If you cannot set this parameter to AUTOMATIC or increase its value, reduce the number of MQTs considered for a given query by using optimization profiles. |
| dbheap | The database manager can now determine when to apply row compression to temporary tables that meet certain criteria to improve query performance. Memory allocated for database heap is used to create the compression dictionary and released once the dictionary is created. If you are using row compression and temporary tables eligible for compression, ensure that you have enough space to create the dictionary by setting the dbheap parameter to AUTOMATIC. See Table compression for details about temporary table compression. |
| locklist | Increase the locklist parameter
to twice the pre-upgrade value. Due to the increase of the lock request
block size to twice the size required in previous releases, active
locks in the database require twice the amount of memory. The limit for the locklist parameter is now 134 217 728 pages (4 KB). |
| logbufsz | The default value for
this parameter is now 256 pages (4KB). In previous releases it was
8 pages (4KB). After database upgrade, if you set the cur_commit configuration
parameter to ON so that currently committed behavior
is enabled on cursor stability scans, ensure that this parameter has
a value of 256 or higher. A log sequence number (LSN) now uses 8 bytes. In previous releases, LSN was 6 bytes in length. You might need to increase the value of this parameter according to your database logging activity. |
| logfilsiz , logprimary |
A log sequence number (LSN) uses now 8 bytes. In previous releases, LSN was 6 bytes in length. You might need to increase the value of these parameters according to your database logging activity. See Maximum limit of log sequence numbers has increased for details. |
| pckcachesz | To support the new access
plan reuse and section diagnostic facilities, package cache memory
requirements can increase 25 to 40 percent. For certain types of complex
queries, the package cache memory requirements have doubled. The impact
from the database upgrade should be minimal because of the small size
of this cache relative to overall memory requirements. By setting
this parameter to AUTOMATIC, the new requirements
are taken into account. For upgraded databases, the INLINE LENGTH default value is the maximum size of the LOB descriptor. LOB data is inlined when the length of the LOB data plus the overhead do not exceed the INLINE LENGTH value. Therefore, if the LOB data length plus the overhead is less than the LOB descriptor size for the LOB column, the LOB data is implicitly inlined in a table row after the database upgrade. Storing LOB data as inlined might require that you increase the pckcachesz database configuration parameter. By setting this parameter to AUTOMATIC, the new requirements are taken into account. The maximum limit for pckcachesz on 64-bit operating systems has been changed to 2 147 483 646. Although this parameter retains its value after database upgrade, consider tuning this parameter. See Adopting new DB2 Version 9.7 functionality in database applications and routines for details. |
You should remove the use of database configuration parameters that are deprecated because the functionality associated with the variable is obsolete or has been replaced by new functionality. See Some database configuration parameters have been changed to determine the upgrade impact of deprecated registry variables.
If you are upgrading from DB2 Version 9.1 or earlier, consider removing deprecated database configuration parameters in pre-Version 9.7 releases because the functionality associated with the parameter is obsolete or has been replaced by new functionality. Also, remove the use of discontinued database configuration parameters in pre-Version 9.7 releases as they do not have the intended effect. See Changes that impact Version 9.1 or earlier releases for details.
| Physical characteristic | Upgrade impact |
|---|---|
| BUFFER POOL | If you choose to store
qualifying LOB data in the row of a table instead of in the default
storage objects, the buffer pool usage can increase. In upgraded databases, if the LOB data length plus the overhead is less than the LOB descriptor size for the LOB column, the LOB data is implicitly inlined in a table row. As a result, row length increases, a lower number of rows fit in a page, and a higher number of pages are read into the buffer pool. |
| LONG VARCHAR and LONG VARGRAPHIC data types | The LONG VARCHAR and LONG
VARGRAPHIC data types are deprecated and might be removed in a future
release. Although the use of these data types is still supported in
the current release, consider migrating to other data types that will
continue to be supported in future releases such as VARCHAR, VARGRAPHIC,
CLOB, or DBCLOB. You can use the new SYSPROC.ADMIN_MOVE_TABLE system-defined procedure to change the column definition to a compatible data type while the data remains online and available for access. See Moving tables using the ADMIN_MOVE_TABLE procedure. |
| TIMESTAMP data type | The TIMESTAMP data type
now supports optional fractional seconds. You can specify the number
of digits in the fractional seconds as an attribute. The number of
digits ranges from 0 to 12 with a default of 6. Therefore, the only
impact from upgrading your databases is a minor performance overhead
in datetime arithmetic operations. However, if you create new tables using the TIMESTAMP data type with a different number of digits in the fractional seconds other than 6 digits, take into account that the string length for internal representation of a timestamp now ranges from 7 and 13 bytes and adjust your applications accordingly. In previous releases, the string length was 10 bytes. See Datetime values for details. |
| XML data type | In Version 9.7, the XML storage object has a new format to support new functionality such as row compression on XML data and collection of statistics to estimate the inline length for XML columns. If you have tables with XML columns created in a pre-Version 9.7 release and you want to use this new functionality, convert the XML storage object to the Version 9.7 format by re-creating these tables. See Converting XML storage objects to the Version 9.7 format for details. |
| Name | Upgrade impact |
|---|---|
| DBADM authority | In DB2 Version 9.7, there are new authorities for
access control and data access. For each authorization ID holding
DBADM authority, including the SYSADM group, the UPGRADE
DATABASE command explicitly grants ACCESSCTRL and DATAACCESS
authorities so that existing database administrators maintain the
same authority access and privileges as in previous releases. Review Database authorities for
details about these authorities. The UPGRADE DATABASE command also grants EXECUTE privilege on all system-defined routines by explicitly granting the SYSROLE_AUTH_DBADM system role to any authorization ID holding DBADM authority. Revoking DBADM authority now implicitly revokes all these authorities. |
| EXECUTE privilege | In DB2 Version 9.7, the UPGRADE DATABASE command revokes the EXECUTE privilege from PUBLIC on the audit routines, AUDIT_LIST_LOGS, AUDIT_DELIM_EXTRACT, and AUDIT_ARCHIVE. For each authorization ID holding SECADM authority, the UPGRADE DATABASE command explicitly grants the EXECUTE privilege on the audit routines by granting the SYSROLE_AUTH_SECADM system role. |
| SECADM authority | In DB2 Version 9.7, SECADM authority is required
for security administration and it is the only authority that provides
the ability to grant and revoke all authorities and privileges. If the database does not have a user with SECADM authority, the UPGRADE DATABASE command explicitly grants SECADM authority to the user performing this command. If any users in the SYSADM group need SECADM authority, you must explicitly grant it to them. Also, the UPGRADE DATABASE command revokes the EXECUTE privilege from PUBLIC on the audit routines, AUDIT_LIST_LOGS, AUDIT_DELIM_EXTRACT, and AUDIT_ARCHIVE. For each authorization ID holding SECADM authority, the UPGRADE DATABASE command explicitly grants the EXECUTE privilege on the audit routines by granting the SYSROLE_AUTH_SECADM system role. |
| SYSADM authority | In DB2 Version 9.7, DBADM authority is required
for database administration and SECADM authority is required for security
administration. If users in the SYSADM group need either authority,
you have to explicitly grant it. Also, a user who holds SYSADM authority
is no longer able to grant any authorities or privileges, except to
grant tablespace privileges. The UPGRADE DATABASE command explicitly grants DBADM authority to the SYSADM group. Therefore, there should be no upgrade impact but you should review all of the changes in authorities and make any necessary changes. |
| SYSMON authority | In DB2 Version 9.7, the SYSMON authority now enables a user to also run several LIST commands. See SYSMON authority for details. |
When the database upgrade is called implicitly using the RESTORE DATABASE command from a pre-Version 9.7 database backup, the changes described in this table are also applied to the database that you are restoring.
On Windows operating systems, the setting for DB2_GRP_LOOKUP can have an impact on database upgrade. See the row for the DB2_GRP_LOOKUP registry variable in Table 2 for more details.
See Upgrade impact from DB2 command changes and Upgrade impact from SQL statement changes for a summary of DB2 command and SQL statement changes with upgrade impact. See the Command Reference and SQL Reference for details about all the changes in authorization.