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 theEXEC 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.
EXEC
SQL
statements with database db2pli
, and
do Db2 file I/O using database db2cob
by
setting environment variable DB2DBDFT: export DB2DBDFT=db2cob
<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)
. . .