Transaction Support for SQL CLR Stored Procedures

The Problem: Serial Transaction Chaining is Not Supported in SQL CLR Environments

In non-SQL CLR environments, such as z/OS DB2, you can typically chain transactions from the calling application through SQL activity in a COBOL stored procedure. For example, consider the following sequence executed from a COBOL client application:

EXEC SQL Insert 1 END-EXEC
EXEC SQL Call COBOL stored procedure A END-EXEC
     EXEC SQL Insert 2 END-EXEC
     Cobol call to COBOL routine B
          EXEC SQL Commit END-EXEC
          EXEC SQL Insert 3 END-EXEC
     EXEC SQL Rollback END-EXEC
     EXEC SQL Insert 4 END-EXEC
EXEC SQL Insert 5 END-EXEC
EXEC SQL Commit END-EXEC

The DBMS typically inserts records 1, 2, 4, and 5.

With SQL Server using SQL CLR, SQL Server forms a nested rather than a chained transaction when an application calls a stored procedure flow of any kind. The outermost transaction of this nested transaction ultimately determines whether to commit or roll back the work of all the transactions contained therein, regardless of what those transactions did. In the above example, because the COBOL client application starts the nested transaction and ends it with a COMMIT, all five records are inserted; if the COBOL client application ends with a ROLLBACK instead, as is often the case, none of the five records are inserted.

This behavior is not acceptable if you want your migrated SQL Server application to mimic serial transaction chaining.

Although OpenESQL cannot always commit or roll back chained transactions precisely like z/OS DB2, it does provide the SQLCLRTRANS compiler directive option to address this issue. SQLCLRTRANS emulates the z/OS DB2 behavior, and uses a specific SQL Server save point named MF__SAVEPOINT.

Possible Solutions: Using OpenESQL with SQLCLRTRANS

Solution 1: Complete Serial Transaction Chaining

Consider using this solution when both the calling application and the called stored procedure(s) each execute a workload that must be either committed or rolled back. This means that the calling application starts a transaction, and all DML is done using the same sequence as shown in the code example above.

Note: If the calling application itself is not used for transaction management, please see Solutions 2 and 3 below as alternatives.

Important: With this solution, the calling application must execute with autocommit mode turned off for the duration of serial transaction chain(s) as explained in the Coding Requirements section below.

In this solution, when you compile your COBOL code using the SQLCLRTRANS compiler directive option, OpenESQL automatically makes the following code adjustments:

Table 1. Solution 1 – SQLCLRTRANS
	ON ENTRY¹	EXEC SQL COMMIT	EXEC SQL ROLLBACK	ON EXIT²
Client application (COBOL only)	start transaction set savepoint, MF_SAVEPOINT	commit set savepoint, MF_SAVEPOINT	roll back set savepoint, MF_SAVEPOINT	roll back disconnect
Lead (called) stored procedure		reset MF_SAVEPOINT	roll back to MF_SAVEPOINT
Nested stored procedures		reset MF_SAVEPOINT	roll back to MF_SAVEPOINT
¹ Or, the connection is opened by the client application
² Return, or the connection is closed by the client application

Solution 1 Advantages/Disadvantages

The advantages and disadvantages of using this solution are:

Advantages

No changes required for stored procedure code
Minimal (if any) code changes for COBOL calling applications
Nested stored procedures remain intact
Strongest data integrity solution since no work done by an application or called stored procedures can be lost

Disadvantages

Locks are held in SQL Server until a commit or rollback is actually performed by the application; this lowers data concurrency because SQL Server locks are held longer in comparison to z/OS DB2 and in comparison to solutions 2 and 3
Code changes to enable serial transaction chaining are required for non-COBOL calling programs because you cannot use OpenESQL and its SQLCLRTRANS directive to automatically insert the code into non-COBOL calling programs

Coding Requirements

The possible coding requirements for this solution vary depending on whether the application calling SQL CLR COBOL stored procedures is written in COBOL, or if it is written in another programming language.

COBOL application calling COBOL SQL CLR stored procedures

If the application that calls a COBOL SQL CLR stored procedure is COBOL itself, make the following checks/changes to your COBOL calling application code before compiling with the SQLCLRTRANS directive:

Verify/Change autocommit status

Default COBOL OpenESQL applications run with autocommit off. If your application does not explicitly turn autocommit on via an EXEC SQL statement and you do not have a requirement to compile using the AUTOCOMMIT directive, skip to the Insert COMMIT or ROLLBACK section.

However, if you routinely compile your applications using the AUTOCOMMIT directive, or if explicitly turn autocommit on in your COBOL application, consider the following:

AUTOCOMMIT directive required

If you have need to compile your COBOL code using the AUTOCOMMIT SQL compiler directive option, this automatically turns autocommit mode on. The easiest way to avoid this is to compile without the AUTOCOMMIT directive. However, if that is not an option, edit your application code as follows:

To temporarily turn off autocommit mode, insert an EXEC SQL SET AUTOCOMMIT OFF END-EXEC statement where appropriate in your application before the stored procedure call
To turn autocommit mode back on, insert an EXEC SQL SET AUTOCOMMIT ON END-EXEC statement after committing or rolling back the serial transaction chain (see Insert COMMIT or ROLLBACK below for details)

COBOL code includes EXEC SQL SET AUTOCOMMIT ON END-EXEC

Even when you compile without the AUTOCOMMIT directive, parts of your application could run in autocommit mode if the code includes one or more EXEC SQL SET AUTOCOMMIT ON END-EXEC statements. We recommend that you scan your code for this statement, and if found, edit your application to turn autocommit mode off before each stored procedure call, and turn it back on afterwards. For instructions, see the bulleted list in AUTOCOMMIT directive required section above.

Insert COMMIT or ROLLBACK

After each COBOL stored procedure is called, issue either an EXEC SQL COMMIT END-EXEC or an EXEC SQL ROLLBACK END-EXEC statement to commit or roll back the work of the serial transaction chain and free SQL Server locks.

Important: Strictly speaking, if always running with autocommit off, you are not required to issue a COMMIT or ROLLBACK after a stored procedure call. Instead, you can defer it as deemed appropriate in the calling application; however, be careful that a delay in issuing a COMMIT or ROLLBACK does not delay the completion of the serial transaction chain. Such a delay creates SQL Server data concurrency issues.

After you have made necessary code adjustments, if required, compile your COBOL application and all COBOL stored procedure code with the OpenESQL SQLCLRTRANS compiler directive option.

Note: In applications that call multiple stored procedures, you must ensure that autocommit is turned off before each stored procedure call, and turned on again after each call, if required.

Non-COBOL Application calling COBOL SQL CLR stored procedures

Important: While you can call SQL CLR stored procedures from a non-COBOL application, you must manually adjust your code to emulate the code produced for COBOL clients compiled with SQLCLRTRANS. This section walks you through the code adjustment process. Also, see the Solution 1 – SQLCLRTRANS table above for details on the effects of compiling COBOL with SQLCLRTRANS.

For non-COBOL applications that call stored procedures, make the following checks/changes to your calling application code before compiling it:

Coding Requirements

The possible coding requirements for this solution are as follows:

Verify/Change autocommit status

Just as with a COBOL application, using this solution you cannot call a stored procedure from a non-COBOL application when in autocommit mode. Check your calling application code and make any necessary changes to ensure that autocommit mode is turned off before issuing a stored procedure call, and turned back on only after issuing a COMMIT or ROLLBACK and, for applications that call multiple stored procedures, also setting a save point. For more information, see the Verify/Change autocommit status section under COBOL Application calling SQL CLR stored procedures above, and the Set MF__SAVEPOINT section below.

Verify the start of a transaction

Ensure that your calling application starts a transaction with autocommit turned off. To start a transaction, use these TSQL statements:

SET IMPLICIT_TRANSACTIONS ON
BEGIN TRANSACTION

Tip: For some non-COBOL applications, you might be able to use API calls to start a transaction with autocommit turned off.

Set the transaction save point

Non-COBOL applications must set a save point to the default OpenESQL save point name, MF__SAVEPOINT. To do this, insert the TSQL statement SAVE TRANSACTION MF__SAVEPOINT after the transaction is started.

Commit or roll back

After each COBOL stored procedure is called, issue the appropriate API call to either commit or roll back the work of the serial transaction chain.

Set MF_SAVEPOINT

If your non-COBOL calling application makes multiple calls to COBOL stored procedures, you must insert the TSQL statement SAVE TRANSACTION MF__SAVEPOINT after the call that either commits or rolls back the work of a serial transaction chain. This statement ensures that transactions executed by subsequent calls to COBOL stored procedures are handled appropriately.

Compile all non-COBOL calling applications as required. Then, compile all COBOL stored procedure code with the OpenESQL SQLCLRTRANS compiler directive option.

Solution 2: Stored Procedure Serial Transaction Chaining Using Nested Stored Procedures

Consider using this solution when the calling application does not use serial transaction chaining, and the called stored procedure calls other stored procedures, which requires serialized transaction chaining within its invocation. In this solution, your lead COBOL stored procedure manages all serial transaction chaining for your application. As an example, consider this pseudo-code:

EXEC SQL Call COBOL stored procedure A END-EXEC
     EXEC SQL Insert 1 END-EXEC
     EXEC SQL CALL COBOL stored procedure B END-EXEC
          EXEC SQL Commit END-EXEC
          EXEC SQL Insert 2 END-EXEC
     EXEC SQL Rollback END-EXEC
     EXEC SQL Insert 3 END-EXEC
     EXEC SQL Commit END-EXEC

Also consider that this solution is best suited for applications for which concurrency to SQL Server data is important, as is retaining nested stored procedures rather than converting them to COBOL routines.

In this solution, when you compile your COBOL code using the SQLCLRTRANS compiler directive option and optionally the SPCOMMITONRETURN directive as well, OpenESQL automatically makes the following code adjustments:

Table 2. Solution 2 – SQLCLRTRANS
	ON ENTRY	EXEC SQL COMMIT	EXEC SQL ROLLBACK	ON EXIT (return)
Lead (called) Stored Procedure	start transaction set savepoint, MF_SAVEPOINT	commit reset MF_SAVEPOINT	roll back to MF_SAVEPOINT commit reset MF_SAVEPOINT	roll back to MF_SAVEPOINT Or commit¹
Nested Stored Procedures		reset MF_SAVEPOINT	roll back to MF_SAVEPOINT
Called COBOL Routines		commit reset MF_SAVEPOINT	roll back to MF_SAVEPOINT commit reset MF_SAVEPOINT
¹ If you specified SQL(SPCOMMITONRETURN) when compiling, the commit is executed when the stored procedure returns to the calling program. If you did not specify SQL(SPCOMMITONRETURN), a rollback is performed to the last save point set by OpenESQL.

Solution 2 Advantages/Disadvantages

The advantages and disadvantages of using this solution are:

Advantages

No changes required for stored procedure code
Minimal code changes for COBOL or non-COBOL calling applications
Nested stored procedures remain intact
Locks are freed by the lead stored procedure when it commits or rolls back the stored procedure workload
Data concurrency is better than Solution 1

Disadvantages

No work from the client can be committed or rolled back by the stored procedure
No work from the stored procedure can be committed or rolled back by the client
Locks are held by nested stored procedures
Integrity is not as good as Solution 1

Coding Requirements

COBOL application calling COBOL SQL CLR stored procedures

If the application that calls a COBOL SQL CLR stored procedure is COBOL itself, verify that your application is in autocommit mode when a stored procedure is called. You can turn on autocommit mode using either of these methods:

Compile using the AUTOCOMMIT directive

If you compile your COBOL code using the AUTOCOMMIT SQL compiler directive option, this automatically turns autocommit mode on.

Include EXEC SQL SET AUTOCOMMIT ON END-EXEC in your COBOL

If you choose to compile your COBOL code without using the AUTOCOMMIT directive, you must edit the code to turn on autocommit mode before issuing each call to a SQL CLR COBOL stored procedure, and turn autocommit off following each stored procedure call:

To temporarily turn on autocommit mode, insert an EXEC SQL SET AUTOCOMMIT ON END-EXEC statement just before the stored procedure call.
To turn autocommit mode back off, insert an EXEC SQL SET AUTOCOMMIT OFF END-EXEC statement after the stored procedure call.

Non-COBOL Application calling COBOL SQL CLR stored procedures

In this solution, non-COBOL applications that call SQL CLR COBOL stored procedures must be in autocommit mode before issuing the call to a SQL CLR COBOL stored procedure. You can do this by inserting the following TSQL statement before the call:

SET IMPLICIT_TRANSACTIONS ON

To optionally turn off autocommit mode after issuing the call, insert the following TSQL statement:

SET IMPLICIT_TRANSACTIONS OFF

If your code issues multiple calls to SQL CLR COBOL stored procedures, be sure that autocommit mode is turned on before each call is issued.

Tip: For some non-COBOL applications, you might be able to use API calls to turn autocommit mode on and off.

After you have made all necessary code adjustments, compile your application and also compile all COBOL stored procedure code with the OpenESQL SQLCLRTRANS compiler directive option, and optionally also with the SPCOMMITONRETURN directive.

Solution 3: Stored Procedure Serial Transaction Chaining Using Called COBOL Routines

Consider using this solution when the calling application does not use serial transaction chaining, and the called stored procedure calls COBOL routines, which requires serialized transaction chaining within its invocation. As an example, consider this pseudo-code:

EXEC SQL Call COBOL stored procedure A END-EXEC
     EXEC SQL Insert 1 END-EXEC
     CALL COBOL routine B END-EXEC
          EXEC SQL Commit END-EXEC
          EXEC SQL Insert 2 END-EXEC
     EXEC SQL Rollback END-EXEC
     EXEC SQL Insert 3 END-EXEC
     EXEC SQL Commit END-EXEC

Also consider that this solution is best suited for applications for which concurrency to SQL Server data is most important, and your lead COBOL stored procedure can manage all serial transaction chaining for your application.

Table 3. Solution 3 – SQLCLRTRANS
	ON ENTRY	EXEC SQL COMMIT	EXEC SQL ROLLBACK	ON EXIT (return)
Lead (called) Stored Procedure	start transaction set savepoint, MF_SAVEPOINT	commit reset MF_SAVEPOINT	roll back to MF_SAVEPOINT commit reset MF_SAVEPOINT	roll back to MF_SAVEPOINT Or commit¹
Nested Stored Procedures		reset MF_SAVEPOINT	roll back to MF_SAVEPOINT
Called COBOL Routines		commit reset MF_SAVEPOINT	roll back to MF_SAVEPOINT commit reset MF_SAVEPOINT
¹ If you specified SQL(SPCOMMITONRETURN) when compiling, the commit is executed when the stored procedure returns to the calling program. If you did not specify SQL(SPCOMMITONRETURN), a rollback is performed to the last save point set by OpenESQL.

Solution 3 Advantages/Disadvantages

The advantages and disadvantages of using this solution are:

Advantages

No changes required to stored procedure code
Minimal code changes for non-COBOL calling applications
Locks are freed by the lead stored procedure and called COBOL routines when they commit or roll back
Data concurrency with SQL Server is better than Solutions 1 and 2

Disadvantages

No work from the client can be committed or rolled back by the stored procedure
No work from the stored procedure can be committed or rolled back by the client
Nested COBOL stored procedures must be converted to COBOL routines
Currency is better than Solution 1 and Solution 2
Integrity is not as good as Solution 1

Coding Requirements

COBOL application calling COBOL SQL CLR stored procedures

Compile using the AUTOCOMMIT directive

If you compile your COBOL code using the AUTOCOMMIT SQL compiler directive option, this automatically turns autocommit mode on.

Include EXEC SQL SET AUTOCOMMIT ON END-EXEC in your COBOL

To temporarily turn on autocommit mode, insert an EXEC SQL SET AUTOCOMMIT ON END-EXEC statement just before the stored procedure call.
To turn autocommit mode back off, insert an EXEC SQL SET AUTOCOMMIT OFF END-EXEC statement after the stored procedure call.

After you have made all necessary code adjustments, compile your COBOL application and all COBOL stored procedure code with the OpenESQL SQLCLRTRANS compiler directive option, and optionally also with the SPCOMMITONRETURN directive, which automatically commits the work done by a stored procedure when it returns to the calling application. If SPCOMMITONRETURN is not specified, OpenESQL rolls back to the last commit done on behalf of the stored procedure.

Non-COBOL Application calling COBOL SQL CLR stored procedures

In this solution, non-COBOL applications that call SQL CLR COBOL stored procedures must be in autocommit mode before issuing a call to a SQL CLR COBOL stored procedure. You can do this by inserting the following TSQL statement before each call:

SET IMPLICIT_TRANSACTIONS ON

To optionally turn off autocommit mode after issuing a call, insert the following TSQL statement:

SET IMPLICIT_TRANSACTIONS OFF

If your code issues multiple calls to SQL CLR COBOL stored procedures, be sure that autocommit mode is turned on before each call is issued.

After you have made all necessary code adjustments, compile all of your COBOL stored procedure code with the OpenESQL SQLCLRTRANS compiler directive option, and optionally also with the SPCOMMITONRETURN directive. SPCOMMITONRETURN automatically commits the work done by a stored procedure when it returns to the calling application. If SPCOMMITONRETURN is not specified, OpenESQL rolls back to the last commit done on behalf of the stored procedure.