During run time, the Execute Query activity
runs a select SQL statement.
The Execute Query activity supports
parameterized queries - a query that contains input, or input and
output parameters. When input parameters are specified in a query,
the value of the parameter is supplied at run time. The ? character
in the following example select statement specifies that there is
one input parameter:
select * from myUserSchema.mySourceTable where myUserSchema.mySourceTable.myColumn=?
In
the preceding select statement, the
* character,
specifies that the query returns the column or columns found in the
mySourceTable table
as the output parameter or parameters. For example, if the
mySource
table contains two columns, one of type CHAR and one of
type VARCHAR, the
Execute Query activity returns
two output parameters, one of type CHAR and the other of type VARCHAR.
Note: Only
the select SQL statement is supported in the Execute Query activity.
The insert SQL statement is not supported in the Execute
Query activity.
The steps for adding and
configuring an
Execute Query activity to an
orchestration are described in the following procedures:
Add an Execute Query in the orchestration
- Create or
open an orchestration. A graphical representation of the orchestration
displays.
- Select the Activities tab and expand the Database folder.
- Drag the Execute Query activity onto the
orchestration.
- Select the Execute Query activity
Create, select, or edit
a database endpoint
- Click Pick Endpoint task from the Checklist and
choose from one of the following actions:
- Select an existing endpoint:
- Select Browse. The Project Explorer opens.
- Select an existing endpoint and click OK.
Skip to the Specify the query SQL statement procedure.
- Select an existing endpoint and edit the endpoint:
- Select Browse. The Project Explorer opens.
- Select an existing endpoint and click OK
- Click Edit. The Edit Endpoint pane
opens.
- Create an endpoint: Select New. The Create
Endpoint pane opens.
- Create
or edit a database endpoint
Specify the query SQL statement
- Select the Enter Query task from the Checklist.
The Enter Query pane opens.
- Enter the query in the white space displayed in the pane. Do not
place semicolons at the end of your query.
- Click View Columns to
review column details for a selected table to assist you in building
your SQL statement by entering valid table names and columns.
- Select an alternate character encoding setting for the
input and output parameters if the wanted encoding differs from the
default encoding for the database using one of the following options:
- From the Encoding list, select one of the
default encoding types.
- Enter your encoding type directly by clicking in the Encoding field
and typing in your encoding type.
- Click Validate Query. This action checks
if your query is valid and populates input and output parameters in
the Map Inputs and Map Outputs tasks.
- Enter a value into the MaxRows field. The MaxRows field
specifies the maximum number of rows to return from the query during
run time.
Note: If the same query is started multiple times on the same
data, the same set of rows are returned. For example if MaxRows is
equal to 1, the same row (the first row) is always returned each time
the query is started - there is no iteration though the set of rows.
Specify retry options for
the query
To specify the retry options for the query:
- Configure the Retry
Options.
Note: For the Database Execute Query activity,
if you set the retry option to zero, the Integration Appliance retries
the connection indefinitely.
Map the output of the activity
- Select the Map Outputs task in the Checklist.
The columns returned by select statement are the elements displayed
under the rows/row element in the From Activity pane.
- You are not required to map the output parameters of the activity.
See Creating a map for general instructions
on mapping.