148 lines
8.5 KiB
HTML
148 lines
8.5 KiB
HTML
|
<?xml version="1.0" encoding="UTF-8"?>
|
||
|
|
<!DOCTYPE html
|
||
|
|
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
||
|
|
<html lang="en-us" xml:lang="en-us">
|
||
|
|
<head>
|
||
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
||
|
|
<meta name="security" content="public" />
|
||
|
|
<meta name="Robots" content="index,follow" />
|
||
|
|
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
||
|
|
<meta name="DC.Type" content="reference" />
|
||
|
|
<meta name="DC.Title" content="Manage distributed unit of work connections" />
|
||
|
|
<meta name="abstract" content="The CONNECT, SET CONNECTION, DISCONNECT, and RELEASE statements are used to manage connections in the DUW environment." />
|
||
|
|
<meta name="description" content="The CONNECT, SET CONNECTION, DISCONNECT, and RELEASE statements are used to manage connections in the DUW environment." />
|
||
|
|
<meta name="DC.subject" content="distributed unit of work, managing connections, sample program, examples, distributed unit of work program" />
|
||
|
|
<meta name="keywords" content="distributed unit of work, managing connections, sample program, examples, distributed unit of work program" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafyduwex.htm" />
|
||
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Format" content="XHTML" />
|
||
|
|
<meta name="DC.Identifier" content="rbafydrdamanageduk" />
|
||
|
|
<meta name="DC.Language" content="en-us" />
|
||
|
|
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
||
|
|
<!-- US Government Users Restricted Rights -->
|
||
|
|
<!-- Use, duplication or disclosure restricted by -->
|
||
|
|
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ic.css" />
|
||
|
|
<title>Manage distributed unit of work connections</title>
|
||
|
|
</head>
|
||
|
|
<body id="rbafydrdamanageduk"><a name="rbafydrdamanageduk"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Manage distributed unit of work connections</h1>
|
||
|
|
<div><p>The CONNECT, SET CONNECTION, DISCONNECT, and RELEASE statements
|
||
|
|
are used to manage connections in the DUW environment.</p>
|
||
|
|
<div class="section"><p>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.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>The following is an example of a C program running in a DUW environment
|
||
|
|
that uses commitment control.</p>
|
||
|
|
</div>
|
||
|
|
<div class="example"> <div class="fignone"><span class="figcap">Figure 1. Example of distributed unit of work program</span><pre> ....
|
||
|
|
EXEC SQL <strong>WHENEVER SQLERROR GO TO</strong> done;
|
||
|
|
EXEC SQL <strong>WHENEVER NOT FOUND GO TO</strong> done;
|
||
|
|
....
|
||
|
|
EXEC SQL
|
||
|
|
<strong>DECLARE</strong> C1 <strong>CURSOR WITH HOLD FOR</strong>
|
||
|
|
<strong>SELECT</strong> PARTNO, PRICE
|
||
|
|
<strong>FROM</strong> PARTS
|
||
|
|
<strong>WHERE</strong> SITES_UPDATED = 'N'
|
||
|
|
<strong>FOR UPDATE OF</strong> SITES_UPDATED;
|
||
|
|
/* Connect to the systems */
|
||
|
|
EXEC SQL <strong>CONNECT TO</strong> LOCALSYS;
|
||
|
|
EXEC SQL <strong>CONNECT TO</strong> SYSB;
|
||
|
|
EXEC SQL <strong>CONNECT TO</strong> SYSC;
|
||
|
|
/* Make the local system the current connection */
|
||
|
|
EXEC SQL <strong>SET CONNECTION</strong> LOCALSYS;
|
||
|
|
/* Open the cursor */
|
||
|
|
EXEC SQL <strong>OPEN</strong> C1;</pre>
|
||
|
|
<pre> while (SQLCODE==0)
|
||
|
|
{
|
||
|
|
/* Fetch the first row */
|
||
|
|
EXEC SQL <strong>FETCH</strong> C1 <strong>INTO</strong> :partnumber,:price;
|
||
|
|
/* Update the row which indicates that the updates have been
|
||
|
|
propagated to the other sites */
|
||
|
|
EXEC SQL <strong>UPDATE</strong> PARTS <strong>SET</strong> SITES_UPDATED='Y'
|
||
|
|
<strong>WHERE CURRENT OF</strong> 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 <strong>SET CONNECTION</strong> SYSB;
|
||
|
|
EXEC SQL <strong>UPDATE</strong> PARTS
|
||
|
|
<strong>SET</strong> PRICE=:price
|
||
|
|
<strong>WHERE</strong> PARTNO=:partnumber;
|
||
|
|
}</pre>
|
||
|
|
<pre> /* Check if the part data is on SYSC */
|
||
|
|
if ((partnumber > 50) && (partnumber < 200))
|
||
|
|
{
|
||
|
|
/* Make SYSC the current connection and update the price */
|
||
|
|
EXEC SQL <strong>SET CONNECTION</strong> SYSC;
|
||
|
|
EXEC SQL <strong>UPDATE</strong> PARTS
|
||
|
|
<strong>SET</strong> PRICE=:price
|
||
|
|
<strong>WHERE</strong> PARTNO=:partnumber;
|
||
|
|
}
|
||
|
|
/* Commit the changes made at all 3 sites */
|
||
|
|
EXEC SQL <strong>COMMIT</strong>;
|
||
|
|
/* Set the current connection to local so the next row
|
||
|
|
can be fetched */
|
||
|
|
EXEC SQL <strong>SET CONNECTION</strong> LOCALSYS;
|
||
|
|
}
|
||
|
|
done:</pre>
|
||
|
|
<pre> EXEC SQL <strong>WHENEVER SQLERROR CONTINUE</strong>;
|
||
|
|
/* Release the connections that are no longer being used */
|
||
|
|
EXEC SQL <strong>RELEASE</strong> SYSB;
|
||
|
|
EXEC SQL <strong>RELEASE</strong> SYSC;
|
||
|
|
/* Close the cursor */
|
||
|
|
EXEC SQL <strong>CLOSE</strong> C1;
|
||
|
|
/* Do another commit which will end the released connections.
|
||
|
|
The local connection is still active because it was not
|
||
|
|
released. */
|
||
|
|
EXEC SQL <strong>COMMIT</strong>;
|
||
|
|
…</pre>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>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.</p>
|
||
|
|
<p>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.</p>
|
||
|
|
<p>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.</p>
|
||
|
|
<p>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.</p>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rbafyduwex.htm" title="Distributed unit of work (DUW) allows access to multiple application servers within the same unit of work. Each SQL statement can access only one application server. Using distributed unit of work allows changes at multiple applications servers to be committed or rolled back within a single unit of work.">Distributed unit of work</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|