When using Enterprise Server how do I connect to two DB2 databases?

The first approach is to use XA.  To do this you first need to define an XA resource manager for each database.  Unfortunatley you need to code the program(s) to specify to which database you're SQL statements refer.  You do this using EXEC SQL CONNECT RESET and then EXEC SQL CONNECT.


      $SET DB2(DB=SAMPLE,bind,validate=run,noaccess,connect=2)
       program-id. DEMO1.
       exec sql include sqlca end-exec.

       01 ws-name pic x(30).

       01 ws-fname pic x(30).

       01 usr pic x(30) value "userid".
       01 pwd pic x(30) value "passwd".

       procedure division.

      * following select is from the SAMPLE database and the connection
      * has already been done by the XA connector specified
      * in the DSN SYS in calling JCL.
       exec sql
         select firstnme
         into :ws-fname
         from employee
         where empno = '000010'
       end-exec

       display "first name from employee is " ws-fname

      * reset the first connection   
       exec sql
         connect reset
       end-exec

      * now connect to a TEST database
       exec sql
         connect to test
         user :usr
         using :pwd
       end-exec

       exec sql
         select col1
         into :ws-name
         from table1
         where col2 = 'x'
       end-exec

       display "data from table1 is " ws-name

       goback.

Using the DB2(BIND) directive creates a .bnd file.  You must then manually bind the program to both databases.

Because you have XA resource managers defined for both connections the application does not need to do any COMMIT/ROLLBACK processing as this will be handled by Enterprise Server & XA.

Submitted JCL was as follows:

//TESTDB2 JOB ,' ',MSGCLASS=X,
// CLASS=A
//*-----------------------------------------------------------------*
//* TEST DB2 XA CONNECTION *
//*-----------------------------------------------------------------*
//STEP05 EXEC PGM=IKJEFT01
//IEFRDER DD DUMMY
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYS(DB21)
RUN PROGRAM(DEMO1)
END
/*

In the above JCL, DSN SYS(DB21) refers to the XA resource name of the first database used in the DEMO1 program.  This means that the program does not have to explicitely reset the connection or issue a CONNECT statement for the first accesses to the SAMPLE database, however these could also be coded into the program if desired.

As an alternative to using the container managed approach you can also choose not to use XA and instead let your application manage the database connections.  Using this approach you can code both CONNECT statements at the start of the application then use SET CONNECTION SAMPLE or a SET CONNECTION TEST to change the database used for any subsequent SQL statements until the next SET CONNETION statement is issued.

      $SET DB2(DB=SAMPLE,bind,validate=run,noaccess,connect=2)
       program-id. DEMO2.
       exec sql include sqlca end-exec.

       01 ws-name pic x(30).
       01 ws-fname pic x(30).

       01 usr pic x(30) value "userid".
       01 pwd pic x(30) value "passwd".

       procedure division.

      * connect to a SAMPLE database
       exec sql
         CONNECT TO sample
         USER :usr
         USING :pwd
       end-exec

      * now connect to a TEST database
       exec sql
         CONNECT TO test
         USER :usr
         USING :pwd
       end-exec

       exec sql
         SET CONNECTION sample
       end-exec

       exec sql
         SELECT firstnme
         INTO :ws-fname
         FROM employee
         WHERE empno = '000010'
       end-exec

       display "first name from employee is " ws-fname

      * reset the first connection   
       exec sql
         SET CONNECTION test
       end-exec

       exec sql
         SELECT col1
         INTO :ws-name
         FROM table1
         WHERE col2 = 'x'
       end-exec

       display "data from table1 is " ws-name

       goback.

The JCL for this test was slightly different as IKJEFT01 is not needed so you can code EXEC PGM=DEMO2:

//TESTDB2 JOB ,' ',MSGCLASS=X,
// CLASS=A
//*-----------------------------------------------------------------*
//* TEST DB2 XA CONNECTION *
//*-----------------------------------------------------------------*
//STEP05 EXEC PGM=DEMO2
//SYSPRINT DD SYSOUT=*
/*

Because the application is now handling the connections it also needs to handle COMMIT/ROLLBACK processing if any table updates have been made.