235 lines
16 KiB
HTML
235 lines
16 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="concept" />
|
||
|
|
<meta name="DC.Title" content="Distributed relational database processing" />
|
||
|
|
<meta name="abstract" content="A relational database is a set of data stored in one or more tables in a computer." />
|
||
|
|
<meta name="description" content="A relational database is a set of data stored in one or more tables in a computer." />
|
||
|
|
<meta name="DC.subject" content="relational database, definition, table, SQL terms, definition list, corresponding system terms, system, terms, collection, SQL collection, index, view, catalog, system database, user database, remote database, schema, application server, application requester, committed work, unit of work, commitment control, overview, rollback" />
|
||
|
|
<meta name="keywords" content="relational database, definition, table, SQL terms, definition list, corresponding system terms, system, terms, collection, SQL collection, index, view, catalog, system database, user database, remote database, schema, application server, application requester, committed work, unit of work, commitment control, overview, rollback" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbal1intro.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbal1ruw.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbal1duw.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbal1odbconc.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="../db2/rbafzmstrelationaldb.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="../rzakj/rzakjtrouble.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="../rzakj/rzakjxatransaction.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="rbal1drdbp" />
|
||
|
|
<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>Distributed relational database processing</title>
|
||
|
|
</head>
|
||
|
|
<body id="rbal1drdbp"><a name="rbal1drdbp"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Distributed relational database processing</h1>
|
||
|
|
<div><p>A <dfn class="term">relational database</dfn> is a set of data stored in one
|
||
|
|
or more tables in a computer. </p>
|
||
|
|
<p>A <dfn class="term">table</dfn> is a two-dimensional arrangement of data consisting
|
||
|
|
of horizontal rows and vertical columns as shown in the following table. Each <dfn class="term">row</dfn> contains
|
||
|
|
a sequence of values, one for each column of the table. A <dfn class="term">column</dfn> has
|
||
|
|
a name and contains a particular data type (for example, character, decimal,
|
||
|
|
or integer).</p>
|
||
|
|
|
||
|
|
<div class="tablenoborder"><a name="rbal1drdbp__tabl"><!-- --></a><table cellpadding="4" cellspacing="0" summary="" id="rbal1drdbp__tabl" width="100%" frame="border" border="1" rules="all"><caption>Table 1. A typical relational table</caption><thead align="left"><tr><th valign="top" width="25%" id="d0e122">Item</th>
|
||
|
|
<th valign="top" width="25%" id="d0e124">Name</th>
|
||
|
|
<th valign="top" width="25%" id="d0e126">Supplier</th>
|
||
|
|
<th valign="top" width="25%" id="d0e128">Quantity</th>
|
||
|
|
</tr>
|
||
|
|
</thead>
|
||
|
|
<tbody><tr><td valign="top" width="25%" headers="d0e122 ">78476</td>
|
||
|
|
<td valign="top" width="25%" headers="d0e124 ">Baseball</td>
|
||
|
|
<td valign="top" width="25%" headers="d0e126 ">ACME</td>
|
||
|
|
<td valign="top" width="25%" headers="d0e128 ">650</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" width="25%" headers="d0e122 ">78477</td>
|
||
|
|
<td valign="top" width="25%" headers="d0e124 ">Football</td>
|
||
|
|
<td valign="top" width="25%" headers="d0e126 ">Imperial</td>
|
||
|
|
<td valign="top" width="25%" headers="d0e128 ">228</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" width="25%" headers="d0e122 ">78478</td>
|
||
|
|
<td valign="top" width="25%" headers="d0e124 ">Basketball</td>
|
||
|
|
<td valign="top" width="25%" headers="d0e126 ">ACME</td>
|
||
|
|
<td valign="top" width="25%" headers="d0e128 ">105</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" width="25%" headers="d0e122 ">78479</td>
|
||
|
|
<td valign="top" width="25%" headers="d0e124 ">Soccer ball</td>
|
||
|
|
<td valign="top" width="25%" headers="d0e126 ">ACME</td>
|
||
|
|
<td valign="top" width="25%" headers="d0e128 ">307</td>
|
||
|
|
</tr>
|
||
|
|
</tbody>
|
||
|
|
</table>
|
||
|
|
</div>
|
||
|
|
<p>Tables can be defined and accessed in several ways on the server. One way
|
||
|
|
to describe and access tables on the server is to use a language like Structured
|
||
|
|
Query Language (SQL). SQL is the standard IBM<sup>®</sup> database language and provides the necessary
|
||
|
|
consistency to enable distributed data processing across different servers.</p>
|
||
|
|
<p>Another way to describe and access tables on the server is to describe
|
||
|
|
physical and logical files using data description specifications (DDS) and
|
||
|
|
access tables using file interfaces (for example, read and write high-level
|
||
|
|
language statements).</p>
|
||
|
|
<p>SQL uses different terminology from that used on the <span class="keyword">iSeries™ server</span>.
|
||
|
|
For most SQL objects there is a corresponding server object on the <span class="keyword">iSeries server</span>. The following table shows
|
||
|
|
the relationship between SQL relational database terms and <span class="keyword">iSeries server</span> terms.</p>
|
||
|
|
|
||
|
|
<table cellpadding="4" cellspacing="0" border="1" class="tableborder"><tr><td>
|
||
|
|
<a name="rbal1drdbp__dbterms"><!-- --></a><table cellpadding="4" cellspacing="0" summary="" id="rbal1drdbp__dbterms" width="100%" border="0"><caption>Table 2. Relationship of SQL terms to system
|
||
|
|
terms</caption><thead align="left"><tr><th valign="top" width="44.44444444444444%" id="d0e199">SQL term</th>
|
||
|
|
<th valign="top" width="55.55555555555556%" id="d0e201">System term</th>
|
||
|
|
</tr>
|
||
|
|
</thead>
|
||
|
|
<tbody><tr><td valign="top" width="44.44444444444444%" headers="d0e199 "><strong>relational database</strong>. A database that can be perceived
|
||
|
|
as a set of tables and can be manipulated in accordance with the relational
|
||
|
|
model of data. There are three types of relational databases a user can access
|
||
|
|
from an <span class="keyword">iSeries server</span>, as listed
|
||
|
|
under the system term column. For more information, see the Relational database
|
||
|
|
topic.</td>
|
||
|
|
<td valign="top" width="55.55555555555556%" headers="d0e201 "> <p><strong>system relational database, or system database</strong>.
|
||
|
|
All the database objects that exist on disk attached to the <span class="keyword">iSeries server</span> that
|
||
|
|
are not stored on independent auxiliary storage pools.</p>
|
||
|
|
<div class="p"><strong>user relational
|
||
|
|
database, or user database</strong>. All the database objects that exist in a single
|
||
|
|
independent auxiliary storage pool group along with those database objects
|
||
|
|
that are not stored on independent auxiliary storage pools. <div class="note"><span class="notetitle">Notes:</span> <ul><li>As of V5R2, an <span class="keyword">iSeries server</span> can
|
||
|
|
be host to multiple relational databases if independent auxiliary storage
|
||
|
|
pools are configured on the server. There will always be one system relational
|
||
|
|
database, and there can be one or more user relational databases. Each user
|
||
|
|
database includes all the objects in the system database. </li>
|
||
|
|
<li>The user should be aware, however, that from a commitment control point
|
||
|
|
of view, the system database is treated as a separate database, even when
|
||
|
|
from an SQL point of view, it is viewed as being included within a user database.
|
||
|
|
For more information, see the Transactions and commitment control topic.</li>
|
||
|
|
</ul>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<p><strong>remote relational database, or remote database</strong>.
|
||
|
|
A database that resides on an <span class="keyword">iSeries</span> or
|
||
|
|
another server that can be accessed remotely.</p>
|
||
|
|
</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" width="44.44444444444444%" headers="d0e199 "><strong>schema</strong>. Consists of a library, a journal, a journal receiver,
|
||
|
|
an SQL catalog, and an optional data dictionary. A schema groups related objects
|
||
|
|
and allows you to find the objects by name. <div class="note"><span class="notetitle">Note:</span> A schema is also commonly
|
||
|
|
referred to as a collection.</div>
|
||
|
|
</td>
|
||
|
|
<td valign="top" width="55.55555555555556%" headers="d0e201 "><strong>library</strong>. Groups related objects and allows you to find the objects
|
||
|
|
by name.</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" width="44.44444444444444%" headers="d0e199 "><strong>table</strong>. A set of columns and rows.</td>
|
||
|
|
<td valign="top" width="55.55555555555556%" headers="d0e201 "><strong>physical file</strong>. A set of records.</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" width="44.44444444444444%" headers="d0e199 "><strong>row</strong>. The horizontal part of a table containing a serial set
|
||
|
|
of columns.</td>
|
||
|
|
<td valign="top" width="55.55555555555556%" headers="d0e201 "><strong>record</strong>. A set of fields.</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" width="44.44444444444444%" headers="d0e199 "><strong>column</strong>. The vertical part of a table of one data type.</td>
|
||
|
|
<td valign="top" width="55.55555555555556%" headers="d0e201 "><strong>field</strong>. One or more bytes of related information of one data
|
||
|
|
type.</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" width="44.44444444444444%" headers="d0e199 "><strong>view</strong>. A subset of columns and rows of one or more tables.</td>
|
||
|
|
<td valign="top" width="55.55555555555556%" headers="d0e201 "><strong>logical file</strong>. A subset of fields, records or both of up to 32
|
||
|
|
physical files.</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" width="44.44444444444444%" headers="d0e199 "><strong>index</strong>. A collection of data in the columns of a table, logically
|
||
|
|
arranged in ascending or descending order.</td>
|
||
|
|
<td valign="top" width="55.55555555555556%" headers="d0e201 ">A type of logical file.</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" width="44.44444444444444%" headers="d0e199 "><strong>package</strong>. An object that contains control structures for SQL
|
||
|
|
statements to be used by an application server.</td>
|
||
|
|
<td valign="top" width="55.55555555555556%" headers="d0e201 "><strong>SQL package</strong>. Has the same meaning as the SQL term.</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" width="44.44444444444444%" headers="d0e199 "><strong>catalog</strong>. A set of tables and views that contain information
|
||
|
|
about tables, packages, views, indexes, and constraints. The catalog views
|
||
|
|
in QSYS2 contain information about all tables, packages, views, indexes, and
|
||
|
|
constraints on the <span class="keyword">iSeries server</span>.
|
||
|
|
Additionally, an SQL schema will contain a set of these views that only contains
|
||
|
|
information about tables, packages, views, indexes, and constraints only in
|
||
|
|
the schema.</td>
|
||
|
|
<td valign="top" width="55.55555555555556%" headers="d0e201 ">No similar object. However, the <a href="../cl/dspfd.htm">Display File Description (DSPFD) command</a> and the <a href="../cl/dspffd.htm">Display File Field Description
|
||
|
|
(DSPFFD) command</a> provide some of the same information that querying
|
||
|
|
an SQL catalog provides.</td>
|
||
|
|
</tr>
|
||
|
|
</tbody>
|
||
|
|
</table>
|
||
|
|
</td></tr></table>
|
||
|
|
<p>A <dfn class="term">distributed relational database</dfn> exists when the application
|
||
|
|
programs that use the data and the data itself are located on different machines,
|
||
|
|
or when the programs use data that is located on multiple databases on the
|
||
|
|
same server. In the latter case, the database is distributed in the sense
|
||
|
|
that DRDA<sup>®</sup> protocols
|
||
|
|
are used to access one or more of the databases within the single server.
|
||
|
|
The connection to a database in such an environment will be one of two types:
|
||
|
|
local or DRDA.
|
||
|
|
There will be, at most, only one local database connection at one time. One
|
||
|
|
simple form of a distributed relational database is shown in the following
|
||
|
|
figure where the application program runs on one machine, and the data is
|
||
|
|
located on a remote server.</p>
|
||
|
|
<p>When using a distributed relational database, the system on which the application
|
||
|
|
program is run is called the <dfn class="term">application requester (AR)</dfn>, and
|
||
|
|
the system on which the remote data resides is called the <dfn class="term">application
|
||
|
|
server (AS)</dfn>. The term <dfn class="term">client</dfn> is often used interchangeably
|
||
|
|
with AR, and <dfn class="term">server</dfn> with AS.</p>
|
||
|
|
<div class="fignone" id="rbal1drdbp__rbal1drbpic"><a name="rbal1drdbp__rbal1drbpic"><!-- --></a><span class="figcap">Figure 1. A distributed relational database</span><br /><img src="rbal1501.gif" alt="A Distributed Relational Database" /><br /></div>
|
||
|
|
<p>A <dfn class="term">unit of work</dfn> is one or more database requests and the associated
|
||
|
|
processing that make up a completed piece of work as shown in the following
|
||
|
|
figure. A simple example is taking a part from stock in an inventory control
|
||
|
|
application program. An inventory program can tentatively remove an item from
|
||
|
|
a shop inventory account table and then add that item to a parts reorder table
|
||
|
|
at the same location. The term <dfn class="term">transaction</dfn> is another expression
|
||
|
|
used to describe the unit of work concept.</p>
|
||
|
|
<p>In the preceding example, the unit of work is not complete until the part
|
||
|
|
is both removed from the shop inventory account table and added to a reorder
|
||
|
|
table. When the requests are complete, the application program can <dfn class="term">commit</dfn> the
|
||
|
|
unit of work. This means that any database changes associated with the unit
|
||
|
|
of work are made permanent. </p>
|
||
|
|
<p>With unit of work support, the application program can also <dfn class="term">roll
|
||
|
|
back</dfn> changes to a unit of work. If a unit of work is rolled back, the
|
||
|
|
changes made since the last commit or rollback operation are not applied.
|
||
|
|
Thus, the application program treats the set of requests to a database as
|
||
|
|
a unit.</p>
|
||
|
|
<div class="fignone" id="rbal1drdbp__rbal1unit"><a name="rbal1drdbp__rbal1unit"><!-- --></a><span class="figcap">Figure 2. Unit of work in a local relational database</span><br /><img src="rbal1502.gif" alt="Unit of Work in a Local Relational Database" /><br /></div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<ul class="ullinks">
|
||
|
|
<li class="ulchildlink"><strong><a href="rbal1ruw.htm">Remote unit of work</a></strong><br />
|
||
|
|
<dfn class="term">Remote unit of work</dfn> (RUW) is a form of distributed
|
||
|
|
relational database processing in which an application program can access
|
||
|
|
data on a remote database within a unit of work. A remote unit of work can
|
||
|
|
include more than one relational database request, but all requests
|
||
|
|
must be made to the same remote database. All requests to a relational database
|
||
|
|
must be completed (either committed or rolled back) before requests can be
|
||
|
|
sent to another relational database.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbal1duw.htm">Distributed unit of work</a></strong><br />
|
||
|
|
<dfn class="term">Distributed unit of work</dfn> (DUW) enables a user or application
|
||
|
|
program to read or update data at multiple locations within a unit of work.
|
||
|
|
Within one unit of work, an application running on one system can direct SQL
|
||
|
|
requests to multiple remote database management systems using the SQL supported
|
||
|
|
by those systems.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbal1odbconc.htm">Other distributed relational database terms and concepts</a></strong><br />
|
||
|
|
This discussion provides an overview of additional distributed relational database concepts.</li>
|
||
|
|
</ul>
|
||
|
|
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rbal1intro.htm" title="Distributed relational database support on the iSeries server consists of an implementation of IBM Distributed Relational Database Architecture (DRDA) and integration of other SQL clients by use of Application Requester Driver (ARD) programs.">Introduction to distributed database programming</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relconcepts"><strong>Related concepts</strong><br />
|
||
|
|
<div><a href="../db2/rbafzmstrelationaldb.htm">Relational database</a></div>
|
||
|
|
<div><a href="../rzakj/rzakjtrouble.htm">Troubleshoot transactions and commitment control</a></div>
|
||
|
|
<div><a href="../rzakj/rzakjxatransaction.htm">XA transaction support for commitment control</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|