Using Db2 files and SQL statements in the same program

To use EXEC SQL statements and Db2® file I/O in the same program, there are some important facts that you must know, as explained below.

About this task

  • Both facilities (EXEC SQL statements and Db2 file I/O) use the same Db2 connection.
  • Each COBOL I/O update operation that uses the Db2 file system is committed to the database immediately.
  • If an existing Db2 connection is available, the Db2 file system uses that connection.

    If a connection is not available, the Db2 file system establishes a connection to the database that is referenced by the value of the DB2DBDFT environment variable.

EXEC SQL statements and Db2 file I/O can use the same database, or, if you explicitly control the connection, different databases.

Using the same database:

Using the same database for EXEC SQL statements and Db2 file I/O in the same program is simpler than using different databases. But you must handle this configuration carefully nonetheless:

  • To avoid anomalies, do not rely on the Db2 file system's use of an existing connection. To ensure consistent results regardless of which type of database access (Db2 file I/O or EXEC SQL) occurs first, set environment variable DB2DBDFT to the same database that the EXEC SQL statements use.
  • Db2 file update operations commit all pending work for the database. Therefore roll back or commit any pending EXEC SQL updates before initiating any Db2 file I/O operations.

    Although opening a Db2 file for input and reading the file does not cause a database commit, it is recommended that you not rely on this behavior.

Using different databases:

To use different databases for EXEC SQL statements and for Db2 file I/O in the same program, you must explicitly control the database connections as shown in the examples below.

Suppose that you want to use EXEC SQL statements with database db2pli, and do Db2 file I/O using database db2cob by setting environment variable DB2DBDFT:
export DB2DBDFT=db2cob
In the example below, doing the sequence of steps shown (with angle brackets indicating pseudocode) will not use the intended databases correctly, as the inline comments explain:

<DB2 file I/O>                        *> Uses database db2cob (from DB2DBDFT)
EXEC SQL CONNECT TO db2pli END-EXEC   *> Switches to database db2pli
<Other EXEC SQL operations>           *> Use database db2pli
<DB2 file I/O>                        *> Uses the existing connection--and
. . .                                 *>   thus database db2pli--incorrectly!

To access the intended databases, first disconnect from the database used by the EXEC SQL statements before doing any Db2 file I/O operations. Then either rely on the value in environment variable DB2DBDFT or explicitly connect to the database that you want to use for Db2 file I/O.

The following sequence of steps illustrates reliance on DB2DBDFT to correctly make the intended connections:


<DB2 file I/O>                        *> Uses database db2cob (from DB2DBDFT)
EXEC SQL CONNECT TO db2pli END-EXEC   *> Switches to database db2pli
<Other EXEC SQL operations>           *> Use database db2pli
* Commit or roll back pending operations
* here, because the following statement
* unconditionally commits pending work:
EXEC SQL CONNECT RESET END-EXEC       *> Disconnect from database db2pli
<DB2 file I/O>                        *> Uses database db2cob (from DB2DBDFT)
. . .

Related tasks  
Coding SQL statements  

Related references  
Compiler and runtime environment variables