Managing distributed unit of work connections

 

You can use the CONNECT, SET CONNECTION, DISCONNECT, and RELEASE statements to manage connections in the distributed unit of work (DUW) environment.

A distributed unit of work CONNECT is run when the program is precompiled using RDBCNNMTH(*DUW), which is the default. This form of the CONNECT statement does not disconnect existing connections but instead places the previous connection in the dormant state. The relational database specified on the CONNECT statement becomes the current connection. The CONNECT statement can only be used to start new connections; if you want to switch between existing connections, the SET CONNECTION statement must be used. Because connections use system resources, connections should be ended when they are no longer needed. The RELEASE or DISCONNECT statement can be used to end connections. The RELEASE statement must be followed by a successful commit in order for the connections to end.

The following is an example of a C program running in a DUW environment that uses commitment control. Figure 1. Example of distributed unit of work program

    ....
     EXEC SQL WHENEVER SQLERROR GO TO done;
     EXEC SQL WHENEVER NOT FOUND GO TO done;
    ....
     EXEC SQL        DECLARE C1 CURSOR WITH HOLD FOR
           SELECT PARTNO, PRICE                 FROM PARTS                 WHERE SITES_UPDATED = 'N'
                FOR UPDATE OF SITES_UPDATED;
    /*    Connect to the systems      */
    EXEC SQL CONNECT TO LOCALSYS;
    EXEC SQL CONNECT TO SYSB;
    EXEC SQL CONNECT TO SYSC;
    /* Make the local system the current connection */
    EXEC SQL SET CONNECTION LOCALSYS;
    /* Open the cursor */
    EXEC SQL OPEN C1;

    while (SQLCODE==0)
       {
        /* Fetch the first row */
        EXEC SQL FETCH C1 INTO :partnumber,:price;
        /* Update the row which indicates that the updates have been            propagated to the other sites */
        EXEC SQL UPDATE PARTS SET SITES_UPDATED='Y'
                     WHERE CURRENT OF C1;
        /* Check if the part data is on SYSB */
        if ((partnumber > 10) && (partnumber < 100))
           {
            /* Make SYSB the current connection and update the price */
            EXEC SQL SET CONNECTION SYSB;
            EXEC SQL UPDATE PARTS                          SET PRICE=:price                          WHERE PARTNO=:partnumber;
            }

         /* Check if the part data is on SYSC */
         if ((partnumber > 50) && (partnumber < 200))
            {
             /* Make SYSC the current connection and update the price */
             EXEC SQL SET CONNECTION SYSC;
             EXEC SQL UPDATE PARTS                           SET PRICE=:price                           WHERE PARTNO=:partnumber;
            }
         /* Commit the changes made at all 3 sites */
         EXEC SQL COMMIT;
         /* Set the current connection to local so the next row             can be fetched */
         EXEC SQL SET CONNECTION LOCALSYS;
        }
     done:

     EXEC SQL WHENEVER SQLERROR CONTINUE;
     /* Release the connections that are no longer being used */
     EXEC SQL RELEASE SYSB;
     EXEC SQL RELEASE SYSC;
     /* Close the cursor */
     EXEC SQL CLOSE C1;
     /* Do another commit which will end the released connections.
        The local connection is still active because it was not         released. */
     EXEC SQL COMMIT;
    …

In this program, there are three application servers active: LOCALSYS which the local system, and two remote systems, SYSB and SYSC. SYSB and SYSC also support distributed unit of work and two-phase commit.

Initially all connections are made active by using the CONNECT statement for each of the application servers involved in the transaction. When using DUW, a CONNECT statement does not disconnect the previous connection, but instead places the previous connection in the dormant state. After all the application servers have been connected, the local connection is made the current connection using the SET CONNECTION statement. The cursor is then opened and the first row of data fetched. It is then determined at which application servers the data needs to be updated. If SYSB needs to be updated, then SYSB is made the current connection using the SET CONNECTION statement and the update is run. The same is done for SYSC. The changes are then committed.

Because two-phase commit is being used, it is guaranteed that the changes are committed at the local system and the two remote systems. Because the cursor was declared WITH HOLD, it remains open after the commit. The current connection is then changed to the local system so that the next row of data can be fetched. This set of fetches, updates, and commits is repeated until all the data has been processed.

After all the data has been fetched, the connections for both remote systems are released. They cannot be disconnected because they use protected connections. After the connections are released, a commit is issued to end the connections. The local system is still connected and continues processing.

 

Parent topic:

Distributed unit of work