ADMIN_UTL_MODIFY stored procedure

ADMIN_UTL_MODIFY is an autonomic stored procedure that maintains the SYSIBM.SYSAUTORUNS_HIST and SYSIBM.SYSAUTOALERTS catalog tables.

Begin general-use programming interface information.The ADMIN_UTL_MODIFY stored procedure removes all entries in the SYSIBM.SYSAUTORUNS_HIST table that are older than a configurable threshold and removes all entries in the SYSIBM.SYSAUTOALERTS table that are older than the configured threshold and are in COMPLETE state.

Environment

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

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 diagramSkip visual syntax diagramCALLADMIN_UTL_MODIFY(modify-options,history-entry-id,return-code,message)

Option descriptions

The ADMIN_UTL_MODIFY stored procedure has the following options:

modify-options
A string representation of the configuration parameters for the ADMIN_UTIL_MODIFY stored procedure, consisting of "NAME = VALUE" pairs separated by commas. Optional parameters include:
HISTORY-DAYS
Number of days after that entries in the SYSIBM.SYSAUTORUNS_HIST table and completed alerts in SYSIBM.SYSAUTOALERTS table are deleted. The value be a positive number of days.

modify-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 when the SYSPROC.ADMIN_UTL_MODIFY 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_MODIFY stored procedure:

String options = "history-days=90";
CallableStatement callStmt =
con.prepareCall("CALL SYSPROC.ADMIN_UTL_MODIFY(?, ?, ?, ?)");
callStmt.setString(1, options);
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 AUTO_UTL_MODIFY stored procedure reads additional input from the SYSIBM.SYSAUTORUNS_HIST and SYSIBM.SYSAUTOALERTS catalog tables.

Output

The ADMIN_UTL_MONITOR stored procedure returns the following output parameters:
  • history-entry-id
  • return-code
  • message

The ADMIN_UTL_MODIFY stored procedure also deletes rows from the SYSIBM.SYSAUTORUNS_HIST and SYSIBM.SYSAUTOALERTS tables and inserts a single row in to the SYSIBM.SYSAUTORUNS_HIST table to log its activity.End general-use programming interface information.