Previous Topic Next topic Print topic


Mimicking Mainframe DB2 Transaction Support for COBOL Stored Procedures

When a mainframe DB2 application calls a stored procedure flow of any form, a sequence of serial chained transactions is executed. For example, consider the following sequence executed from a COBOL client application:
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.

Note: For more information on setting savepoints and implicit_transactions, see the topics OPTION, SET AUTOCOMMIT, and AUTOCOMMIT.

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)
Previous Topic Next topic Print topic