Adding SQL snippet result set reference settings

Processes using SQL snippets often include result set variables that operate on temporary or staging tables. To avoid having to create these tables before a process starts and dropping them again when they are no longer needed, you can control the life cycle of a database table at the activity level by specifying preparation and cleanup statements.

To add life cycle settings to a result set reference in an SQL snippet:

  1. Select an existing SQL snippet information service activity on the canvas, or create a new one.
  2. Click the Details tab in the properties area to see the SQL snippet properties.
  3. Click Edit to add or edit a result set reference.
  4. Select a result set reference variable and the preparation and cleanup statements for the variable.

    Note that a SELECT statement has exactly one result set reference, a stored procedure call may have 0 or more result set references, and that all other statements do not have result set references.

    The following preparation options are supported:
    • Do not create table: No preparation action is performed. This is the default.
    • Always create new table: The table is created if it does not exist. If a table exists that has the same name, this table is first dropped and a new table is created.
    • Create table only if it does not exist yet: The table is created only if it does not exist.
    The cleanup options include:
    • Do not drop table: No cleanup action is performed. This is the default.
    • Drop table at end of activity: The table is dropped after the last statement of the activity is executed.
    • Drop table at end of process instance: The table is dropped when the process instance is stopped.

Example

Consider an SQL snippet with a SELECT statement that declares a result set reference with the preparation option set to “Always create new table” and the cleanup option set to “Drop table at end of process instance”. This means that the table is created just before it is filled with the result of the SELECT statement and dropped again when the process instance is stopped.

If the same set reference is again used as input in a subsequent UPDATE or INSERT statement, the default is used which is no preparation and cleanup.

Preparation and cleanup statements can be inferred from the context. For example, if the preparation option “Always create new table” is specified for a set reference used as the output of a query, an appropriate CREATE TABLE statement is inferred. The output table is created with the correct column type to fit the result of the query.

Preparation and cleanup statements are only inferred if no preparation and cleanup statements were defined for the result set reference as initial values.

(C) Copyright IBM Corporation 2006. All Rights Reserved.