Declaring Multiple Databases
Go Up to Declaring a Database
A SQL program, but not a DSQL program, can access multiple databases at the same time. In multi-database programs, database handles are required. A handle is used to:
- Reference individual databases in a multi-database transaction.
- Qualify table names.
- Specify databases to open in
CONNECTstatements. - Indicate databases to close with
DISCONNECT,COMMIT RELEASE, andROLLBACK RELEASE.
DSQL programs can access only a single database at a time, so database handle use is restricted to connecting to and disconnecting from a database.
In multi-database programs, each database must be declared in a separate SET DATABASE statement. For example, the following code contains two SET DATABASE statements:
. . .
EXEC SQL
SET DATABASE DB2 = 'employee2.ib';
EXEC SQL
SET DATABASE DB1 = 'employee.ib';
. . .
Using Handles for Table Names
When the same table name occurs in more than one simultaneously accessed database, a database handle must be used to differentiate one table name from another. The database handle is used as a prefix to table names, and takes the form handle.table.
For example, in the following code, the database handles, TEST and EMP, are used to distinguish between two tables, each named EMPLOYEE:
. . .
EXEC SQL
DECLARE IDMATCH CURSOR FOR
SELECT TESTNO INTO :matchid FROM TEST.EMPLOYEE
WHERE TESTNO > 100;
EXEC SQL
DECLARE EIDMATCH CURSOR FOR
SELECT EMPNO INTO :empid FROM EMP.EMPLOYEE
WHERE EMPNO = :matchid;
. . .
This use of database handles applies only to embedded SQL applications. DSQL applications cannot access multiple databases simultaneously.
Using Handles with Operations
In multi-database programs, database handles must be specified in CONNECT statements to identify which databases among several to open and prepare for use in subsequent transactions.
Database handles can also be used with DISCONNECT, COMMIT RELEASE, and ROLLBACK RELEASE to specify a subset of open databases to close.
To open and prepare a database with CONNECT, see Opening a Database. To close a database with DISCONNECT, COMMIT RELEASE, or ROLLBACK RELEASE, see Closing a Database. To learn more about using database handles in transactions, see Accessing an Open Database.