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.
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:
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.
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) . . .
related tasks
Coding SQL statements
related references
Compiler and runtime environment variables