139 lines
7.6 KiB
HTML
139 lines
7.6 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="Use live data" />
|
||
|
|
<meta name="abstract" content="The term live data refers to the type of access that the database manager uses when it retrieves data without making a copy of the data. Using this type of access, the data, which is returned to the program, always reflects the current values of the data in the database. The programmer can control whether the database manager uses a copy of the data or retrieves the data directly. This is done by specifying the allow copy data (ALWCPYDTA) parameter on the precompiler commands or on the Start SQL (STRSQL) command." />
|
||
|
|
<meta name="description" content="The term live data refers to the type of access that the database manager uses when it retrieves data without making a copy of the data. Using this type of access, the data, which is returned to the program, always reflects the current values of the data in the database. The programmer can control whether the database manager uses a copy of the data or retrieves the data directly. This is done by specifying the allow copy data (ALWCPYDTA) parameter on the precompiler commands or on the Start SQL (STRSQL) command." />
|
||
|
|
<meta name="DC.subject" content="using, a copy of the data, allow copy data (ALWCPYDTA), close SQL cursor (CLOSQLCSR), improving performance, parameters, command, ALWCPYDTA (allow copy data), ALWCPYDTA (allow copy data) parameter, allow copy data (ALWCPYDTA) parameter, performance improvement, using live data, live data, using to improve performance, precompiler parameter, ALWCPYDTA" />
|
||
|
|
<meta name="keywords" content="using, a copy of the data, allow copy data (ALWCPYDTA), close SQL cursor (CLOSQLCSR), improving performance, parameters, command, ALWCPYDTA (allow copy data), ALWCPYDTA (allow copy data) parameter, allow copy data (ALWCPYDTA) parameter, performance improvement, using live data, live data, using to improve performance, precompiler parameter, ALWCPYDTA" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="opens.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="../cl/strsql.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="livedata" />
|
||
|
|
<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>Use live data</title>
|
||
|
|
</head>
|
||
|
|
<body id="livedata"><a name="livedata"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Use live data</h1>
|
||
|
|
<div><p>The term <em>live data</em> refers to the type of access
|
||
|
|
that the database manager uses when it retrieves data without making a copy
|
||
|
|
of the data. Using this type of access, the data, which is returned to the
|
||
|
|
program, always reflects the current values of the data in the database. The
|
||
|
|
programmer can control whether the database manager uses a copy of the data
|
||
|
|
or retrieves the data directly. This is done by specifying the allow copy
|
||
|
|
data (ALWCPYDTA) parameter on the precompiler commands or on the <span class="cmdname">Start
|
||
|
|
SQL (STRSQL)</span> command.</p>
|
||
|
|
<div class="section"><p>Specifying ALWCPYDTA(*NO) instructs the database manager to always
|
||
|
|
use live data. In most cases, forcing live data access is a detriment to
|
||
|
|
performance as it severely limits the possible plan choices that the optimizer
|
||
|
|
may use to implement the query. Consequently, in most cases it should be avoided.
|
||
|
|
However, in specialized cases involving a simple query, live data access can
|
||
|
|
be used as a performance advantage because the cursor does not need be closed
|
||
|
|
and opened again to refresh the data being retrieved. An example application
|
||
|
|
demonstrating this advantage is one that produces a list on a display. If
|
||
|
|
the display screen can only show 20 elements of the list at a time, then,
|
||
|
|
after the initial 20 elements are displayed, the application programmer can
|
||
|
|
request that the next 20 rows be displayed. A typical SQL application designed
|
||
|
|
for an operating system other than the <span class="keyword">i5/OS™</span> operating
|
||
|
|
system, might be structured as follows: </p>
|
||
|
|
<pre>EXEC SQL
|
||
|
|
<strong>DECLARE</strong> C1 <strong>CURSOR FOR</strong>
|
||
|
|
<strong>SELECT</strong> EMPNO, LASTNAME, WORKDEPT
|
||
|
|
<strong>FROM</strong> CORPDATA.EMPLOYEE
|
||
|
|
<strong>ORDER BY</strong> EMPNO
|
||
|
|
END-EXEC.
|
||
|
|
|
||
|
|
EXEC SQL
|
||
|
|
<strong>OPEN</strong> C1
|
||
|
|
END-EXEC.
|
||
|
|
|
||
|
|
* PERFORM FETCH-C1-PARA 20 TIMES.
|
||
|
|
|
||
|
|
MOVE EMPNO to LAST-EMPNO.
|
||
|
|
|
||
|
|
EXEC SQL
|
||
|
|
<strong>CLOSE</strong> C1
|
||
|
|
END-EXEC.
|
||
|
|
|
||
|
|
* Show the display and wait for the user to indicate that
|
||
|
|
* the next 20 rows should be displayed.
|
||
|
|
|
||
|
|
EXEC SQL
|
||
|
|
<strong>DECLARE</strong> C2 <strong>CURSOR FOR</strong>
|
||
|
|
<strong>SELECT</strong> EMPNO, LASTNAME, WORKDEPT
|
||
|
|
<strong>FROM</strong> CORPDATA.EMPLOYEE
|
||
|
|
<strong>WHERE</strong> EMPNO > :LAST-EMPNO
|
||
|
|
<strong>ORDER BY</strong> EMPNO
|
||
|
|
END-EXEC.
|
||
|
|
|
||
|
|
EXEC SQL
|
||
|
|
<strong>OPEN</strong> C2
|
||
|
|
END-EXEC.
|
||
|
|
|
||
|
|
* PERFORM FETCH-C21-PARA 20 TIMES.
|
||
|
|
|
||
|
|
* Show the display with these 20 rows of data.
|
||
|
|
|
||
|
|
EXEC SQL
|
||
|
|
<strong>CLOSE</strong> C2
|
||
|
|
END-EXEC.</pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>In the above example, notice that an additional cursor had to
|
||
|
|
be opened to continue the list and to get current data. This can result in
|
||
|
|
creating an additional ODP that increases the processing time on the <span class="keyword">iSeries™</span> server. In place of the above
|
||
|
|
example, the programmer can design the application specifying ALWCPYDTA(*NO)
|
||
|
|
with the following SQL statements: </p>
|
||
|
|
<pre>EXEC SQL
|
||
|
|
<strong>DECLARE</strong> C1 <strong>CURSOR FOR</strong>
|
||
|
|
<strong>SELECT</strong> EMPNO, LASTNAME, WORKDEPT
|
||
|
|
<strong>FROM</strong> CORPDATA.EMPLOYEE
|
||
|
|
<strong>ORDER BY</strong> EMPNO
|
||
|
|
END-EXEC.
|
||
|
|
|
||
|
|
EXEC SQL
|
||
|
|
<strong>OPEN</strong> C1
|
||
|
|
END-EXEC.
|
||
|
|
|
||
|
|
* Display the screen with these 20 rows of data.
|
||
|
|
|
||
|
|
* PERFORM FETCH-C1-PARA 20 TIMES.
|
||
|
|
|
||
|
|
* Show the display and wait for the user to indicate that
|
||
|
|
* the next 20 rows should be displayed.
|
||
|
|
|
||
|
|
* PERFORM FETCH-C1-PARA 20 TIMES.
|
||
|
|
|
||
|
|
EXEC SQL
|
||
|
|
<strong>CLOSE</strong> C1
|
||
|
|
END-EXEC.</pre>
|
||
|
|
<p>In the above example, the query might perform better
|
||
|
|
if the FOR 20 ROWS clause was used on the multiple-row FETCH statement. Then,
|
||
|
|
the 20 rows are retrieved in one operation.</p>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="opens.htm" title="There are some design tips that you can apply when designing SQL applications to maximize your database performance.">Application design tips for database performance</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relinfo"><strong>Related information</strong><br />
|
||
|
|
<div><a href="../cl/strsql.htm">Start SQL (STRSQL) command</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|