friedkiwi/ibm-information-center
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
master
ibm-information-center/dist/eclipse/plugins/i5OS.ic.rzajq_5.4.0.1/qryopt.htm

667 lines
43 KiB
HTML
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

<?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="QAQQINI query options" />
<meta name="abstract" content="There are different options available for parameters in the QAQQINI file." />
<meta name="description" content="There are different options available for parameters in the QAQQINI file." />
<meta name="DC.subject" content="query options file, changing, file, query options, query options, QAQQINI, command (CL)" />
<meta name="keywords" content="query options file, changing, file, query options, query options, QAQQINI, command (CL)" />
<meta name="DC.Relation" scheme="URI" content="qryoptf.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="qryopt" />
<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>QAQQINI query options</title>
</head>
<body id="qryopt"><a name="qryopt"><!-- --></a>
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
<h1 class="topictitle1">QAQQINI query options</h1>
<div><p>There are different options available for parameters in the QAQQINI
file.</p>
<div class="section"><p>The following table summarizes the query options that can be specified
on the QAQQINI command:</p>
</div>
<div class="tablenoborder"><img src="./delta.gif" alt="Start of change" /><a name="qryopt__qryoptable"><!-- --></a><table cellpadding="4" cellspacing="0" summary="" id="qryopt__qryoptable" width="100%" frame="border" border="1" rules="all"><caption>Table 1. Query
Options Specified on QAQQINI Command</caption><thead align="left"><tr><th align="center" valign="middle" width="31.90255220417633%" id="d0e47">Parameter</th>
<th align="center" valign="middle" width="15.19721577726218%" id="d0e49">Value</th>
<th align="center" valign="middle" width="52.90023201856149%" id="d0e51">Description</th>
</tr>
</thead>
<tbody><tr><td rowspan="3" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>ALLOW_TEMPORARY_INDEXES</p>
</td>
<td valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">The default value is set to *YES.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*YES</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">Allow temporary indexes to be considered.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*ONLY_ REQUIRED</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">Do not allow any temporary indexes to be considered
for this access plan. Choose any other implementation regardless of cost to
avoid the creation of a temporary index. Only if no viable plan can be found,
will a temporary index be allowed. </td>
</tr>
<tr><td rowspan="3" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>APPLY_REMOTE</p>
</td>
<td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The default value is set to *YES.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*NO</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The <span class="cmdname">CHGQRYA</span> attributes
for the job are not applied to the remote jobs. The remote jobs will use the
attributes associated to them on their servers.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*YES</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The query attributes for the job are applied
to the remote jobs used in processing database queries involving distributed
tables. For attributes where *SYSVAL is specified, the system value on the
remote server is used for the remote job. This option requires that, if <span class="cmdname">CHGQRYA</span> was
used for this job, the remote jobs must have authority to use the <span class="cmdname">CHGQRYA</span> command.</td>
</tr>
<tr><td rowspan="5" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>ASYNC_JOB_USAGE</p>
</td>
<td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The default value is set to *LOCAL.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*LOCAL</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">Asynchronous jobs may be used for database
queries that involve only tables local to the server where the database queries
are being run. In addition, for queries involving distributed tables, this
option allows the communications required to be asynchronous. This allows
each server involved in the query of the distributed tables to run its portion
of the query at the same time (in parallel) as the other servers.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*DIST</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">Asynchronous jobs may be used for database
queries that involve distributed tables.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*ANY</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">Asynchronous jobs may be used for any database
query.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*NONE</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">No asynchronous jobs are allowed to be used
for database query processing. In addition, all processing for queries involving
distributed tables occurs synchronously. Therefore, no inter-system parallel
processing will occur.</td>
</tr>
<tr><td rowspan="4" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>CACHE_RESULTS</p>
</td>
<td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The default value is the same as *SYSTEM.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*SYSTEM</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The database manager may cache a query result
set. A subsequent run of the query by that job or, if the ODP for the query
has been deleted, by any job, will consider reusing the cached result set.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*JOB</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The database manager may cache a query result
set from one run to the next for a job, as long as the query uses a reusable
ODP. When the reusable ODP is deleted, the cached result set is destroyed.
This value mimics V5R2 processing.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*NONE</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The database does not cache any query results.</td>
</tr>
<tr><td rowspan="2" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>COMMITMENT_CONTROL_<br />
LOCK_LIMIT</p>
</td>
<td valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">*DEFAULT is equivalent to 500,000,000.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">Integer Value</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">The maximum number of records that can be locked to
a commit transaction initiated after setting the new value. The valid integer
value is 1–500,000,000.</td>
</tr>
<tr><td rowspan="5" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>FORCE_JOIN_ORDER</p>
</td>
<td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The default is set to *NO.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*NO</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">Allow the optimizer to reorder join tables.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*SQL</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">Only force the join order for those queries that use
the SQL JOIN syntax. This mimics the behavior for the optimizer before V4R4M0.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*PRIMARY nnn</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">Only force the join position for the file listed by
the numeric value nnn (nnn is optional and will default to 1) into the primary
position (or dial) for the join. The optimizer will then determine the join
order for all of the remaining files based upon cost.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*YES</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">Do not allow the query optimizer to reorder
join tables as part of its optimization process. The join will occur in the
order in which the tables were specified in the query.</td>
</tr>
<tr><td rowspan="3" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>IGNORE_DERIVED_INDEX</p>
</td>
<td valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">The default value is the same as *NO.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*YES</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">Allow the SQE optimizer to ignore the derived index
and process the query. The resulting query plan will be created without any
regard to the existence of the derived index(s). The index types that are
ignored include: <ul><li>Keyed logical files defined with select or omit criteria and with the
DYNSLT keyword omitted</li>
<li>Keyed logical files built over multiple physical file members (V5R2 restriction,
not a restriction for V5R3)</li>
<li>Keyed logical files where one or more keys reference an intermediate derivation
in the DDS. Exceptions to this are: 1. when the intermediate definition is
defining the field in the DDS so that shows up in the logical's format and
2. RENAME of a field (these two exceptions do not make the key derived)</li>
<li>Keyed logical files with K *NONE specified. </li>
<li>Keyed logical files with Alternate Collating Sequence (ACS) specified</li>
<li>SQL indexes created when the sort sequence active at the time of creation
requires a weighting (translation) of the key to occur. This is true when
any of several non-US language IDs are specified. It also occurs if language
ID shared weight is specified, even for language US.</li>
</ul>
</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*NO</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">Do not ignore the derived index. If a derived index
exists, have CQE process the query. </td>
</tr>
<tr><td rowspan="3" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>IGNORE_LIKE_<br />
REDUNDANT_SHIFTS</p>
</td>
<td valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">The default value is set to *OPTIMIZE.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*ALWAYS</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">When processing the SQL LIKE predicate or <span class="cmdname">OPNQRYF</span> command
%WLDCRD built-in function, redundant shift characters are ignored for DBCS-Open
operands. Note that this option restricts the query optimizer from using an
index to perform key row positioning for SQL LIKE or <span class="cmdname">OPNQRYF</span> %WLDCRD
predicates involving DBCS-Open, DBCS-Either, or DBCS-Only operands.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*OPTIMIZE</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">When processing the SQL LIKE predicate or the <span class="cmdname">OPNQRYF</span> command
%WLDCRD built-in function, redundant shift characters may or may not be ignored
for DBCS-Open operands depending on whether an index is used to perform key
row positioning for these predicates. Note that this option will enable the
query optimizer to consider key row positioning for SQL LIKE or <span class="cmdname">OPNQRYF</span> %WLDCRD
predicates involving DBCS-Open, DBCS-Either, or DBCS-Only operands.</td>
</tr>
<tr><td rowspan="3" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>LIMIT_PREDICATE_ <br />
OPTIMIZATION</p>
</td>
<td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">Do not eliminate the predicates that are
not simple isolatable predicates (OIF) when doing index optimization. Same
as *NO.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*NO</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">Do not eliminate the predicates that are
not simple isolatable predicates (OIF) when doing index optimization.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*YES</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">Eliminate the predicates that are not simple
isolatable predicates (OIF) when doing index optimization.</td>
</tr>
<tr><td rowspan="2" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>LOB_LOCATOR_THRESHOLD</p>
</td>
<td valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">The default value is set to 0. This indicates that the
database will take no action to free locators.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">Integer Value</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">If the value is 0, then the database will take no action
to free locators. For values 1 through 250,000, on a FETCH request, the database
will compare the active LOB locator count for the job against the threshold
value. If the locator count is greater than or equal to the threshold, the
database will free host server created locators that have been retrieved.
This option applies to all host server jobs (QZDASOINIT) and has no impact
to other jobs.</td>
</tr>
<tr><td rowspan="4" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>MATERIALIZED_QUERY_ <br />
TABLE_REFRESH_AGE</p>
</td>
<td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The default value is set to 0.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">0</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">No materialized query tables may be used.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*ANY</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">Any tables indicated by the MATERIALIZED_
QUERY_TABLE_USAGE INI parameter may be used.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">timestamp_ duration</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">Only tables indicated by MATERIALIZED_ QUERY_TABLE_USAGE
INI option which have a REFRESH TABLE performed within the specified timestamp
duration may be used.</td>
</tr>
<tr><td rowspan="4" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>MATERIALIZED_QUERY_ <br />
TABLE_USAGE</p>
</td>
<td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The default value is set to *NONE.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*NONE</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">Materialized query tables may not be used
in query optimization and implementation.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*ALL</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">User-maintained materialized query tables
may be used.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*USER</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">User-maintained materialized query tables
may be used.</td>
</tr>
<tr><td rowspan="3" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>MESSAGES_DEBUG</p>
</td>
<td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The default is set to *NO.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*NO</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">No debug messages are to be displayed.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*YES</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">Issue all debug messages that are generated
for <span class="cmdname">STRDBG</span>.</td>
</tr>
<tr><td rowspan="3" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>NORMALIZE_DATA</p>
</td>
<td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The default is set to *NO.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*NO</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">Unicode constants, host variables, parameter
markers, and expressions that combine strings will not be normalized.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*YES</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">Unicode constants, host variables, parameter
markers, and expressions that combine strings will be normalized</td>
</tr>
<tr><td rowspan="2" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>OPEN_CURSOR_CLOSE_<br />
COUNT</p>
</td>
<td valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">*DEFAULT is equivalent to 0. See Integer Value for details.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">Integer Value</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">OPEN_CURSOR_CLOSE_COUNT is used in conjunction with
OPEN_CURSOR_THRESHOLD to manage the number of open cursors within a job. If
the number of open cursors, which includes open cursors and pseudo-closed
cursors, reaches the value specified by the OPEN_CURSOR_THRESHOLD, pseudo-closed
cursors are hard (fully) closed with the least recently used cursors being
closed first. This value determines the number of cursors to be closed. The
valid values for this parameter are 1 to 65536. The value for this parameter
should be less than or equal to the number in the OPEN_CURSOR_THREHOLD parameter.
This value is ignored if OPEN_CURSOR_THRESHOLD is *DEFAULT. If OPEN_CURSOR_THRESHOLD
is specified and this value is *DEFAULT, the number of cursors closed is equal
to OPEN_CURSOR_THRESHOLD multiplied by 10 percent and rounded up to the next
integer value.</td>
</tr>
<tr><td rowspan="2" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>OPEN_CURSOR_<br />
THRESHOLD</p>
</td>
<td valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">*DEFAULT is equivalent to 0. See Integer Value for details.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">Integer Value</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">OPEN_CURSOR_THRESHOLD is used in conjunction with OPEN_CURSOR_CLOSE_COUNT
to manage the number of open cursors within a job. If the number of open cursors,
which includes open cursors and pseudo-closed cursors, reaches this threshold
value, pseudo-closed cursors are hard (fully) closed with the least recently
used cursors being closed first. The number of cursors to be closed is determined
by OPEN_CURSOR_CLOSE_COUNT. The valid user-entered values for this parameter
are 1 - 65536. Having a value of 0 (default value) indicates that there is
no threshold and hard closes will not be forced on the basis of the number
of open cursors within a job.</td>
</tr>
<tr><td rowspan="3" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>OPTIMIZATION_GOAL</p>
</td>
<td valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">Optimization goal is determined by the interface (ODBC,
SQL precompiler options, OPTIMIZE FOR nnn ROWS clause).</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*FIRSTIO</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">All queries will be optimized with the goal of returning
the first page of output as fast as possible. This goal works well when the
control of the output is controlled by a user who is most likely to cancel
the query after viewing the first page of output data. Queries coded with
an OPTIMIZE FOR nnn ROWS clause will honor the goal specified by the clause.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*ALLIO</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">All queries will be optimized with the goal of running
the entire query to completion in the shortest amount of elapsed time. This
is a good option for when the output of a query is being written to a file
or report, or the interface is queuing the output data. Queries coded with
an OPTIMIZE FOR nnn ROWS clause will honor the goal specified by the clause.</td>
</tr>
<tr><td rowspan="4" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>OPTIMIZE_STATISTIC_<br />
LIMITATION</p>
</td>
<td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The amount of time spent in gathering index
statistics is determined by the query optimizer.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*NO</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">No index statistics will be gathered by
the query optimizer. Default statistics will be used for optimization. (Use
this option sparingly.)</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*PERCENTAGE integer value</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">Specifies the maximum percentage of the index
that will be searched while gathering statistics. Valid values for are 1 to
99.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*MAX_ NUMBER_ OF_RECORDS_ ALLOWED integer
value</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">Specifies the largest table size, in number
of rows, for which gathering statistics is allowed. For tables with more rows
than the specified value, the optimizer will not gather statistics and will
use default values.</td>
</tr>
<tr><td rowspan="8" align="left" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>PARALLEL_DEGREE</p>
</td>
<td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The default value is set to *SYSVAL.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*SYSVAL</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The processing option used is set to the
current value of the system value, QQRYDEGREE.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*IO</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">Any number of tasks can be used when the
database query optimizer chooses to use I/O parallel processing for queries.
SMP parallel processing is not allowed.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*OPTIMIZE</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The query optimizer can choose to use any
number of tasks for either I/O or SMP parallel processing to process the query
or database file keyed access path build, rebuild, or maintenance. SMP parallel
processing is used only if the system feature, DB2<sup>®</sup> Symmetric Multiprocessing for <span class="keyword">i5/OS™</span>, is installed. Use of parallel
processing and the number of tasks used is determined with respect to the
number of processors available in the server, this job has a share of the
amount of active memory available in the pool in which the job is run, and
whether the expected elapsed time for the query or database file keyed access
path build or rebuild is limited by CPU processing or I/O resources. The query
optimizer chooses an implementation that minimizes elapsed time based on
the job has a share of the memory in the pool.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*OPTIMIZE xxx</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">This option is very similar to *OPTIMIZE.
The value xxx indicates the ability to specify an integer percentage value
from 1-200. The query optimizer determines the parallel degree for the query
using the same processing as is done for *OPTIMIZE, Once determined, the optimizer
will adjust the actual parallel degree used for the query by the percentage
given. This provides the user the ability to override the parallel degree
used to some extent without having to specify a particular parallel degree
under *NUMBER_OF_TASKS.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*MAX</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The query optimizer chooses to use either
I/O or SMP parallel processing to process the query. SMP parallel processing
will only be used if the system feature, DB2 Symmetric Multiprocessing for <span class="keyword">i5/OS</span>, is installed. The choices
made by the query optimizer are similar to those made for parameter value
*OPTIMIZE except the optimizer assumes that all active memory in the pool
can be used to process the query or database file keyed access path build,
rebuild, or maintenance.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*NONE</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">No parallel processing is allowed for database
query processing or database table index build, rebuild, or maintenance.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*NUMBER_OF _TASKS nn</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">Indicates the maximum number of tasks that can be used
for a single query. The number of tasks will be capped off at either this
value or the number of disk arms associated with the table.</td>
</tr>
<tr><td rowspan="3" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>PARAMETER_MARKER_<br />
CONVERSION</p>
</td>
<td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The default value is set to *YES.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*NO</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">Constants cannot be implemented as parameter
markers.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*YES</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">Constants can be implemented as parameter
markers.</td>
</tr>
<tr><td rowspan="4" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>QUERY_TIME_LIMIT</p>
</td>
<td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The default value is set to *SYSVAL.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*SYSVAL</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The query time limit for this job will be
obtained from the system value, QQRYTIMLMT.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*NOMAX</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">There is no maximum number of estimated elapsed
seconds.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">integer value</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">Specifies the maximum value that is checked
against the estimated number of elapsed seconds required to run a query. If
the estimated elapsed seconds is greater than this value, the query is not
started. Valid values range from 0 to 2,147,352,578.</td>
</tr>
<tr><td rowspan="5" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>REOPTIMIZE_ACCESS_PLAN</p>
</td>
<td valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">The default value is set to *NO.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*NO</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">Do not force the existing query to be reoptimized. However,
if the optimizer determines that optimization is necessary, the query will
be reoptimized.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*YES</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">Force the existing query to be reoptimized.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*FORCE</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">Force the existing query to be reoptimized.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*ONLY_ REQUIRED</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">Do not allow the plan to be reoptimized for any subjective
reasons. For these cases, continue to use the existing plan since it is still
a valid workable plan. This may mean that you may not get all of the performance
benefits that a reoptimization plan may derive. Subjective reasons include,
file size changes, new indexes, and so on. Non-subjective reasons include,
deletion of an index used by existing access plan, query file being deleted
and recreated, and so on.</td>
</tr>
<tr><td rowspan="3" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>SQLSTANDARDS_MIXED_<br />
CONSTANT</p>
</td>
<td valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">The default value is set to *YES.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*YES</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">SQL IGC constants will be treated as IGC-OPEN constants.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*NO</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">If the data in the IGC constant only contains shift-out
DBCS-data shift-in, then the constant will be treated as IGC-ONLY, otherwise
it will be treated as IGC-OPEN.</td>
</tr>
<tr><td rowspan="4" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>SQL_FAST_DELETE_ROW_COUNT</p>
</td>
<td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The default value is set to 0. <p></p>
Having
a value of 0 indicates that the database manager will choose how many rows
to consider when determining whether fast delete should be used instead
of a traditional delete. When using the default value, the database manager
will most likely use 1000 as a row count. This means that using the INI option
with a value of 1000 result in no operational difference than
using 0 for the option.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*NONE</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">This value will force the database manager
to never attempt to fast delete on the rows.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*OPTIMIZE</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">This value is same as using *DEFAULT.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">integer value</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">Specifying a value for this option allows
the user to tune the behavior of DELETE. The target table for the DELETE
statement must match or exceed the number of rows specified on the option,
for fast delete to be attempted. A fast delete will not write individual
rows into a journal. The valid values range from 1 to 999,999,999,999,999.</td>
</tr>
<tr><td rowspan="2" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>SQL_STMT_COMPRESS_MAX</p>
</td>
<td valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">The default value is set to 2, which indicates that
the access plan associated with any statement will be removed after a statement
has been compressed twice without being executed. </td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">Integer Value</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">The integer value represents the number of times that
a statement is compressed before the access plan is removed to create more
space in the package. Note that executing the SQL statement resets the count
for that statement to 0. The valid Integer values are 1 to 255.</td>
</tr>
<tr><td rowspan="3" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>SQL_SUPPRESS_WARNINGS</p>
</td>
<td valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">The default value is set to *NO.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*YES</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">Examine the SQLCODE in the SQLCA after execution of
a statement. If the SQLCODE &gt; 0, then alter the SQLCA so that no warning
is returned to the caller. <p></p>
Set the SQLCODE to 0, the SQLSTATE to '00000'
and SQLWARN to ' '.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*NO</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">Specifies that SQL warnings will be returned to the
caller.</td>
</tr>
<tr><td rowspan="3" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>SQL_TRANSLATE_ASCII_<br />
TO_JOB</p>
</td>
<td valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">The default value is set to *NO.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*YES</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">Translate ASCII SQL statement text to the CCSID of the iSeries™ job.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*NO</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">Translate ASCII SQL statement text to the EBCIDIC CCSID
associated with the ASCII CCSID.</td>
</tr>
<tr><td rowspan="3" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>STAR_JOIN (see note)</p>
</td>
<td valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">The default value is set to *NO</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*NO</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">The EVI Star Join optimization support is not enabled.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*COST</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">Allow query optimization to consider (cost) the usage
of EVI Star Join support. <p></p>
The determination of whether the Distinct
List selection is used will be determined by the optimizer based on how much
benefit can be derived from using that selection.</td>
</tr>
<tr><td rowspan="3" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>STORAGE_LIMIT</p>
</td>
<td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The default value is set to *NOMAX.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*NOMAX</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">Never stop a query from running because of
storage concerns.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">Integer Value</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The maximum amount of temporary storage in
megabytes that may be used by a query. This value is checked against the estimated
amount of temporary storage required to run the query as calculated by the
query optimizer. If the estimated amount of temporary storage is greater than
this value, the query is not started. Valid values range from 0 through 2147352578.</td>
</tr>
<tr><td rowspan="3" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>SYSTEM_SQL_STATEMENT_<br />
CACHE</p>
</td>
<td valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">The default value is set to *YES.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*YES</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">Examine the SQL system-wide statement cache when an
SQL prepare request is processed. If a matching statement already exists in
the cache, use the results of that prepare. This allows the application to
potentially have better performing prepares.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*NO</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">Specifies that the SQL system-wide statement cache should
not be examined when processing an SQL prepare request.</td>
</tr>
<tr><td rowspan="3" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>UDF_TIME_OUT (see note)</p>
</td>
<td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The amount of time to wait is determined
by the database. The default is 30 seconds.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">*MAX</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">The maximum amount of time that the database
will wait for the UDF to finish.</td>
</tr>
<tr><td align="left" valign="middle" width="15.19721577726218%" headers="d0e49 ">integer value</td>
<td align="left" valign="top" width="52.90023201856149%" headers="d0e51 ">Specify the number of seconds that the database
should wait for a UDF to finish. If the value given exceeds the database maximum
wait time, the maximum wait time will be used by the database. Minimum value
is 1 and maximum value is system defined.</td>
</tr>
<tr><td rowspan="3" valign="middle" width="31.90255220417633%" headers="d0e47 "> <p>VARIABLE_LENGTH_<br />
OPTIMIZATION</p>
</td>
<td valign="middle" width="15.19721577726218%" headers="d0e49 ">*DEFAULT</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">The default value is set to *YES.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*YES</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">Allow aggressive optimization of variable length columns.
Allows index only access for the column(s). It also allows constant value
substitution when an equal predicate is present against the column(s). As
a consequence, the length of the data returned for the variable length column
may not include any trailing blanks that existed in the original data.</td>
</tr>
<tr><td valign="middle" width="15.19721577726218%" headers="d0e49 ">*NO</td>
<td valign="top" width="52.90023201856149%" headers="d0e51 ">Do not allow aggressive optimization of variable length
columns.</td>
</tr>
</tbody>
</table>
<img src="./deltaend.gif" alt="End of change" /></div>
<div class="section"> <div class="note"><span class="notetitle">Note:</span> Only modifies the environment for the Classic Query Engine.</div>
</div>
</div>
<div>
<div class="familylinks">
<div class="parentlink"><strong>Parent topic:</strong> <a href="qryoptf.htm" title="The query options file QAQQINI support provides the ability to dynamically modify or override the environment in which queries are executed through the Change Query Attributes (CHGQRYA) command and the QAQQINI file. The query options file QAQQINI is used to set some attributes used by the database manager.">Control queries dynamically with the query options file QAQQINI</a></div>
</div>
</div>
</body>
</html>
Reference in New Issue View Git Blame Copy Permalink