Start of change

ADMIN_UTL_EXECUTE stored procedure

ADMIN_UTL_EXECUTE is a stored procedure that solves alerts stored in the SYSIBM.SYSAUTOALERTS catalog table within the maintenance windows defined by the SYSIBM.SYSAUTOTIMEWINDOWS catalog table. It is required for the use of autonomic statistics.

Begin general-use programming interface information.
The ADMIN_UTL_EXECUTE stored procedure performs the following tasks:

  • Determines the order for resolving alerts.
  • Within the maintenance windows that are defined in the SYSIBM.SYSAUTOTIMEWINDOWS catalog table, calls the ADMIN_UTL_SCHEDULE stored procedure to resolve alerts by executing the RUNSTATS utility. For each alert, the options for RUNSTATS are provided in the SYSIBM.SYSAUTOALERTS table.
  • Updates the status and output of resolved alerts in the SYSIBM.SYSAUTOALERTS table.
  • If the administrative task scheduler is used, reschedules its own execution for the next maintenance window when unsolved alerts remain.

Environment

The ADMIN_UTL_EXECUTE stored procedure runs in a WLM-established stored procedure address space and uses the Resource Recovery Services attachment facility to connect to DB2® The ADMIN_UTL_EXECUTE stored procedures calls ADMIN_UTL_SCHEDULE stored procedures to execute a RUNSTATS operations. The ADMIN_UTL_EXECUTE stored procedure is required and must be installed and executable.

It calls the DSNADM.ADMIN_TASK_LIST and the SYSPROC.ADMIN_TASK_UPDATE routines.

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

Read syntax diagram
>>-CALL--SYSPROC.ADMIN_UTL_EXECUTE--(----execute-options---,---->

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

Option descriptions

The ADMIN_UTL_EXECUTE stored procedure has the following options:

execute-options
A string representation of the configuration parameters for the ADMIN_UTL_EXECUTE stored procedure, consisting of "NAME=VALUE" pairs separated by commas. Optional parameters include:
STAND-ALONE
NO
The ADMIN_UTL_EXECUTE stored procedure interacts with the administrative task scheduler to its own next execution. NO is the default value.
YES
The ADMIN_UTL_EXECUTE stored procedure does not interact with the administrative task scheduler.

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

history-entry-id
Provides a unique identifier that can be used to read the execution log written in the SYSIBM.SYSAUTORUNS_HIST table when the SYSPROC.ADMIN_UTL_EXECUTE 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_EXECUTE stored procedure:

long taskid = 7;
CallableStatement callStmt =
con.prepareCall("CALL SYSPROC.ADMIN_UTL_EXECUTE(?, ?, ?, ?)");
callStmt.setString(1, "");
callStmt.registerOutParameter(2,Types.BIGINT);
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_EXECUTE stored procedure reads additional input from the SYSIBM.SYSAUTOALERTS and SYSIBM.SYSAUTOTIMEWINDOWS catalog tables.

Output

The ADMIN_UTL_EXECUTE stored procedure returns the following output parameters:

  • history-entry-id
  • return-code
  • message
The ADMIN_UTL_EXECUTE stored procedure also updates the following catalog tables:
  • In the SYSIBM.SYSAUTOALERTS catalog table, the following columns are updated for each alert that is solved:
    • STARTTS
    • ENDTS
    • STATUS
    • RETURN_CODE
    • MESSAGE
    • OUTPUT
  • In the SYSIBM.SYSAUTORUNS_HIST stored procedure, one row of data is added to log the activity.
End general-use programming interface information.
End of change