Execute Query Activity

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.

Add an Execute Query in the orchestration

  1. Create or open an orchestration. A graphical representation of the orchestration displays.
  2. Select the Activities tab and expand the Database folder.
  3. Drag the Execute Query activity onto the orchestration.
  4. Select the Execute Query activity

Create, select, or edit a database endpoint

  1. Click Pick Endpoint task from the Checklist and choose from one of the following actions:
    • Select an existing endpoint:
      1. Select Browse. The Project Explorer opens.
      2. Select an existing endpoint and click OK. Skip to the Specify the query SQL statement procedure.
    • Select an existing endpoint and edit the endpoint:
      1. Select Browse. The Project Explorer opens.
      2. Select an existing endpoint and click OK
      3. Click Edit. The Edit Endpoint pane opens.
    • Create an endpoint: Select New. The Create Endpoint pane opens.
  2. Create or edit a database endpoint

Specify the query SQL statement

  1. Select the Enter Query task from the Checklist. The Enter Query pane opens.
  2. Enter the query in the white space displayed in the pane. Do not place semicolons at the end of your query.
  3. 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.
  4. 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.
    Note: Some double-byte characters are not converted using the SHIFT-JIS encoding. For more information, see Using the Shift-JIS encoding.
  5. 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.
  6. 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.

Configure the input parameters for the query

  1. Select the Setup Input Parameters task from the Checklist. The Setup Input Parameters pane shows the input parameter or parameters of the query.
  2. 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.
  3. Click in the heading of the column to sort the rows by the column value. For example, to sort the rows by their column name, click the Column Name heading of the column. An arrow is displayed to the right of the column header name. An up arrow indicates the column is sorted by alphabetic order starting with a and continuing to z. A down arrow indicates the column is sorted by reverse alphabetic order starting with z and continuing to a. Click the arrow to reverse the alphabetic order.
  4. If necessary, change the settings of the Data type, Encoding, or Nullable values for each query input parameter. Click the field to make a new selection:
    • Parameter Name - You can change the default name to a more meaningful name. Parameters are listed in the order they are specified in the query.
    • Data Type - Select a data type for this parameter.
      Note: (for MONEY and XMLTYPE data types) For the XMLTYPE data type in an Oracle database and the MONEY data types in a SQL Server database, Studio does not automatically associate the correct data type so you must manually select the appropriate data type.
    • Encoding - Select an alternate character encoding setting for the parameter if the required encoding differs from the default encoding for the database.
    • Size - Size of the column defined in the table. This size is retrieved from the database table and is not configurable.
  5. Click Reset Values to interrogate the database and get the data types and size from the database. This overrides the data type and size set manually in the pane.

Configure output parameters

  1. Select the Setup Result Set task from the Checklist. The Setup Result Set pane opens and the defaults for the ouput parameter or parameters of the query are listed.
  2. 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.
  3. If necessary, change the settings of the Data type, Encoding, or Nullable values for each query output parameter:
    • Parameter Name - You can change the default name to a more meaningful name. Parameters are listed in the order they are specified in the query.
    • Data Type - Select a data type for this parameter.
    • Encoding - Select an alternate character encoding setting for the parameter if the required encoding differs from the default encoding for the database.
    • Size - Size of the column defined in the table. This size is retrieved from the database table and is not configurable.
  4. Click Reset Values to interrogate the database and get the data types and size from the database. This overrides the data type and size set manually in the pane.

Specify retry options for the query

To specify the retry options for the query:
  1. 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 input of the activity

  1. Select the Map Inputs task in the Checklist. The input parameters specified for the query are the input parameters for the activity. The input parameters of the activity are displayed under the parameters element in the To Activity pane.
  2. You must map all the input parameters of the activity. See Creating a map for general instructions on mapping.

Map the output of the activity

  1. 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.
  2. You are not required to map the output parameters of the activity. See Creating a map for general instructions on mapping.