185 lines
12 KiB
HTML
185 lines
12 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="Reduce the number of open operations" />
|
|||
|
|
<meta name="abstract" content="The SQL data manipulation language statements must do database open operations in order to create an open data path (ODP) to the data. An open data path is the path through which all input/output operations for the table are performed. In a sense, it connects the SQL application to a table. The number of open operations in a program can significantly affect performance." />
|
|||
|
|
<meta name="description" content="The SQL data manipulation language statements must do database open operations in order to create an open data path (ODP) to the data. An open data path is the path through which all input/output operations for the table are performed. In a sense, it connects the SQL application to a table. The number of open operations in a program can significantly affect performance." />
|
|||
|
|
<meta name="DC.subject" content="performance considerations, reducing number of open database operations, improving performance, example, performance improvement, reducing number of open database operation, open, reducing number, effect on performance, number of open database operations, improving performance by reducing, examples, reducing the number of open database operation, command, Delete Override (DLTOVR), command (CL), DLTOVR (Delete Override), closing, determining number, Trace Job (TRCJOB), Display Journal (DSPJRN), Display Journal (DSPJRN), TRCJOB (Trace Job), DSPJRN (Display Journal)" />
|
|||
|
|
<meta name="keywords" content="performance considerations, reducing number of open database operations, improving performance, example, performance improvement, reducing number of open database operation, open, reducing number, effect on performance, number of open database operations, improving performance by reducing, examples, reducing the number of open database operation, command, Delete Override (DLTOVR), command (CL), DLTOVR (Delete Override), closing, determining number, Trace Job (TRCJOB), Display Journal (DSPJRN), Display Journal (DSPJRN), TRCJOB (Trace Job), DSPJRN (Display Journal)" />
|
|||
|
|
<meta name="DC.Relation" scheme="URI" content="opens.htm" />
|
|||
|
|
<meta name="DC.Relation" scheme="URI" content="../cl/rclrsc.htm" />
|
|||
|
|
<meta name="DC.Relation" scheme="URI" content="../cl/trcjob.htm" />
|
|||
|
|
<meta name="DC.Relation" scheme="URI" content="../cl/dspjrn.htm" />
|
|||
|
|
<meta name="DC.Relation" scheme="URI" content="../rzahg/rzahgrpgile.htm" />
|
|||
|
|
<meta name="DC.Relation" scheme="URI" content="../rzahg/rzahgcobolile.htm" />
|
|||
|
|
<meta name="DC.Relation" scheme="URI" content="../rzahg/rzahgcandcplus.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="reduceopens" />
|
|||
|
|
<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>Reduce the number of open operations</title>
|
|||
|
|
</head>
|
|||
|
|
<body id="reduceopens"><a name="reduceopens"><!-- --></a>
|
|||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
|||
|
|
<h1 class="topictitle1">Reduce the number of open operations</h1>
|
|||
|
|
<div><p>The SQL data manipulation language statements must do database
|
|||
|
|
open operations in order to create an open data path (ODP) to the data. An
|
|||
|
|
open data path is the path through which all input/output operations for the
|
|||
|
|
table are performed. In a sense, it connects the SQL application to a table.
|
|||
|
|
The number of open operations in a program can significantly affect performance.</p>
|
|||
|
|
<div class="section"><p>A database open operation occurs on: </p>
|
|||
|
|
<ul><li>An OPEN statement</li>
|
|||
|
|
<li>SELECT INTO statement</li>
|
|||
|
|
<li>An INSERT statement with a VALUES clause</li>
|
|||
|
|
<li>An UPDATE statement with a WHERE condition</li>
|
|||
|
|
<li>An UPDATE statement with a WHERE CURRENT OF cursor and SET clauses that
|
|||
|
|
refer to operators or functions</li>
|
|||
|
|
<li>SET statement that contains an expression</li>
|
|||
|
|
<li>VALUES INTO statement that contains an expression</li>
|
|||
|
|
<li>A DELETE statement with a WHERE condition</li>
|
|||
|
|
</ul>
|
|||
|
|
</div>
|
|||
|
|
<div class="section"><p>An INSERT statement with a select-statement requires two open
|
|||
|
|
operations. Certain forms of subqueries may also require one open per subselect.</p>
|
|||
|
|
</div>
|
|||
|
|
<div class="section"><p>To minimize the number of opens, <span class="keyword">DB2 Universal Database™ for iSeries™</span> leaves
|
|||
|
|
the open data path (ODP) open and reuses the ODP if the statement is run again,
|
|||
|
|
unless: </p>
|
|||
|
|
<ul><li>The ODP used a host variable to build a subset temporary index. The <span class="keyword">i5/OS™</span> database support may choose
|
|||
|
|
to build a temporary index with entries for only the rows that match the row
|
|||
|
|
selection specified in the SQL statement. If a host variable was used in the
|
|||
|
|
row selection, the temporary index will not have the entries required for
|
|||
|
|
a different value contained in the host variable.</li>
|
|||
|
|
<li>Ordering was specified on a host variable value.</li>
|
|||
|
|
<li>An <span class="cmdname">Override Database File (OVRDBF)</span> or <span class="cmdname">Delete
|
|||
|
|
Override (DLTOVR)</span> CL command has been issued since the ODP was opened,
|
|||
|
|
which affects the SQL statement execution. The ODPs opened by <span class="keyword">DB2 Universal Database for iSeries</span> <div class="note"><span class="notetitle">Note:</span> Only
|
|||
|
|
overrides that affect the name of the table being referred to will cause the
|
|||
|
|
ODP to be closed within a given program invocation.</div>
|
|||
|
|
</li>
|
|||
|
|
<li>The join is a complex join that requires temporaries to contain the intermediate
|
|||
|
|
steps of the join.</li>
|
|||
|
|
<li>Some cases involve a complex sort, where a temporary file is required,
|
|||
|
|
may not be reusable.</li>
|
|||
|
|
<li>A change to the library list since the last open has occurred, which changes
|
|||
|
|
the table selected by an unqualified referral in system naming mode.</li>
|
|||
|
|
<li>The join was implemented by the CQE optimizer using hash join.</li>
|
|||
|
|
</ul>
|
|||
|
|
</div>
|
|||
|
|
<div class="section"><p>For embedded static SQL, <span class="keyword">DB2 Universal Database for iSeries</span> only
|
|||
|
|
reuses ODPs opened by the same statement. An identical statement coded later
|
|||
|
|
in the program does not reuse an ODP from any other statement. If the identical
|
|||
|
|
statement must be run in the program many times, code it once in a subroutine
|
|||
|
|
and call the subroutine to run the statement.</p>
|
|||
|
|
</div>
|
|||
|
|
<div class="section"><img src="./delta.gif" alt="Start of change" /> <p>The ODPs opened by DB2 Universal Database for iSeries
|
|||
|
|
are closed when any of the following occurs:</p>
|
|||
|
|
<ul><li>A CLOSE, INSERT, UPDATE, DELETE, or SELECT INTO statement completes and
|
|||
|
|
the ODP required a temporary result that was not reusable or a subset temporary
|
|||
|
|
index.</li>
|
|||
|
|
<li>The <span class="cmdname">Reclaim Resources (RCLRSC)</span> command is issued. A <span class="cmdname">Reclaim
|
|||
|
|
Resources (RCLRSC)</span> is issued when the first COBOL program on the
|
|||
|
|
call stack ends or when a COBOL program issues the STOP RUN COBOL statement. <span class="cmdname">Reclaim
|
|||
|
|
Resources (RCLRSC)</span> will not close ODPs created for programs precompiled
|
|||
|
|
using CLOSQLCSR(*ENDJOB). For interaction of <span class="cmdname">Reclaim Resources (RCLRSC)</span> with
|
|||
|
|
non-default activation groups, see the following books: <ul><li>WebSphere<sup>®</sup> Development
|
|||
|
|
Studio: ILE C/C++ Programmer's Guide</li>
|
|||
|
|
<li>WebSphere Development
|
|||
|
|
Studio: ILE COBOL Programmer's Guide</li>
|
|||
|
|
<li>WebSphere Development
|
|||
|
|
Studio: ILE RPG Programmer's Guide</li>
|
|||
|
|
</ul>
|
|||
|
|
</li>
|
|||
|
|
<li>When the last program that contains SQL statements on the call stack exits,
|
|||
|
|
except for ODPs created for programs precompiled using CLOSQLCSR(*ENDJOB)
|
|||
|
|
or modules precompiled using CLOSQLCSR(*ENDACTGRP).</li>
|
|||
|
|
<li>When a CONNECT (Type 1) statement changes the application server for an
|
|||
|
|
activation group, all ODPs created for the activation group are closed.</li>
|
|||
|
|
<li>When a DISCONNECT statement ends a connection to the application server,
|
|||
|
|
all ODPs for that application server are closed.</li>
|
|||
|
|
<li>When a released connection is ended by a successful COMMIT, all ODPs for
|
|||
|
|
that application server are closed.</li>
|
|||
|
|
<li>When the threshold for open cursors specified by the query options file
|
|||
|
|
(QAQQINI) parameter OPEN_CURSOR_THRESHOLD is reached.</li>
|
|||
|
|
<li>The SQL LOCK TABLE or CL ALCOBJ OBJ((filename *FILE *EXCL)) CONFLICT(*RQSRLS)
|
|||
|
|
command will close any psuedo-closed cursors associated with the specified
|
|||
|
|
table.</li>
|
|||
|
|
</ul>
|
|||
|
|
<img src="./deltaend.gif" alt="End of change" /></div>
|
|||
|
|
<div class="section"><p>You can control whether the system keeps the ODPs open in the
|
|||
|
|
following ways: </p>
|
|||
|
|
</div>
|
|||
|
|
<div class="section"> <ul><li>Design the application so a program that issues an SQL statement is always
|
|||
|
|
on the call stack</li>
|
|||
|
|
<li>Use the CLOSQLCSR(*ENDJOB) or CLOSQLCSR(*ENDACTGRP) parameter</li>
|
|||
|
|
<li>By specifying the OPEN_CURSOR_THRESHOLD and OPEN_CURSOR_CLOSE_COUNT parameters
|
|||
|
|
of the query options file (QAQQINI)</li>
|
|||
|
|
</ul>
|
|||
|
|
</div>
|
|||
|
|
<div class="section"><p>The system does an open operation for the first execution of each
|
|||
|
|
UPDATE WHERE CURRENT OF when any expression in the SET clause contains an
|
|||
|
|
operator or function. The open can be avoided by coding the function or operation
|
|||
|
|
in the host language code.</p>
|
|||
|
|
</div>
|
|||
|
|
<div class="section"><div class="p">For example, the following UPDATE causes the system to do an open
|
|||
|
|
operation: <pre>EXEC SQL
|
|||
|
|
<strong>FETCH</strong> EMPT <strong>INTO</strong> :SALARY
|
|||
|
|
END-EXEC.
|
|||
|
|
|
|||
|
|
EXEC SQL
|
|||
|
|
<strong>UPDATE</strong> CORPDATA.EMPLOYEE
|
|||
|
|
<strong>SET</strong> SALARY = :SALARY + 1000
|
|||
|
|
<strong>WHERE CURRENT OF</strong> EMPT
|
|||
|
|
END-EXEC.
|
|||
|
|
</pre>
|
|||
|
|
</div>
|
|||
|
|
</div>
|
|||
|
|
<div class="section"><div class="p">Instead, use the following coding technique to avoid opens: <pre>EXEC SQL
|
|||
|
|
<strong>FETCH</strong> EMPT <strong>INTO</strong> :SALARY
|
|||
|
|
END EXEC.
|
|||
|
|
|
|||
|
|
ADD 1000 TO SALARY.
|
|||
|
|
|
|||
|
|
EXEC SQL
|
|||
|
|
<strong>UPDATE</strong> CORPDATA.EMPLOYEE
|
|||
|
|
<strong>SET</strong> SALARY = :SALARY
|
|||
|
|
<strong>WHERE CURRENT OF</strong> EMPT
|
|||
|
|
END-EXEC.</pre>
|
|||
|
|
</div>
|
|||
|
|
</div>
|
|||
|
|
<div class="section"><p>You can determine whether SQL statements result in full opens
|
|||
|
|
in several ways. The preferred methods are to use the Database Monitor or
|
|||
|
|
by looking at the messages issued while debug is active. You can also use
|
|||
|
|
the CL commands <span class="cmdname">Trace Job (TRCJOB)</span> or <span class="cmdname">Display
|
|||
|
|
Journal (DSPJRN)</span>.</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/rclrsc.htm">Reclaim Resources (RCLRSC) command</a></div>
|
|||
|
|
<div><a href="../cl/trcjob.htm">Trace Job (TRCJOB) command</a></div>
|
|||
|
|
<div><a href="../cl/dspjrn.htm">Display Journal (DSPJRN) command</a></div>
|
|||
|
|
<div><a href="../rzahg/rzahgrpgile.htm">ILE RPG</a></div>
|
|||
|
|
<div><a href="../rzahg/rzahgcobolile.htm">ILE COBOL</a></div>
|
|||
|
|
<div><a href="../rzahg/rzahgcandcplus.htm">C and C++</a></div>
|
|||
|
|
</div>
|
|||
|
|
</div>
|
|||
|
|
</body>
|
|||
|
|
</html>
|