DB2 Version 10.1 for Linux, UNIX, and Windows

Supported built-in SQL routines and views

Provides information about supported built-in SQL routines and views.

This topic provides information about the following built-in SQL routines:
  • Administrative SQL routines and the ADMIN_CMD stored procedure: Table 1
  • Administrative task scheduler routines and views: Table 2
  • Audit routines and procedures: Table 3
  • Automatic maintenance built-in SQL routines and views:Table 4
  • Common SQL API stored procedures: Table 5
  • Configuration built-in SQL routines and views: Table 6
  • DB2® pureScale® instance administrative views: Table 7
  • Environment built-in SQL routines and views: Table 8
  • Explain routines: Table 9
  • Monitor built-in SQL routines: Table 10
  • MQSeries® built-in SQL routines: Table 11
  • Security built-in SQL routines and views: Table 12
  • Snapshot built-in SQL routines and views: Table 13
  • SQL procedure built-in routines: Table 14
  • Stepwise redistribute built-in SQL routines: Table 15
  • Storage management tool built-in SQL routines: Table 16
  • Text search built-in SQL routines: Table 17
  • Workload Management routines: Table 18
  • Miscellaneous built-in SQL routines and views: Table 19
Table 1. 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_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_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_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_INTRA_PARALLEL scalar function SYSPROC This scalar function returns the current state of intrapartition parallelism for the application.
ADMIN_GET_MEM_USAGE table function SYSPROC This table function returns the memory usage statistics for a given member.
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_GET_STORAGE_PATHS table function 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
ADMIN_GET_TAB_COMPRESS_INFO table function SYSPROC This table function returns compression estimates for tables, materialized query tables (MQT) and hierarchy tables.
ADMIN_GET_TAB_DICTIONARY_INFO table function SYSPROC This table function returns dictionary information for tables, materialized query tables (MQT) and hierarchy tables.
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_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.
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.
ADMIN_SET_INTRA_PARALLEL procedure SYSPROC This procedure enables or disables intrapartition parallelism for a database application.
ADMINTABINFO and ADMIN_GET_TAB_INFO 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 2. 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 3. 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 4. Automatic Maintenance built-in 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 5. 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 IBM DB2 10.1 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 6. Configuration built-in SQL routines and views
Routine or view name Schema Description
DBCFG administrative view SYSIBMADM This administrative view returns database configuration information.
DBMCFG administrative view SYSIBMADM This administrative view returns database manager configuration information.
Table 7. DB2 pureScale instance administrative views
View name Schema Description
DB_MEMBERS table function SYSIBMADM This table function returns basic member information about a DB2 pureScale instance.
DB2_CLUSTER_HOST_STATE administrative view SYSIBMADM The DB2_CLUSTER_HOST_STATE administrative view and the associated DB2_GET_CLUSTER_HOST_STATE table function retrieve information about the hosts that are part of a DB2 pureScale instance.
DB2_INSTANCE_ALERTS administrative view SYSIBMADM This view provides information about alerts in the DB2 pureScale instance.
DB2_MEMBER and DB2_CF administrative views SYSIBMADM The DB2_MEMBER and DB2_CF administrative views and the associated DB2_GET_INSTANCE_INFO table function return information about the members and cluster caching facilities of a DB2 pureScale instance, including state information where applicable.
Table 8. Environment built-in SQL routines and views
View name Schema Description
ENV_CF_SYS_RESOURCES administrative view SYSIBMADM This administrative view returns a list of system resources used by the cluster caching facilities (also known as CFs) on the system.
ENV_FEATURE_INFO administrative view SYSPROC This administrative view returns information about all available features for which a license is required.
ENV_GET_DB2_SYSTEM_RESOURCES table function SYSPROC This table function returns CPU usage and DB2 process information for specified members in the current instance.
ENV_GET_NETWORK_RESOURCES table function SYSPROC This table function returns information for all active network adaptors on the host machines running DB2.
ENV_GET_REG_VARIABLES table function SYSPROC This table function returns the DB2 registry settings from one or all database members.
ENV_GET_SYSTEM_RESOURCES table function SYSPROC This table function returns operating system, CPU, memory and other information related to members on the system.
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.
Table 9. Explain Routines
Routine Name Schema Description
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.
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_FROM_ACTIVITY procedure SYSPROC This procedure explains a specific execution of a statement using the contents of the section obtained from an activity event monitor.
EXPLAIN_FROM_CATALOG procedure SYSPROC This procedure explains a statement using the contents of the section obtained from the catalogs.
EXPLAIN_FROM_DATA procedure SYSPROC This procedure explains a statement using the contents of the input section.
EXPLAIN_FROM_SECTION procedur SYSPROC This procedure explains a statement using the contents of the section obtained from the package cache or from the package cache event monitor.
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.
EVMON_UPGRADE_TABLES procedure SYSPROC This procedure alters event monitor target SQL or unformatted event tables to accommodate new or changed monitoring elements that have been added since the event monitor was created.
MON_BP_UTILIZATION administrative view SYSIBMADM This administrative view returns key monitoring metrics, including hit ratios and average read and write times, for all buffer pools and all database partitions in the currently connected database.
MON_CONNECTION_SUMMARY administrative view SYSIBMADM This administrative view returns key metrics for all connections in the currently connected database.
MON_CURRENT_SQL administrative view SYSIBMADM This administrative view returns key metrics for all activities that were submitted on all members of the database and have not yet been completed.
MON_CURRENT_UOW administrative view SYSIBMADM This administrative view returns key metrics for all units of work that were submitted on all members of the database.
MON_DB_SUMMARY administrative view SYSIBMADM This administrative view returns key metrics aggregated over all service classes in the currently connected database.
MON_FORMAT_LOCK_NAME table function SYSPROC This table function formats the internal lock name and returns details regarding the lock in a row-based format.
MON_FORMAT_XML_COMPONENT_TIMES_BY_ROW table function SYSPROC This table function returns formatted row-based output for the component times contained in an XML metrics document.
MON_FORMAT_XML_METRICS_BY_ROW table function SYSPROC This table function returns formatted row-based output for all metrics contained in an XML metrics document.
MON_FORMAT_XML_TIMES_BY_ROW table function SYSPROC This table function returns formatted row based output for the combined hierarchy of wait and processing times that are contained in an XML metrics document.
MON_FORMAT_XML_WAIT_TIMES_BY_ROW table function SYSPROC This table function returns formatted row-based output for the wait times contained in an XML metrics document.
MON_GET_ACTIVITY_DETAILS table function SYSPROC This table function returns details about an activity, including general activity information and a set of metrics for the activity.
MON_GET_APPL_LOCKWAIT table function SYSPROC This table function returns information about all locks that each application's agents (that are connected to the current database) are waiting to acquire.
MON_GET_APPLICATION_HANDLE scalar function SYSPROC This scalar function returns the application handle of the invoking application.
MON_GET_APPLICATION_ID scalar function SYSPROC This scalar function returns the application ID of the invoking application.
MON_GET_AUTO_MAINT_QUEUE table function SYSPROC This table function returns information about all automatic maintenance jobs (with the exception of real-time statistics which does not submit jobs on the automatic maintenance queue) that are currently queued for execution by the autonomic computing daemon (db2acd).
MON_GET_AUTO_RUNSTATS_QUEUE table function SYSPROC This table function returns information about all objects which are currently queued for evaluation by automatic statistics collection in the currently connected database.
MON_GET_BUFFERPOOL table function SYSPROC This table function returns monitor metrics for one or more buffer pools.
MON_GET_CF table function SYSPROC This table function returns status information about one or more cluster caching facilities in a DB2 pureScale environment.
MON_GET_CF_CMD table function SYSPROC This table function returns information about the processing time for cluster caching facility (CF) commands.
MON_GET_CF_WAIT_TIME table function SYSPROC This table function reports the total amount of time, in microseconds, that are spent waiting for the cluster caching facilities (CFs) to process a request. This time includes the time that is taken for related communications to the cluster caching facilities.
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_EXTENDED_LATCH_WAIT table function SYSPROC This function returns information for latches which have been involved in extended latch waits.
MON_GET_EXTENT_MOVEMENT_STATUS table function SYSPROC This table function returns the status of the extent movement operation.
MON_GET_FCM table function SYSPROC This table function returns metrics for the fast communication manager (FCM).
MON_GET_FCM_CONNECTION_LIST table function SYSPROC This table function returns monitor metrics for all the fast communication manager (FCM) connections on the specified member or members.
MON_GET_GROUP_BUFFERPOOL table function SYSPROC This table function returns statistics about the group bufferpool, including the number of times the GBP_FULL error is encountered.
MON_GET_HADR table function SYSPROC This function returns high availability disaster recovery (HADR) monitoring information.
MON_GET_INDEX table function SYSPROC This table function returns metrics for one or more indexes.
MON_GET_INDEX_USAGE_LIST table function SYSPROC This table function returns information from a usage list defined for an index.
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 memory pools contained within a memory set.
MON_GET_PAGE_ACCESS_INFO table function SYSPROC This table function returns information about bufferpool pages that are being waited on for a specified table.
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_PKG_CACHE_STMT_DETAILS table function SYSPROC This table function returns detailed metrics for one or more package cache entries.
MON_GET_REBALANCE_STATUS table function SYSPROC This table function returns the status of a rebalance operation on a table space.
MON_GET_ROUTINE table function SYSPROC This table function returns aggregated execution metrics for procedures, external procedures, compiled functions, compiled triggers, and anonymous blocks invoked since the database was activated.
MON_GET_ROUTINE_DETAILS table function SYSPROC This table function returns aggregated execution metrics for procedures, external functions, compiled functions, compiled triggers, and anonymous blocks invoked since the database was activated. The metrics are returned in an XML document.
MON_GET_ROUTINE_EXEC_LIST table function SYSPROC This table function returns a list of all statements (sections) executed by each procedure, external function, compiled function, compiled trigger, and anonymous block invoked since the database was activated.
MON_GET_RTS_RQST table function SYSPROC This table function returns information about all real-time statistics requests that are pending in the system, and the set of requests that are currently being processed by the real time statistics daemon (such as on the real-time statistics processing queue).
MON_GET_SECTION_ROUTINE table function SYSPROC This table function returns a list of all procedures, external functions, compiled functions, and compiled triggers that might be invoked during the execution of the input section.
MON_GET_SERVERLIST table function SYSPROC This table function returns metrics on the server list for the currently connected database as cached on one or more members.
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_TABLE_USAGE_LIST table function SYSPROC This table function returns information from a usage list defined for a table.
MON_GET_TRANSACTION_LOG table function SYSPROC This table function returns information about the transaction logging subsystem for the currently connected database.
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_USAGE_LIST_STATUS table function SYSPROC This table function returns current status on a usage list.
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.
MON_INCREMENT_INTERVAL_ID procedure SYSPROC This procedure increments the monitoring interval by 1 and returns the new value in the output argument.
MON_LOCKWAITS administrative view SYSPROC This administrative view returns information about agents working on behalf of applications that are waiting to obtain locks in the currently connected database.
MON_PKG_CACHE_SUMMARY administrative view SYSIBMADM This administrative view returns key metrics for both static and dynamic SQL statements in the cache, providing a high-level summary of the database package cache.
MON_SAMPLE_SERVICE_CLASS_METRICS table function SYSPROC The table function reads system metrics for one or more service classes across one or more databases at two points in time: at the time the function is called and after a given amount of time has passed.
MON_SAMPLE_WORKLOAD_METRICS table function SYSPROC The table function reads system metrics for one or more workloads across one or more databases at two points in time: at the time the function is called and after a given amount of time has passed.
MON_SERVICE_SUBCLASS_SUMMARY administrative view SYSIBMADM This administrative view returns key metrics for all service subclasses in the currently connected database.
MON_TBSP_UTILIZATION administrative view SYSIBMADM This administrative view returns key monitoring metrics, including hit ratios and utilization percentage, for all table spaces and all database partitions in the currently connected database.
MON_WORKLOAD_SUMMARY administrative view SYSIBMADM This administrative view returns key metrics for all workloads in the currently connected database.
Table 11. MQSeries built-in 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 built-in SQL routines and views:
Routine or view name Schema Description
AUTH_GET_INSTANCE_AUTHID scalar function SYSPROC This scalar function returns the authorization ID of the instance owner.
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 built-in 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.
SNAPAPPL administrative view and SNAP_GET_APPL 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 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 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 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 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 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 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 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.
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.
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 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 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 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 procedure built-in 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_NAME SYSPROC This procedure returns the name of an SQL PL object given the object's LIB_ID.
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 built-in 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 built-in 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 built-in 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_CLEANUP procedure SYSPROC This procedure enables removal of obsolete DB2 Text Search index collections within a database.
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_CONFIGURE procedure SYSPROC This procedure applies text search server connection information to the text search catalog
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.
SYSTS_UPGRADE_CATALOG procedure SYSPROC This procedure upgrades the DB2 Text Search catalog, including the administrative tables and administrative views, to the latest product version.
SYSTS_UPGRADE_INDEX procedure SYSPROC This procedure updates DB2 Text Search index information in the text search catalog tables.
Table 18. Workload management built-in 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_CONN_ENV - get settings for activity data collection for a connection SYSPROC This table function returns for a particular connection the values of settings that control collection of activity data and section actuals.
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 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 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 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 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 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.
WLM_SET_CONN_ENV - enable collection of activity data and measurement of section actuals SYSPROC This procedure enables for a particular connection the collection of activity data and measurement of section actuals.
Table 19. Miscellaneous built-in SQL routines and views
Routine or view name Schema Description
ALTOBJ procedure SYSPROC This procedure alters an existing table using the input CREATE TABLE statement as the target table definition.
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.
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.
SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY procedure SYSPROC This procedure sets the internal option to disable recording of temporal history for a system-period temporal table and allows values to be specified for the special temporal columns.
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.
Related reference:
Deprecated SQL administrative routines and views