Using Pre- and Post-Session SQL Commands

By PenchalaRaju.Yanamala

For information about configuring SAP Filters in the session properties, see the
PowerExchange for SAP NetWeaver User Guide.

For information about entering MQ filter conditions, see the PowerExchange for
IBM WebSphere MQ User Guide.

For more information about overriding SQL defined in the mapping, see
Overriding the SQL Query.

For information on configuring environment SQL, see Configuring Environment

SQL.

You can specify pre- and post-session SQL in the Source Qualifier
transformation and the target instance when you create a mapping. When you
create a Session task in the Workflow Manager you can override the SQL
commands on the Mapping tab. You might want to use these commands to drop
indexes on the target before the session runs, and then recreate them when the
session completes.

The Integration Service runs pre-session SQL commands before it reads the
source. It runs post-session SQL commands after it writes to the target.

You can use parameters and variables in SQL executed against the source and
target. Use any parameter or variable type that you can define in the parameter
file. You can enter a parameter or variable within the SQL statement, or you can
use a parameter or variable as the command. For example, you can use a
session parameter, $ParamMyPreSQL, as the source pre-session SQL
command, and set $ParamMyPreSQL to the SQL statement in the parameter
file.

Guidelines for Entering Pre- and Post-Session SQL Commands

Use the following guidelines when creating the SQL statements:

Use any command that is valid for the database type. However, the Integration
Service does not allow nested comments, even though the database might.
Use a semicolon (;) to separate multiple statements. The Integration Service
issues a commit after each statement.
The Integration Service ignores semicolons within /* ...*/.
If you need to use a semicolon outside of comments, you can escape it with a
backslash (\).
The Workflow Manager does not validate the SQL.

Error Handling

You can configure error handling on the Config Object tab. You can choose to
stop or continue the session if the Integration Service encounters an error issuing
the pre- or post- session SQL command.
Using Pre- and Post-Session Shell Commands

The Integration Service can perform shell commands at the beginning of the
session or at the end of the session. Shell commands are operating system
commands. Use pre- or post-session shell commands, for example, to delete a
reject file or session log, or to archive target files before the session begins.

The Workflow Manager provides the following types of shell commands for each
Session task:

Pre-session command. The Integration Service performs pre-session shell

commands at the beginning of a session. You can configure a session to stop or
continue if a pre-session shell command fails.
Post-session success command. The Integration Service performs post-
session success commands only if the session completed successfully.
Post-session failure command. The Integration Service performs post-session
failure commands only if the session failed to complete.

Use the following guidelines to call a shell command:

Use any valid UNIX command or shell script for UNIX nodes, or any valid DOS
or batch file for Windows nodes.
Configure the session to run the pre- or post-session shell commands.

The Workflow Manager provides a task called the Command task that lets you
configure shell commands anywhere in the workflow. You can choose a reusable
Command task for the pre- or post-session shell command. Or, you can create
non-reusable shell commands for the pre- or post-session shell commands.

If you create a non-reusable pre- or post-session shell command, you can make
it into a reusable Command task.

The Workflow Manager lets you choose from the following options when you
configure shell commands:

Create non-reusable shell commands. Create a non-reusable set of shell

commands for the session. Other sessions in the folder cannot use this set of
shell commands.
Use an existing reusable Command task. Select an existing Command task
to run as the pre- or post-session shell command.

Configure pre- and post-session shell commands in the Components tab of the
session properties.

Related Topics:
Working with the Command Task

Using Parameters and Variables

You can use parameters and variables in pre- and post-session commands. Use
any parameter or variable type that you can define in the parameter file. You can
enter a parameter or variable within the command, or you can use a parameter
or variable as the command. For example, you can include service process
variable $PMTargetFileDir in the command text in pre- and post-session
commands. When you use a service process variable instead of entering a
specific directory, you can run the same workflow on different Integration
Services without changing session properties. You can also use a session
parameter, $ParamMyCommand, as the pre- or post-session shell command,
and set $ParamMyCommand to the command in a parameter file.

Related Topics:
Where to Use Parameters and Variables

Configuring Non-Reusable Shell Commands

When you create non-reusable pre- or post-session shell commands, the

commands are only visible in the session properties. The Workflow Manager
does not create Command tasks from these non-reusable commands. You can
convert a non-reusable shell command to a reusable Command task.

To create non-reusable pre- or post-session shell commands:

In the Components tab of the session properties, select Non-reusable for pre-
1.or post-session shell command.
Click the Edit button in the Value field to open the Edit Pre- or Post-Session
2.Command dialog box.
3.Enter a name for the command in the General tab.
If you want the Integration Service to perform the next command only if the
previous command completed successfully, select Fail Task if Any Command
4.Fails in the Properties tab.
5.In the Commands tab, click the Add button to add shell commands.
Enter one command for each line.
6.Click OK.

Creating a Reusable Command Task from Pre- or Post-Session Commands

If you create non-reusable pre- or post-session shell commands, you can make
them into a reusable Command task. After you make the pre- or post-session
shell commands into a reusable Command task, you cannot revert back.

To create a Command Task from non-reusable pre- or post-session shell

commands, click the Edit button to open the Edit dialog box for the shell
commands. In the General tab, select the Make Reusable check box.

After you select the Make Reusable check box and click OK, a new Command
task appears in the Tasks folder in the Navigator window. Use this Command
task in other workflows, just as you do with any other reusable workflow tasks.

Configuring Reusable Shell Commands

Use the following procedure to call an existing reusable Command task as the
pre- or post-session shell command for the Session task.

To select an existing Command task as the pre-session shell command:

1.In the Components tab of the session properties, click Reusable for the pre- or
 post-session shell command.
2.Click the Edit button in the Value field to open the Task Browser dialog box.
Select the Command task you want to run as the pre- or post-session shell
3.command.
Click the Override button in the Task Browser dialog box if you want to change
the order of the commands, or if you want to specify whether to run the next
4.command when the previous command fails.
Changes you make to the Command task from the session properties only apply
to the session. In the session properties, you cannot edit the commands in the
Command task.
Click OK to select the Command task for the pre- or post-session shell
5.command.
The name of the Command task you select appears in the Value field for the
shell command.

Pre-Session Shell Command Errors

You can configure the session to stop or continue if a pre-session shell command
fails. If you select stop, the Integration Service stops the session, but continues
with the rest of the workflow. If you select Continue, the Integration Service
ignores the errors and continues the session. By default the Integration Service
stops the session upon shell command errors.

Configure the session to stop or continue if a pre-session shell command fails in

the Error Handling settings on the Config Object tab.

Validating a Session

The Workflow Manager validates a Session task when you save it. You can also
manually validate Session tasks and session instances. Validate reusable
Session tasks in the Task Developer. Validate non-reusable sessions and
reusable session instances in the Workflow Designer.

The Workflow Manager marks a reusable session or session instance invalid if

you perform one of the following tasks:

Edit the mapping in a way that might invalidate the session. You can edit the
mapping used by a session at any time. When you edit and save a mapping, the
repository might invalidate sessions that already use the mapping. The
Integration Service does not run invalid sessions.
You must reconnect to the folder to see the effect of mapping changes on
Session tasks.
When you edit a session based on an invalid mapping, the Workflow Manager
displays a warning message:

The mapping [mapping_name] associated with the session [session_name] is

invalid.

Delete a database, FTP, or external loader connection used by the session.

Leave session attributes blank. For example, the session is invalid if you do not
specify the source file name.
Change the code page of a session database connection to an incompatible
code page.
If you delete objects associated with a Session task such as session
configuration object, Email, or Command task, the Workflow Manager marks a
reusable session invalid. However, the Workflow Manager does not mark a non-
reusable session invalid if you delete an object associated with the session.

If you delete a shortcut to a source or target from the mapping, the Workflow
Manager does not mark the session invalid.

The Workflow Manager does not validate SQL overrides or filter conditions
entered in the session properties when you validate a session. You must validate
SQL override and filter conditions in the SQL Editor.

If a reusable session task is invalid, the Workflow Manager displays an invalid

icon over the session task in the Navigator and in the Task Developer
workspace. This does not affect the validity of the session instance and the
workflows using the session instance.

If a reusable or non-reusable session instance is invalid, the Workflow Manager

marks it invalid in the Navigator and in the Workflow Designer workspace.
Workflows using the session instance remain valid.

To validate a session, select the session in the workspace and click Tasks >
Validate. Or, right-click the session instance in the workspace and choose
Validate.

Validating Multiple Sessions

You can validate multiple sessions without fetching them into the workspace. You
must select and validate the sessions from a query results view or a view
dependencies list. You can save and optionally check in sessions that change
from invalid to valid status.

Note: If you use the Repository Manager, you can select and validate multiple
sessions from the Navigator.

To validate multiple sessions:

1.Select sessions from either a query list or a view dependencies list.

2.Right-click one of the selected sessions and choose Validate.
The Validate Objects dialog box appears.
3.Choose whether to save objects and check in objects that you validate.