Database Integration

Note: To apply these Database Best Practices, you should be familiar with general Process Development, Building Connectors and the Database Connector components.

Querying Records

Setting the Grouping Options in the Database Read Operation is critical for most database integrations. There are no database record splitting options available within the Data Process step mid-process, so you must define the operation's Link Element and/or Batch Count to group records by a matching field or record count.

Example

Database Read Profile
SELECT ORDERNUM, LINENUMBER, PRODUCTDATA FROM ORDERS

Return Data

12345|^|1|^|ROUTERS
12345|^|2|^|HUBS
12346|^|1|^|NETWORK CARDS

If you need to map individual orders to a destination system, you should batch the orders in the Database Read Operation:

Configuration
Link Element- ORDERNUM (Element definition from Profile)
Batch Count - 1 (Meaning 1 unique ORDERNUM per batch)

Result
Order 1 Document

12345|^|1|^|ROUTERS
12345|^|2|^|HUBS

Order 2 Document

12346|^|1|^|NETWORK CARDS

Setting the Max Rows option is helpful when you want to limit the amount of SQL records entering the Process. If you are building a process that is migrating a large data set to another system, you will ultimately want to prevent the same records from being sent repeatedly during each Process execution.

In order to prevent this, you can try one of the following options:

• Add a final branch into a SQL Type Program Command step at the end of the Process flow to update each record based on its record ID
• Add an SQL Update map function into one of the main Data Maps to perform the update per record
• Add a new map and Database Write Connector that builds and executes the SQL update

Example Program Command Script
UPDATE ORDERS SET STATUS = 'PROCESSED' WHERE ORDERNUM = ?

Example Parameter
Profile Element > Database > Orders Select Profile > OrderNum Element

Sending Records

Setting the Commit Options in the Database Write operation is helpful when wanting to finalize the execution of sql statements in groups or by Data Sets for multi-table relationships such as Header/Detail or Parent/Child.

Example

Operation Configuration
Commit Option: Commit by Profile
Batch Count: 1

The goal is to commit each unique order/orderdetail instance uniquely. In the event of a connection failure mid-execution, the entire order insert will fail to commit vs. the main order record and, for example, 2 out of 5 detail records.

Setting Database Parameters

In order to set a dynamic parameter in SQL script, you must use the ? symbol. The questionmark will bind positionally to the parameter listed in either the Profile or Parameters list(Program Command, Decision, etc.).

Understanding Retry Schedules

When setting and deploying a Retry Schedule to automatically re-run failed documents, it is important to set the proper Batch Count in the Database Read Operation. You will want to set a Batch Count of 1 to treat each record as an individual document and/or apply the proper Link Element. If a document/record fails for this case, the retry schedule will only re-run the failed instance vs. re-running the entire database record set.