Sybase Chain Modes and the IDM Driver for JDBC

  • Document ID:3573260
  • Creation Date:02-Jan-2007
  • Modified Date:26-Apr-2012
    • Micro Focus Products:
      Identity Manager

Environment

Novell Identity Manager Driver- JDBC Driver
Novell Identity Manager Identity Manager 3.0

Situation

Sybase can execute stored procedures in two distinct modes: chained and unchained. Depending upon the configuration of the IDM Driver for JDBC and stored procedures in a database, various problems can arise. This topic is sufficiently nuanced to warrant a detailed discussion of chain modes and their impact on the IDM Driver for JDBC, hereafter referred to as "the driver”.

Resolution

Error 226: SET CHAINED command not allowed within multi-statement transaction.
Effect:
Exception com.sybase.jdbc2.jdbc.SybSQLExceptionwith error-code226and an SQL state ofZZZZZis thrown. The description reads as follows: "SET CHAINED command not allowed within multi-statement transaction.”.
Cause:
This exception is usually caused by a defect in older versions of jConnect 5.5.
Solution:

Error 7112: Stored procedure'x'may be run only in chained transaction mode.

Effect:
Exception com.sybase.jdbc2.jdbc.SybSQLExceptionwith error-code7712and an SQL state ofZZZZZis thrown. The description reads as follows: "Stored procedure 'x' may be run only in chained transaction mode. The 'SET CHAINED ON' command will cause the current session to use chained transaction mode (wherexis the name of the stored procedure in question).”
Cause:
The stored procedure was created in, or later altered to run in, chained mode and the driver is currently running in unchained mode. The likely cause is that parameteruse-manual-transactions(https://www.novell.com/documentation/idmdrivers/jdbc/data/b1pu3ie.html#b1pu3m7) is set to false.
Another possibility is that the transaction type has been overridden to'auto' in policy.
Solution:
Use stored proceduresp_procxmodeto change the stored procedure's mode to 'unchained' or 'anymode' (preferred) or change the driver's parameteruse-manual-transactions(https://www.novell.com/documentation/idmdrivers/jdbc/data/b1pu3ie.html#b1pu3m7) to true or change the policy transaction type to 'manual'.
Error 7113: Stored procedure'x'may be run only in unchained transaction mode.
Effect:
Exceptioncom.sybase.jdbc2.jdbc.SybSQLExceptionwith error-code7713and an SQL state ofZZZZZis thrown. The description reads as follows: "Stored procedure 'x' may be run only in unchained transaction mode. The 'SET CHAINED OFF' command will cause the current session to use unchained transaction mode (wherexis the name of the stored procedure in question).”
Cause:
The stored procedure was created in or later altered to run in unchained mode and the driver is currently running in chained mode. The likely cause is that parameteruse-manual-transactions(https://www.novell.com/documentation/idmdrivers/jdbc/data/b1pu3ie.html#b1pu3m7) is set to true.

Another possibility is that the transaction type has been overridden to'manual' in policy.

Solution:
Use stored proceduresp_procxmodeto change the stored procedure's mode to 'chained' or 'anymode' (preferred) or change the driver's parameteruse-manual-transactions(https://www.novell.com/documentation/idmdrivers/jdbc/data/b1pu3ie.html#b1pu3m7) to false or change the policy transaction type to 'auto'.

NOTE: If you setuse-manual-transactionsto false, all transactions will consist of, at most, one statement.

Using Stored Procedure sp_proxmode

The preferred way to avoid errors 7112 and 7113 is to alter all stored procedures invoked directly or indirectly (via triggers, for example) by the driver to run in both chained and unchained mode. To alter a procedure, invoke thesp_procxmodewith two arguments: the procedure name and the mode.

The following is an example of how to invoke said procedure from the isql command line client:

sp_procxmodemy_procedure, anymode

GO

Of course, not all customers will be willing to alter stored procedure modes. Altering a procedure's mode may alter its runtime behavior which could alter the behavior of other applications that invoke the procedure.

Chained vs. Unchained Mode

Unchained mode is Sybase's native way of executing SQL. A second mode, chained mode, was later added to make the database compatible w/ SQL standards.

In summary:

chained = SQL-compatible mode

unchained = Sybase native mode

Sybase provides a third-party JDBC driver called jConnect. The default mode of jConnect is unchained. Whenever the methodConnection.setAutoCommit(boolean autoCommit):void(http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Connection.html) is invoked, jConnect switches modes. WhenConnection.setAutoCommit(true)is invoked, jConnect switches to unchained mode.
WhenConnection.setAutoCommit(false)is invoked, jConnect switches to chained mode.

In Summary:

Connection.setAutoCommit(true) = switch to unchained mode

Connection.setAutoCommit(false) = switch chained mode

The driver will invokeConnection.setAutoCommit(true)(i.e., enter unchained mode) if the parameteruse-manual-transactionsis set to false. This is the normal processing mode for SELECT statements and SQL embedded in policy where the transaction type is set to 'auto'
(https://www.novell.com/documentation/idmdrivers/jdbc/data/af899ky.html#af8bdjt). It's in this state that any chained stored procedures invoked directly or indirectly by the driver will yield the 7112 error.

The driver will invokeConnection.setAutoCommit(false)(i.e., enter chained mode) if the parameteruse-manual-transactionsis set to true. This is the normal processing mode for all statements except SELECT statements and SQL embedded in policy where the transaction type is set to'manual' (https://www.novell.com/documentation/idmdrivers/jdbc/data/af899ky.html#af8bdjt). It's in this state that any unchained stored procedures invoked directly or indirectly by the driver will yield the 7113 error.

Managing Transactions in Policy

Please consult the implementation guide, specifically section 5.4.4 Manual vs. Automatic Transactions

Useful Links

Transact-SQL User's Guide: Transaction modes and stored procedures

Transact-SQL User's Guide: Selecting the transaction mode and isolation level

http://manuals.sybase.com/onlinebooks/groupas/asg1250e/sqlug/@Generic__BookTextView/53713;pt=53001