120 lines
9.7 KiB
HTML
120 lines
9.7 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="Query design for performance with DB2 Multisystem" />
|
||
|
|
<meta name="abstract" content="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." />
|
||
|
|
<meta name="description" content="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." />
|
||
|
|
<meta name="DC.subject" content="query, performance" />
|
||
|
|
<meta name="keywords" content="query, performance" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzaf3kickoff.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="optov.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="sfquery.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rcdorder.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="union.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="dstdta.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="joins.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="grouping.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="subquery.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="access.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="odp.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="tempresult.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="debugms.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="chgqrya.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="summary.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="choosepart.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="../db2/rbafzmst02.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="../sqlp/rbafykickoff.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="../dbp/rbafokickoff.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="../rbam6/rbam6clmain.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="qqp" />
|
||
|
|
<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>Query design for performance with DB2 Multisystem</title>
|
||
|
|
</head>
|
||
|
|
<body id="qqp"><a name="qqp"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Query design for performance with DB2<sup>®</sup> Multisystem</h1>
|
||
|
|
<div><p>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.</p>
|
||
|
|
<p>This topic also discusses how queries that use distributed files are implemented.
|
||
|
|
This information can be used to tune queries so that they run more efficiently
|
||
|
|
in a distributed environment.</p>
|
||
|
|
<p>Distributed files can be queried using SQL, the Open Query File (OPNQRYF)
|
||
|
|
command, or any query interface on the system. The queries can be single file
|
||
|
|
queries or join queries. You can use a combination of distributed and local
|
||
|
|
files in a join.</p>
|
||
|
|
<p>This chapter assumes that you are familiar with running and optimizing
|
||
|
|
queries in a nondistributed environment. If you want more information about these
|
||
|
|
topics:</p>
|
||
|
|
<ul><li>SQL users should refer to SQL Reference and SQL Programming Concepts.</li>
|
||
|
|
<li>Non-SQL users should refer to Database Programming and the CL Reference
|
||
|
|
topic.</li>
|
||
|
|
</ul>
|
||
|
|
<p>This chapter also shows you how to improve the performance of distributed
|
||
|
|
queries by exploiting parallelism and minimizing data movement.</p>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<ul class="ullinks">
|
||
|
|
<li class="ulchildlink"><strong><a href="optov.htm">Optimization overview with DB2 Multisystem</a></strong><br />
|
||
|
|
Distributed queries are optimized at the distributed level and the local level.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="sfquery.htm">Implementation and optimization of a single file query with DB2 Multisystem</a></strong><br />
|
||
|
|
To do a single file query, the system where the query was specified, the coordinator node, determines the nodes of the file to which to send the query. Those nodes run the query and return the queried records to the coordinator node.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rcdorder.htm">Implementation and optimization of record ordering with DB2 Multisystem</a></strong><br />
|
||
|
|
When ordering is specified on a query, the ordering criteria is sent along with the query, so that each node can perform the ordering in parallel. Whether a final merge or a sort is performed on the coordinator node is dependent on the type of query that you specify.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="union.htm">Implementation and optimization of the UNION and DISTINCT clauses with DB2 Multisystem</a></strong><br />
|
||
|
|
If a unioned SELECT statement refers to a distributed file, the statement is processed as a distributed query.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="dstdta.htm">Processing of the DSTDTA and ALWCPYDTA parameters with DB2 Multisystem</a></strong><br />
|
||
|
|
The allow copy data (ALWCPYDTA) parameter can change the value specified for the distribute data (DSTDTA) parameter of the Override Database File (OVRDBF) command.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="joins.htm">Implementation and optimization of join operations with DB2 Multisystem</a></strong><br />
|
||
|
|
In addition to the performance considerations for nondistributed join queries, more performance considerations exist for queries involving distributed files.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="grouping.htm">Implementation and optimization of grouping with DB2 Multisystem</a></strong><br />
|
||
|
|
The implementation method for grouping in queries that use distributed files is dependent on whether the partitioning key is included in the grouping criteria.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="subquery.htm">Subquery support with DB2 Multisystem</a></strong><br />
|
||
|
|
Distributed files can be specified in subqueries.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="access.htm">Access plans with DB2 Multisystem</a></strong><br />
|
||
|
|
The access plans stored for queries that refer to distributed files are different from the access plans stored for local files.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="odp.htm">Reusable open data paths with DB2 Multisystem</a></strong><br />
|
||
|
|
Reusable open data paths (ODPs) have special considerations for distributed queries. Like most other aspects of distributed queries, ODPs have two levels: distributed and local.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="tempresult.htm">Temporary result writer with DB2 Multisystem</a></strong><br />
|
||
|
|
Temporary result writers are system-initiated jobs that are always active.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="debugms.htm">Optimizer messages with DB2 Multisystem</a></strong><br />
|
||
|
|
The i5/OS™ distributed
|
||
|
|
query optimizer provides you with information messages on the current query
|
||
|
|
processing when the job is in debug mode.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="chgqrya.htm">Changes to the change query attributes (CHGQRYA) command with DB2 Multisystem</a></strong><br />
|
||
|
|
The CHGQRYA command has two parameters that are applicable to distributed queries.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="summary.htm">Summary of performance considerations</a></strong><br />
|
||
|
|
There are performance factors you should consider when developing queries that use distributed files.</li>
|
||
|
|
</ul>
|
||
|
|
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rzaf3kickoff.htm" title="This topic describes the fundamental concepts of DB2 Multisystem, such as distributed relational database files, node groups, and partitioning, and provides the information necessary to create and to use database files that are partitioned across multiple iSeries servers. Information is provided on how to configure the systems, how to create the files, and how the files can be used in applications. This topic also describes table partitioning. Table partitioning varies from multisystem partitioning in that it is a table partitioned on a single server.">DB2 Multisystem</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relconcepts"><strong>Related concepts</strong><br />
|
||
|
|
<div><a href="choosepart.htm" title="For the system to process the partitioned file in the most efficient manner, there are some tips you can consider when setting up or using a partitioning key.">Choose partitioning keys with DB2 Multisystem</a></div>
|
||
|
|
<div><a href="../sqlp/rbafykickoff.htm">SQL Programming Concepts</a></div>
|
||
|
|
<div><a href="../dbp/rbafokickoff.htm">Database Programming</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relref"><strong>Related reference</strong><br />
|
||
|
|
<div><a href="../db2/rbafzmst02.htm">SQL Reference</a></div>
|
||
|
|
<div><a href="../rbam6/rbam6clmain.htm">CL Reference</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|