82 lines
5.4 KiB
HTML
82 lines
5.4 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="Testing performance with the query governor" />
|
||
<meta name="abstract" content="You can use the query governor to test the performance of your queries." />
|
||
<meta name="description" content="You can use the query governor to test the performance of your queries." />
|
||
<meta name="DC.subject" content="performance considerations, Print SQL Information (PRTSQLINF), command (CL)" />
|
||
<meta name="keywords" content="performance considerations, Print SQL Information (PRTSQLINF), command (CL)" />
|
||
<meta name="DC.Relation" scheme="URI" content="govrle.htm" />
|
||
<meta name="DC.Relation" scheme="URI" content="../cl/prtsqlinf.htm" />
|
||
<meta name="DC.Relation" scheme="URI" content="../cl/chgqrya.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="testperfwithqrygov" />
|
||
<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>Testing performance with the query governor</title>
|
||
</head>
|
||
<body id="testperfwithqrygov"><a name="testperfwithqrygov"><!-- --></a>
|
||
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
<h1 class="topictitle1">Testing performance with the query governor</h1>
|
||
<div><p>You can use the query governor to test the performance of your
|
||
queries.</p>
|
||
<div class="section"><p>To test the performance of a query with the query governor, do
|
||
the following:</p>
|
||
</div>
|
||
<div class="section"> <ol><li>Set the query time limit to zero ( QRYTIMLMT(0) ) using the <span class="cmdname">Change
|
||
Query Attributes (CHGQRYA)</span> command or in the INI file. This forces
|
||
an inquiry message from the governor stating that the estimated time to run
|
||
the query exceeds the query time limit.</li>
|
||
<li>Prompt for message help on the inquiry message and find the same information
|
||
that you can find by running the <span class="cmdname">Print SQL Information (PRTSQLINF)</span> command.</li>
|
||
</ol>
|
||
</div>
|
||
<div class="section"><p>The query governor lets you optimize performance without having
|
||
to run through several iterations of the query. </p>
|
||
</div>
|
||
<div class="section"><p>Additionally, if the query is canceled, the query optimizer evaluates
|
||
the access plan and sends the optimizer debug messages to the job log. This
|
||
occurs even if the job is <em>not</em> in debug mode. You can then review the
|
||
optimizer tuning messages in the job log to see if additional tuning is needed
|
||
to obtain optimal query performance. This allows you to try several permutations
|
||
of the query with different attributes, indexes, and syntax or both to determine
|
||
what performs better through the optimizer without actually running the query
|
||
to completion. This saves on system resources because the actual query of
|
||
the data is never actually done. If the tables to be queried contain a large
|
||
number of rows, this represents a significant savings in system resources.</p>
|
||
</div>
|
||
<div class="section"><p>Be careful when you use this technique for performance testing,
|
||
because all query requests will be stopped before they are run. This is especially
|
||
important for a CQE query that cannot be implemented in a single query step.
|
||
For these types of queries, separate multiple query requests are issued, and
|
||
then their results are accumulated before returning the final results. Stopping
|
||
the query in one of these intermediate steps gives you only the performance
|
||
information that relates to that intermediate step, and not for the entire
|
||
query.</p>
|
||
</div>
|
||
</div>
|
||
<div>
|
||
<div class="familylinks">
|
||
<div class="parentlink"><strong>Parent topic:</strong> <a href="govrle.htm" title="The DB2 Universal Database for iSeries Predictive Query Governor can stop the initiation of a query if the estimated run time (elapsed execution time) or estimated temporary storage for the query is excessive. The governor acts before a query is run instead of while a query is run. The governor can be used in any interactive or batch job on the iSeries. It can be used with all DB2 Universal Database for iSeries query interfaces and is not limited to use with SQL queries.">Set resource limits with the Predictive Query Governor</a></div>
|
||
</div>
|
||
<div class="relinfo"><strong>Related information</strong><br />
|
||
<div><a href="../cl/prtsqlinf.htm">Print SQL Information (PRTSQLINF) command</a></div>
|
||
<div><a href="../cl/chgqrya.htm">Change Query Attributes (CHGQRYA) command</a></div>
|
||
</div>
|
||
</div>
|
||
</body>
|
||
</html> |