RDBMS Process Designs
SQL step
SQL statement operations can be performed in a map using the ExecuteCommand action as part of an ECA rule. Pre-processing operations are normally performed in the TransformationStarted event while post-processing operations are done in the TransformationEnded event. However, in most cases it is better to separate these steps into a process workflow using a map and an SQL step. This loosely coupled approach allows you to reuse logic more readily and provides an easier to maintain set of artifacts because the rules and scripting are inherently more visible.
Use SQL Steps to facilitate the performance items mentioned in the context of Transformation Maps. It is recommended to design small, simple, and reusable workflows to perform these operations independently when you have to reuse functionality. For example, doing a bulk copy from an external table to a file or a staging table can be completely parameterized and generic for use with any table regardless of its schema. Accordingly, the same operation can be used in conjunction with any map.
Use SQL Steps in a process when you do not expect a result set back. For example, executing DDL statements that perform operations. The steps support the DJX syntax so can be designed for reuse and customization. You can also reference the scripts from file if required.
Application Step
The Application step is useful when you have to call any executable that has a command line interface as part of your integration workflow. One useful technique when working with RDBMS servers is the ability to call bulk loader utilities.
Use the Application step type to facilitate transformation mapping, to incorporate other database server specific operations, entire scripts written in another language into a process workflow.
Make sure to include the executable on all servers or systems where the DataConnect Runtime Engine will be installed and it has appropriate user access and permissions.
Sessions
In the context of DataConnect, sessions are essentially governed by the client drivers and are used to connect to the database server. As such, each database may behave differently and you must see the documentation for the database for details.
Generally, sessions provide two main uses in DataConnect:
• Reusable connection information to the database server
• Ability to manage commits and rollbacks during the integration runtime.
Make sure the following:
• You must limit the number of active sessions and close them when they are no longer used.
• Name the sessions according to the steps or branch of the process they represent.
• Multi-threaded processes must generally have a separate session for each branch.
• Use the same session for each step that you want included in the same transaction.
• Use SQL steps to issue commit or rollback commands as required.
• The Global Transaction default setting is "Off". Therefore, make sure to select this option if you want the entire process to complete before committing the transaction. If it does not complete, all the steps using the session are rolled back.