Mimicking Mainframe DB2 Transaction Support for COBOL Stored Procedures

Insert 1
Call COBOL stored procedure A
     Insert 2
     Call COBOL routine B
          Commit
          Insert 3
     Rollback
     Insert 4
Insert 5
Commit

DB2 inserts records 1,2,4 and 5.

However, with SQL Server, when an application calls a stored procedure flow of any kind, it forms a nested transaction. The outermost transaction of this nested transaction ultimately determines whether to commit or rollback 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 5 records are inserted; if the COBOL client application ends with a ROLLBACK instead, none of the 5 records are inserted.

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

With SQL Server, you can use a combination of implicit_transactions and savepoints. When implicit_transactions are set to OFF or to ON, this positions the lead transaction that commits the workload to SQL Server and releases locks. Savepoints ensure that serially chained COMMIT and ROLLBACK transactions behave as you intend them to.

HCOSS offers three solutions to mimic mainframe DB2 transaction behavior with COBOL stored procedures. The first two begin with implicit_transactions set to OFF, and the third begins with implicit_transactions set to ON. Here, we provide a description of each solution and a summary of the advantages and disadvantages for each.

Solutions 1 and 2: Caller - Implicit Transactions Off (auto-commit mode)

You can set implicit_transactions to OFF either by building the application with SQL(AUTOCOMMIT) or by executing an EXEC SQL SET AUTOCOMMIT ON END-EXEC statement before making the call to a stored procedure. This is auto-commit mode. You can turn auto-commit mode off by executing an EXEC SQL SET AUTOCOMMIT ON END-EXEC statement after the call to the stored procedure.

HCOSS does the following:

	ON ENTRY	EXEC SQL COMMIT	EXEC SQL ROLLBACK	ON EXIT (return)
Lead (called) Stored Procedure	start transaction set savepoint	commit reset savepoint	roll back to savepoint commit reset savepoint	Option 1: roll back to savepoint commit Option 2: Use SQL(OPTION=SPCOMMITONRETURN)
Nested Stored Proceduress		reset savepoint	roll back to savepoint
Called COBOL Routines		commit reset savepoint	roll back to savepoint commit reset savepoint

Solution 1: Keep nested Stored Procedures

Advantages	Disadvantages
Minimal code changes: No changes to stored procedure code (SQLCLRTRANS compile option) Application code might be required to set implicit_transactions to OFF before calling the stored procedure, and back to ON after calling the stored procedure Locks are freed by the lead stored procedure when it commits or rolls back workload (concurrency is better than Solution 3)	No work from the client can be committed or rolled back by the stored procedure (integrity is the same as Solution 2, but worse than Solution 3) No work from the stored procedure can be committed or rolled back by the client (integrity is the same as same as Solution 2, but worse than Solution 3) Locks are held by nested stored procedures (concurrency is worse than Solution 2)

Solution 2: Replace nested Stored Procedures with Called COBOL routines

Advantage

Disadvantages

Locks are freed by the lead stored procedure and called COBOL routines when they commit or rollback (concurrency is better than both Solution 1 and Solution 3)

Code remediation: Change nested stored procedure calls to COBOL calls (SQLCLRTRANS compile option) Application code might be required to set implicit_transactions to OFF before calling the stored procedure, and back to ON after calling the stored procedure No work from the client can be committed or rolled back by the stored procedure (integrity is the same as Solution 2, but worse than Solution 3) No work from the stored procedure can be committed or rolled back by the client (integrity is the same as same as Solution 2, but worse than Solution 3)

Solution 3: Caller - Implicit Transactions On

Implicit_transaction is set to ON before the call to a stored procedure. Auto-commit mode is turned off. In this solution, nested Stored Procedures remain intact.

HCOSS does the following:

	ON ENTRY	EXEC SQL COMMIT	EXEC SQL ROLLBACK	ON EXIT (return)
Lead (called) SP		reset savepoint	roll back to savepoint
Nested SPs		reset savepoint	roll back to savepoint

Advantages

Disadvantage

Minimal code changes for non-COBOL applications: No changes to stored procedure code (compile with SQL(OPTION=SQLCLRTRANS)) Application code must set a savepoint before the stored procedure call Application code must issue a commit or rollback after the stored procedure call If additional SP calls are made, the pplication code must set a savepoint Minimal code changes for COBOL applications: Compile with SQL(OPTION=SQLCLRTRANS) Application code must issue a commit or rollback after the stored procedure call Because all work is committed and rolled back by the client, work cannot be lost (integrity is better than both Solution 1 and Solution 2)

Locks acquired by stored procedures are not released until the client commits or rolls back (concurrency is worse than both Solution 1 and Solution 2)