70 lines
4.8 KiB
HTML
70 lines
4.8 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="Eliminate redundant validation with SQL PREPARE statements" />
|
||
|
|
<meta name="abstract" content="The processing which occurs when an SQL PREPARE statement is run is similar to the processing which occurs during precompile processing." />
|
||
|
|
<meta name="description" content="The processing which occurs when an SQL PREPARE statement is run is similar to the processing which occurs during precompile processing." />
|
||
|
|
<meta name="DC.subject" content="improving performance, PREPARE statement, performance improvement, statements, PREPARE" />
|
||
|
|
<meta name="keywords" content="improving performance, PREPARE statement, performance improvement, statements, PREPARE" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="progtech.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="../db2/rbafzmstpreph2.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="precompileopts.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="ipps" />
|
||
|
|
<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>Eliminate redundant validation with SQL PREPARE statements</title>
|
||
|
|
</head>
|
||
|
|
<body id="ipps"><a name="ipps"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Eliminate redundant validation with SQL PREPARE statements</h1>
|
||
|
|
<div><p>The processing which occurs when an SQL PREPARE statement is run
|
||
|
|
is similar to the processing which occurs during precompile processing. </p>
|
||
|
|
<div class="section"><p>The following processing occurs for the statement that is being
|
||
|
|
prepared: </p>
|
||
|
|
<ul><li>The syntax is checked.</li>
|
||
|
|
<li>The statement is validated to ensure that the usage of objects are valid.</li>
|
||
|
|
<li>An access plan is built.</li>
|
||
|
|
</ul>
|
||
|
|
<p>Again when the statement is executed or opened, the database manager
|
||
|
|
will re-validate that the access plan is still valid. Much of this open processing
|
||
|
|
validation is redundant with the validation which occurred during the PREPARE
|
||
|
|
processing. The DLYPRP(*YES) parameter specifies whether PREPARE statements
|
||
|
|
in this program will completely validate the dynamic statement.
|
||
|
|
The validation will be completed when the dynamic statement is opened or executed.
|
||
|
|
This parameter can provide a significant performance enhancement for programs
|
||
|
|
which use the PREPARE SQL statement because it eliminates redundant validation.
|
||
|
|
Programs that specify this precompile option should check the SQLCODE and
|
||
|
|
SQLSTATE after running the OPEN or EXECUTE statement to ensure that the statement
|
||
|
|
is valid. DLYPRP(*YES) will not provide any performance improvement if the
|
||
|
|
INTO clause is used on the PREPARE statement or if a DESCRIBE statement uses
|
||
|
|
the dynamic statement before an OPEN is issued for the statement.</p>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="progtech.htm" title="By changing the coding of your queries, you can improve their performance.">Programming techniques for database performance</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relref"><strong>Related reference</strong><br />
|
||
|
|
<div><a href="precompileopts.htm" title="Several precompile options are available for creating SQL programs with improved performance. They are only options because using them may impact the function of the application. For this reason, the default value for these parameters is the value that will ensure successful migration of applications from prior releases. However, you can improve performance by specifying other options.">Effects of precompile options on database performance</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relinfo"><strong>Related information</strong><br />
|
||
|
|
<div><a href="../db2/rbafzmstpreph2.htm">Prepare statement</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|