DB2 Version 9.7 for Linux, UNIX, and Windows

Supported system-defined SQL routines and views

The following tables summarize information about the supported administrative SQL routines and views.

Activity monitor administrative SQL routines: Table 1
ADMIN_CMD stored procedure and associated administrative SQL routines: Table 2
Administrative task scheduler routines and views: Table 3
Audit routines and proceduresTable 4
Automatic maintenance administrative SQL routines and views:Table 5
Common SQL API stored procedures: Table 6
Configuration administrative SQL routines and views: Table 7
Environment administrative views: Table 8
Health snapshot administrative SQL routines: Table 9
Monitor administrative SQL routines: Table 10
MQSeries® administrative SQL routines: Table 11
Security administrative SQL routines and views: Table 12
Snapshot administrative SQL routines and views: Table 13
SQL procedures administrative SQL routines: Table 14
Stepwise redistribute administrative SQL routines: Table 15
Storage management tool administrative SQL routines: Table 16
Text search administrative SQL routines: Table 17
Workload Management routines: Table 18
Miscellaneous administrative SQL routines and views: Table 19

Table 1. Activity monitor administrative SQL routines
Routine name	Schema	Description
AM_BASE_RPT_RECOMS table function	SYSPROC	This table function returns recommendations for activity reports used by the activity monitor.
AM_BASE_RPTS table function	SYSPROC	This table function returns activity reports used by the activity monitor.
AM_DROP_TASK procedure	SYSPROC	This procedure deletes a monitoring task.
AM_GET_LOCK_CHN_TB procedure	SYSPROC	This procedure returns application lock chain data in tabular format.
AM_GET_LOCK_CHNS procedure	SYSPROC	This procedure displays lock chains for a specified application using a formatted string.
AM_GET_LOCK_RPT procedure	SYSPROC	This procedure displays lock details for an application.
AM_GET_RPT procedure	SYSPROC	This procedure displays activity monitor data for a report.
AM_SAVE_TASK procedure	SYSPROC	This procedure creates or modifies a monitoring task.

Table 2. ADMIN_CMD stored procedure and associated administrative SQL routines
Routine name	Schema	Description
ADMIN_CMD procedure	SYSPROC	This procedure allows the administrator to execute administrative commands (including DB2® command line processor (CLP) commands) by running ADMIN_CMD through a CALL statement.
ADMIN_EST_INLINE_LENGTH function	SYSIBM	This function returns an estimate of the inline length that is required to inline the data stored in an XML column, BLOB column, CLOB column, or DBCLOB column.
ADMIN_GET_DBP_MEM_USAGE table function	SYSPROC	This table function gets the total memory consumption for a given instance.
ADMIN_GET_INDEX_COMPRESS_INFO	SYSPROC	This table function returns the potential index compression savings for uncompressed indexes or reports the index compression statistics from the catalogs.
ADMIN_GET_INDEX_INFO table function	SYSPROC	This table function returns index information not available in the catalog views.
ADMIN_GET_MSGS table function	SYSPROC	This table function is used to retrieve messages generated by data movement utilities that are executed through the ADMIN_CMD procedure.
ADMIN_IS_INLINED function	SYSIBM	This function retrieves state information about inline data for an XML column, BLOB column, CLOB column, or DBCLOB column.
ADMIN_REMOVE_MSGS procedure	SYSPROC	This procedure is used to clean up messages generated by data movement utilities that are executed through the ADMIN_CMD procedure.
ADMIN_REVALIDATE_DB_OBJECTS procedure	SYSPROC	This procedure revalidates invalid database objects.
ADMINTABCOMPRESSINFO view and ADMIN_GET_TAB_COMPRESS_INFO_V97 table function	SYSIBMADM (administrative view), SYSPROC (table function)	This view and table function return compression information for tables, materialized query tables (MQT) and hierarchy tables.
ADMINTABINFO and ADMIN_GET_TAB_INFO_V97	SYSIBMADM (administrative view), SYSPROC (table function)	This view and table function return size and state information for tables, materialized query tables (MQT) and hierarchy tables.
ADMINTEMPCOLUMNS view and ADMIN_GET_TEMP_COLUMNS table function	SYSIBMADM (administrative view), SYSPROC (table function)	This view and table function retrieve column attribute information for created temporary tables and declared temporary tables
ADMINTEMPTABLES view and ADMIN_GET_TEMP_TABLES table function	SYSIBMADM (administrative view), SYSPROC (table function)	This view and table function retrieve table attribute and instantiation time information for instances of created temporary tables and declared temporary tables.

Table 3. Administrative task scheduler routines and views
Routine or view name	Schema	Description
ADMIN_TASK_ADD	SYSPROC	This procedure schedules an administrative task.
ADMIN_TASK_LIST	SYSTOOLS	This administrative view retrieves information about each task defined in the scheduler.
ADMIN_TASK_REMOVE	SYSPROC	This procedure removes scheduled tasks or task status records.
ADMIN_TASK_STATUS	SYSTOOLS	This administrative view retrieves information about the status of each task.
ADMIN_TASK_UPDATE	SYSPROC	This procedure updates an existing task

Table 4. Audit routines and procedures
Routine or view name	Schema	Description
AUDIT_ARCHIVE procedure and table function	SYSPROC	This procedure and table function archives the current audit log.
AUDIT_DELIM_EXTRACT procedure	SYSPROC	This procedure extracts data from the binary archived logs and loads it into delimited files.
AUDIT_LIST_LOGS table function	SYSPROC	This table function returns a list of the archived audit logs at the specified path, for the current database.

Table 5. Automatic Maintenance administrative SQL routines and views
Routine or view name	Schema	Description
AUTOMAINT_GET_POLICY procedure	SYSPROC	This procedure gets the current automatic maintenance settings for the database.
AUTOMAINT_GET_POLICYFILE procedure	SYSPROC	This procedure gets the current automatic maintenance settings for the database.
AUTOMAINT_SET_POLICY procedure	SYSPROC	This procedure sets the automatic maintenance policy settings for the currently connected database.
AUTOMAINT_SET_POLICYFILE procedure	SYSPROC	This procedure sets the automatic maintenance settings for the currently connected database.

Table 6. Common SQL API stored procedures
Routine or view name	Schema	Description
CANCEL_WORK procedure	SYSPROC	This procedure cancels a specified activity. If no unique activity ID is specified, cancels all activity for a connected application, and forces the application off of the system.
DESIGN_ADVISOR procedure	SYSPROC	This procedure retrieves design advisor recommendations from a DB2 server.
GET_CONFIG procedure	SYSPROC	This procedure retrieves data server configuration data, including nodes.cfg file data, database manager configuration data, database configuration data, and registry settings from all database partitions.
GET_MESSAGE procedure	SYSPROC	This procedure retrieves the short message text, long message text, and SQLSTATE for an SQLCODE.
GET_SYSTEM_INFO procedure	SYSPROC	This procedure retrieves information about the data server, including information about the system, the current instance, installed DB2 database products, environment variables, available CPUs, and other system information.
SET_CONFIG procedure	SYSPROC	This procedure updates the configuration parameters retrieved by the GET_CONFIG procedure.

Table 7. Configuration administrative SQL routines and views
Routine or view name	Schema	Description
DB_PARTITIONS table function	SYSPROC	This table function returns the contents of the db2nodes.cfg file in table form.
DBCFG administrative view	SYSIBMADM	This administrative view returns database configuration information.
DBMCFG administrative view	SYSIBMADM	This administrative view returns database manager configuration information.
REG_VARIABLES administrative view	SYSIBMADM	This administrative view returns the DB2 registry settings from all database partitions.

Table 8. Environment administrative views
View name	Schema	Description
ENV_FEATURE_INFO administrative view	SYSPROC	This administrative view returns information about all available features for which a license is required.
ENV_INST_INFO administrative view	SYSIBMADM	This administrative view returns information about the current instance.
ENV_PROD_INFO administrative view	SYSIBMADM	This administrative view returns information about installed DB2 database products.
ENV_SYS_INFO administrative view	SYSIBMADM	This administrative view returns information about the system.
ENV_SYS_RESOURCES administrative view	SYSIBMADM	This administrative view returns operating system, CPU, memory and other information related to the system

Table 9. Health snapshot administrative SQL routines
Routine name	Schema	Description
HEALTH_CONT_HI table function	SYSPROC	This table function returns a table with health indicator information for containers from a health snapshot of a database.
HEALTH_CONT_HI_HIS table function	SYSPROC	This table function returns a table with health indicator history information for containers from a health snapshot of a database.
HEALTH_CONT_INFO table function	SYSPROC	This table function returns a table with rolled-up alert state information for containers from a health snapshot of a database.
HEALTH_DB_HI table function	SYSPROC	This table function returns a table with health indicator information from a health snapshot of a database.
HEALTH_DB_HI_HIS table function	SYSPROC	This table function returns a table with health indicator history information from a health snapshot of a database.
HEALTH_DB_HIC table function	SYSPROC	This table function returns collection health indicator information from a health snapshot of a database.
HEALTH_DB_HIC_HIS table function	SYSPROC	This table function returns collection health indicator history information from a health snapshot of a database.
HEALTH_DB_INFO table function	SYSPROC	This table function returns a table with rolled-up alert state information from a health snapshot of one or all databases.
HEALTH_DBM_HI table function	SYSPROC	This table function returns a table with health indicator information from a health snapshot of the DB2 database manager.
HEALTH_DBM_HI_HIS table function	SYSPROC	This table function returns a table with health indicator history information from a health snapshot of the DB2 database manager.
HEALTH_DBM_INFO table function	SYSPROC	This table function returns a table with rolled-up alert state information from a health snapshot of the DB2 database manager.
HEALTH_GET_ALERT_ACTION_CFG table function	SYSPROC	This table function returns health alert action configuration settings for objects of various types (dbm, database, table space, and table space containers) and for various configuration levels (install default, instance, global, and object).
HEALTH_GET_ALERT_CFG table function	SYSPROC	This table function returns health alert configuration settings for objects of various types (dbm, database, table space, table space containers) and for various configuration levels (install default, global, and object).
HEALTH_GET_IND_DEFINITION table function	SYSPROC	This table function returns the health indicator definition.
HEALTH_HI_REC procedure	SYSPROC	This procedure retrieves a set of recommendations that address a health indicator in alert state on a particular DB2 object.
HEALTH_TBS_HI table function	SYSPROC	This table function returns a table with health indicator information for table spaces from a health snapshot of a database.
HEALTH_TBS_HI_HIS table function	SYSPROC	This table function returns a table with health indicator history information for table spaces from a health snapshot of a database.
HEALTH_TBS_INFO table function	SYSPROC	This table function returns a table with rolled-up alert state information for table spaces from a health snapshot of a database.

Table 10. Monitor SQL routines
Routine name	Schema	Description
EVMON_FORMAT_UE_TO_TABLES procedure	SYSPROC	This procedure retrieves data stored in an unformatted event table and moves the XML document into a set of relational tables.
EVMON_FORMAT_UE_TO_XML table function	SYSPROC	This table function extracts binary events from an unformatted event table and formats them into an XML document.
MON_GET_ACTIVITY_DETAILS	SYSPROC	This table function returns details about an activity, including general activity information and a set of metrics for the activity.
MON_GET_BUFFERPOOL table function	SYSPROC	This table function returns monitor metrics for one or more buffer pools.
MON_GET_CONNECTION table function	SYSPROC	This table function returns metrics for one or more connections.
MON_GET_CONNECTION_DETAILS table function	SYSPROC	This table function returns detailed metrics for one or more connections.
MON_GET_CONTAINER table function	SYSPROC	This table function returns monitor metrics for one or more table space containers.
MON_GET_EXTENT_MOVEMENT_STATUS table function	SYSPROC	This table function returns the status of the extent movement operation.
MON_GET_FCM	SYSPROC	This table function returns metrics for the fast communication manager (FCM).
MON_GET_FCM_CONNECTION_LIST	SYSPROC	This table function returns monitor metrics for all the fast communication manager (FCM) connections on the specified member or members.
MON_GET_INDEX table function	SYSPROC	This table function returns metrics for one or more indexes.
MON_GET_LOCKS table function	SYSPROC	This table function returns a list of all locks in the currently connected database.
MON_GET_MEMORY_POOL table function	SYSPROC	This table function retrieves metrics from the memory pools contained within a memory set.
MON_GET_MEMORY_SET table function	SYSPROC	This table function retrieves metrics from the allocated memory sets, both at the instance level and for all active databases within the instance.
MON_GET_PKG_CACHE_STMT table function	SYSPROC	This table function returns a point-in-time view of both static and dynamic SQL statements in the database package cache.
MON_GET_SERVICE_SUBCLASS table function	SYSPROC	This table function returns metrics for one or more service subclasses.
MON_GET_SERVICE_SUBCLASS_DETAILS table function	SYSPROC	This table function returns detailed metrics for one or more service subclasses.
MON_GET_TABLE table function	SYSPROC	This table function returns monitor metrics for one or more tables.
MON_GET_TABLESPACE table function	SYSPROC	This table function returns monitor metrics for one or more table spaces.
MON_GET_UNIT_OF_WORK table function	SYSPROC	This table function returns metrics for one or more units of work.
MON_GET_UNIT_OF_WORK_DETAILS table function	SYSPROC	This table function returns detailed metrics for one or more units of work.
MON_GET_WORKLOAD table function	SYSPROC	This table function returns metrics for one or more workloads.
MON_GET_WORKLOAD_DETAILS table function	SYSPROC	This table function returns detailed metrics for one or more workloads.

Table 11. MQSeries administrative SQL routines
Routine name	Schema	Description
MQPUBLISH scalar function	DB2MQ, DB2MQ1C	This scalar function publishes data to an MQSeries location.
MQREAD scalar function	DB2MQ, DB2MQ1C	This scalar function returns a message from an MQSeries location.
MQREADALL table function	DB2MQ, DB2MQ1C	This table function returns a table with messages and message metadata from an MQSeries location.
MQREADALLCLOB table function	DB2MQ	This table function returns a table containing messages and message metadata from a specified MQSeries location.
MQREADCLOB scalar function	DB2MQ	This scalar function returns a message from a specified MQSeries location.
MQRECEIVE scalar function	DB2MQ, DB2MQ1C	This scalar function returns a message from an MQSeries location and removes the message from the associated queue.
MQRECEIVEALL table function	DB2MQ, DB2MQ1C	This table function returns a table containing the messages and message metadata from an MQSeries location and removes the messages from the associated queue.
MQRECEIVEALLCLOB table function	DB2MQ	This table function returns a table containing messages and message metadata from a specified MQSeries location.
MQRECEIVECLOB scalar function	DB2MQ	This scalar function returns a message from a specified MQSeries location.
MQSEND scalar function	DB2MQ, DB2MQ1C	This scalar function sends data to an MQSeries location.
MQSUBSCRIBE scalar function	DB2MQ, DB2MQ1C	This scalar function subscribes to MQSeries messages published on a specific topic.
MQUNSUBSCRIBE scalar function	DB2MQ, DB2MQ1C	This scalar function unsubscribes from MQSeries messages published on a specific topic.

Table 12. Security administrative SQL routines and views:
Routine or view name	Schema	Description
AUTH_LIST_AUTHORITIES_FOR_AUTHID table function	SYSPROC	This table function returns all authorities held by the authorization ID, either found in the database configuration file or granted to an authorization ID directly or indirectly through a group or a role.
AUTH_LIST_GROUPS_FOR_AUTHID table function	SYSPROC	This table function returns the list of groups of which the given authorization ID is a member.
AUTH_LIST_ROLES_FOR_AUTHID function	SYSPROC	This function returns the list of roles in which the given authorization ID is a member.
AUTHORIZATIONIDS administrative view	SYSIBMADM	This administrative view contains a list of authorization IDs that have been granted privileges or authorities, along with their types, for the currently connected database.
OBJECTOWNERS administrative view	SYSIBMADM	This administrative view contains all object ownership information for the currently connected database.
PRIVILEGES administrative view	SYSIBMADM	This administrative view contains all explicit privileges for the currently connected database.

Table 13. Snapshot administrative SQL routines and views
Routine or view name	Schema	Description
APPL_PERFORMANCE administrative view	SYSIBMADM	This administrative view displays information about the rate of rows selected versus rows read per application.
APPLICATIONS administrative view	SYSIBMADM	This administrative view returns information about the connected database applications.
BP_HITRATIO administrative view	SYSIBMADM	This administrative view returns bufferpool hit ratios, including total, data, and index, in the database.
BP_READ_IO administrative view	SYSIBMADM	This administrative view returns bufferpool read performance information.
BP_WRITE_IO administrative view	SYSIBMADM	This administrative view returns bufferpool write performance information per bufferpool.
CONTAINER_UTILIZATION administrative view	SYSIBMADM	This administrative view returns information about table space containers and utilization rates.
LOCKS_HELD administrative view	SYSIBMADM	This administrative view returns information about the current locks held.
LOCKWAITS administrative view	SYSIBMADM	This administrative view returns information about the locks that are waiting to be granted.
LOG_UTILIZATION administrative view	SYSIBMADM	This administrative view returns information about log utilization for the currently connected database.
LONG_RUNNING_SQL administrative view	SYSIBMADM	This administrative view returns the longest running SQL statements in the currently connected database.
QUERY_PREP_COST administrative view	SYSIBMADM	This administrative view returns a list of statements with information about the time required to prepare the statement.
SNAP_WRITE_FILE procedure	SYSPROC	This procedure writes system snapshot data to a file in the tmp subdirectory of the instance directory.
SNAPAGENT administrative view and SNAP_GET_AGENT table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return information about agents from an application snapshot, in particular, the agent logical data group.
SNAPAGENT_MEMORY_POOL administrative view and SNAP_GET_AGENT_MEMORY_POOL table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return information about memory usage at the agent level.
SNAPAPPL administrative view and SNAP_GET_APPL_V95 table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return information about applications from an application snapshot, in particular, the appl logical data group.
SNAPAPPL_INFO administrative view and SNAP_GET_APPL_INFO_V95 table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return information about applications from an application snapshot, in particular, the appl_info logical data group.
SNAPBP administrative view and SNAP_GET_BP_V95 table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return information about buffer pools from a bufferpool snapshot, in particular, the bufferpool logical data group.
SNAPBP_PART administrative view and SNAP_GET_BP_PART table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return information about buffer pools from a bufferpool snapshot, in particular, the bufferpool_nodeinfo logical data group.
SNAPCONTAINER administrative view and SNAP_GET_CONTAINER_V91 table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return table space snapshot information from the tablespace_container logical data group.
SNAPDB administrative view and SNAP_GET_DB_V95 table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return snapshot information from the database (dbase) and database storage (db_storage_group) logical groupings.
SNAPDBM administrative view and SNAP_GET_DBM_V95 table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return the snapshot monitor DB2 database manager (dbm) logical grouping information.
SNAPDETAILLOG administrative view and SNAP_GET_DETAILLOG_V91 table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return snapshot information from the detail_log logical data group.
SNAPDYN_SQL administrative view and SNAP_GET_DYN_SQL_V95 table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return snapshot information from the dynsql logical data group.
SNAPFCM administrative view and SNAP_GET_FCM table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return information about the fast communication manager (FCM) from a database manager snapshot, in particular, the fcm logical data group.
SNAPFCM_PART administrative view and SNAP_GET_FCM_PART table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return information about the fast communication manager (FCM) from a database manager snapshot, in particular, the fcm_node logical data group.
SNAPHADR administrative view and SNAP_GET_HADR table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return information about high availability disaster recovery from a database snapshot, in particular, the hadr logical data group.
SNAPLOCK administrative view and SNAP_GET_LOCK table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return snapshot information about locks, in particular, the lock logical data group.
SNAPLOCKWAIT administrative view and SNAP_GET_LOCKWAIT table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return snapshot information about lock waits, in particular, the lockwait logical data group.
SNAPSTMT administrative view and SNAP_GET_STMT table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return information about statements from an application snapshot.
SNAPSTORAGE_PATHS administrative view and SNAP_GET_STORAGE_PATHS table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return a list of automatic storage paths for the database including file system information for each storage path, specifically, from the db_storage_group logical data group
SNAPSUBSECTION administrative view and SNAP_GET_SUBSECTION table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return information about application subsections, namely the subsection logical monitor grouping.
SNAPSWITCHES administrative view and SNAP_GET_SWITCHES table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return information about the database snapshot switch state.
SNAPTAB administrative view and SNAP_GET_TAB_V91 table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return snapshot information from the table logical data group.
SNAPTAB_REORG administrative view and SNAP_GET_TAB_REORG table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return table reorganization information.
SNAPTBSP administrative view and SNAP_GET_TBSP_V91 table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return snapshot information from the table space logical data group.
SNAPTBSP_PART administrative view and SNAP_GET_TBSP_PART_V91 table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return snapshot information from the tablespace_nodeinfo logical data group.
SNAPTBSP_QUIESCER administrative view and SNAP_GET_TBSP_QUIESCER table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return information about quiescers from a table space snapshot.
SNAPTBSP_RANGE administrative view and SNAP_GET_TBSP_RANGE table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return information from a range snapshot.
SNAPUTIL administrative view and SNAP_GET_UTIL table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return snapshot information about the utilities from the utility_info logical data group.
SNAPUTIL_PROGRESS administrative view and SNAP_GET_UTIL_PROGRESS table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return information about utility progress, in particular, the progress logical data group.
TBSP_UTILIZATION administrative view	SYSIBMADM	This administrative view returns table space configuration and utilization information.
TOP_DYNAMIC_SQL administrative view	SYSIBMADM	This administrative view returns the top dynamic SQL statements sortable by number of executions, average execution time, number of sorts, or sorts per statement.

Table 14. SQL procedures administrative SQL routines
Routine name	Schema	Description
ALTER_ROUTINE_PACKAGE procedure	SYSPROC	This procedure alters values for the package associated with a compiled SQL routine or a compiled trigger, without the need for rebinding.
GET_ROUTINE_OPTS scalar function	SYSPROC	This scalar function returns a character string value of the options that are to be used for the creation of SQL procedures in the current session.
GET_ROUTINE_SAR procedure	SYSFUN	This procedure returns the information necessary to install an identical routine on another database server running at least at the same level and operating system.
PUT_ROUTINE_SAR procedure	SYSFUN	This procedure passes the information necessary to create and define an SQL routine at the database server.
REBIND_ROUTINE_PACKAGE procedure	SYSPROC	This procedure rebinds the package associated with an SQL procedure.
SET_ROUTINE_OPTS procedure	SYSPROC	This procedure sets the options that are to be used for the creation of SQL procedures in the current session.

Table 15. Stepwise redistribute administrative SQL routines
Routine name	Schema	Description
ANALYZE_LOG_SPACE procedure	SYSPROC	This procedure returns log space analysis information.
GENERATE_DISTFILE procedure	SYSPROC	This procedure generates a data distribution file.
GET_SWRD_SETTINGS procedure	SYSPROC	This procedure returns redistribute information.
SET_SWRD_SETTINGS procedure	SYSPROC	This procedure creates or changes the redistribute registry.
STEPWISE_REDISTRIBUTE_DBPG procedure	SYSPROC	This procedure redistributes part of database partition group.

Table 16. Storage management tool administrative SQL routines
Routine name	Schema	Description
CAPTURE_STORAGEMGMT_INFO procedure	SYSPROC	This procedure returns storage-related information for a given root object.
CREATE_STORAGEMGMT_TABLES procedure	SYSPROC	This procedure creates storage management tables.
DROP_STORAGEMGMT_TABLES procedure	SYSPROC	This procedure drops all storage management tables.

Table 17. Text search administrative SQL routines
Routine name	Schema	Description
SYSTS_ADMIN_CMD stored procedure	SYSPROC	This procedure runs text search administrative commands using the SQL CALL statement.
SYSTS_ALTER procedure	SYSPROC	This procedure changes the update characteristics of an index.
SYSTS_CLEAR_COMMANDLOCKS procedure	SYSPROC	This procedure removes all command locks for a specific text search index or for all text search indexes in the database.
SYSTS_CLEAR_EVENTS procedure	SYSPROC	This procedure deletes indexing events from an index's event table used for administration.
SYSTS_CREATE procedure	SYSPROC	This procedure creates a text search index for a text column which allows the column data to be searched using text search functions.
SYSTS_DISABLE procedure	SYSPROC	This procedure disables DB2 Text Search for the current database.
SYSTS_DROP procedure	SYSPROC	This procedure drops an existing text search index associated with any table column.
SYSTS_ENABLE procedure	SYSPROC	This procedure must be issued successfully before text search indexes on columns in tables within the database can be created.
SYSTS_UPDATE procedure	SYSPROC	This procedure updates the text search index to reflect the current contents of the text columns with which the index is associated.

Table 18. Workload management administrative SQL routines
Routine name	Schema	Description
WLM_CANCEL_ACTIVITY procedure	SYSPROC	This procedure cancels the given activity.
WLM_CAPTURE_ACTIVITY_IN_PROGRESS procedure	SYSPROC	This procedure sends information about the given activity to the activities event monitor.
WLM_COLLECT_STATS procedure	SYSPROC	This procedure sends statistics for service classes, workloads, work classes and threshold queues to the statistics event monitor and resets the in-memory copy of the statistics.
WLM_GET_QUEUE_STATS table function	SYSPROC	This table function returns basic statistic information for one or more threshold queues.
WLM_GET_SERVICE _CLASS_AGENTS_V97 table function	SYSPROC	This table function returns the list of agents on the given partition that are executing in the service class given by the SERVICE_SUPERCLASS_NAME and SERVICE_SUBCLASS_NAME or on behalf of the application given by the APPLICATION_HANDLE.
WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES_V97 table function	SYSPROC	This table function returns the list of all workload occurrences executing in a given service class on a particular partition.
WLM_GET_SERVICE_SUBCLASS_STATS_V97 table function	SYSPROC	This table function returns basic statistics of one or more service subclasses.
WLM_GET_SERVICE_SUPERCLASS_STATS table function	SYSPROC	This table function returns basic statistics of one or more service superclasses.
WLM_GET_WORK_ACTION_SET_STATS table function	SYSPROC	This table function returns basic statistics for work classes in a work action set.
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES_V97 table function	SYSPROC	This table function returns the list of all activities that were submitted through the given application on the specified partition and have not yet completed.
WLM_GET_WORKLOAD_STATS_V97 table function	SYSPROC	This table function returns basic statistics for one or more workloads.
WLM_SET_CLIENT_INFO procedure	SYSPROC	This procedure sets client information associated with the current connection at the DB2 database server.

Table 19. Miscellaneous administrative SQL routines and views
Routine or view name	Schema	Description
ADMIN_COPY_SCHEMA procedure	SYSPROC	This procedure is used to copy a specific schema and all objects contained in it.
ADMIN_DROP_SCHEMA procedure	SYSPROC	This procedure is used to drop a specific schema and all objects contained in it.
ADMIN_MOVE_TABLE procedure	SYSPROC	This procedure moves data in an active table into a new table object with the same name, while the data remains online and available for access.
ADMIN_MOVE_TABLE_UTIL procedure	SYSPROC	This procedure alters the user definable values used by the ADMIN_MOVE_TABLE procedure.
ALTOBJ procedure	SYSPROC	This procedure alters an existing table using the input CREATE TABLE statement as the target table definition.
APPLICATION_ID scalar function	SYSFUN	This scalar function returns the application ID of the current connection.
COMPILATION_ENV table function	SYSPROC	This table function returns the elements of a compilation environment.
CONTACTGROUPS administrative view	SYSIBMADM	This administrative view returns the list of contact groups.
CONTACTS administrative view	SYSIBMADM	This administrative view returns the list of contacts defined on the database server.
DB_HISTORY administrative view	SYSIBMADM	This administrative view returns information from the history file that is associated with the currently connected database partition.
DBPATHS administrative view	SYSIBMADM	This administrative view returns the values for database paths required for tasks such as split mirror backups.
EXPLAIN_FORMAT_STATS scalar function	SYSPROC	This new scalar function is used to display formatted statistics information which is parsed and extracted from explain snapshot captured for a given query.
EXPLAIN_GET_MSGS table function	The schema is the same as the Explain table schema.	This table function queries the EXPLAIN_DIAGNOSTIC and EXPLAIN_DIAGNOSTIC_DATA Explain tables, and returns formatted messages.
GET_DBSIZE_INFO procedure	SYSPROC	This procedure calculates the database size and maximum capacity.
NOTIFICATIONLIST administrative view	SYSIBMADM	This administrative view returns the list of contacts and contact groups that are notified about the health of an instance.
PD_GET_DIAG_HIST table function	SYSPROC	The table function returns log records, event records and notification records from a given facility.
PDLOGMSGS_LAST24HOURS administrative view and PD_GET_LOG_MSGS table function	SYSIBMADM (administrative view), SYSPROC (table function)	This administrative view and table function return problem determination log messages that were logged in the DB2 notification log. The information is intended for use by database and system administrators.
REORGCHK_IX_STATS procedure	SYSPROC	This procedure checks index statistics to determine whether or not there is a need for reorganization.
REORGCHK_TB_STATS procedure	SYSPROC	This procedure checks table statistics to determine whether or not there is a need for reorganization.
SQLERRM scalar function	SYSPROC	This scalar function has two versions. The first allows for full flexibility of message retrieval including using message tokens and language selection. The second is a simple interface which takes only an SQLCODE as an input parameter and returns the short message in English.
SYSINSTALLOBJECTS procedure	SYSPROC	This procedure creates or drops the database objects that are required for a specific tool.