Chapter 8: Database Jobs

Chapter 8: Database Jobs

Database Jobs :

Database jobs let you automate common database tasks on Oracle, Microsoft SQL
Server, Sybase, and IBM DB2 databases.
Note: To run these jobs, your system requires CA WA Agent for UNIX, Linux, or Windows
and CA WA Agent for Databases.
You can define the following database jobs:
SQL
Lets you run an SQL query against a database.
Database Stored Procedure
Lets you run a stored procedure.
Database Trigger
Lets you monitor for added, deleted, and updated rows in a database table.
Database Monitor
Lets you monitor for added and deleted rows in a database table.


 How Database Trigger Jobs Differ from Database Monitor Jobs


How Database Trigger Jobs Differ from Database Monitor Jobs
You can monitor database changes either by using a Database Trigger job or a Database
Monitor job. The Database Trigger job offers the following advantages over Database
Monitor jobs:
■ You can monitor for more conditions. With Database Trigger jobs, you can monitor
for rows added, deleted, and updated. With Database Monitor jobs, you can only
monitor for rows added and deleted. Database Trigger jobs define triggers to the
underlying database being monitored. Database Monitor jobs poll the database
table for changes in row counts or column values.
■ Database Trigger jobs detect all changes made to the database; Database Monitor
jobs monitor for changes only in 10 second intervals, by default.

Suppose that you want to send a notification when a new row is added. Within a
10-second interval, assume a row is added while another row is deleted. A Database
Trigger job that monitors for an INSERT would complete and send the notification when
the new row is detected. A Database Monitor job that monitors for an INCREASE would
not complete or send a notification because no change in the total number of rows was
detected.

Each Database Trigger job creates a database trigger on the database. The database
trigger templates are provided with the agent. Before using a Database Trigger job,
consult with your database administrator.
Notes:
■ A table being monitored should not be dropped because the Database Trigger or
Database Monitor job remains in the RUNNING status even if the table has been
dropped.
■ For more information about the database trigger templates, see the db.trig.propfile
parameter in the CA Workload Automation Agent for Databases Implementation
Guide.

User IDs and Passwords for Database Jobs
All database jobs require a user ID that has the appropriate permissions to access the
information in the database. The job runs under that user ID. You specify a user ID using
the owner attribute.
These database user IDs and passwords must be defined on CA Workload Automation
AE by using the autosys_secure command. When you define a database job, specify a
database user ID using the owner attribute, or use the default owner value.
Note: For more information about the autosys_secure command, see the Reference
Guide.
 Define a Database Monitor Job

Chapter 8: Database Jobs 255

Define a Database Monitor Job
You can define a Database Monitor job to monitor a database table for an increase or
decrease in the number of rows. To monitor the database table for specific changes, you
can add a monitor condition to the job definition. When the condition is met, the job
completes.
Note: To run these jobs, your system requires CA WA Agent for UNIX, Linux, or Windows
and CA WA Agent for Databases.
Follow these steps:
1. Insert a job and specify the following attributes in the definition:
job_type: DBMON
Specifies that the job type is Database Monitor.

machine
Specifies the name of the machine on which the job runs.

tablename
Specifies the name of the database table to monitor for the changes.

2. Do one of the following:
■ Ensure that a default database resource location is defined in the agent's
agentparm.txt file using the db.default.url parameter.
■ Add the following attribute to the definition:
connect_string
Specifies the database resource location.
 Note: This attribute overrides the db.default.url agent parameter.
3. (Optional) Specify the following attribute:
owner
Specifies the user ID that the job runs under. This value overrides the default
owner of the job.
Default: The user ID who invokes jil to define the job
Note: Windows authentication is not supported.
 Define a Database Monitor Job

256 User Guide

4. (Optional) Specify optional Database Monitor attributes:
■ continuous
■ job_class
■ job_terminator
■ monitor_cond
■ monitor_type
■ user_role
5. (Optional) Specify common attributes that apply to all job types.
The Database Monitor job is defined.

Notes:
■ Attributes that have a default value automatically apply to the job definitions;
therefore, they are optional. For example, jobs with no specified job type are
defined as command jobs by default. Other optional attributes specify information
that is not required but affects how or when a job runs, such as attributes that
specify scheduling conditions.
■ Some optional attributes are common to all job types but others apply to certain
jobs types only. Optional attributes that apply to all job types are known as
common optional attributes. For more information about common optional
attributes and the values that you can specify for them (including their default
values when applicable), see the Reference Guide.
■ For information about required attributes and job type specific optional attributes,
see the procedure topics that provide instructions for defining jobs.
■ This guide provides instructions for defining jobs interactively. You also create job
definitions in script files and then import them using the jil command or use CA
WCC to define them. For more information about the JIL command and JIL syntax,
see the Reference Guide. For more information about using CA WCC to define the
job, see the CA Workload Control Center Workload Scheduling Guide.

Example: Monitor a Table for Added Rows
This example monitors for an increase in the number of rows in the staff table. The table
is in a SQL Server database named ORDERS.
insert_job: dbmon_add
job_type: DBMON
machine: dbagent
owner: dbuser@MSSQL
tablename: staff
monitor_type: INCREASE
connect_string: "jdbc:sqlserver://myhost:1433;DatabaseName=ORDERS" Define a Database Monitor Job

Chapter 8: Database Jobs 257

Example: Monitor a Table for Added or Deleted Rows
This example monitors the STAFF table for a change in the number of rows. When a row
that contains the name Jonson is added or deleted, the job completes. The job connects
to the default database resource location defined on the agent.
insert_job: dbmon_job
job_type: DBMON
machine: dbagent
owner: entadm@myhost
monitor_type: VARIANCE
tablename: staff
monitor_cond: NAME='Jonson'
Example: Monitor a Table for Added Rows With a Condition
This example monitors the emp table for an increase in the number of rows. When a
new row has a sal greater than 100000, the job completes. The job connects to a SQL
Server database named ORDERS. The database user ID is set to the user who invokes jil
to define the job (the default owner).
insert_job: dbmon1
job_type: DBMON
machine: DB_agent
owner: dbuser@MSSQL
monitor_type: INCREASE
monitor_cond: sal>100000
tablename: emp
connect_string: "jdbc:sqlserver://myhost:1433;DatabaseName=ORDERS"
More information:
Insert a Job Definition (see page 88)

 Define a Database Trigger Job

258 User Guide

Define a Database Trigger Job
You can define a Database Trigger job to monitor a database table for added, deleted,
or updated rows. To monitor the database table for specific changes, you can add a
condition to the job definition. When the condition is met, the job completes.
Note: To run these jobs, your system requires CA WA Agent for UNIX, Linux, or Windows
and CA WA Agent for Databases.
Follow these steps:
1. Insert a job and specify the following attributes in the definition:
job_type: DBTRIG
Specifies that the job type is Database Trigger.
machine
Specifies the name of the machine on which the job runs.
dbtype
Specifies the type of the database that the job monitors.
tablename
Specifies the name of the database table to monitor for the changes.
2. Do one of the following:
■ Ensure that a default database resource location is defined in the agent's
agentparm.txt file using the db.default.url parameter.
■ Add the following attribute to the definition:
connect_string
Specifies the database resource location.
 Note: This attribute overrides the db.default.url agent parameter.

3. (Optional) Specify the following attribute:
owner
Specifies the user ID that the job runs under. This value overrides the default
owner of the job.
Default: The user ID who invokes jil to define the job
Note: This user ID must be authorized to create triggers on the database or
schema the table belongs to. For Microsoft SQL Server, this user ID must own
the database table identified by the tablename attribute. The password for the
user must be defined in the database using the autosys_secure command.
Windows authentication is not supported.
 Define a Database Trigger Job

Chapter 8: Database Jobs 259

4. (Optional) Specify optional Database Trigger attributes:
■ continuous
■ job_class
■ job_terminator
■ trigger_cond
■ trigger_type
■ user_role
5. (Optional) Specify common attributes that apply to all job types.
The Database Trigger job is defined.

Notes:
■ Attributes that have a default value automatically apply to the job definitions;
therefore, they are optional. For example, jobs with no specified job type are
defined as command jobs by default. Other optional attributes specify information
that is not required but affects how or when a job runs, such as attributes that
specify scheduling conditions.
■ Some optional attributes are common to all job types but others apply to certain
jobs types only. Optional attributes that apply to all job types are known as
common optional attributes. For more information about common optional
attributes and the values that you can specify for them (including their default
values when applicable), see the Reference Guide.
■ For information about required attributes and job type specific optional attributes,
see the procedure topics that provide instructions for defining jobs.
■ This guide provides instructions for defining jobs interactively. You also create job
definitions in script files and then import them using the jil command or use CA
WCC to define them. For more information about the JIL command and JIL syntax,
see the Reference Guide. For more information about using CA WCC to define the
job, see the CA Workload Control Center Workload Scheduling Guide.

Example: Monitor a Table for Added Rows
This example monitors the emp table. The trigger_type attribute is not specified, so the
job monitors for added rows by default. When a row is added, the job completes. The
database resource location is defined on the agent, so the connect_string attribute is
not required in the job definition.
insert_job: dbtrig_job
job_type: DBTRIG
machine: DB_agent
owner: dbuser@ORA
dbtype: Oracle
tablename: emp Define a Database Trigger Job

260 User Guide

More information:
Insert a Job Definition (see page 88)


Examples: Monitoring Oracle Database Tables
The following examples are Database Trigger Jobs that monitor Oracle database tables:
Note: These examples use optional database attributes. For more information about the
optional attributes and their JIL syntax, see the Reference Guide.
Example: Monitor an Oracle Database Table for Deleted Rows
This example monitors the emp table for deleted rows. The job runs under the user
named scott, who has the authority to create triggers on the database or schema the
table belongs to. When a row is deleted, the job completes.
insert_job: dbtrig2
job_type: DBTRIG
machine: DB_agent
dbtype: Oracle
trigger_type: DELETE
tablename: emp
owner: scott@orcl
connect_string: "jdbc:oracle:thin:@myhost:1521:orcl"
Example: Monitor an Oracle Database Table for an Added or Deleted Row
This example monitors the emp table for an added row or a deleted row. The job runs
under the user named scott, who has the authority to create triggers on the database or
schema the table belongs to. The job remains in a RUNNING state while waiting for an
added or deleted row. When a row is either added or deleted, the job completes.
insert_job: dbtrig_ora
job_type: DBTRIG
machine: dbagent
dbtype: Oracle
trigger_type: DELETE,INSERT
tablename: emp
owner: scott@orcl
connect_string:"jdbc:oracle:thin:@myhost:1521:orcl" Define a Database Trigger Job

Chapter 8: Database Jobs 261

Examples: Specify Trigger Conditions for Deleted Rows in an Oracle Database
■ This example monitors the emp table for deleted rows. The job runs under a user
who has the authority to create triggers on the database or schema the table
belongs to. The job connects to the default database resource location defined on
the agent. When a row containing deptno 75 is deleted, the job completes.
insert_job: dbtrig_delete
job_type: DBTRIG
machine: dbagent
dbtype: Oracle
trigger_type: DELETE
trigger_cond: old.deptno=75
tablename: emp
owner: scott@orcl
■ This example monitors the emp table for added rows. The job runs under a user
who has the authority to create triggers on the database or schema the table
belongs to. When a row containing an ename beginning with the letter g is added,
the job completes.
insert_job: dbtrig_insert
job_type: DBTRIG
machine: dbagent
dbtype: Oracle
trigger_type: INSERT
trigger_cond: new.ename like 'g%%'
tablename: emp
owner: scott@orcl
connect_string:"jdbc:oracle:thin:@myhost:1521:orcl"
■ This example monitors the emp table for added or updated rows. The job runs
under a user who has the authority to create triggers on the database or schema
the table belongs to. The job completes when a new or updated row does not
contain a job field equal to sales.
Note: The <> symbol indicates not equal to.
insert_job: dbtrig_insertupdate
job_type: DBTRIG
machine: dbagent
dbtype: Oracle
trigger_type: INSERT,UPDATE
trigger_cond: new.job<>'sales'
tablename: emp
owner: scott@orcl
connect_string:"jdbc:oracle:thin:@myhost:1521:orcl" Define a Database Trigger Job

262 User Guide

Examples: Monitoring Microsoft SQL Server Database Tables
The following examples are Database Trigger jobs that monitor Microsoft SQL Server
database tables:
Note: These examples use optional database attributes. For more information about the
optional attributes and their JIL syntax, see the Reference Guide.
Example: Monitor a SQL Server Database Table for a New or Deleted Row
This example monitors the stores table for an added row or a deleted row. The job runs
under the sa user, who owns the table and is authorized to create triggers on the
database or schema the table belongs to. The job remains in a RUNNING state waiting
for an added or deleted row. When a row is either added or deleted, the job completes.
insert_job: dbtrig1
job_type: DBTRIG
machine: DB_agent
trigger_type: DELETE,INSERT
tablename: stores
dbtype: MSSQL
owner: sa@myhost
connect_string:"jdbc:sqlserver://myhost:1433;DatabaseName=pubs"
Example: Monitor a SQL Server Database Table for Two Changes
This example monitors the sales table for changes to the ord_date and qty columns. The
job runs under the sa user, who owns the table and is authorized to create triggers on
the database or schema the table belongs to. The job completes only when both
columns have changed.
insert_job: dbtrig_sqlsvr
job_type: DBTRIG
machine: dbagent
dbtype: MSSQL
owner: sa@myhost
connect_string: "jdbc:sqlserver://myhost:1433;DatabaseName=pubs"
tablename: sales
trigger_type: UPDATE
trigger_cond: UPDATE(ord_date) and UPDATE(qty) Define a Database Trigger Job

Chapter 8: Database Jobs 263

Example: Monitor a SQL Server Database Table for Added Rows with a Trigger
Condition
This example monitors the sales table for added rows. The job runs under the sa user,
who owns the table and is authorized to create triggers on the database or schema the
table belongs to. The job connects to the default database resource location defined on
the agent. When the qty for inserted title ID TC7777 is greater than or equal to 20, the
job completes.
insert_job: dbtrig3
job_type: DBTRIG
machine: DB_agent
dbtype: MSSQL
trigger_type: INSERT
trigger_cond: (select QTY from INSERTED where TITLE_ID='TC7777')>=20
tablename: sales
owner: sa@myhost
Example: Monitor a SQL Server Database Table for Deleted Rows
This example monitors the sales table for deleted rows. The job runs under the sa user,
who owns the table and is authorized to create triggers on the database or schema the
table belongs to. The job completes when a row is deleted.
insert_job: dbtrig4
job_type: DBTRIG
machine: DB_agent
dbtype: MSSQL
trigger_type: DELETE
tablename: sales
owner: sa@myhost
connect_string:"jdbc:sqlserver://myhost:1433;DatabaseName=pubs" Define a Database Trigger Job

264 User Guide

Examples: Monitoring IBM DB2 Database Tables
The following examples are Database Trigger jobs that monitor IBM DB2 Server
database tables:
Note: These examples use optional database attributes. For more information about the
optional attributes and their JIL syntax, see the Reference Guide.
Example: Monitor an IBM DB2 Database Table for Added Rows with a Trigger
Condition
This example monitors the STAFF table for added rows. The job runs under the entadm
user, who owns the table and is authorized to create triggers on the database or schema
the table belongs to. When the total number of rows is greater than or equal to 37, the
job completes.
insert_job: dbtrig1
job_type: DBTRIG
machine: DB_agent
dbtype: DB2
trigger_type: INSERT
trigger_cond: (select count(*) from STAFF)>=37
tablename: STAFF
owner: entadm@myhost
connect_string:"jdbc:db2://172.31.255.255:50000/SAMPLE"
Example: Monitor an IBM DB2 Database Table for Changes
This example monitors the STAFF table for changes. The job connects to the SAMPLE
database and runs under the entadm user, who is authorized to create triggers on the
database or schema the table belongs to. The job completes when the table has
changed.
insert_job: dbtrig_db2
job_type: DBTRIG
machine: dbagent
dbtype: DB2
owner: entadm@myhost
connect_string:"jdbc:db2://172.31.255.255:50000/SAMPLE"
tablename: STAFF
dbtype: DB2
trigger_type: UPDATE Define a Database Trigger Job

Chapter 8: Database Jobs 265

Example: Monitoring a Sybase Database Table
The following example is a Database Trigger Job that monitors a Sybase database table:
Note: These examples use optional database attributes. For more information about the
optional attributes and their JIL syntax, see the Reference Guide.
Example: Monitor a Sybase Database Table for Changes
This example monitors the ap_invoices table for changes. The job connects to the
Sybase database named APDB that is reachable on port 5001 on a host named myhost.
The job completes when the table has changed.
insert_job: db_sqltrig_upd
job_type: DBTRIG
machine: localhost
dbtype: Sybase
connect_string: "jdbc:sybase:Tds:myhost:5001/APDB"
trigger_type: UPDATE
tablename: ap_invoices
owner: admin@myhost Define a Database Stored Procedure Job

266 User Guide

Define a Database Stored Procedure Job
You can define a Database Stored Procedure 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. When the procedure
executes, the output parameter values from the database are returned to CA Workload
Automation AE. You can view the output parameter values in the job’s spool file. By
default, the agent separates the output parameter values in the return string with a
vertical bar ( | ).
If you are using 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.
Follow these steps:
1. Insert a job and specify the following attributes in the definition:
job_type: DBPROC
Specifies that the job type is Database Stored Procedure.
machine
Specifies the name of the machine on which the job runs.
sp_name
Specifies the database stored procedure to run.

2. Do one of the following:
■ Ensure that a default database resource location is defined in the agent's
agentparm.txt file using the db.default.url parameter.
■ Add the following attribute to the definition:
connect_string
Specifies the database resource location.
 Note: This attribute overrides the db.default.url agent parameter.

3. (Optional) Specify the following attribute:
owner
Specifies the user ID that the job runs under. This value overrides the default
owner of the job.
Default: The user ID who invokes jil to define the job
Note: Windows authentication is not supported.
 Define a Database Stored Procedure Job

Chapter 8: Database Jobs 267

4. (Optional) Specify optional Database Stored Procedure attributes:
■ job_class
■ job_terminator
■ result_type
■ sp_arg
■ success_criteria
■ user_role

5. (Optional) Specify common attributes that apply to all job types.
The Database Stored Procedure job is defined.

Notes:
■ Attributes that have a default value automatically apply to the job definitions;
therefore, they are optional. For example, jobs with no specified job type are
defined as command jobs by default. Other optional attributes specify information
that is not required but affects how or when a job runs, such as attributes that
specify scheduling conditions.
■ Some optional attributes are common to all job types but others apply to certain
jobs types only. Optional attributes that apply to all job types are known as
common optional attributes. For more information about common optional
attributes and the values that you can specify for them (including their default
values when applicable), see the Reference Guide.
■ For information about required attributes and job type specific optional attributes,
see the procedure topics that provide instructions for defining jobs.
■ This guide provides instructions for defining jobs interactively. You also create job
definitions in script files and then import them using the jil command or use CA
WCC to define them. For more information about the JIL command and JIL syntax,
see the Reference Guide. For more information about using CA WCC to define the
job, see the CA Workload Control Center Workload Scheduling Guide.

Example: Invoke a Stored Procedure from a Database
This example invokes the calcproc stored procedure. The default database resource
location is defined on the agent, so the connect_string attribute is not required in the
job definition.
insert_job: sp_default
job_type: DBPROC
machine: DB_agent
owner: dbuser@dbhost
sp_name: calcproc Define a Database Stored Procedure Job

268 User Guide

Example: Invoke a Stored Procedure from a SQL Server Database
This example invokes the byroyalty stored procedure located in the pubs database.
When the job runs, a value of 40 is passed to the input parameter named percentage.
insert_job: sp1_job
job_type: DBPROC
machine: DB_agent
owner: sa
sp_name: byroyalty
sp_arg: name=percentage, argtype=IN, datatype=INTEGER, value=40
connect_string:"jdbc:sqlserver://myhost:1433;DatabaseName=pubs"
Example: Invoke a Stored Procedure with Input and Output Parameters from a SQL
Server Database
This example invokes the following stored procedure located in the pubs database. The
procedure returns a value from the emp table.
CREATE PROCEDURE EMPLOY
(@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
The job returns the pubid that matches the employee named John Doe. The pubid is
recorded in the job’s spool file.
insert_job: sp2_job
job_type: DBPROC
machine: DB_agent
sp_name: EMPLOY
sp_arg: name=f_name, argtype=IN, datatype=VARCHAR, value=John
sp_arg: name=l_name, argtype=IN, datatype=VARCHAR, value=Doe
sp_arg: name=pubid, argtype=OUT, datatype=CHAR
owner: sa
connect_string:"jdbc:sqlserver://myhost:1433;DatabaseName=pubs" Define a Database Stored Procedure Job

Chapter 8: Database Jobs 269

Example: Invoke a Stored Procedure with Input and Output Parameters from an IBM
DB2 Database
This example invokes the following stored procedure under the user entadm:
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
DEPT_MEDIAN returns the median salary for the department with deptNumber 20 from
the STAFF table. The median salary, 18171.25, is recorded in the job’s spool file.
insert_job: deptmed
job_type: DBPROC
machine: DB_agent
sp_name: ENTADM.DEPT_MEDIAN
sp_arg: name=deptNumber, argtype=IN, datatype=SMALLINT, value=20
sp_arg: name=medianSalary, argtype=OUT, datatype=DOUBLE
owner: entadm
connect_string:"jdbc:db2://172.31.255.255:50000/SAMPLE"
The spool file for this job contains the following output:
----------------------------------------------------------------
Output of messages for workload object DEPTMED/DBAPPL.7/MAIN
Start date Thu Aug 31 15:23:44 EDT 2006
----------------------------------------------------------------
{ call ENTADM.DEPT_MEDIAN(?, ?) }
medianSalary=18171.25 Define an SQL Job

270 User Guide

More information:
Insert a Job Definition (see page 88)


Define an SQL Job
You can define an SQL job to run an SQL query against an Oracle, SQL Server, Sybase, or
DB2 database. When the job runs, the SQL statement is invoked and the results are
stored in an output file or job spool file. You can also add criteria to the job definition to
test the query result. If the result matches the criteria, the job completes successfully.
Otherwise, the job fails.
Note: To run these jobs, your system requires CA WA Agent for UNIX, Linux, or Windows
and CA WA Agent for Databases.

Follow these steps:
1. Insert a job and specify the following attributes in the definition:
job_type: SQL
Specifies that the job type is SQL.
machine
Specifies the name of the machine on which the job runs.
sql_command
Specifies the SQL statement to run against a database table.

2. Do one of the following:
■ Ensure that a default database resource location is defined in the agent's
agentparm.txt file using the db.default.url parameter.
■ Add the following attribute to the definition:
connect_string
Specifies the database resource location.
 Note: This attribute overrides the db.default.url agent parameter.

3. (Optional) Specify the following attribute:
owner
Specifies the user ID that the job runs under. This value overrides the default
owner of the job.
Default: The user ID who invokes jil to define the job
Note: Windows authentication is not supported.
 Define an SQL Job

Chapter 8: Database Jobs 271

4. (Optional) Specify optional SQL attributes:
■ destination_file
■ job_class
■ job_terminator
■ success_criteria
■ user_role

5. (Optional) Specify common attributes that apply to all job types.
The SQL job is defined.

Notes:
■ Attributes that have a default value automatically apply to the job definitions;
therefore, they are optional. For example, jobs with no specified job type are
defined as command jobs by default. Other optional attributes specify information
that is not required but affects how or when a job runs, such as attributes that
specify scheduling conditions.
■ Some optional attributes are common to all job types but others apply to certain
jobs types only. Optional attributes that apply to all job types are known as
common optional attributes. For more information about common optional
attributes and the values that you can specify for them (including their default
values when applicable), see the Reference Guide.
■ For information about required attributes and job type specific optional attributes,
see the procedure topics that provide instructions for defining jobs.
■ This guide provides instructions for defining jobs interactively. You also create job
definitions in script files and then import them using the jil command or use CA
WCC to define them. For more information about the JIL command and JIL syntax,
see the Reference Guide. For more information about using CA WCC to define the
job, see the CA Workload Control Center Workload Scheduling Guide.

Example: Delete a Row from a Table
This example deletes the row for stor_id 6523 from the stores table. The default
database resource location is defined on the agent, so the connect_string attribute is
not required in the job definition.
insert_job: deletejob
job_type: SQL
machine: DB_agent
sql_command: DELETE FROM stores WHERE stor_id='6523'
owner: scott@orcl Define an SQL Job

272 User Guide

More information:
Insert a Job Definition (see page 88)


Examples: Running SQL Queries Against Oracle Database Tables
The following examples are jobs that run SQL queries against Oracle database tables:
Note: These examples use optional database attributes. For more information about the
optional attributes and their JIL syntax, see the Reference Guide.
Example: Add a Row to an Oracle Database Table
This example adds a row of data to the emp table.
insert_job: insertjob
job_type: SQL
machine: DB_agent
sql_command: INSERT INTO EMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES(2476, 'robert', 'sales', 435, '01-OCT-2011', 65000, 10, 75)
owner: scott@orcl
connect_string:"jdbc:oracle:thin:@myhost:1521:orcl"
Example: Update a Row in an Oracle Database Table
This example updates a record in the emp table and changes the sal to 75,000 for the
employee with ename robert.
insert_job: updatejob
job_type: SQL
machine: DB_agent
sql_command: UPDATE EMP SET SAL=75000 where ENAME='robert'
owner: scott@orcl
connect_string:"jdbc:oracle:thin:@myhost:1521:orcl"
Example: Delete a Row from an Oracle Database Table
This example deletes a row from the emp table for the employee with ename robert.
insert_job: deletejob
job_type: SQL
machine: DB_agent
sql_command: DELETE FROM EMP WHERE ENAME='robert'
owner: scott@orcl
connect_string:"jdbc:oracle:thin:@myhost:1521:orcl" Define an SQL Job

Chapter 8: Database Jobs 273

Example: Return Data from an Oracle Database Table that Match a Condition
This example queries the emp table for enames that have salaries greater than 40,000.
If the query returns an ename that begins with the letter d, the job completes:
insert_job: selectjob
job_type: SQL
machine: DB_agent
sql_command: SELECT ename FROM emp WHERE sal > 40000
owner: scott@orcl
success_criteria: ENAME=d.*
connect_string:"jdbc:oracle:thin:@myhost:1521:orcl"
destination_file: /emp/salary.txt
For example, the salary.txt file contains the following output:
Output for: SELECT ename FROM emp WHERE sal > 40000
ENAME
-----------
donald
Examples: Running SQL Queries Against Microsoft SQL Server Database Tables
The following examples are jobs that run SQL queries against Microsoft SQL Server
database tables:
Note: These examples use optional database attributes. For more information about the
optional attributes and their JIL syntax, see the Reference Guide.
Example: Add a Row to a SQL Server Database Table
This example adds a row for a new store to the stores table.
insert_job: insertjob
job_type: SQL
machine: DB_agent
sql_command: INSERT INTO stores(stor_id, stor_name, stor_address, city, state, zip)
VALUES('6523', 'BooksMart', '6523 Main St.', 'San Diego', 'CA', '93223')
owner: sa@myhost
connect_string:"jdbc:sqlserver://myhost:1433;DatabaseName=pubs" Define an SQL Job

274 User Guide

Example: Delete a Row from a SQL Server Database Table
This example deletes the row for stor_id 6523 from the stores table.
insert_job: deletejob
job_type: SQL
machine: DB_agent
sql_command: DELETE FROM stores WHERE stor_id='6523'
owner: sa@myhost
connect_string:"jdbc:sqlserver://myhost:1433;DatabaseName=pubs"
Example: Update a Row in a SQL Server Database Table
This example updates the row in the sales table that matches ord_num 6871 and
changes the values for the ord_date and qty.
insert_job: updatejob
job_type: SQL
machine: DB_agent
sql_command: UPDATE sales SET ord_date='6/15/2006', qty=10 WHERE ord_num='6871'
owner: sa@myhost
connect_string:"jdbc:sqlserver://myhost:1433;DatabaseName=pubs"
Example: Return Data from a SQL Server Database Table that Match a Condition
This example queries the sales table for ord_num that have a qty greater than 20. The
ord_num that match the query appear in the output file ordnum.txt.
insert_job: selectjob
job_type: SQL
machine: DB_agent
sql_command: SELECT ord_num FROM sales WHERE qty > 20
owner: sa@myhost
success_criteria: ord_num=A2976
connect_string:"jdbc:sqlserver://myhost:1433;DatabaseName=pubs"
destination_file: "C:\sales\ordnum.txt" Define an SQL Job

Chapter 8: Database Jobs 275

The ordnum.txt file contains the following ord_num:
A2976
QA7442.3
P2121
N914014
P3087a
P3087a
X999
P723
QA879.1
The job completes because the query returns an ord_num that matches the job criteria
A2976.

Suppose that we change the success_criteria attribute to the following:
success_criteria: B+[0-9]
In this case, the query would return the same order numbers, but the job fails because it
cannot find a matching ord_num containing the letter B and followed by a number.

Examples: Running SQL Queries Against Sybase Database Tables
The following examples are jobs that run SQL queries against Sybase database tables:
Note: These examples use optional database attributes. For more information about the
optional attributes and their JIL syntax, see the Reference Guide.
Example: Add a Row to a Sybase Database Table
This example adds a row for a new store to the stores table.
insert_job: insertjob
job_type: SQL
machine: DB_agent
sql_command: INSERT INTO stores(stor_id, stor_name, stor_address, city, state, zip)
VALUES('6523', 'BooksMart', '6523 Main St.', 'San Diego', 'CA', '93223')
owner: sa@myhost
connect_string:"jdbc:sybase:Tds:myhost:5001/APDB" Define an SQL Job

276 User Guide

Example: Delete a Row from a Sybase Table
This example deletes the row for stor_id 6523 from the stores table.
insert_job: deletejob
job_type: SQL
machine: DB_agent
sql_command: DELETE FROM stores WHERE stor_id='6523'
owner: sa@myhost
connect_string:"jdbc:sybase:Tds:myhost:5001/APDB"
Example: Update a Row in a Sybase Table
This example updates the row in the sales table that matches ord_num 6871 and
changes the values for the ord_date and qty.
insert_job: updatejob
job_type: SQL
machine: DB_agent
sql_command: UPDATE sales SET ord_date='6/15/2006', qty=10 WHERE ord_num='6871'
owner: sa@myhost
connect_string:"jdbc:sybase:Tds:myhost:5001/APDB"
Examples: Running SQL Queries Against IBM DB2 Database Tables
The following examples are jobs that run SQL queries against IBM DB2 database tables:
Note: These examples use optional database attributes. For more information about the
optional attributes and their JIL syntax, see the Reference Guide.
Example: Add a Row to an IBM DB2 Database Table
This example adds a row of data to the STAFF table under the user entadm.
insert_job: insertjob
job_type: SQL
machine: DB_agent
sql_command: INSERT into ENTADM.STAFF(ID, NAME, DEPT, JOB, YEARS, SALARY, COMM)
VALUES(556, 'Jonson', 84, 'Sales', 1, 40500.50, 100)
owner: entadm@myhost
connect_string:"jdbc:db2://172.31.255.255:50000/SAMPLE" Define an SQL Job

Chapter 8: Database Jobs 277

Example: Update a Row in an IBM DB2 Database Table
This example updates a record in the STAFF table under the user entadm. The job
changes the years to 3 for the employee with the name Jonson.
insert_job: updatejob
job_type: SQL
machine: DB_agent
sql_command: UPDATE ENTADM.STAFF SET YEARS=3 where NAME="Jonson"
owner: entadm@myhost
connect_string:"jdbc:db2://172.31.255.255:50000/SAMPLE"
Example: Delete a Row from an IBM DB2 Database Table
This example deletes a row from the STAFF table under the user entadm for the
employee with the name Jonson.
insert_job: deletejob
job_type: SQL
machine: DB_agent
sql_command: DELETE FROM ENTADM.STAFF where NAME="Jonson"
owner: entadm@myhost
connect_string:"jdbc:db2://172.31.255.255:50000/SAMPLE"
Example: Return Data from an IBM DB2 Database Table that Match a Condition
This example queries the STAFF table under the user entadm for names that have
salaries greater than 40,000. If the query returns a name that begins with the letter J,
the job completes.
insert_job: selectjob
job_type: SQL
machine: DB_agent
sql_command: SELECT NAME FROM ENTADM.STAFF where SALARY > 40000
owner: entadm@myhost
success_criteria: NAME=J.*
connect_string:"jdbc:db2://172.31.255.255:50000/SAMPLE"
destination_file: /staff/salary.txt
For example, the salary.txt file contains the following output:
Output for: SELECT NAME FROM ENTADM.STAFF where SALARY > 40000
NAME
-----------
Jonson Attributes with Default Values

278 User Guide

Attributes with Default Values
Attributes that have a default value automatically apply to the job definition. Therefore,
you do not have to specify those attributes in the definition. Your agent administrator
can define some default values on the agent in the agentparm.txt file.
If you specify the attribute in a job definition, it overrides the default.
The following Database job attributes have default values:
connect_string
Specifies the database resource location.
Default: db.default.url agent parameter, if specified
Note: If you do not specify the connect_string attribute, the default database
resource location must be defined in the db.default.url parameter in the agent's
agentparm.txt file. Otherwise, the job fails.

destination_file (SQL jobs only)
Specifies the output destination file that stores the SQL query results.
Default: spooldir agent parameter, if specified

monitor_type (Database Monitor jobs only)
Specifies the type of database change to monitor for.
Default: VARIANCE (The job monitors for an increase or a decrease in the number
of rows in the table.)

owner
Specifies the user ID that the job runs under.
Default: The user ID who invokes jil to define the job
Note: Windows authentication is not supported.
trigger_type (Database Trigger jobs only)
Specifies the type of database change to monitor for.
Default: INSERT (The job monitors for an insertion of a row in the table.)

user_role
Specifies the Oracle database user type.
Default: db.default.userType agent parameter, if specified
Note: For more information about JIL job types and other job definition attributes, the
values that you can specify for those attributes, and JIL syntax, see the Reference Guide.
 Attributes with Default Values

Chapter 8: Database Jobs 279

Example: Override Default Values
Suppose that you want to run an SQL job that queries the NEWORDS table. This job
overrides the default database resource location defined on the agent using the
connect_string attribute. This job also overrides the default owner with the dbuser1
user ID, who is logged in with sysdba privileges. The output is stored in the job's spool
file by default.
insert_job: QRY1
job_type: SQL
machine: dbagent
owner: dbuser1@myhost
user_role: as sysdba
sql_command: SELECT * from NEWORDS
connect_string: "jdbc:oracle:thin:@172.31.255.255:1433:ORDERS"