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.

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:

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