Working with Workload Objects › Database Jobs › Define an SQL Job

Define an SQL Job

You can define an SQL job to run an SQL query against an Oracle, Microsoft SQL Server, or IBM 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. 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. Open the Application that you want to add the job to in the Define perspective..

   The Application appears in the workspace.

2. Select the SQL job from the Database group in the Palette view, and drag the job to the workspace.

   The SQL icon appears on the Application workspace view.

3. Right-click the SQL icon, and select Edit from the pop-up menu.

   The Basic page of the SQL dialog opens.

4. Complete the following required fields:

   Name

   Defines the name of the job that you want to schedule.

   Limits: 128 alphanumeric characters, plus the special characters commercial at (@), pound (#), dollar sign ($), underscore (_), square brackets ([]), brace brackets ({}), and percent sign (%) as a symbolic variable introducer character.

   Agent name

   Specifies the name of the Database Agent that runs the job.

   Note: The drop-down list displays all the agents that are defined in the Topology for the specified job type.

   SQL command

   Specifies the SQL statement to be run.

   Note: You can use a JavaScript symbolic variable in this field, preceded by the percent sign (%), for example, %TEST. In SQL, the percent sign represents a wildcard character. To use a wildcard character in this field, escape the percent sign by doubling it, for example, %%TEST.

5. (Optional) Specify the following additional information:

   DB user

   Specifies the database user the job runs under. The database user specified in the job definition overrides the default specified in the agentparm.txt file. The user must be defined in the Topology. This field supports the use of a namespace for a user that has more than one password. Contact your administrator for the user name defined in the Topology.

   Note: The drop-down list displays all the user IDs that are defined in the Topology for the specified agent. You must have at least Read access to the ADMIN.Network Topology permission to view this list.

   Examples: Bob, Production:Bob

   Oracle DB user type

   Specifies the type of Oracle user. In Oracle, a user with sufficient authority can log in with different system privileges. For example, if a job requires sysdba privileges, you would enter as sysdba in this field.

   DB URL

   Specifies the database resource location. The server uses Java database connectivity (JDBC) to connect to the database. This URL overrides the default specified in the agentparm.txt file.

   For an Oracle database, use the following format:

   jdbc:oracle:thin:@host:port:dbname
   

   For an Microsoft SQL Server database, use the following format:

   jdbc:sqlserver://host:port;DatabaseName=dbname
   

   For an IBM DB2 database, use the following format:

   jdbc:db2://host:port/dbname
   

   Output file

   Defines the output file to store the results of the SQL query.

   Default: Spool file

   Success criteria

   Defines a regular expression that is used to evaluate a return string. If the return string matches the regular expression, the job completes successfully. Otherwise, the job fails.

   Notes:

   • This field only applies to SQL queries that are SELECT statements.
   • Each return string includes the field name from the SELECT statement and its value, separated by an equal sign (=). For example, consider the query SELECT ORD_NUM FROM SALES. To match order number A2976, specify the regular expression ORD_NUM=A2976. Specifying the regular expression A2976 does not match any return string causing the job to fail. You can also specify the regular expression .*A2976, which matches any return string that ends with A2976.
   • To compose a regular expression, follow the rules for Java class java.util.regex.Pattern. You can find these rules using a Google search for java pattern.
   • Some characters have a special meaning in regular expressions. To use these characters literally, precede the characters with one backward slash (\). For example, to match the characters *.* literally, specify \*\.\* in your regular expression. The backward slashes escape the characters' special meanings.

6. Click OK.

   The SQL job is defined.

Example: Run an SQL Query against an Oracle Database

Suppose that you want a job named SQL to run a query against an Oracle database and store the results in the job's spool file on the DBAGENT agent computer.

To run an SQL query against an Oracle database

1. Enter the following information in the Basic page:
   • Name—SQL
   • Agent name—DBAGENT
   • DB URL—jdbc:oracle:thin:@10.1.1.10:1521:ESP
   • SQL command—select jobname from tbtco
2. Click OK.

More information:

Using a Namespace for a User that has Different Passwords