Skip to Content

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.

 

image

Figure 1: Messages in waiting status

 

image

Figure 2: Detailed error description of the message with waiting status.

 

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.

.

image

Figure 3: JDBC Receiver Adapter configured in Chained Mode

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.

image

Figure 4: JDBC Receiver Adapter configured in Unchained Mode

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.

image

Figure 5: Stored procedure is successfully executed without errors
 
To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Michal Krawczyk
    Hi Naveen,

    I like the style of your weblog
    (long explanations) this is what we need:
    good quality XI weblogs:)

    I’ll have to start doing the same
    and not just step by step procedures:)

    Regards,
    michal

    (0) 
    1. Anonymous
      Hi michal,

      Frankly you are my inspiration. I was too lazy to write blogs or technical docs. But after going over tons of ur documents, i felt obliged to share my knowledge too with SDN, that has been so helpful for me in expanding my realm of knowledge.

      Regards,
      Naveen

      (0) 

Leave a Reply