DB2 Version 9.7 for Linux, UNIX, and Windows
DB2 Version 9.7 for Linux, UNIX, and Windows
In DB2® Version 9.7, system catalog objects are modified to support new functionality. These changes can impact your existing applications and scripts after you upgrade to DB2 Version 9.7.
| View name | Summary of changes with upgrade impact |
|---|---|
| SYSCAT.ATTRIBUTES | The SCALE
column now has a value for TIMESTAMP data type to indicate the number
of digits of fractional seconds. New ATTR_TYPEMODULENAME, SOURCE_TYPEMODULENAME, TARGET_TYPEMODULENAME, TYPEMODULENAME, columns are added. |
| SYSCAT.BUFFERPOOLS | New column NUMBLOCKPAGES is added. |
| SYSCAT.CONSTDEP, SYSCAT.INDEXDEP, SYSCAT.INDEXEXTENSIONDEP, SYSCAT.PACKAGEDEP, SYSCAT.TABDEP , SYSCAT.TRIGDEP, SYSCAT.XSROBJECTDEP |
New columns BMODULEID and BMODULENAME are added. |
| and SYSSTAT.COLUMNS |
New column PCT_INLINED
is added. The AVGCOLLEN column returns has a value of -1 only when statistics have not been collected. It no longer returns -1 when the column data type is a long field or LOB as in previous releases. The SCALE column now has a value for TIMESTAMP data type to indicate the number of digits of fractional seconds. The INLINE_LENGTH column in SYSCAT.COLUMNS returns the LOB maximum size for inline length. During database upgrade, this column is set to the maximum LOB descriptor size. |
| SYSCAT.DATAPARTITIONS | New columns ACTIVE_BLOCKS INDEX_TBSPACEID, AVGCOMPRESSEDROWSIZE, AVGROWCOMPRESSIONRATIO, AVGROWSIZE, CARD, FPAGES, NPAGES, OVERFLOW, PCTPAGESSAVED, PCTROWSCOMPRESSED, and STATS_TIME are added. |
| SYSCAT.DATATYPEDEP | New columns BMODULEID, BMODULENAME, TYPEMODULEID, and TYPEMODULENAME are added. |
| SYSCAT.DATATYPES | The SCALE
column now has a value for TIMESTAMP data type to indicate the number
of digits of fractional seconds. New columns ARRAY_LENGTH, ARRAYINDEXTYPESCHEMA, ARRAYINDEXTYPENAME, ARRAYINDEXTYPEID, ARRAYINDEXTYPELENGTH, PUBLISHED, SOURCEMODULEID, SOURCEMODULENAME, TYPEMODULEID, TYPEMODULENAME, and VALID are added. |
| SYSCAT.DBAUTH | New columns ACCESSCTRLAUTH, DATAACCESSAUTH, EXPLAINADMAUTH, SQLADMAUTH, and WLMADMAUTH are added. |
| SYSCAT.HISTOGRAM TEMPLATEUSE | New column WORKLOADNAME is added. |
| SYSCAT.INDEXEXTENSION PARMS | The SCALE column now has a value for TIMESTAMP data type to indicate the number of digits of fractional seconds. |
| SYSCAT.INDEXES, SYSSTAT.INDEXES |
New columns AVGLEAFKEYSIZE, AVGNLEAFKEYSIZE, LASTUSED, COMPRESSION, and PCTPAGESSAVED are added. |
| SYSCAT.PACKAGES | New columns ANONBLOCK, ALTER_TIME, APREUSE, CONCURRENTACCESSRESOLUTION, EXTENDEDINDICATOR, DBPARTITIONNUM, PKGID, LASTUSED, and VALID are
added. The PKG_CREATE_TIME column has been renamed CREATE_TIME. The PKG_CREATE_TIME column is still available for compatibility with previous releases. |
| SYSCAT.PARTITIONMAPS | The size of the distribution
map in the SYSCAT.PARTITIONMAPS view has increased from 4 096
entries to 32 768 entries. During database upgrade, the size
of the distribution map is increased to 32 768 entries. Use the new db2GetPmap API to read the distribution map. See Upgrade impact from DB2 API changes for details. |
| SYSCAT.ROUTINEDEP | New columns BMODULEID, BMODULENAME, ROUTINEMODULEID, and ROUTINEMODULENAME are added. |
| SYSCAT.ROUTINEPARMS | The SCALE
column now has a value for TIMESTAMP data type to indicate the number
of digits of fractional seconds. New columns DEFAULT, ROUTINEMODULEID, ROUTINEMODULENAME, TARGET_TYPEMODULENAME, and TYPEMODULENAME are added. |
| SYSCAT.ROUTINES, SYSSTAT.ROUTINES |
New columns DIALECT, MODULEROUTINEIMPLEMENTED, PUBLISHED, RETURN_TYPEMODULE, ROUTINEMODULEID, and ROUTINEMODULENAME are added. |
| SYSCAT.SEQUENCES | New columns BASE_SEQNAME and BASE_SEQSCHEMA are added. This catalog view now returns rows that represent an alias for a sequence in addition to rows that represent a sequence. |
| SYSCAT.SERVICECLASSES | New columns BUFFERPOOLPRIORITY, COLLECTACTMETRICS, and COLLECTREQMETRICS are added. |
| SYSCAT.TABDETACHEDDEP | New columns BMODULEID and TABMODULENAME are added. |
| SYSCAT.TABLES | New columns LASTUSED, LOGGED, ONCOMMIT, and ONROLLBACK are added. |
| SYSCAT.TABLESPACES | New column DATAPRIORITY is added. The DATATYPE column value U now indicates created or declared temporary tables. In previous releases, the U value indicated only declared temporary tables. |
| SYSCAT.THRESHOLDS | New columns CHECK_INTERVAL, REMAPSCID, and VIOLATIONRECORDLOGGED are added. |
| SYSCAT.VARIABLEDEP | New BMODULEID, VARMODULEID, BMODULENAME, and VARMODULENAME columns are added. |
| SYSCAT.VARIABLES | The SCALE
column now has a value for TIMESTAMP data type to indicate the number
of digits of fractional seconds. New columns PUBLISHED, VARMODULEID, VARMODULENAME, VALID, and READONLY are added. |
| SYSCAT.WORKLOADS | New columnsCOLLECTACTMETRICS, COLLECTDEADLOCK, COLLECTLOCKTIMEOUT, COLLECTLOCKWAIT, and LOCKWAITVALUE are added. |
| Routine name | Summary of changes with upgrade impact |
|---|---|
| ADD_MONTHS, EXTRACT, LAST_DAY, MONTHS_BETWEEN, NEXT_DAY, and TRUNC_TIMESTAMP |
If you have user-defined functions with the same names as these new built-in functions and they are not fully qualified in your queries, the resolution path might result in these new built-in functions being called. Fully qualify your user-defined function calls with the schema name or start using these new built-in functions instead. |
| ARRAY_DELETE, ARRAY_FIRST, ARRAY_LAST, ARRAY_NEXT, ARRAY_PRIOR, CURSOR_ROWCOUNT |
If you have user-defined functions with the same names as these new built-in functions and they are not fully qualified in your queries, the resolution path might result in these new built-in functions being called. Fully qualify your user-defined function calls with the schema name or start using these new built-in functions instead. |
| DAYNAME, DECFLOAT_FORMAT, INITCAP, INSTR, LPAD, LOCATE_IN_STRING, MONTHNAME, RPAD, TO_CLOB, TO_NUMBER, TO_TIMESTAMP |
If you have user-defined functions with the same names as these new built-in functions and they are not fully qualified in your queries, the resolution path might result in these new built-in functions being called. Fully qualify your user-defined function calls with the schema name or start using these new built-in functions instead. |
| CHAR (decimal-expression) | If the dec_to_char_fmt database
configuration parameter is set to NEW, the CHAR function
now 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. In previous releases the character string included leading zeros and a decimal separator when the decimal part was zero. The dec_to_char_fmt database configuration parameter must be set to V95 to obtain the same result as in previous releases. See Table 5 for details. |
| DOUBLE_PRECISION | If you have user-defined functions with the same names as these new built-in functions and they are not fully qualified in your queries, the resolution path might result in these new built-in functions being called. Fully qualify your user-defined function calls with the schema name or start using these new built-in functions instead. |
| SYSIBM.DOUBLE | The SYSIBM.DOUBLE function
returns a double-precision floating-point representation of a number
or a string representation of a number. When you call this function with a string-expression argument, the function now removes only leading and trailing blanks from the argument string-expression. In previous releases this function removed leading and trailing blanks, tabs, carriage returns, new lines, and line feeds. If you are using the default setting for SQL path, function calls to DOUBLE resolves to SYSIBM.DOUBLE over SYSFUN.DOUBLE. The SYSFUN.DOUBLE is still available. If you rely on the previous release behavior for this function, fully qualify references to SYSFUN.DOUBLE. |
| LONG_VARGRAPHIC, LONG_VARCHAR |
LONG_VARGRAPHIC and LONG_VARCHAR scalar functions are deprecated. Although the use of these scalar functions is still supported in the current release, consider using other scalar functions such as CHAR, VARCHAR, and CLOB. The LONG VARCHAR and LONG VARGRAPHIC data types are deprecated and might be removed in a future release. See Table 7 for details. |
| Routine or view name | Summary of changes with upgrade impact |
|---|---|
| ADMIN_GET_TAB_ COMPRESS_INFO and ADMINTABCOMPRESSINFO | The ADMIN_GET_TAB_ COMPRESS_INFO
table function is deprecated. The ADMINTABCOMPRESSINFO
view now calls ADMIN_GET_TAB_COMPRESS_INFO_V97 and returns the new
additional columns. Use the new ADMIN_GET_TAB_COMPRESS_INFO_V97
function or the ADMINTABCOMPRESSINFO view which returns the new column OBJECT_TYPE. The value
of the EXPAND_DICT_SIZE column now includes the sum of the current
and historical dictionary sizes. Both ADMIN_GET_TAB_COMPRESS_INFO_V97 and ADMINTABCOMPRESSINFO can now return additional rows for information on compression dictionaries for XML data stored in the XML storage object. |
| ADMIN_GET_TAB_INFO_V95 | The ADMIN_GET_TAB_INFO_V95 table function is deprecated. The ADMINTABINFO view now calls ADMIN_GET_TAB_INFO_V97 and returns the new additional columns. Use the new ADMIN_GET_TAB_INFO_V97 function or the ADMINTABINFO view which returns the new columns RECLAIMABLE_SPACE, XML_DICTIONARY_SIZE, and XML_RECORD_TYPE. Also, the description of the DICTIONARY_SIZE column is changed to indicate that the value of this column now includes the sum of the current and historical dictionary sizes. |
| ADMIN_EST_INLINE_ LENGTH | This new function provides
a method to estimate the inline length of XML, CLOB, BLOB and DBCLOB
columns. XML columns are only supported when they are created in DB2 Version 9.7 because new statistics
are collected to estimate the inline length. This function returns
the value -2 for XML columns created in previous releases because
it cannot estimate the inline length without the new statistics. To convert the XML storage object to the new Version 9.7 format that supports this function, re-create the table. The new SYSPROC.ADMIN_MOVE_TABLE system-defined procedure allows you to re-create the table while the data remains online and available for access. See Moving tables using the ADMIN_MOVE_TABLE procedure. |
| AUDIT_ARCHIVE, AUDIT_DELIM_EXTRACT, AUDIT_LIST_LOGS |
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. You need to explicitly grant the EXECUTE privilege on these audit routines to any users that do not hold SECADM authority but need to call these routines. |
| DBCFG, GET_DB_CONFIG |
Selecting from the DBMCFG view or the GET_DBM_CONFIG table function now returns new database configuration manager parameters listed in Table 5. |
| DBMCFG, GET_DBM_CONFIG |
Selecting from the DBMCFG view or the GET_DBM_CONFIG table function now returns new database configuration manager parameters listed in Table 3. |
| REBIND_ROUTINE_PACKAGE | In DB2 Version 9.7, the options parameter of VARCHAR(1024) data type replaces the resolve parameter of VARCHAR(12) data type. For compatibility with previous releases, 'ANY' and 'CONSERVATIVE' are accepted as valid values for options. 'ANY' is a synonym for 'RESOLVE ANY' and 'CONSERVATIVE' is a synonym for 'RESOLVE CONSERVATIVE'. If you are using parameter markers or host variables when you call this procedure from your applications, adjust the length of your variables. |
| REORGCHK_IX_STATS | This function has been modified and now returns the new column DATAPARTITIONNAME. |
| REORGCHK_TB_STATS | This function has been modified and now returns the new column DATAPARTITIONNAME. |
| SNAP_GET_STORAGE _PATHS, SNAPSTORAGE_PATHS |
The SNAP_GET_STORAGE_PATHS function is deprecated. The new SNAP_GET_STORAGE_PATHS_V97 function replaces SNAP_GET_STORAGE_PATHS and returns the new DB_STORAGE_PATH_WITH_DPE and DB_STORAGE_PATH_STATE columns. The SNAPSTORAGE_PATHS view now calls SNAP_GET_STORAGE_PATHS_V97 and returns the new additional columns. |
| SNAP_GET_TBSP_PART_V91, SNAPTBSP_PART |
The SNAP_GET_TBSP_PART_V91 function is deprecated. The new SNAP_GET_TBSP_PART_V97 function replaces SNAP_GET_TBSP_PART_V91 and returns the new TBSP_PATHS_DROPPED column. The SNAPTBSP_PART view now calls SNAP_GET_TBSP_PART_V97 and returns the new additional column. |
| XDB_DECOMP_XML_ FROM_QUERY | This new procedure can process multiple XML documents stored in binary or XML columns in one call and does not require serialization of the XML documents. The new DECOMPOSE XML DOCUMENTS command calls this new procedure. Pre-Version 9.7 databases do not have the correct version of this procedure, this command fails to run on such databases. See Adopting new DB2 Version 9.7 functionality in database applications and routines for details on how to use this new procedure. |
| WLM_GET_ACTIVITY _DETAILS | This table function is deprecated. Use the new MON_GET_ACTIVITY_DETAILS table function instead. This new function replaces WLM_GET_ACTIVITY_DETAILS and returns additional columns WL_WORK_ACTION_SET_ID and WL_WORK_CLASS_ID to support new WLM functionality. |
| WLM_GET_SERVICE_CLASS_AGENTS | This table function is deprecated. Use the new WLM_GET_SERVICE_CLASS_AGENTS_V97 table function instead. This new function replaces WLM_GET_SERVICE_CLASS_AGENTS and returns the new column EVENT_OBJECT_NAME. |
| WLM_GET_SERVICE_CLASS_ WORKLOAD_OCCURRENCES | This table function is deprecated. Use the new WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES_V97table function instead. This new function replaces WLM_GET_SERVICE_CLASS_AGENTS and returns the new column ADDRESS. |
| WLM_GET_SERVICE_SUBCLASS_ STATS | This table function is deprecated. Use the new WLM_GET_SERVICE_SUBCLASS_STATS_V97 table function instead. This new function replaces WLM_GET_SERVICE_SUBCLASS_STATS and returns additional column UOW_TOTAL_TIME_TOP to support new WLM functionality. |
| WLM_GET_WORKLOAD_STATS | This table function is deprecated. Use the new WLM_GET_WORKLOAD_STATS_V97 table function instead. This new function replaces WLM_GET_WORKLOAD_STATS and returns additional column UOW_TOTAL_TIME_TOP to support new WLM functionality. |
| WLM_GET_WORKLOAD_ OCCURRENCE_ACTIVITIES | This table function is deprecated. Use the new WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES_V97 table function instead. This new function replaces WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES and returns the new column EXECUTABLE_ID. |
Review the list of the deprecated administrative routines and their replacement routines or views to determine additional changes that might impact your applications and scripts.