Implications of migrating to DB2 10 new-function mode

DB2 10 supports the use of DFSMS striped or compressed extended format archive logs. If you are migrating from DB2 Version 8 and plan to use extended format archive logs, do not make this change until after migration to new-function mode. In a coexistence environment, you might encounter archive log read failures because DB2 DB2 Version 8 cannot read DFSMS extended format archive logs. Disaster recovery systems and all DB2 subsystems in a data sharing group must be in DB2 10 new-function mode before you use extended format archive logs.

During enabling-new-function mode (from both Version 8 and Version 9) processing, change data capture is disabled on the SYSPACKSTMT catalog table. You should not re-enable change data capture until your DB2 subsystem is in DB2 10 new-function mode. If change data capture is enabled on other tables then change data capture should be disabled before migration to DB2 10 conversion mode (from Version 8 or Version 9).

By default, DB2 ignores the LOAD and REORG parameter KEEPDICTIONARY when tables are converted from basic row format to reordered row format in DB2 10 new-function mode. If you want the KEEPDICTIONARY parameter to be honored, you need to set subsystem parameter HONOR_KEEPDICTIONARY to YES.

In DB2 10 new-function mode, you can specify an application defaults module to load instead of DSNHDECP. However, you should always have at least one module that has the name DSNHDECP because some applications might continue to have a dependency on the DSNHDECP name. You should not use this capability to specify your own application defaults load module in production until you have thoroughly tested the capability on a test system. To ensure fallback capability, you cannot modify applications to load an application defaults load module with a name other than DSNHDECP until your subsystem is in new-function mode.

After processing in enabling-new-function mode (from Version 8 or Version 9) is complete, the STMT_ID column of the SYSIBM.SYSPACTSTMT catalog table is populated for existing rows in the table. The SYSIBM.SYSPACTSTMT table is not synchronized with the DB2 directory in SPT01 until you rebind the existing package.

Also, any packages for static SQL statements that existed prior to DB2 10 must be rebound for any user who wants to use enhanced messages and traces that provide statement identifier information.

When a table with XML columns is created by a CREATE TABLE or CREATE TABLE LIKE statement in DB2 10 new-function mode, and the table is created in a universal table space, the XML columns and their associated XML tables are created in the XML versioning format. Prior to new-function mode, the XML columns and their associated XML tables are not created in the XML versioning format.

Also, when an ALTER TABLE ADD statement is used to add an XML column to a table in DB2 10 new-function mode, and the table is created in a universal table space, the XML column and the associated XML table are created in the XML versioning format if it is the first XML column in the table or if all the XML columns in the table are in the XML versioning format.

In DB2 10 conversion mode (from both Version 8 and Version 9), references to SYSDATE as an ordinary identifier result in SQLCODE -4700. In DB2 10 new-function mode, references to SYSDATE as an ordinary identifier are interpreted as synonyms for CURRENT TIMESTAMP(0).

In DB2 10 conversion mode (from both Version 8 and Version 9), references to CURRENT TIMESTAMP in a function call result in SQLCODE -4700. In DB2 10 new-function mode, references to CURRENT TIMESTAMP(integer) are interpreted as references to the new CURRENT TIMESTAMP special register.

Before DB2 10 new-function mode, the ADD_MONTHS and LAST_DAY functions always return a result of the DATE data type. After migration to new-function mode, these functions return a result data type that is determined from the data type of the input argument. In some cases, this result data type is TIMESTAMP WITHOUT TIME ZONE or a string.

Before DB2 10 new-function mode, labeled duration SECONDS values are expressed as DECIMAL (15,0) numbers. After migration to new-function mode, labeled duration SECONDS values are expressed as a DECIMAL (27,12) number.

If you have implemented SECOND, TIMESTAMP, or TIMESTAMP_FORMAT user-defined functions, after migration to DB2 10 new-function mode, built-in functions SECOND, TIMESTAMP, and TIMESTAMP_FORMAT either allow additional arguments or the argument allows additional data types. If the user-defined function name is not qualified and SYSIBM precedes the function's schema in the SQL path, the built-in functions might be invoked instead of the user-defined function.

Before DB2 10 new-function mode, TIMESTAMP columns have a length of 10 bytes and a scale of 0. After migration to new-function mode, the length of a TIMESTAMP column can be 7 - 13 bytes, and the scale can be 0 - 12. However, rows that previously existed continue to have a length of 10 and scale of 0, and an assumed timestamp precision of 6 digits for fractional seconds. This incompatibility applies to the following catalogs: SYSCOLUMNS, SYSCOLUMNS_HIST, SYSDATATYPES, SYSKEYTARGETS, SYSKEYTARGETS_ HIST, and SYSPARMS.

Each table in the following table spaces is moved to its own partition-by-growth table space in new-function mode: DSNDB06.SYSOBJ, DSNDB06.SYSDBASE, DSNDB06.SYSVIEWS, DSNDB06.SYSPLAN, DSNDB06.SYSPKAGE, DSNDB06.SYSDBAUT, DSNDB06.SYSGROUP, DSNDB01.DBD01, and DSNDB01.SPT01. Each table space uses row-level locking, and all tables are in reordered-row format.

In previous versions, SYSIBM.SYSDUMMYA SYSIBM.SYSDUMMYE, and SYSIBM.SYSDUMMYU tables are user-defined and created as part of the DSNTIJSG job. After migration to new-function mode, these tables are part of the DSNDB06 catalog.

After migration to new-function mode, SQL statements that were previously stored in text columns of SYSVIEWS.TEXT, SYSTRIGGERS.TEXT, SYSROUTINES_SRC.CREATEMST, and SYSPACKSTMT.TEXT are now stored in new LOB columns.

As of new-function mode, all links in the catalog and directory are removed.

There might be an incompatible change after migration to new-function mode for existing applications that read the output data set created by the UNLOAD utility. The application might need to be modified to read the data set because the data set record format is VBS.

After migration to new-function mode, existing applications inherit the inline length value from subsystem parameter LOB_INLINE_LENGTH if they have CREATE TABLE statements with LOB columns or ALTER TABLE statements that add LOB columns. However, if you maintain the default setting of 0 for LOB_INLINE_LENGTH, then LOB columns continue to be created with no inline attribute by default. Also, if the inline length from the subsystem parameter exceeds the page size of an explicitly or implicitly created universal table space, the LOB column does not inherit the subsystem parameter value.

Due to improvements in the DB2 record retrieval process, the order in which DB2 selects volumes for storage groups in conversion mode (from Version 8 or Version 9) might be different from the order in which DB2 selects volumes in new-function mode.

When you convert to new-function mode, a portion of the contents of the SPT01 table space is moved into a LOB table space. Data in the LOB table space cannot be compressed, so the amount of space that the SPT01 table space requires in new-function mode is greater than the amount of space that SPT01 requires in enabling-new-function mode (from Version 8 or Version 9). However, by default, DB2 places most of the LOB data for SPT01 in the base table space, which can be compressed. Therefore, the amount of space that is required for the STP01 table space does not increase significantly when you convert to new-function mode. However, if you set the SPT01_INLINE_LENGTH subsystem parameter to a smaller value, the amount of space that is needed for SPT01 increases because more data is in the LOB table space. You should not need to decrease the SPT01_INLINE_LENGTH value unless the size of SPT01 is close to its 64 GB limit.