Explicit CONNECT statements

Using CONNECT statements provides application portability across all DB2® clients and requires the application to manage connections.

With the CONNECT statement, an application program explicitly connects to each server. You must bind the DBRMs for the SQL statements that are to execute at the server to packages that reside at that server.

The application connects to each server based on the location name in the CONNECT statement. You can explicitly specify a location name, or you can specify a location name in a host variable. Issuing the CONNECT statement changes the special register CURRENT SERVER to show the location of the new server.

Begin general-use programming interface information.

Example: Assume that an application includes a program loop that reads a location name, connects to the location, and executes an INSERT statement. The application inserts a new location name into a host variable, LOCATION_NAME, and executes the following statements:

EXEC SQL
   CONNECT TO :LOCATION_NAME;
EXEC SQL
   INSERT INTO IDP101.PROJ VALUES (:PROJNO, :PROJNAME, :DEPTNO, 
   :RESPEMP, :MAJPROJ);

The host variables match the declaration for the PROJ table.

End general-use programming interface information.

DB2 guarantees the consistency of data across a distributed transaction. To keep the data consistent at all locations, the application commits the work only after the program loop executes for all locations. Either every location commits the INSERT, or, if a failure prevents any location from inserting, all other locations roll back the INSERT.