Using CONNECTION=GLOBAL or CONNECTION=SHARED can result in incorrect data when accessing DB2 tables with SAS® on z/OS


When the LIBNAME option CONNECTION=SHARED or CONNECTION=GLOBAL is used when accessing DB2 with SAS on z/OS, incorrect results can occur. This occurs when one cursor is open after having read in some data from one DB2 table and a COMMIT is issued for something else. Since both of these cursors were sharing the connection, the COMMIT messes up the cursor that was used for the read.

The following code illustrates one case where rows are not inserted into the new table causing incorrect results:

LIBNAME test db2;

PROC SQL;
title 'output from the 1st DB2 table';
select * from test.class;

create table test.mynewclass as
select * from test.class;

title 'output from the 2nd DB2 table';
title2 'not all of the input rows were inserted';

select * from test.mynewclass;
quit;
 

In this example, right before the table MYNEWCLASS is created, a fetch is executed and a cursor is opened to retrieve the rows from the table CLASS that need to be inserted into the new table. In the meantime, the table MYNEWCLASS is created and a COMMIT is issued. This messes with the data that was present in the cursor so that when the insert is done into MYNEWCLASS not all of the rows that should have been inserted are actually inserted.

This behavior is warned about in the See "CONNECTION= LIBNAME option" in the SAS/ACCESS® 9.2 for Relational Databases: Reference, Fourth Edition.

Use this option with caution. When you use a single SHARED connection for multiple table opens, a commit or rollback that is performed on one table that is being updated also applies to all other tables that are opened for update. Even if you open a table only for READ, its READ cursor might be resynchronized as a result of this commit or rollback. If the cursor is resynchronized, there is no guarantee that the new solution table will match the original solution table that was being read.