Show Menu
TOPICS×

SQL Data Management

The SQL Data Management activity lets you write your own SQL scripts to create and populate work tables.

Prerequisites

Before configuring the activity, make sure the following prerequisites are fulfilled:
  • The activity is available for remote data sources only. The FDA (Federated Data Access) package must therefore be installed on your instance (see this section ).
  • The Outbound schema must exist in the database and be linked to an FDA database (for more on data schemas, refer to this section ).
  • The operator executing the workflow must have the USE SQL DATA MANAGEMENT ACTIVITY (useSqlDmActivity) named right. For more on named rights, refer to this section .

Configuring the SQL Data Management activity

  1. Specify the activity Label .
  2. Select the External account to use, then select the Outbound schema linked to this external account.
    The Outbound schema is fixed and cannot be edited.
  3. Add the SQL script.
    It is the SQL script writer's responsibility to make sure that the SQL script is functional, and that its references (fields names, etc.) are in accordance with the Outbound schema.
    If you want to load an existing SQL code, select the The SQL script is contained in an entity stored in the database option. SQL scripts must be created and stored in the Administration / Configuration / SQL scripts menu.
    Otherwise, type or copy-paste your SQL script in the dedicated area.
    The activity lets you use the following variables in the script:
    • activity.tableName : SQL name of the outbound work table.
    • task.incomingTransitionByName(‘name’).tableName : SQL name of the work table carried by the incoming transition to use (the transition is identified by its name).
      The ('name') value corresponds to the Name field from the transition properties.
  4. If the SQL script already contains commands to create an outbound work table, unselect the Automatically create work table option. Otherwise, a work table is automatically created once the workflow executes.
  5. Click Ok to confirm the activity configuration.
The activity is now configured. It is ready to be executed in the workflow.
Once the activity executed, the outbound transition records count is indicative only. It may vary according to the level of complexity of the SQL script.
If the activity is restarted, the whole script is executed from its beginning, regardless of it execution status.

SQL script samples

The script samples in this section are meant to be executed under PostgreSQL.
Below script lets you create a work table and insert data into this same work table:
CREATE UNLOGGED TABLE <%= activity.tableName %> (
  iRecipientId INTEGER DEFAULT 0,
  sFirstName VARCHAR(100),
  sMiddleName VARCHAR(100),
  sLastName VARCHAR(100),
  sEmail VARCHAR(100)
);

INSERT INTO <%= activity.tableName %>
SELECT iRecipientId, sFirstName, sMiddleName, sLastName, sEmail
FROM nmsRecipient
GROUP BY iRecipientId, sFirstName, sMiddleName, sLastName, sEmail;

Below script lets you perform a CTAS operation (CREATE TABLE AS SELECT) and create a work table index:
CREATE TABLE <%= activity.tableName %>
AS SELECT iRecipientId, sEmail, sFirstName, sLastName, sMiddleName
FROM nmsRecipient
WHERE sEmail IS NOT NULL
GROUP BY iRecipientId, sEmail, sFirstName, sLastName, sMiddleName;

CREATE INDEX ON <%= activity.tableName %> (sEmail);

ANALYZE <%= activity.tableName %> (sEmail);

Below script lets you merge two working tables:
CREATE TABLE <%= activity.tableName %>
AS SELECT i1.sFirstName, i1.sLastName, i2.sEmail
FROM <%= task.incomingTransitionByName('input1').tableName %> i1
JOIN <%= task.incomingTransitionByName('input2').tableName %> i2 ON (i1.id = i2.id)