JDBC Adapter execution mode Chained or Unchained ??

Scenario: You have to update data in Sybase tables  by calling a stored procedure. You finish developing the interfaces in repository and configure the scenario in Integration Builder. And just when you think you are done and start testing the interface, you find messages in runtime workbench (RWB) in waiting status as shown in figure 1.

Problem: On looking up the detailed information of the message you find message “Unable to execute statement for table or stored procedure … stored procedure may run only in unchained transaction mode” as shown in figure 2.

Solution: In the database world there are two transaction modes:

a) In Chained mode, transactions are implicitly started before executing statements like delete, insert, open, fetch, select, and update. But these transactions must be explicitly ended with commit transaction or rollback transaction.

b) In unchained mode (default mode), an explicit “begin transaction statement” is required to be paired with commit transaction or rollback transaction statements to complete the transaction.
 

Unlike statements like delete, insert which are run in chained mode stored procedures run only in unchained transaction mode. Hence if the adapter is running in chained mode and tries to execute a stored procedure one would encounter an error as in Figure 2. There are two ways to solve this problem

1. To change the transaction mode for the stored procedure to “any mode”. this can be achieved by executing the following SQL code  > sp_procxmode sp_myproc, “Anymode”

2. Or to configure the adapter to run in unchained mode.

Approach 1 looks appealing if you don’t want to change the adapter settings. Figure 3 shows the adapter setting in Integration Builder running in Chained Mode. The disadvantage of this approach is that the mode of stored procedure is reverted back to the database default.

.

Hence the obvious choice for solving the problem is approach 2 , i.e. to change the transaction mode of the JDBC adapter as shown in figure 4.

Make the necessary adapter changes as in figure 4, activate changes and test the interface again. This time the message makes it to the database without any error as shown in figure 5.