Changes to system catalog views, built-in functions and global variables, built-in administrative routines and views

Details

System catalog view changes

The following system catalog views are changed in version 11.1. Most modifications to catalog views consist of new columns, changed descriptions, changed column data types, and increased column lengths.

• SYSCAT.COLUMNS catalog view
• SYSCAT.DATAPARTITIONS catalog view
• SYSCAT.MEMBERSUBSETMEMBERS catalog view
• SYSCAT.ROUTINES catalog view
• SYSCAT.SCHEMAAUTH catalog view
• SYSCAT.TABLES catalog view
• SYSSTAT.COLUMNS catalog view
• SYSSTAT.TABLES catalog view

Built-in function changes

The following built-in functions that are changed in version 11.1:

• HEXTORAW scalar function returns a different data type than previous releases.

The following built-in functions are new:

• ADD_DAYS
• ADD_HOURS
• ADD_MINUTES
• ADD_SECONDS
• ADD_YEARS
• AGE
• BTRIM
• COLLATION_KEY
• COVARIANCE_SAMP
• CUME_DIST
• DATE_PART
• DATE_TRUNC
• DAYOFMONTH
• DAYS_BETWEEN
• DAYS_TO_END_OF_MONTH
• EXTRACT
• FIRST_DAY
• HASH
• HASH4
• HASH8
• HOURS_BETWEEN
• INTNAND , INTNOR, INTNXOR, and INTNNOT
• MEDIAN
• MINUTES_BETWEEN
• NEXT_MONTH
• NEXT_QUARTER
• NEXT_WEEK
• NEXT_YEAR
• NOW
• PERCENTILE_CONT
• PERCENTILE_DISC
• PERCENT_RANK
• RAWTOHEX
• SECONDS_BETWEEN
• STDDEV_SAMP
• THIS_MONTH
• THIS_QUARTER
• THIS_WEEK
• THIS_YEAR
• TO_HEX
• VARIANCE_SAMP
• WEEKS_BETWEEN
• WIDTH_BUCKET
• YEARSBETWEEN
• YMDBETWEEN

Built-in global variable changes and special registers

The following built-in global variables and special register are added:

• SQL_COMPAT global variable
• LOCALTIMESTAMP special register synonym of CURRENT TIMESTAMP special register.

Built-in administrative views and routine changes

The following administrative views and routines are changed in version 11.1. Most modifications consist of new columns, new values, changed column data types, and increased column lengths:

• ADMINTEMPTABLES administrative view and ADMIN_GET_TEMP_TABLES table function - Retrieve information for temporary tables
• ADMIN_GET_ENCRYPTION_INFO table function - Get database encryption settings
• ADMIN_MOVE_TABLE procedure - Move tables online
• ADMIN_ROTATE_MASTER_KEY procedure - Change the database master key
• MON_FORMAT_LOCK_NAME table function - Format the internal lock name and return details
• MON_FORMAT_XML_COMPONENT_TIMES_BY_ROW - Get formatted row-based component times
• MON_FORMAT_XML_METRICS_BY_ROW - Get formatted row-based output for all metrics
• MON_FORMAT_XML_TIMES_BY_ROW - Get formatted row-based combined hierarchy wait and processing times
• MON_GET_ACTIVITY table function - Return a list of activities
• MON_GET_ACTIVITY_DETAILS table function - Return information about an activity as an XML document
• MON_GET_AGENT table function - List agents running in a service class
• MON_GET_CONNECTION table function - Get connection metrics
• MON_GET_CONNECTION_DETAILS table function - Get connection metrics as an XML document
• MON_GET_DATABASE table function - Get database metrics
• MON_GET_DATABASE_DETAILS table function - Get database metrics as an XML document
• MON_GET_EXTENDED_LATCH_WAIT table function - Return information for latches
• MON_GET_HADR table function - Returns high availability disaster recovery (HADR) monitoring information
• MON_GET_PKG_CACHE_STMT table function - Get package cache statement metrics
• MON_GET_PKG_CACHE_STMT_DETAILS table function - Get package cache statement metrics as an XML document
• MON_GET_ROUTINE table function - get aggregated routine execution metrics
• MON_GET_ROUTINE_DETAILS table function - get aggregated routine execution metrics as an XML document
• MON_GET_RTS_RQST table function - Retrieve information about real-time statistics requests
• MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics
• MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get service subclass metrics as an XML document
• MON_GET_TABLE table function - get table metrics
• MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list
• MON_GET_UNIT_OF_WORK table function - Get unit of work metrics
• MON_GET_UTILITY table function - Get utilities running on the database
• MON_GET_WORKLOAD table function - Get workload metrics
• MON_GET_WORKLOAD_DETAILS table function - Get workload metrics as an XML document
• MON_GET_UNIT_OF_WORK_DETAILS table function - Get unit of work metrics as an XML document
• WLM_ALTER_MEMBER_SUBSET procedure - Alter a member subset object
• WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities

The following monitor routines and views are new:

• MON_GET_LATCH table function - List all latches in the current member

Resolution

Adjust your applications to the changes in existing system catalog objects such as new columns, or modified data type in columns. Also, start by using new system catalog objects that provide more comprehensive views or routines, or access to information about new functionality.

Review the list of the deprecated administrative routines and the associated replacement routines or views to determine any additional changes that might impact your applications and scripts. To minimize the impact of changes to built-in routines and views, review Best practices for calling built-in routines and views in applications.

For a list of the data dictionary-compatible views, see Data dictionary-compatible views.

To have access to new administrative routines in a version 11.1 fix pack release in databases that were created in version 11.1 prior to the fix pack release, you must run the db2updv111 command. If your database was created before version 11.1 and you upgrade to a version 11.1 fix pack release, it is not necessary to run the db2updv111 command because the system catalog is automatically updated by the database upgrade.