Performance Services

These services include procedures that provide interfaces to work with indexes and a view to see information about database monitors.

ACT_ON_INDEX_ADVICE procedure
The ACT_ON_INDEX_ADVICE procedure creates new indexes for a table based on indexes that have been advised for the table.
ACTIVE_QUERY_INFO table function
The ACTIVE_QUERY_INFO table function returns information about active SQL Query Engine (SQE) queries. An active query is either open or pseudo-closed.
ADD_QUERY_THRESHOLD procedure
The ADD_QUERY_THRESHOLD procedure defines a new threshold to be used by the Query Supervisor. Adding a threshold only affects subsequently executed queries. Queries that are currently running continue to use the thresholds that were in effect when the query execution was initiated. The Query Supervisor only supervises user jobs. It does not affect system jobs or queries run by the operating system. The thresholds only apply to resources used by the SQL Query Engine (SQE).
DATABASE_MONITOR_INFO view
The DATABASE_MONITOR_INFO view returns information about database monitors and plan cache event monitors on the server. Database monitors are started using the Start Database Monitor (STRDBMON) command. The QSYS2.START_PLAN_CACHE_EVENT_MONITOR procedure is used to start a plan cache event monitor. SQL Performance Monitors within IBM i Navigator are synonymous with database monitors and are included in this view.
HARVEST_INDEX_ADVICE procedure
The HARVEST_INDEX_ADVICE procedure generates one or more CREATE INDEX statements in source file members for a specified table based on indexes that have been advised for the table.
MTI_INFO table function
The MTI_INFO table function returns information about Maintained Temporary Indexes (MTIs).
QUERY_SUPERVISOR view
The QUERY_SUPERVISOR view contains the threshold rules defined for the Query Supervisor.
REMOVE_INDEXES procedure
The REMOVE_INDEXES procedure drops any indexes meeting the specified criteria.
REMOVE_QUERY_THRESHOLD procedure
The REMOVE_QUERY_THRESHOLD procedure removes a threshold that is monitored by the Query Supervisor. Removing a threshold only affects subsequently executed queries. Queries that are currently running will continue to use the thresholds that were in effect when the query execution was initiated.
RESET_TABLE_INDEX_STATISTICS procedure
The RESET_TABLE_INDEX_STATISTICS procedure clears usage statistics for indexes defined over a table or tables and optionally deletes rows from the index advice tracking table.

Parent topic: Db2 for i Services

Related reference

QAQQINI file override support