Creating a Batch Statement and an Iterative Process

Batch statements enable you to execute multiple SQL and/or parameterized SQL statements within one transaction.

Iterative processes are batch type processes that reference other processes, including stored procedures. In an iterative batch statement, each statement and procedure can be called multiple times. This differs from a regular batch statement, in which a statement or procedure can be called in a single batch; instead, an iterative batch statement allows you to iterate the batch itself.

If you add a statement to a batch without selecting the Iterate option, you can call the statement only once. If you iterate the statement only, which is done by default, you can iterate only a single statement. By choosing the Iterate option when you add a statement to a batch, you will be able to call several statements multiple times from a single batch.

All iterative processes reside within the Batches node. All prepared statements created in the Statements node are automatically imported into the Iterate container. However, you can import processes from Tables or stored procedures as well.

Procedure: How to Create a Batch Statement

To create a batch statement:

Connect to a defined target.
In the left pane, select the Batches node.
Right-click the Batches node and select Create A Batch.
The Create A Batch pane opens.
Type a name for the new Batch and click Create.
The batch properties information appears in the right pane as shown in the following image.
Right-click the batch and select Edit Batch.
The Node Type drop-down selection appears.
From the drop-down list, select the statement, table, or procedure and click Next.
Select Iterate if you want to make the procedure, statement or table iterative.
To add more statements or procedures, select and right-click the batch node in the left pane and select the appropriate option.

Procedure: How to Create An Iterative Process

To import tables or processes into an iterative process:

Connect to a defined target.
Expand the Batches node.
Select Iterate.
By default, the Iterate node contains all the statements created in the Statements node.
Right-click the Iterate node and select Import Process.
The Import Process pane appears.
Select either Tables or Procedures to import and click Next.
Select Schema and click Next.
Select the tables or procedures you want to import and click Next.
If you import tables, the GET, INSERT, UPDATE, DELETE, and UPSERT (but not CURSOR) functions are imported for each table you select. The following image shows the addition of the table functions for the STUDENT table added to the Iterate node:

The stored procedures you Import will appear the same way.

Top of page

Example: Creating an Iterative Process

The following is an example of the input XML for a batch statement named batchtest. Two parameterized SQL statements were added to this statement, one named ParamSTMT1, with an INSERT accessing TableA, and another named ParamSTMT2, with an INSERT accessing TableB. When the SQL statements were added to the batch statement, the Iterate check box was selected.

In this input XML document, two different statements are being called, with two different sets of values for each statement.

Note: Added statements or procedures to a batch must be included in the input document.

The following is an example of the input XML for a regular ITERATE process.

Procedure: How to Add Processes to a Batch Process

You can add a new process to a batch statement after you create it. The adapter also allows you to add a process to a batch even if the batch already contains it, allowing you to make limitless calls to the same process.

Connect to a defined target.
Expand the Batches node.
Select the batch statement you want to edit.
Right-click the statement and select Edit Batch.
The Edit Batch pane appears on the right.
Select the type of process you want to add from the Node Type drop-down box, and click Next.
1. If you select Tables, select the schema and then the particular table, and then the table function, clicking Next after each selection. After selecting the table function, select Iterate to iterate the table function, and then click Add.
  The iterate setting determines whether the table function you choose will be iterative. If so, the adapter searches for the process node in the Iterate node. If not, the adapter will search for it within the Schemas node.
2. If you select Statements, select the statement you want to add from the Prepared Statement drop-down box, select Iterate if you want the prepared statement to be iterative, and click Add.
  You have the option of viewing the details of the statement you are adding by clicking View Details.
  
  The iterate setting determines whether the prepared statement you choose will be iterative. If so, the adapter searches for the process node in the Iterate node. If not, the adapter will search for it within the Statements node.
3. If you select Procedures, select the schema from the Schema drop-down list, and click Get Stored Procedures. Select the stored procedure you want from the Stored Procedure drop-down box, select Iterate if you want the Procedure to be iterative, and click Add Stored Procedure.
  You have the option of viewing the details of the procedure you are adding by clicking Show Details.
  
  The iterate setting determines whether the Procedure you choose will be iterative. If so, the adapter searches for the process node in the Iterate node. If not, the adapter will search for it within the Procedures node.

Procedure: How to Reorder Processes in a Batch Process

You can reorder the processes within a batch statement:

Connect to a defined target.
Expand the Batches node.
Select the batch statement you want to edit.
Right-click the statement and select Edit Batch.
The Edit Batch pane appears.
In the right pane, click Reorder Processes.
A table listing the processes appears in the right pane. Each row includes a position column.
Select the position for each row as appropriate and click Reorder to review the new order.
Click Commit to save the changes.

Procedure: How to Test a Batch Process

To test a batch process:

Connect to a defined target.
Expand the Batches node.
Select the batch statement you want to edit.
Right-click the statement and select Test Run.
The Test Run pane appears on the right. You can edit parameter names and data types for the test.
Click Test.
The results appear in the right pane.