141 lines
8.1 KiB
HTML
141 lines
8.1 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="concept" />
|
|
<meta name="DC.Title" content="Optimizer messages with DB2 Multisystem" />
|
|
<meta name="abstract" content="The i5/OS distributed query optimizer provides you with information messages on the current query processing when the job is in debug mode." />
|
|
<meta name="description" content="The i5/OS distributed query optimizer provides you with information messages on the current query processing when the job is in debug mode." />
|
|
<meta name="DC.subject" content="message, optimizer, messages" />
|
|
<meta name="keywords" content="message, optimizer, messages" />
|
|
<meta name="DC.Relation" scheme="URI" content="qqp.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="debugms" />
|
|
<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>Optimizer messages with DB2 Multisystem</title>
|
|
</head>
|
|
<body id="debugms"><a name="debugms"><!-- --></a>
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
|
<h1 class="topictitle1">Optimizer messages with DB2 Multisystem</h1>
|
|
<div><p>The i5/OS™ distributed
|
|
query optimizer provides you with information messages on the current query
|
|
processing when the job is in debug mode.</p>
|
|
<p>These messages, which show how the distributed query was processed, are
|
|
in addition to the existing optimizer messages. These messages appear for
|
|
the Open Query File (OPNQRYF) command, <span class="keyword">DB2<sup>®</sup> UDB Query Manager and SQL Development Kit</span>,
|
|
interactive SQL, embedded SQL, and in any <span class="keyword">iSeries™</span> server
|
|
high-level language (HLL). Every message appears in the job log; you only
|
|
need to put your job into debug mode.</p>
|
|
<p>You can evaluate the performance of your distributed query by using the
|
|
informational messages put in the job log by the database manager. The database
|
|
manager can send any of the following distributed messages or existing optimizer
|
|
messages when appropriate. The ampersand variables <em>(&1, &X)</em> are
|
|
replacement variables that contain either an object name or another substitution
|
|
value when the message appears in the job log:</p>
|
|
<ul><li><tt class="msgph">CPI4341 Performing distributed query.</tt></li>
|
|
<li><tt class="msgph">CPI4342 Performing distributed join for query.</tt></li>
|
|
<li><tt>CPI4343 Optimizer debug messages for distributed query step <em>&1</em> of <em>&2</em>.</tt></li>
|
|
<li><tt>CPI4345 Temporary distributed result file <em>&4</em> built for
|
|
query.</tt></li>
|
|
</ul>
|
|
<p>These messages provide feedback on how a distributed query was run and,
|
|
in some cases, indicate the improvements that can be made to help the query
|
|
run faster. The causes and user responses for the messages are paraphrased
|
|
below. The actual message help is more complete and should be used when trying
|
|
to determine the meaning and responses for each message.</p>
|
|
<p>A detailed explanation for each message follows:</p>
|
|
<dl><dt class="dlterm"><tt>CPI4341</tt></dt>
|
|
<dd><tt class="msgph">Performing distributed query.</tt> <p>This message indicates
|
|
that a single distributed file was queried and was not processed in multiple
|
|
steps. This message lists the nodes of the file where the query was run.</p>
|
|
</dd>
|
|
<dt class="dlterm"><tt>CPI4342</tt></dt>
|
|
<dd><tt class="msgph">Performing distributed join for query.</tt> <p>This message
|
|
indicates that a distributed join occurred. This message also lists the nodes
|
|
where the join was run as well as the files that were joined together.</p>
|
|
</dd>
|
|
<dt class="dlterm"><tt>CPI4343</tt></dt>
|
|
<dd><tt>Optimizer debug messages for distributed query step <em>&1</em> of <em>&2</em></tt>.
|
|
<p>This message indicates that a distributed query was processed in multiple
|
|
steps and lists the current step number. Following this message are all the
|
|
optimizer messages for that step.</p>
|
|
</dd>
|
|
<dt class="dlterm"><tt>CPI4345</tt></dt>
|
|
<dd><tt>Temporary distributed result file &4 built for query.</tt> <p>This
|
|
message indicates that a temporary distributed result file was created and
|
|
lists a reason code as to why the temporary file was required. This message
|
|
also shows the partitioning key that was used to create the file and the nodes
|
|
that the temporary file was created on.</p>
|
|
</dd>
|
|
</dl>
|
|
<div class="p">The following example shows you how to look at the distributed optimizer
|
|
messages that were generated to determine how the distributed query was processed.
|
|
The example uses the distributed files, EMPLOYEE and DEPARTMENT. <pre>SQL: SELECT A.EMPNO, B.MGRNO, C.MGRNO, D.EMPNO
|
|
FROM EMPLOYEE A, DEPARTMENT B, DEPARTMENT C, EMPLOYEE D
|
|
WHERE A.EMPNO=B.MGRNO
|
|
AND B.ADMRDEPT=C.DEPTNO
|
|
AND C.DEPTNO=D.WORKDEPT
|
|
|
|
OPNQRYF: OPNQRYF FILE((EMPLOYEE) (DEPARTMENT) (DEPARTMENT) (EMPLOYEE))
|
|
FORMAT(JFMT)
|
|
JFLD((1/EMNO 2/MGRNO *EQ)
|
|
(2/ADMRDEPT 3/DEPTNO)
|
|
(3/DEPTNO 4/WORKDEPT))</pre>
|
|
</div>
|
|
<p>The following list of distributed optimizer messages is
|
|
generated:</p>
|
|
<ul><li><tt>CPI4343 Optimizer debug messages for distributed query step <em>&1</em> of <em>&4</em> </tt>follow:
|
|
<ul><li><tt>CPI4345 Temporary distributed result file *QQTDF0001 built for query.</tt> <p>File
|
|
B was directed into temporary file *QQTDF0001.</p>
|
|
</li>
|
|
</ul>
|
|
</li>
|
|
<li><tt>CPI4343 Optimizer debug messages for distributed query step <em>&2</em> of <em>&4</em> </tt>follow:
|
|
<ul><li><tt>CPI4342 Performing distributed join for query.</tt> <p>Files B, C
|
|
and *QQTDF0001 were joined. This was a combination of a collocated join (between
|
|
files B and C) and a directed join (with file *QQTDF0001).</p>
|
|
</li>
|
|
<li><tt>CPI4345 Temporary distributed result file *QQTDF0002 built for query.</tt> <p>Temporary
|
|
distributed file *QQTDF0002 was created to contain the result of joining files
|
|
B, C and *QQTDF0001. This file was directed.</p>
|
|
</li>
|
|
</ul>
|
|
</li>
|
|
<li><tt>CPI4343 Optimizer debug messages for distributed query step <em>&3</em> of <em>&4</em></tt> follow:
|
|
<ul><li><tt>CPI4345 Temporary distributed result file *QQTDF0003 built for query.</tt> <p>File
|
|
A was directed into temporary file *QQTDF0003.</p>
|
|
</li>
|
|
</ul>
|
|
</li>
|
|
<li><tt>CPI4343 Optimizer debug messages for distributed query step &4
|
|
of &4</tt> follow: <ul><li><tt>CPI4342 Performing distributed join for query.</tt> <p>Files *QQTDF0002
|
|
and *QQTDF0003 were joined. This was a repartitioned join, because both files
|
|
were directed before the join occurred.</p>
|
|
</li>
|
|
</ul>
|
|
</li>
|
|
</ul>
|
|
<p>Additional tools that you might want to use when tuning queries for performance
|
|
include the CL commands Print SQL Information (PRTSQLINF), which applies to
|
|
SQL programs and packages, and Change Query Attributes (CHGQRYA).</p>
|
|
</div>
|
|
<div>
|
|
<div class="familylinks">
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="qqp.htm" title="This topic provides you with some guidelines for designing queries so that they use query resources more efficiently when you run queries that use distributed files.">Query design for performance with DB2 Multisystem</a></div>
|
|
</div>
|
|
</div>
|
|
</body>
|
|
</html> |