Specifying Do Actions

Although distinct from the others, these four types of Application Engine actions can be grouped together:

Do When
Do While
Do Until
Do Select

Use these actions to control the running of your program. These action types enable you to control the execution of subsequent sections, actions, or SQL statements, depending on the results of a Do SQL statement in the form of a Select statement. If you coded in COBOL, you would perform similar actions using the If and While functions.

Any of the Do actions can control the running of a section, a SQL statement, a PeopleCode program, or a log message. For example, a Do Select can run a SQL statement for each row returned by the included Select statement.

Do When

When using a Do When action, note that:

The Do When action is a Select statement that allows subsequent actions to be run if any rows of data are returned.
This action is similar to a COBOL If statement.

A Do When statement runs before any other actions in a step. If the Do When statement returns any rows, the next action is executed. If the Do When conditions are not met, the remaining actions within that step are not executed. Your program runs a Do When action only once when the owning step executes.
The only property that you can specify for a Do When action is the ReUse Statement property, which applies to all SQL-based actions.

Do While

The Do While action is a Select statement that, if present, runs before subsequent actions of the step. If the Do While statement does not return any rows of data, the action terminates. The Do While statement is identical to the COBOL While statement. Subsequent actions within the step are executed in a loop as long as at least one row is returned by the Select statement for the Do While action. If the Do While statement does not return any rows, the step is complete.

The only property that you can specify for a Do While action is the ReUse Statement property, which applies to all SQL-based actions.

Do Until

A Do Until action is a Select statement that runs after each action when a step completes. If the Select statement returns any rows of data, the step terminates. When using a Do Until action, note that:

You use a Do Until action if you want the processing actions to execute at least once and to execute repeatedly until a certain condition is true, such as a Select statement returns some rows.
You can use a Do Until action to stop a Do Select action prematurely.

For example, if a Select statement for a Do Until action does not return any rows, then the actions in the step are repeated (except if a Do When action appears in the step). Normally, a Do Select action continues until no rows are returned. If any rows of data are returned, the Do Select action stops and the step is not repeated.
The only property that you can specify for a Do Until action is the ReUse Statement property, which applies to all SQL-based actions.

Do Select

The Do Select action is a Select statement that executes subsequent actions once for every row of data that the Do Select statement returns. For instance, a Do Select statement can run a SQL statement for each row returned from the Select statement. The subsequent actions within the step are executed in a loop based on the results of the Select statement. The type of the Do Select determines the specific looping rules.

Like the other Do actions, you can specify the ReUse Statement property for the Do Select action; this property applies to all SQL-based actions.

In addition to the ReUse Statement property, you must also specify this Do Select property: Do Select Type.

Note:

Application Engine does not commit a step containing a Do Select action with the Select/Fetch option enabled until the entire step completes successfully, regardless of the other options you have selected.

For example, suppose at the step level you specified to commit every 100 iterations of the step. One of the actions of this step is a Do Select action with Select/Fetch selected. Because Application Engine does not checkpoint or commit while a Do Select action is active, the transaction performed by the actions within a step is not committed until the entire step completes successfully. This note also applies if any sections are called from inside the loop.

Do Select Type Property

When you specify the Do Select Type property in a Do Select action, you select from the following values:

Field or Control	Description
Select/Fetch	Application Engine opens a cursor for the Do Select action and then, within that cursor, Application Engine performs a Fetch statement for each iteration of the loop to get each row from the Select statement. When a Fetch statement results in an end of table message, the looping is complete. You cannot restart this type of Select statement because Application Engine does not perform a checkpoint or a commit within the step containing this action while Select/Fetch is running. Ultimately, your program ignores the commit settings at runtime until the outermost Select/Fetch completes. Note: When an Application Engine program is not set up for the capability to restart, then commits are not controlled, monitored, or restricted by Application Engine. When Restart is disabled, commits are controlled by the program.
Re-Select	For each iteration of the loop, Application Engine opens a cursor and fetches the first row. Your program processes the first row returned from the Select statement. The cursor is reopened for each iteration of the loop. With this type of Fetch statement, you typically want some aspect of the loop to eventually cause the Select statement to return no rows. Otherwise, no mechanism is in place by which to exit the loop. This type of Do Select is restartable.
Restartable	This option is similar to Select/Fetch in that Application Engine opens the cursor associated with the Do Select action once, and then it performs a Fetch statement on each iteration of the loop to get each row from the Select statement. However, unlike the Select/Fetch option, you can restart this action because Application Engine performs a checkpoint in the middle of the step. Application Engine treats this loop as if it is restartable, but it does not manage the restart. Make sure that the SQL you include within this action is such that, upon restart, the program recognizes where the previous run failed and where to restart processing. For example, you can employ a processed switch or base the next Select statement on the key.

Field or Control

Description

Select/Fetch

Application Engine opens a cursor for the Do Select action and then, within that cursor, Application Engine performs a Fetch statement for each iteration of the loop to get each row from the Select statement. When a Fetch statement results in an end of table message, the looping is complete. You cannot restart this type of Select statement because Application Engine does not perform a checkpoint or a commit within the step containing this action while Select/Fetch is running. Ultimately, your program ignores the commit settings at runtime until the outermost Select/Fetch completes.

Note: When an Application Engine program is not set up for the capability to restart, then commits are not controlled, monitored, or restricted by Application Engine. When Restart is disabled, commits are controlled by the program.

Re-Select

For each iteration of the loop, Application Engine opens a cursor and fetches the first row. Your program processes the first row returned from the Select statement. The cursor is reopened for each iteration of the loop. With this type of Fetch statement, you typically want some aspect of the loop to eventually cause the Select statement to return no rows. Otherwise, no mechanism is in place by which to exit the loop. This type of Do Select is restartable.

Restartable

This option is similar to Select/Fetch in that Application Engine opens the cursor associated with the Do Select action once, and then it performs a Fetch statement on each iteration of the loop to get each row from the Select statement. However, unlike the Select/Fetch option, you can restart this action because Application Engine performs a checkpoint in the middle of the step. Application Engine treats this loop as if it is restartable, but it does not manage the restart. Make sure that the SQL you include within this action is such that, upon restart, the program recognizes where the previous run failed and where to restart processing. For example, you can employ a processed switch or base the next Select statement on the key.