Leveraging PowerCenter Concurrent Workflows

Challenge

Before the introduction of PowerCenter's Concurrent Workflow feature, customers would make copies of workflows and run those using different names. This not only caused additional work, but also created maintenance issues during changes to the workflow logic. With PowerCenter's Concurrent Workflow feature, it is now possible to run more than one instance of a workflow.

Description

Use Case Scenarios

Message Queue Processing

When data is read from a message queue, the data values in the queue can be used to determine which source data to process and which targets to load the processed data. In this scenario, different instances of the same workflow should run concurrently and pass different connection parameters to the instances of the workflow depending on the parameters read from the message queue. One example is a hosted data warehouse for 120 financial institutions where it is necessary to execute workflows for all the institutions in a small time frame.

Web Services

Different consumers of a web service need the capability to launch workflows to extract data from different external systems and integrate it with internal application data. Each instance of the workflow can accept different parameters to determine where to extract the data from and where to load the data to. For example, the Web Services Hub needs to execute multiple instances of the same web service workflow when web services requests increase.

Configuring Concurrent Workflows

One option is to run with the same instance name. When a workflow is configured to run with the same instance name, the Integration Service uses the same variables and parameters for each run. The Workflow Monitor displays the Run Id to distinguish between each workflow.

Informatica recommends using unique instance names instead of the same name with different Run Id values to implement Concurrent Workflows. With unique instance names, it is possible to allow concurrent runs only with unique instance names. This option enables execution of Concurrent Workflows with different instance names. For example, a different name can be used for the configuration of each workflow instance and a separate parameter file can be used for each instance. The Integration Service can persist variables for each workflow instance. When the workflow is executed, the Integration Service runs only the configured instances of the workflow.

Tips & Techniques

There are several tips and techniques that should be considered for the successful implementation of Concurrent Workflows. If the target is a database system, database partitioning can be used to prevent contention issues when inserting or updating the same table. When database partitioning is used, concurrent writes to the same table will less likely encounter deadlock issues.

Competing resources such as lookups are another source of concern that should be addressed when running Concurrent Workflows. Lookup caches as well as log files should be exclusive for concurrent workflows to avoid contention.

Partitioning should also be considered. Mapping Partitioning or data partitioning is not impacted by the Concurrent Workflow feature and can be used with minimal impact.

On the other hand, parameter files should be created dynamically for the dynamic concurrent workflow option. This requires the development of a standard way to generate the parameter files at run time. A database driven option can be used for maintaining the parameters in database tables. During the execution of the Concurrent Workflows, the parameter files can be generated from the database.