Using IBM Business Process Manager SQL Integration services

To integrate with an external database, you can use the SQL Integration services available in the IBM® BPM System Data Toolkit.

About this task

During IBM BPM installation, the System Data toolkit is imported into the Process Center repository so that each process application and toolkit that you create has access to IBM BPM system data. The System Data toolkit includes SQL Integration services to enable you to easily integrate with external databases.

The SQL Integration services support common database interactions, including support for parameterized queries. In addition, these services can automatically map query results directly into the relevant variable type. The SQL Integration services enable you to develop implementations to:

Read existing data from a database
Update existing data in a database
Write new data to a database

In addition, when passing data between IBM BPM and a connected database, the SQL Integration services enable you to specify certain types of data (like integers, BLOBs, and CLOBs).

Important: The SQL connector services in the System Data toolkit support local transactions only. They do not work properly in global transactions, for example, during deployment or in an installation service. When SQL connector services are used in a scenario that requires a global transaction, you might receive an error similar to the following error:

java.sql.SQLException: DSRA9350E: Operation Connection.commit  
is not allowed during a global transaction.                    
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.commit          
  (WSJdbcConnection.java:1092)                                 
at teamworks.sql.SQLExecutor.executeInTransaction             
  (SQLExecutor.java:111)                                       
at teamworks.SQLConnector.executeMultiple                     
  (SQLConnector.java:263)

The SQL Integration services are Java-based integrations that bind to a specific method in the teamworks.SQLConnector Java class. Although you cannot alter the SQL Integration services, you can open them in the Designer in IBM Process Designer to see the method implemented by each one and the available input and output variables as outlined in the following procedure.

Procedure

Open a process application in the Designer in IBM Process Designer.
Click the indicator next to the Toolkits category to see a list of toolkit dependencies for the current process application.
Click the indicator next to the System Data toolkit to see its contents.
Click the Implementation category and then double-click one of the listed SQL services.
For example, double-click the SQL Execute Statement service to open it.
In the service diagram, click the Java Integration component to select it.
Click the Definition option in the properties to display the Java Class and method implemented by the service.
Switch from the diagram view of the service by clicking the Variables tab.
Click on an Input or Output variable to see its details, such as its type and default values (where applicable).

What to do next

To use a SQL Integration service in an implementation, you can:

Select a SQL Integration service as the implementation for an activity as described in Implementing activities .
Nest a SQL Integration service in another service by dragging it from the library to the diagram of the parent service.