ADMIN_UTL_MONITOR stored procedure

SYSPROC.ADMIN_UTL_MONITOR is an autonomic stored procedure that provides functions that enable analysis of database statistics. These functions include alerts for out-of-date, missing, or conflicting statistics, summary reports and detailed table-level reports that describe generated RUNSTATS statements. It is required for the use of autonomic statistics.

Begin general-use programming interface information.

The ADMIN_UTL_MONITOR stored procedure enables for the analysis of database statistics, by providing the following functions:

Issuing RUNSTATS alerts for out-of-date statistics. Counters in the SYSIBM.SYSTABLESPACESTATS table are used to identify out of date statistics. Cloned table spaces, work file table spaces, and directory table spaces are excluded from AUTOSTATS processing.
Issuing RUNSTATS alerts for missing and conflicting database statistics, including statistics for columns, column groups, and indexes.
Generating a summary report for the number of tables analyzed and the number of RUNSTATS alerts that were generated.
Generating detailed table-level reports for RUNSTATS commands, including explanations.
Scheduling execution of the ADMIN_UTL_EXECUTE stored procedure in the administrative task scheduler to resolve alerts.

Environment

The ADMIN_UTL_MONITOR stored procedure runs in a WLM-established address space and uses the Resource Recovery Services attachment facility to connect to DB2®. The ADMIN_UTL_MONITOR stored procedure is required and must be installed and executable.

If the stand-alone option is set to NO, the ADMIN_UTL_MONITOR stored procedure must be defined as a trusted context in RACF® and must run in a specific trusted WLM environment to get a RACF PassTicket for the user that called the stored procedure. The ADMIN_UTL_MONITOR stored procedure calls the following interface routines:

SYSPROC.ADMIN_TASK_ADD
SYSPROC.ADMIN_TASK_REMOVE
SYSPROC.ADMIN_TASK_UPDATE
DSNADM.ADMIN_TASK_LIST
DSNADM.ADMIN_TASK_STATUS

Authorization required

To execute the CALL statement, the owner of the package or plan that contains the CALL statement must have DBADM or higher authority.

Syntax

The following syntax diagram shows the SQL CALL statement for invoking the ADMIN_UTL_MONITOR stored procedure:


>>-CALL--ADMIN_UTL_MONITOR--(----monitor-options---,------------>

>----history-entry-id---,----return-code---,----message---)----><

Option descriptions

The ADMIN_UTIL_MONITOR stored procedure has the following options:

monitor-options

A string representation of the configuration parameters for the ADMIN_UTIL_MONITOR stored procedure, consisting of "NAME = VALUE" pairs separated by commas. Values, including blanks, equal sign, and comma must be surrounded by single or double quotation marks. For example:

restrict-ts='partition=1' is valid.
restrict-ts="partition=1" is valid.
restrict-ts=partition=1 is not valid.

Any value that contains single or double quotation marks must be surrounded by the opposite kind of quotation marks. For example: restrict-ts="DBNAME='DSNDB06'" is valid because the single quotation marks used in the value are surrounded by double quotation marks.

The following optional parameters are available:

The following parameters specify general monitoring options:
restrict-ts
Indicates which table spaces require checking. Allowed values are strings that contain valid contents for a WHERE clause on the SYSIBM.SYSTABLESPACESTATS catalog table. For example:
```
DBNAME LIKE 'MYDB%'
```
If no restriction is specified, all table spaces are checked.
stand-alone

Whether alerts trigger the ADMIN_UTL_EXECUTE stored procedure:
NO

Alerts are written and the ADMIN_UTL_EXECUTE stored procedure is called to resolve the alerts. NO is the default value.

YES

Alerts are written but no call is issued to the ADMIN_UTL_EXECUTE stored procedure.

statistics-scope

Specifies the scope of the statistics for the ADMIN_UTL_MONITOR stored procedure to check:
BASIC

Out-of-date statistics are checked, such as whether RUNSTATS has been run since the last LOAD or REORG operation or whether the number and percentage of changes in a table space are greater that a defined threshold. BASIC is the default value.

PROFILE

Out-of-date statistics and the completeness of statistics are checked, including whether all statistics in the table profile have been collected.

PROFILE-CONSISTENCY

Out-of-date statistics, the completeness of statistics, and the consistency of statistics are checked.
The following optional parameters specify a threshold and settings for the use of table-sampling by RUNSTATS.
sampling-rate

Indicates the percentage of rows that RUNSTATS samples when collecting statistics on non-indexed columns. You can specify any value from 1 through 100. When sampling-rate is not specified, AUTO is used. When a sampling-rate is not specified, the TABLESAMPLE SYSTEM AUTO option is used for RUNSTATS alerts on single-table table spaces and the SAMPLE 25 option is used for RUNSTATS alerts on multi-table table spaces.

sampling-threshold

Indicates a threshold for the absolute number of rows in a table. When the threshold is exceeded, a sampling option is recorded in the RUNSTATS alert, and that option is used later when RUNSTATS executes to resolve the alert. You can specify a positive integer. A value of 500,000 is used when sampling-threshold is not specified. The specified sampling-rate is used for all tables when you specify sampling-threshold=0.

The following optional parameters specify thresholds for out-of-date statistics. A RUNSTATS alert might be written when a threshold is exceeded.

Table 1. Parameters for out-of-date statistics
Option	Description	Allowed values	Default value
num-changes	Absolute number of inserted, updated, or deleted rows in a table space. A RUNSTATS alert is written when the thresholds for both num-changes and pct-changes are exceeded.	Positive integers, in the range 0 to 2,147,483,647	0
num-mass-deletes	Absolute number of massive delete operations. A RUNSTATS alert is written when the num-mass deletes threshold is exceeded.	Positive integers, in the range 0 to 2,147,483,647	0
pct-changes	Percentage of inserted, updated, or deleted rows in a table space. A RUNSTATS alert is written when the thresholds for both num-changes and pct-changes are exceeded.	A real number float value greater than 0.0 and less than or equal to 100.0	20.0

The following parameters specify thresholds for inconsistent statistics. The thresholds apply only when the value of profile-consistency is specified for statistics-scope. A RUNSTATS alert is written when a threshold is exceeded. Each threshold is a percentage difference when related statistics are compared, as described in the following table.

Table 2. Parameters for statistics consistency thresholds
Option	Description	Allowed values	Default value
colgroup-card-greater-than-superset-colgroup-card	The cardinality of a column group is greater than the cardinality of its superset column group.	A real number between 0.0 and 100.0 inclusive	0.1
different-colgroup-card-from-coldist-and-index	The cardinalities of an index column group and a table column group that contain the same columns do not match.	A real number between 0.0 and 100.0 inclusive	0.1
different-colgroup-card-from-indexes	The cardinalities of two index column groups that contain the same columns (independent of order) do not match.	A real number between 0.0 and 100.0 inclusive	0.1
different-single-col-colgroup-card-from-coldist-and-index	An index first key cardinality differs from the corresponding column cardinality	A real number between 0.0 and 100.0 inclusive	0.1
different-single-col-colgroup-card-from-indexes	An index first key cardinality differs from another index first key cardinality on the same column.	A real number between 0.0 and 100.0 inclusive	0.1
drf-greater-than-tabcard	The data repetition factor of an index on a table is greater than the cardinality of this table.	A real number between 0.0 and 100.0 inclusive	0.1
drf-less-than-npages	The data repetition factor of an index on a table is smaller than the number of pages.	A real number between 0.0 and 100.0 inclusive	0.1
frequency-out-of-range	A column group frequency of a table is greater than 1 or smaller than 0.	A real number between 0.0 and 100.0 inclusive	0.1
index-fullkeycard-less-than-any-key-card	The full key cardinality of an index is smaller than the cardinality of any column that is involved with the index.	A real number between 0.0 and 100.0 inclusive	0.1
index-fullkeycard-less-than-firstkeycard	The full key cardinality of an index is smaller than the first key cardinality of the same index	A real number between 0.0 and 100.0 inclusive	0
maximum-frequency-less-than-reciprocal-of-colgroup-card	The maximal frequency of a column group is smaller than 1 divided by the cardinality of the column group.	A real number between 0.0 and 100.0 inclusive	0.1
number-of-frequency-records-greater-than-colgroup-card	The number of frequency records of a column group is greater than the cardinality of a column group.	A real number between 0.0 and 100.0 inclusive	0.1
product-of-colcard-less-than-colgroup-card	The product of the cardinalities of the columns of a column group is smaller than the cardinality of the column group.	A real number between 0.0 and 100.0 inclusive	0
quantile-card-greater-than-colcard	The cardinality of a quantile in a column group, on a single column, is greater than the cardinality of the column.	A real number between 0.0 and 100.0 inclusive	0.1
quantile-card-greater-than-colgroup-card	The cardinality of a quantile in a column of a table is greater than the cardinality of the same column.	A real number between 0.0 and 100.0 inclusive	0.1
quantile-frequency-out-of-range	The cardinality of a column group quantile is smaller than 0 or greater than 1.	A real number between 0.0 and 100.0 inclusive	0
single-col-index-fullkeycard-not-equal-firstkeycard	The full key cardinality of an index, on a single column, is different from the first key cardinality.	A real number between 0.0 and 100.0 inclusive	0.1
sum-of-frequency-greater-than-one	The sum of all frequencies of a column group of a table is greater than one.	A real number between 0.0 and 100.0 inclusive	0.1
sum-of-histogram-frequency-greater-than-one	The sum of all histogram frequencies of a column group is greater than one.	A real number between 0.0 and 100.0 inclusive	0
sum-of-histogram-greater-than-colcard	The sum of histogram cardinalities of a column of column group is greater than 1.	A real number between 0.0 and 100.0 inclusive	0.1
sum-of-histogram-greater-than-colgroup-card	The sum of histogram cardinalities of a column group is greater than the cardinality of the same column group.	A real number between 0.0 and 100.0 inclusive	0.1
tabcard-less-than-colcard	A table cardinality is smaller than the cardinality of a column in the same table	A real number between 0.0 and 100.0 inclusive	0.1
tabcard-less-than-colgroup-card	A table cardinality is smaller than the cardinality of a column group for this same table.	A real number between 0.0 and 100.0 inclusive	0.1
tabcard-less-than-index-keycard	The cardinality of a table is smaller than the cardinality of an index for this table.	A real number between 0.0 and 100.0 inclusive	0.1
tabcard-not-equal-unique-index-fullkeycard	The cardinality of a table is different from the cardinality of a unique index on that table.	A real number between 0.0 and 100.0 inclusive	0.1

monitor-options is an input parameter of type VARCHAR(30000).

history-entry-id

Returns a unique identifier that can be used to read the execution log in the SYSIBM.SYSAUTORUNS_HIST table and the alerts in the SYSIBM.SYSAUTOALERTS table when the SYSPROC.ADMIN_UTL_MONITOR stored procedure executes. This is an output parameter of type BIGINT.

return-code

Provides the return code from the stored procedure. Possible values are:

0: The call completed successfully.
4: The call completed successfully. The message output parameter contains a warning message.
12: The call did not complete successfully. The message output parameter contains messages describing the error.

The return-code parameter is an output parameter of type INTEGER.

message

Contains messages describing the error encountered by the stored procedure.

The message parameter is an output parameter of type VARCHAR(1331)

Example

The following example shows a Java call to the ADMIN_UTL_MONITOR stored procedure:

CallableStatement callStmt =
con.prepareCall("CALL SYSPROC.ADMIN_UTL_MONITOR(?, ?, ?, ?)");
callStmt.setString(1, "");
callStmt.registerOutParameter(2,Types.INTEGER);
callStmt.registerOutParameter(3,Types.INTEGER);
callStmt.registerOutParameter(4,Types.LONGVARCHAR);
callStmt.execute();
if ( callStmt.getInt(3) > 0 )
{
System.err.println("Error: " + callStmt.getString(4));
}

External input

The ADMIN_UTL_MONITOR stored procedure reads additional input from the following catalog tables:

SYSIBM.SYSTABLESPACESTATS
SYSIBM.SYSTABLESPACE
SYSIBM.SYSTABLES
SYSIBM.SYSCOLUMNS
SYSIBM.SYSCOLDIST
SYSIBM.SYSINDEXES
SYSIBM.SYSKEYS
SYSIBM.SYSTABLES_PROFILES

Output

The ADMIN_UTL_MONITOR stored procedure returns the following output parameters:

history-entry-id
return-code
message

The ADMIN_UTL_MONITOR stored procedure also inserts rows of data into the following catalog tables:

In the SYSIBM.SYSAUTOALERTS catalog table, a row of data is inserted for each RUNSTATS alert issued, with values for following columns:
- HISTORY_ENTRY_ID
- ACTION
- TARGET_QUALIFIER
- TARGET_OBJECT
- TARGET_PARTITION
- OPTIONS
- DURATION
- STATUS
In the SYSIBM.SYSTABLES_PROFILES catalog table, a row of data is inserted for each monitored table that doesn't have a profile yet, with values in the following columns:
- SCHEMA
- TBNAME
- PROFILE_TYPE
- PROFILE_MODE
- PROFILE_TEXT
- PROFILE_UPDATE
- PROFILE_USED
In the SYSIBM.SYSAUTORUNS_HIST stored procedure, one row of data is added to log the activity.