ADMIN_TASK_ADD stored procedure

Environment

ADMIN_TASK_ADD runs in a WLM-established stored procedure address space and uses the Resource Recovery Services attachment facility to connect to Db2.

Authorization

Anyone who can execute this Db2 stored procedure is allowed to add a task.

The user who calls this stored procedure must have MONITOR1 privilege.

Syntax

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

Option descriptions

user-ID

Specifies the user ID under which the task execution is performed.

If this parameter is set to NULL, task execution is performed with the default authorization ID associated with the administrative task scheduler instead.

This is an input parameter of type VARCHAR(128).

password

Specifies the password associated with the input parameter user-ID.

The value of password is passed to the stored procedure as part of payload, and is not encrypted. It is not stored in dynamic cache when parameter markers are used.

Recommendation: Have the application that invokes this stored procedure pass an encrypted single-use password called a passticket.

This is an input parameter of type VARCHAR(24). This parameter is NULL only when user-ID is set to NULL, and must be NULL when user-ID is NULL.

begin-timestamp

Specifies when a task can first begin execution. When task execution begins depends on how this and other parameters are set:

Non-null value for begin-timestamp

At begin-timestamp

The task execution begins at begin-timestamp if point-in-time and trigger-task-name are NULL.

Next point in time defined at or after begin-timestamp

The task execution begins at the next point in time defined at or after begin-timestamp if point-in-time is non-null.

When trigger-task-name completes at or after begin-timestamp

The task execution begins the next time that trigger-task-name completes at or after begin-timestamp.

Null value for begin-timestamp

Immediately

The task execution begins immediately if point-in-time and trigger-task-name are NULL.

Next point in time defined

The task execution begins at the next point in time defined if point-in-time is non-null.

When trigger-task-name completes

The task execution begins the next time that trigger-task-name completes.

The value of this parameter cannot be in the past, and it cannot be later than end-timestamp.

This is an input parameter of type TIMESTAMP.

end-timestamp

Specifies when a task can last begin execution. If this parameter is set to NULL, then the task can continue to execute as scheduled indefinitely.

The value of this parameter cannot be in the past, and it cannot be earlier than begin-timestamp.

This is an input parameter of type TIMESTAMP.

max-invocations

Specifies the maximum number of executions allowed for a task. This value applies to all schedules: triggered by events, recurring by time interval, and recurring by points in time. If this parameter is set to NULL, then there is no limit to the number of times this task can execute.

For tasks that execute only one time, max-invocations must be set to 1 and interval, point-in-time and trigger-task-name must be NULL.

If both end-timestamp and max-invocations are specified, the first limit reached takes precedence. That is, if end-timestamp is reached, even though the number of task executions so far has not reached max-invocations, the task will not be executed again. If max-invocations have occurred, the task will not be executed again even if end-timestamp is not reached.

This is an input parameter of type INTEGER.

interval

Defines a duration in minutes between two executions of a repetitive regular task. The first execution occurs at begin-timestamp. If this parameter is set to NULL, the task is not regularly executed. If this parameter contains a non-null value, the parameters point-in-time and trigger-task-name must be set to NULL.

This is an input parameter of type INTEGER.

point-in-time

Defines one or more points in time when a task is executed. If this parameter is set to NULL, the task is not scheduled at fixed points in time. If this parameter contains a non-null value, the parameters interval and trigger-task-name must be set to NULL.

The point-in-time string uses the UNIX cron format. The format contains the following pieces of information separated by blanks: given minute or minutes, given hour or hours, given day or days of the month, given month or months of the year, and given day or days of the week. For each part, you can specify one or several values, ranges, and so forth.

This is an input parameter of type VARCHAR(400).

trigger-task-name

Specifies the name of the task which, when its execution is complete, will trigger the execution of this task.

Task names of DB2START and DB2STOP are reserved for Db2 stop and start events respectively. Those events are handled by the scheduler associated with the Db2 subsystem that is starting or stopping.

If this parameter is set to NULL, the execution of this task will not be triggered by another task. If this parameter contains a non-null value, the parameters interval and point-in-time must be set to NULL.

This is an input parameter of type VARCHAR(128).

trigger-task-cond

Specifies the type of comparison to be made to the return code after the execution of task trigger-task-name. Possible values are:

GT

Greater than

GE

Greater than or equal to

EQ

Equal to

LT

Less than

LE

Less than or equal to

NE

Not equal to

If this parameter is set to NULL, the task execution is triggered without considering the return code of task trigger-task-name. This parameter must be set to NULL if trigger-task-name is set to NULL or is either DB2START or DB2STOP.

This is an input parameter of type CHAR(2).

trigger-task-code

Specifies the return code from executing trigger-task-name.

If the execution of this task is triggered by a stored procedure, trigger-task-code contains the SQLCODE that must be returned by the triggering stored procedure in order for this task to execute.

If the execution of this task is triggered by a JCL job, trigger-task-code contains the MAXRC that must be returned by the triggering job in order for this task to execute.

To find out what the MAXRC or SQLCODE of a task is after execution, invoke the user-defined function DSNADM. ADMIN_TASK_STATUS returns these information in the columns MAXRC and SQLCODE.

The following restrictions apply to the value of trigger-task-code:

• If trigger-task-cond is null, then trigger-task-code must also be null.
• If trigger-task-cond is non-null, then trigger-task-code must also be non-null.

If trigger-task-cond and trigger-task-code are not null, they are used to test the return code from executing trigger-task-name to determine whether to execute this task or not.

For example, if trigger-task-cond is set to "GE" and trigger-task-code is set to "8", then this task will execute if and only if the previous execution of trigger-task-name returned a MAXRC (for a JCL job) or an SQLCODE (for a stored procedure) greater than or equal to 8.

This is an input parameter of type INTEGER.

db2-ssid

Specifies the Db2 subsystem ID whose associated scheduler should execute the task.

This parameter is used in a data sharing environment where, for example different Db2 members have different configurations and executing the task relies on a certain environment. However, specifying a value in db2-ssid will prevent schedulers of other members to execute the task, so that the task can only be executed as long as the scheduler of db2-ssid is running.

For a task being triggered by a Db2 start or Db2 stop event in trigger-task-name, specifying a value in db2-ssid will let the task be executed only when the named subsystem is starting and stopping. If no value is given, each member that starts or stops will trigger a local execution of the task, provided that the executions are serialized.

If this parameter is set to NULL, any scheduler can execute the task.

This is an input parameter of type VARCHAR(4).

procedure-schema

Specifies the schema of the Db2 stored procedure this task will execute. If this parameter is set to NULL, Db2 uses a default schema. This parameter must be set to NULL if procedure-name is set to NULL.

This is an input parameter of type VARCHAR(128).

procedure-name

Specifies the name of the Db2 stored procedure this task will execute. If this parameter is set to NULL, no stored procedure will be called. In this case, a JCL job must be specified.

This is an input parameter of type VARCHAR(128).

procedure-input

Specifies the input parameters of the Db2 stored procedure this task will execute. This parameter must contain a Db2 SELECT statement that returns one row of data. The returned values will be passed as parameter to the stored procedure.

If this parameter is set to NULL, no parameters are passed to the stored procedure. This parameter must be set to NULL when procedure-name is set to NULL.

This is an input parameter of type VARCHAR(4096).

JCL-library

Specifies the name of the data set where the JCL job to be executed is saved.

If this parameter is set to NULL, no JCL job will be executed. In this case, a stored procedure must be specified.

This is an input parameter of type VARCHAR(44).

JCL-member

Specifies the name of the library member where JCL job to be executed is saved.

If this parameter is set to NULL, the data set specified in JCL-library must be sequential and contain the JCL job to be executed. This parameter must be set to NULL if JCL-library is set to NULL.

This is an input parameter of type VARCHAR(8).

job-wait

Specifies whether the job can be executed synchronously or not. This parameter can only be set to NULL if JCL-library is set to NULL. Otherwise, it must be one of the following values:

NO

Specifies asynchronous execution. The sub-thread does not wait until the job completes execution and returns immediately after the job submission. The task execution status is set to the submission status, the result of the job execution itself is unavailable.

YES

Specifies synchronous execution. The sub-thread simulates a synchronous execution of the JCL job. It waits until the job execution completes, gets the job status from the JES reader, and fills in the last execution status of the task.

PURGE

Specifies that the job status in z/OS® is purged. The sub-thread purges the job output from the JES reader after execution. Execution is the same as when job-wait is YES.

This is an input parameter of type VARCHAR(8).

task-name

Specifies a unique name assigned to the task.

A unique task name is returned when the task is created with a NULL task-name value. This name is of the format TASK_ID_xxxx where xxxx is 0001 for the first task named, 0002 for the second task, and so forth.

The following task names are reserved and cannot be given as the value of task-name:

• Names starting with TASK_ID_
• DB2START
• DB2STOP

This is an input-output parameter of type VARCHAR(128).

description

Specifies a description assigned to the task.

This is an input parameter of type VARCHAR(128).

return-code

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

0

The call completed successfully.

12

The call did not complete successfully. The message output parameter contains messages describing the error.

This is an output parameter of type INTEGER.

message

Contains messages describing the error encountered by the stored procedure. The first messages in this area, if any, are generated by the stored procedure. Messages that are generated by Db2 might follow the first messages.

This is an output parameter of type VARCHAR(1331).

Example

The following Java™ sample shows how to invoke ADMIN_TASK_ADD:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;

Connection con = DriverManager.getConnection
   ("jdbc:db2://myserver:myport/mydatabase", "myuser", "mypassword");
CallableStatement callStmt = con.prepareCall
   ("CALL SYSPROC.ADMIN_TASK_ADD("
   + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
// provide the authid
callStmt.setString(1, "myexecuser");
// provide the password
callStmt.setString(2, "myexecpwd");
// set the start time to now
callStmt.setNull(3, Types.TIMESTAMP);
// no end time
callStmt.setNull(4, Types.TIMESTAMP);
// set the max invocation
callStmt.setInt(5, 1);
// This is a non recurrent task
callStmt.setNull(6, Types.INTEGER);
callStmt.setNull(7, Types.VARCHAR);
callStmt.setNull(8, Types.VARCHAR);
callStmt.setNull(9, Types.CHAR);
callStmt.setNull(10, Types.INTEGER);
callStmt.setNull(11, Types.VARCHAR);
// provide the stored procedure schema
callStmt.setString(12, "MYSCHEMA");
// provide the name of the stored procedure to be executed
callStmt.setString(13, "MYPROC");
// provide the stored procedure input parameter
callStmt.setString(14, "SELECT 1 FROM SYSIBM.SYSDUMMY1");
// This is not a JCL job
callStmt.setNull(15, Types.VARCHAR);
callStmt.setNull(16, Types.VARCHAR);
callStmt.setNull(17, Types.VARCHAR);
// add a new task with task name mytask
callStmt.setString(18, "mytask");
callStmt.registerOutParameter(18, Types.VARCHAR);
// provide the task description
callStmt.setString(19, "MY DESCRIPTION");
// register output parameters for error management
callStmt.registerOutParameter(20, Types.INTEGER);
callStmt.registerOutParameter(21, Types.VARCHAR);
// execute the statement
callStmt.execute();
// manage the return code
if ( callStmt.getInt(20) == 0 )
{
  System.out.print("\nSuccessfully added task " + callStmt.getString(18));
}
else
{
  System.out.print("\nError code and message are: "
     + callStmt.getInt(20) + "/" + callStmt.getString(21));
}

Output

The output of this stored procedure is the task name, task-name and the following output parameters, which are described in Option descriptions:

• return-code
• message