Database Jobs › Defining Database Stored Procedure Jobs

Defining Database Stored Procedure Jobs

You can define a Database Stored Procedure (DBSP_JOB) job to invoke a procedure stored in a database. You can add criteria to the job definition to test the procedure's output. If the result matches the criteria, the job completes successfully.

If you use Oracle or SQL Server, you can also define a Database Stored Procedure job to run a stored function.

Note: To run these jobs, your system requires CA WA Agent for UNIX, Linux, or Windows and CA WA Agent for Databases.

Required Statements

To define a Database Stored Procedure job, you must specify the following statements:

• AGENT
• STORED_PROCEDURE

Note: All database jobs require a database user ID and the database resource location. You can use the defaults defined on the agent in the agentparm.txt file or specify the USER and DB_URL statements in the job definition.

Optional Statements

You can specify the following optional statements for a Database Stored Procedure job:

• ARGS
• DB_URL
• JOB_CRITERIA
• JOBCLASS
• RETURN_DATA_TYPE
• USER
• USER_TYPE

Example: Invoke a Simple Stored Procedure from a Microsoft SQL Server Database

Suppose that you want a job to run the byroyalty stored procedure located in the pubs sample database. When the job runs, the agent passes the input parameter percentage a value of 40.

AGENT DB_AGENT
USER sa
DB_URL 'jdbc:sqlserver://myhost:1433;DatabaseName=pubs'
SPNAME byroyalty
ARGS percentage IN INTEGER,40

Example: Invoke a Stored Procedure with Input and Output Parameters from a Microsoft SQL Server Database

Suppose that you want a job to run the emp stored procedure, which returns a value from the emp table. The pubid returned matches the employee named Ann Devon. The pubid, 9952, appears in the job's spool file.

AGENT DB_AGENT
USER sa
DB_URL 'jdbc:sqlserver://myhost:1433;DatabaseName=pubs'
SPNAME emp
ARGS f_name IN VARCHAR,Ann
ARGS l_name IN VARCHAR,Devon
ARGS pubid OUT CHAR

To run the job in this example, create the emp stored procedure in the pubs database using the following script:

CREATE PROCEDURE EMP
(@f_name VARCHAR(20),
@l_name VARCHAR(30),
@pubid CHAR(4) OUTPUT)
AS BEGIN
SELECT
@pubid=pub_id
FROM emp
WHERE
fname=@f_name
and
lname=@l_name
print @l_name+@f_name+@pubid
END
GO

Example: Invoke a Stored Procedure with Input and Output Parameters from an IBM DB2 Database

Suppose that you want a job to run the DEPT_MEDIAN stored procedure under the user entadm. DEPT_MEDIAN returns the median salary of department 20 from the STAFF table. The median salary, 18171.25, appears in the job's spool file.

AGENT DB_AGENT
USER entadm
DB_URL jdbc:db2://10.1.4.131:50000/SAMPLE
STORED_PROCEDURE ENTADM.DEPT_MEDIAN
ARGS deptNumber IN SMALLINT,20
ARGS medianSalary OUT DOUBLE

The spool file for this job contains the following output:

{ call ENTADM.DEPT_MEDIAN(?, ?) }
medianSalary=18171.25

The job in this example runs the following stored procedure in the SAMPLE database:

CREATE PROCEDURE DEPT_MEDIAN
  (IN deptNumber SMALLINT, OUT medianSalary DOUBLE)
  LANGUAGE SQL
  BEGIN
    DECLARE v_numRecords INTEGER DEFAULT 1;
    DECLARE v_counter INTEGER DEFAULT 0;
    DECLARE c1 CURSOR FOR
      SELECT CAST(salary AS DOUBLE) FROM staff
        WHERE DEPT = deptNumber
        ORDER BY salary;
      DECLARE EXIT HANDLER FOR NOT FOUND
        SET medianSalary = 6666;
-- initialize OUT parameter
        SET medianSalary = 0;
        SELECT COUNT(*) INTO v_numRecords FROM staff
          WHERE DEPT = deptNumber;
        OPEN c1;
        WHILE v_counter < (v_numRecords / 2 + 1) DO
          FETCH c1 INTO medianSalary;
          SET v_counter = v_counter + 1;
        END WHILE;
        CLOSE c1;
END