100 lines
6.3 KiB
HTML
100 lines
6.3 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="Determining unnecessary indexes" />
|
||
|
|
<meta name="abstract" content="You can easily determine which indexes are being used for query optimization." />
|
||
|
|
<meta name="description" content="You can easily determine which indexes are being used for query optimization." />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqindexopt.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqstartsummon.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="../apis/qusrmbrd.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="../cl/chgobjd.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="indexstats" />
|
||
|
|
<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>Determining unnecessary indexes</title>
|
||
|
|
</head>
|
||
|
|
<body id="indexstats"><a name="indexstats"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Determining unnecessary indexes</h1>
|
||
|
|
<div><p>You can easily determine which indexes are being used for query
|
||
|
|
optimization.</p>
|
||
|
|
<div class="section"><p>Before V5R3, it was difficult to determine unnecessary indexes.
|
||
|
|
Using the Last Used Date was not dependable, as it was only updated when the
|
||
|
|
logical file was opened using a native database application (for example,
|
||
|
|
in an RPG application). Furthermore, it was difficult to find all the indexes
|
||
|
|
over a physical file. Indexes are created as part of a keyed physical file,
|
||
|
|
a keyed logical file, a join logical file, an SQL index, a primary key or
|
||
|
|
unique constraint, or a referential constraint. However, you can now easily
|
||
|
|
find all indexes and retrieve statistics on index usage as a result of new
|
||
|
|
V5R3 iSeries™ Navigator
|
||
|
|
and <span class="keyword">i5/OS™</span> functionality.
|
||
|
|
To assist you in tuning your performance, this function now produces statistics
|
||
|
|
on index usage as well as index usage in a query.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>To access this through the iSeries Navigator, navigate to: <span class="menucascade"><span class="uicontrol">Database</span> > <span class="uicontrol">Schemas</span> > <span class="uicontrol">Tables</span></span>. Right-click your table and select <span class="uicontrol">Show Indexes</span></p>
|
||
|
|
<div class="note"><span class="notetitle">Note:</span> You
|
||
|
|
can also view the statistics through the Retrieve Member Description (QUSRMBRD)
|
||
|
|
API.</div>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>In addition to all existing attributes of an index, four new fields
|
||
|
|
have been added to the iSeries Navigator. Those four new fields are: </p>
|
||
|
|
<dl><dt class="dlterm"><strong>Last Query Use</strong></dt>
|
||
|
|
<dd>States the timestamp when the index was last used to retrieve data for
|
||
|
|
a query.</dd>
|
||
|
|
<dt class="dlterm"><strong>Last Query Statistic Use</strong></dt>
|
||
|
|
<dd>States the timestamp when the index was last used to provide statistical
|
||
|
|
information.</dd>
|
||
|
|
<dt class="dlterm"><strong>Query Use Count</strong></dt>
|
||
|
|
<dd>Lists the number of instances the index was used in a query.</dd>
|
||
|
|
<dt class="dlterm"><strong>Query Statistics Use</strong></dt>
|
||
|
|
<dd>Lists the number of instances the index was used for statistical information.</dd>
|
||
|
|
<dt class="dlterm">Last Used Date</dt>
|
||
|
|
<dd>The century and date this index was last used.</dd>
|
||
|
|
<dt class="dlterm">Days Used Count</dt>
|
||
|
|
<dd>The number of days the index was used. If the index does
|
||
|
|
not have a last used date, the count is 0.</dd>
|
||
|
|
<dt class="dlterm">Date Reset Days Used Count</dt>
|
||
|
|
<dd>The date that the days used count was last reset. You can reset the days
|
||
|
|
used by <span class="cmdname">Change Object Description (CHGOBJD) </span>command.</dd>
|
||
|
|
</dl>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>The fields start and stop counting based on your situation, or
|
||
|
|
the actions you are currently performing on your system. The following list
|
||
|
|
describes what might affect one or both of your counters: </p>
|
||
|
|
<ul><li>The SQE and CQE query engines increment both counters. As a result, the
|
||
|
|
statistics field will be updated regardless of which query interface is used.</li>
|
||
|
|
<li>A save and restore procedure does not reset the statistics counter if
|
||
|
|
the index is restored over an existing index. If an index is restored that
|
||
|
|
does not exist on the server, the statistics are reset.</li>
|
||
|
|
</ul>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rzajqindexopt.htm" title="Since the iSeries optimizer uses cost based optimization, the more information that the optimizer is given about the rows and columns in the database, the better able the optimizer is to create the best possible (least costly/fastest) access plan for the query. With the information from the indexes, the optimizer can make better choices about how to process the request (local selection, joins, grouping, and ordering).">Indexes and the optimizer</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relref"><strong>Related reference</strong><br />
|
||
|
|
<div><a href="rzajqstartsummon.htm" title="You can start a summary monitor from the iSeries Navigator interface.">Starting a summary monitor</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relinfo"><strong>Related information</strong><br />
|
||
|
|
<div><a href="../apis/qusrmbrd.htm">Retrieve Member Description (QUSRMBRD) API</a></div>
|
||
|
|
<div><a href="../cl/chgobjd.htm">Change Object Description (CHGOBJD) command</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|