147 lines
11 KiB
HTML
147 lines
11 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="Set resource limits with the Predictive Query Governor" />
|
||
<meta name="abstract" content="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." />
|
||
<meta name="description" content="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." />
|
||
<meta name="DC.subject" content="governor, Predictive Query Governor, QRYTIMLMT, CHGQRYA" />
|
||
<meta name="keywords" content="governor, Predictive Query Governor, QRYTIMLMT, CHGQRYA" />
|
||
<meta name="DC.Relation" scheme="URI" content="cqa.htm" />
|
||
<meta name="DC.Relation" scheme="URI" content="howqrygovworks.htm" />
|
||
<meta name="DC.Relation" scheme="URI" content="cancelquery.htm" />
|
||
<meta name="DC.Relation" scheme="URI" content="controldefaultreply.htm" />
|
||
<meta name="DC.Relation" scheme="URI" content="testperfwithqrygov.htm" />
|
||
<meta name="DC.Relation" scheme="URI" content="qrygovexamp.htm" />
|
||
<meta name="DC.Relation" scheme="URI" content="rzajqstoragegov.htm" />
|
||
<meta name="DC.Relation" scheme="URI" content="rzajqstoregovex.htm" />
|
||
<meta name="DC.Relation" scheme="URI" content="../apis/xqrygovr.htm" />
|
||
<meta name="DC.Relation" scheme="URI" content="../cl/endrqs.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="govrle" />
|
||
<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>Set resource limits with the Predictive Query Governor</title>
|
||
</head>
|
||
<body id="govrle"><a name="govrle"><!-- --></a>
|
||
<img src="./delta.gif" alt="Start of change" /><!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
<h1 class="topictitle1">Set resource limits with the Predictive Query Governor</h1>
|
||
<div><p>The <span class="keyword">DB2 Universal Database™ for iSeries™</span> 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 <em>before</em> a query is run instead of while a query is
|
||
run. The governor can be used in any interactive or batch job on the <span class="keyword">iSeries</span>. It can be used with all <span class="keyword">DB2 Universal Database for iSeries</span> query interfaces and is not
|
||
limited to use with SQL queries.</p>
|
||
<div class="section"><div class="p">The ability of the governor to predict and stop queries before
|
||
they are started is important because: <ul><li>Operating a long-running query and abnormally ending the query before
|
||
obtaining any results wastes server resources.</li>
|
||
<li>Some CQE operations within a query cannot be interrupted by the <span class="cmdname">End
|
||
Request (ENDRQS)</span> CL command. The creation of a temporary index or
|
||
a query using a column function without a GROUP BY clause are two examples
|
||
of these types of queries. It is important to not start these operations if
|
||
they will take longer than the user wants to wait.</li>
|
||
</ul>
|
||
</div>
|
||
</div>
|
||
<div class="section"><p>The governor in <span class="keyword">DB2 Universal Database for iSeries</span> is
|
||
based on two measurements:</p>
|
||
<ul><li>The estimated runtime for a query.</li>
|
||
<li>The estimated temporary storage consumption for a query. </li>
|
||
</ul>
|
||
</div>
|
||
<div class="section"><p> If the query’s estimated runtime or temporary storage usage exceed
|
||
the user defined limits, the initiation of the query can be stopped. </p>
|
||
<p>To
|
||
define a time limit (in seconds) for the governor to use, do one of the following: </p>
|
||
<ul><li>Use the Query Time Limit (QRYTIMLMT) parameter on the <span class="cmdname">Change
|
||
Query Attributes (CHGQRYA)</span> CL command. This is the first place where
|
||
the query optimizer attempts to find the time limit.</li>
|
||
<li>Set the Query Time Limit option in the query options file. This is the
|
||
second place where the query optimizer attempts to find the time limit.</li>
|
||
<li>Set the QQRYTIMLMT system value. Allow each job to use the value *SYSVAL
|
||
on the <span class="cmdname">Change Query Attributes (CHGQRYA)</span> CL command, and
|
||
set the query options file to *DEFAULT. This is the third place where the
|
||
query optimizer attempts to find the time limit.</li>
|
||
</ul>
|
||
<p>To define a temporary storage limit (in megabytes) for the governor
|
||
to use, do the following: </p>
|
||
<ul><li>Use the Query Storage Limit (QRYSTGLMT) parameter on the <span class="cmdname">Change
|
||
Query Attributes (CHGQRYA)</span> CL command. This is the first place where
|
||
the query optimizer attempts to find the limit. </li>
|
||
<li>Set the Query Storage Limit option STORAGE_LIMIT in the query options
|
||
file. This is the second place where the query optimizer attempts to find
|
||
the time limit.</li>
|
||
</ul>
|
||
</div>
|
||
<div class="section"><p>It is important to remember that the time and temporary storage
|
||
values generated by the optimizer are <em>only</em> estimates. The actual query
|
||
runtime might be more or less than the estimate. In certain cases when the
|
||
optimizer does not have full information about the data being queried, the
|
||
estimate may vary considerably from the actual resource used. In those case,
|
||
you may need to artificially adjust your limits to correspond to an inaccurate
|
||
estimate.</p>
|
||
<p>When setting the time limit for the entire server, it is typically
|
||
best to set the limit to the maximum allowable time that any query should
|
||
be allowed to run. By setting the limit too low you will run the risk of
|
||
preventing some queries from completing and thus preventing the application
|
||
from successfully finishing. There are many functions that use the query
|
||
component to internally perform query requests. These requests will also
|
||
be compared to the user-defined time limit.</p>
|
||
<p>You can check the inquiry
|
||
message CPA4259 for the predicted runtime and storage. If the query is canceled,
|
||
debug messages will still be written to the job log.</p>
|
||
<p>You can also add
|
||
the Query Governor Exit Program that is called when estimated runtime and
|
||
temporary storage limits have exceeded the specified limits. </p>
|
||
</div>
|
||
</div>
|
||
<div>
|
||
<ul class="ullinks">
|
||
<li class="ulchildlink"><strong><a href="howqrygovworks.htm">Using the Query Governor</a></strong><br />
|
||
The resource governor works in conjunction with the query optimizer.</li>
|
||
<li class="ulchildlink"><strong><a href="cancelquery.htm">Canceling a query with the Query Governor</a></strong><br />
|
||
When a query is expected to take more resources than the set limit, the governor issues inquiry message CPA4259.</li>
|
||
<li class="ulchildlink"><strong><a href="controldefaultreply.htm">Controlling the default reply to the query governor inquiry message</a></strong><br />
|
||
The system administrator can control whether the interactive user
|
||
has the option of ignoring the database query inquiry message by using the <span class="cmdname">Change
|
||
Job (CHGJOB)</span> CL command. </li>
|
||
<li class="ulchildlink"><strong><a href="testperfwithqrygov.htm">Testing performance with the query governor</a></strong><br />
|
||
You can use the query governor to test the performance of your queries.</li>
|
||
<li class="ulchildlink"><strong><a href="qrygovexamp.htm">Examples of setting query time limits</a></strong><br />
|
||
To set the query time limit for the current job or user session
|
||
using query options file QAQQINI, specify QRYOPTLIB parameter on the <span class="cmdname">Change
|
||
Query Attributes (CHGQRYA)</span> command to a user library where the QAQQINI
|
||
file exists with the parameter set to QUERY_TIME_LIMIT, and the value set
|
||
to a valid query time limit. </li>
|
||
<li class="ulchildlink"><strong><a href="rzajqstoragegov.htm">Testing temporary storage usage with the query governor</a></strong><br />
|
||
The predictive storage governor specifies a temporary storage limit for database queries. You can use the query governor to test if a query uses any temporary object to run the query, such as a hash table, sort or temporary index.</li>
|
||
<li class="ulchildlink"><strong><a href="rzajqstoregovex.htm">Examples of setting query temporary storage limits</a></strong><br />
|
||
The temporary storage limit can be specified either in the QAQQINI
|
||
file or on the <span class="cmdname">Change Query Attributes (CHGQRYA)</span> command. </li>
|
||
</ul>
|
||
|
||
<div class="familylinks">
|
||
<div class="parentlink"><strong>Parent topic:</strong> <a href="cqa.htm" title="You can modify different types of attributes of the queries that you will execute during a certain job with the Change Query Attributes (CHGQRYA) CL command, or by using the iSeries Navigator Change Query Attributes interface.">Change the attributes of your queries with the Change Query Attributes (CHGQRYA) command</a></div>
|
||
</div>
|
||
<div class="relinfo"><strong>Related information</strong><br />
|
||
<div><a href="../apis/xqrygovr.htm">Query Governor Exit Program</a></div>
|
||
<div><a href="../cl/endrqs.htm">End Request (ENDRQS) command</a></div>
|
||
<div><a href="../cl/chgqrya.htm">Change Query Attributes (CHGQRYA) command</a></div>
|
||
</div>
|
||
</div>
|
||
<img src="./deltaend.gif" alt="End of change" /></body>
|
||
</html> |