Specifying Actions

This section provides an overview of actions and discusses how to:

  • Insert actions.

  • Set action properties.

  • Specify SQL actions.

  • Specify Do actions.

  • Specify PeopleCode actions.

  • Specify Call Section actions.

  • Specify Log Message actions.

  • Specify XSLT actions.

You can include eight types of actions within a step, and a step can contain multiple actions. The actions you define for a step depend on the results that your program requires at each stage of execution.

The only mutually exclusive actions within a single step are Call Section and SQL Statement; you cannot add a Call Section action to a step that already contains a SQL Statement action, and vice versa. You can include only one of each action type within a single step. Because eight types of actions are available and two of these are mutually exclusive, the maximum number of actions a single step can contain is seven.

Action Execution Order

At runtime, the system evaluates actions by type and runs them within a strict hierarchy. For example, if both a Do When and PeopleCode action exist within a given step, then Application Engine always executes the Do When action first.

Image: Action execution hierarchy

The following diagram shows the sequence and level of execution for each type of action.

Action execution hierarchy

As you add actions to a step in the Definition view, the actions are initially inserted after the selected definition (the owning step or a previous action). However, following a save request or a refresh of the view, the designer reorders all actions to match the execution hierarchy. This feature helps you visualize the sequence in which each step of your program logic runs.

Note: A SQL action and a Call Section action are interchangeable and mutually exclusive. Only one of these two actions can appear within a step.

When inserting actions, remember that:

  • You cannot have more than one action of a specific type within the same step.

  • You cannot have a SQL action and a Call Section action within the same step.

  • You can define only XSLT type actions for programs defined as Transformation types (see the program properties).

To insert an action:

  1. Highlight the step in which you want to insert an action.

  2. Insert the action.

    You do this using one of the following methods:

    • Select Insert, Step/Action.

    • Right-click the step and select Insert Step/Action.

  3. Select the action type from the drop-down list or, when current action type is selected, enter the first one or two characters of the desired action type and then press Tab. The first (or only) type qualified by your entry is updated in this control.

  4. Enter a description of the action.

  5. Specify the appropriate properties for the action you selected.

To modify action properties, the Definition view must be active. Because you can include a variety of actions within a step, different sets of properties are specific to particular action types. Depending on the action type you select, the properties that appear will change.

For example, you can specify the reuse feature with a SQL action. This feature does not apply to a PeopleCode action; instead, you would need to specify how to respond to the return value of the PeopleCode program.

Image: Actions and their associated properties

This example illustrates the fields and controls on the Actions and their associated properties.

Actions and their associated properties

PeopleCode and all SQL action types invoke the related PeopleTools Editor to define or maintain the related text.

ReUse Statement Property

The ReUse Statement property is available for all SQL action types (SQL, Do When, Do While, Do Until, Do Select). You use the ReUse Statement property to optimize the SQL in your batch program. A ReUse Statement converts any %BIND references to state record fields into real bind variables (:1, :2, and so on), enabling the Application Engine runtime process to compile the statement once, dedicate a cursor, and then run it again with new data as often as your program requires. When you use SQL or a Do action to process a large volume of rows one at a time, inside a fetch loop, compiling each statement that you issue can affect performance significantly. ReUse Statement is a way to combat possible performance slowdowns.

Note: You can have Application Engine recompile a reused statement by using the %ClearCursor function.

When setting the ReUse Statement option, choose from these values:

Field or Control

Definition
Bulk Insert

When used in conjunction with statements like INSERT INTO tablename (field1, field2...) VALUES (%BIND(ref1), %BIND(ref2), the Bulk Insert feature offers the most powerful performance enhancement related to the ReUse Statement feature. This option turns on a ReUse Statement and, in addition, holds all the data in a buffer and performs an insert only after a large number of rows have gathered in the buffer. The number of rows allowed to gather in the buffer depends on your database platform. Storing data in the buffers is applicable only if you selected Bulk Insert and the SQL is an Insert statement. For statements other than Insert, the system ignores theBulk Insert option.
No

Select this option to disable a ReUse Statement. With ReUse deselected, the Application Engine runtime process recompiles the SQL statement every time the loop runs. By default, a ReUse Statement is disabled.
Yes

Select this option to enable basic ReUse Statement functionality.

Note: The ReUse Statement property can improve performance significantly. However, do not use it if %BIND variables are building parts of the SQL statement or are in the field list of a Select statement (this note does not apply if you use the Static option in %BIND).

This is the default action type for the first action within a given step. Use this action to perform the following SQL commands on multiple rows:

  • Update

  • Insert

  • Delete

  • Select

Note: Before you insert SQL (select View, SQL) into a SQL action within a new Application Engine program, you must have saved the program previously. Saving is required because the program name you use to save the definition is used to relate your program with the SQL objects you are about to create. The same is true for inserting PeopleCode.

With a SQL action, you use the SQL Editor to create and modify a SQL statement. Following are some examples of SQL statements:

%Select(AF_PERFM_AET.PREV_ASOF_DT)
SELECT %DateOut(ASOF_DT)
FROM PS_AF_FCST_SCHT%Bind(EPM_CORE_AET.TABLE_APPEND,NOQUOTES)
WHERE AFDEFN_ID = %Bind(AF_CORE_AET.AFDEFN_ID)
AND ASOF_DT = (SELECT MAX(ASOF_DT)
FROM PS_AF_FCST_SCHT%Bind(EPM_CORE_AET.TABLE_APPEND,NOQUOTES)
WHERE AFDEFN_ID = %Bind(AF_CORE_AET.AFDEFN_ID)
AND ASOF_DT < %Bind(AF_PERFM_AET.ASOF_DT))

Note: If you intend to include multiple SQL statements within a single action, you should use the meta-SQL construct %EXECUTE. The previous sample SQL statement sample contains bind variables from a previous Application Engine action.

Note: Application Engine framework has restricted the length of executable SQL statement. The maximum SQL length is 32768. Application Engine does not process statements longer than that and will return an error.

No Rows Property

In addition to the ReUse Statement property, the No Rows property is available for SQL actions. If the SQL (Insert, Update, or Delete) associated with the SQL action does not return any rows, you must specify what the Application Engine program should do.

For example, you could use the No Rows property when you insert into a temporary table and then intend to perform further operations on the inserted rows (provided that some rows meet the criteria). If the initial combination of Insert and Select statements provides no rows, you could save the program from having to reselect on the temporary table before executing another operation, or you could prevent the program from performing set operations on the table with no qualifying rows.

When you set the No Rows property, choose from the following values:

Field or Control

Definition
Abort

The program terminates.
Section Break

Application Engine exits the current section immediately, and control returns to the calling step.
Continue

The program continues processing.
Skip Step

Application Engine exits the current step immediately and moves on to the next step. Application Engine ignores the commit for the current step at runtime. If the current step contains only one action, then use Skip Step only to bypass the commit.

Note: Using the No Rows property in conjunction with a Truncate Table operation is unreliable. Some database platforms report zero rows affected for truncations, regardless of how many rows were in the table.

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

Definition
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.

Use this action type to insert PeopleCode within your Application Engine program. You can invoke the PeopleCode Editor directly from the designer interface to code your PeopleCode programs.

With a PeopleCode action, you can specify only one property: On Return.

Use the On Return value to determine how your Application Engine program reacts based on the return of your PeopleCode program. The On Return setting takes effect if your PeopleCode program issues a “return 1” or “exit 1.” You can use the True keyword in place of a non-zero numeric return.

When you specify the On Return property, you select from the following values:

Field or Control

Definition
Abort

The program issues an error and exits immediately.
Break

The program exits the current step and section, and control returns to the calling step.
Skip Step

The program exits the current step and continues processing at the next step in the section. If this step is the last one in the section, then the calling step resumes control of the processing.

Use the Call Section action to call another section defined in an Application Engine program. You can call a local section defined within your current program, and you can make external calls to a section defined in another Application Engine program.

The external section you intend to call must have its access property set to Public. If the access property of a section is set to Private, that section can be called only from within the same program. By default, the access property of a section is Private. If you attempt to make a call to a section that does not allow external calls, you receive an error message at runtime.

Note: You can call only programs that reside within the same database as the calling program.

Program ID Property

Because you can call sections defined in the current program or within external programs, you must first specify the program ID of the program containing the section you intend to call.

The default value is (current). If you call a section defined in another program, make sure that you first select the appropriate external program from the Program ID drop-down list. This drop-down list contains the names of all program definitions that currently exist in the database.

Section Name Property

Select from names defined in the program that appears in the Program ID list. To call a section that is defined in an external program, select the program name in theProgram ID edit box before selecting the section name.

Also use the Call Section action to call an entire external program. First select the program ID, and then select section name MAIN. At runtime, this call executes the entire program defined by the value in the Program ID field.

Note: Application Designer does not prevent you from calling the Main section of the current program or the current section. For instance, Section1 can contain a step that has a local call section reference for Section1. This reference enables recursive calls and should, therefore, be used with caution.

Dynamic Property

Use the AE_APPLID and AE_SECTION fields in the state record to run different sections, depending on the conditions a program encounters during runtime.

You must define these two fields in the default state record for the program. If AE_APPLID is not present or is blank (at runtime), the current program is substituted for the AE_APPLID value. If AE_SECTION is not present or is blank, an error occurs.

When issuing a dynamic call, both the section and the program ID must be dynamically set. You enable a dynamic call by first having your program store different section names in the AE_SECTION field and different program names in AE_APPLID field. The values you insert in these fields are normally based on various conditions met within your program. Then you create a Call Section action that calls the section name defined in the state record field by selecting the Dynamic check box.

Selecting Dynamic automatically populates the AE_SECTION field with the symbolic value %Section and theProgram ID field with the symbolic value %AEAPPLID. At runtime, the program calls the section name stored in AE_SECTION that belongs to the program name defined by AE_APPLID.

Program Properties of Called Sections

When you call a section defined in an external program, the current program (the program containing the defined call section) defines the properties that apply to the running process. Suppose tracing is enabled for the current program but disabled for the called program section. In this case, the called program has the trace option enabled at runtime because it inherits the properties of the calling program.

For example, if program A calls program B, and program B calls program C, then the properties of A apply to both programs B and C. The calling program always controls the properties for the called program. In this case, program A controls the properties for program B and because program B inherits the properties of program A, when program B calls program C the properties of program A also apply to program C.

Note: Although program properties are inherited, state records do not follow this inheritance model.

State Records of Called Programs

When you call a program from another program, the default state record of the called program becomes active until processing returns to the initial program. However, all of the state records associated with both programs are available. State records that are common between the two programs share values. To communicate between the two programs or share %BIND variables, define the same state records in both programs.

Use this type of action to write a message to the message log. The message log refers to the PeopleTools table (PS_MESSAGE_LOG) where execution messages reside. Any substitution parameters are written to PS_MESSAGE_LOGPARM.

Image: Example of a Log Message action

This example illustrates the fields and controls on the Example of a Log Message action. You can find definitions for the fields and controls later on this page.

Example of a Log Message action

You can use the Log Message action to insert any type of message. Typically, a Log Message action writes error messages to the message log, but you can also write informational or status messages.

Note: You can also use MessageBox PeopleCode to populate PS_MESSAGE_LOG instead of using the Log Message action. Using MessageBox PeopleCode enables you to record errors encountered within Application Engine PeopleCode programs easily.

Field or Control

Definition
Message Set and Number

Select a message defined in the message catalog.
Parameters

Enter values to insert in the log message. This field should be a comma-delimited list of values to substitute for the message variables (%1, %2, and so on) in the message text. These parameters can be hard-coded values or %Bind references. The specified information is inserted into the PS_MESSAGE_LOG at runtime, and any %Bind values are replaced by the current state record field values. Then you can view the logged messages from the Process Monitor page.

For example, using message set 1012, number 10, the message reads "The total number of %1 rows exceeds the control count value, %2,” and you need the following parameters:

Invoice, %Bind(CONTROL_CNT)

Suppose you run this program with the CONTROL_CNT field value of 120. When the process ends, the following message would be included on the Process Details dialog box in Process Monitor: “The total number of Invoice rows exceeds the control count value, 120.”

You use XSLT actions only for transform programs.