95 lines
6.5 KiB
HTML
95 lines
6.5 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="Partitioning with DB2 Multisystem" />
|
||
|
|
<meta name="abstract" content="Partitioning is the process of distributing a file across the nodes in a node group." />
|
||
|
|
<meta name="description" content="Partitioning is the process of distributing a file across the nodes in a node group." />
|
||
|
|
<meta name="DC.subject" content="partitioning, keys, map, number, partitioning key, null-capable fields, in partitioning key" />
|
||
|
|
<meta name="keywords" content="partitioning, keys, map, number, partitioning key, null-capable fields, in partitioning key" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="crtdf.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="planpart.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="choosepart.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="nodework.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="dspng.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="chgnga.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="dspng.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="ption" />
|
||
|
|
<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>Partitioning with DB2 Multisystem</title>
|
||
|
|
</head>
|
||
|
|
<body id="ption"><a name="ption"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Partitioning with DB2 Multisystem</h1>
|
||
|
|
<div><p><em>Partitioning</em> is the process of distributing a file across
|
||
|
|
the nodes in a node group.</p>
|
||
|
|
<p>Partitioning is done using the hash algorithm. When a new record is added,
|
||
|
|
the hash algorithm is applied to the data in the partitioning key. The result
|
||
|
|
of the hash algorithm, a number between 0 and 1023, is then applied to the
|
||
|
|
partitioning map to determine the node on which the record resides.</p>
|
||
|
|
<p>The partition map is also used for query optimization, updates, deletes,
|
||
|
|
and joins. The partition map can be customized to force certain key values
|
||
|
|
to certain nodes.</p>
|
||
|
|
<p>For example, during I/O, the system applies the hash algorithm to the values
|
||
|
|
in the partitioning key fields. The result is applied to the partition map
|
||
|
|
stored in the file to determine which node stores the record.</p>
|
||
|
|
<p>The following example shows how these concepts relate to each other:</p>
|
||
|
|
<p>Employee number is the partitioning key and a record is entered into the
|
||
|
|
database for an employee number of 56 000. The value of 56 000
|
||
|
|
is processed by the hash algorithm and the result is a partition number of
|
||
|
|
733. The partition map, which is part of the node group object and is stored
|
||
|
|
in the distributed file when it is created, contains node number 1 for partition
|
||
|
|
number 733. Therefore, this record is physically stored on the system in the
|
||
|
|
node group that is assigned node number 1. The partitioning key (the PTNKEY
|
||
|
|
parameter) was specified by you when you created the partitioned (distributed)
|
||
|
|
file.</p>
|
||
|
|
<p>Fields in the partitioning key can be null-capable. However, records that
|
||
|
|
contain a null value within the partitioning key always hash to partition
|
||
|
|
number 0. Files with a significant number of null values within the partitioning
|
||
|
|
key can result in data skew on the partition number 0, because all of the
|
||
|
|
records with null values hash to partition number 0.</p>
|
||
|
|
<p>After you have created your node group object and a partitioned distributed
|
||
|
|
relational database file, you can use the DSPNODGRP command to view the relationship
|
||
|
|
between partition numbers and node names. See Displaying node groups using
|
||
|
|
the DSPNODGRP command with DB2<sup>®</sup> Multisystem for more information about displaying
|
||
|
|
partition numbers, node groups, and system names.</p>
|
||
|
|
<p>When creating a distributed file, the partitioning key fields are specified
|
||
|
|
either on the PTNKEY parameter of the Create Physical File (CRTPF) command
|
||
|
|
or in the PARTITIONING KEY clause of the SQL CREATE TABLE statement. Fields
|
||
|
|
with the data types DATE, TIME, TIMESTAMP, and FLOAT are not allowed in a
|
||
|
|
partitioning key.</p>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<ul class="ullinks">
|
||
|
|
<li class="ulchildlink"><strong><a href="planpart.htm">Plan for partitioning with DB2 Multisystem</a></strong><br />
|
||
|
|
In most cases, you should plan ahead to determine how you want to use partitioning and partitioning keys.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="choosepart.htm">Choose partitioning keys with DB2 Multisystem</a></strong><br />
|
||
|
|
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.</li>
|
||
|
|
</ul>
|
||
|
|
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="crtdf.htm" title="A distributed file is a database file that is spread across multiple iSeries servers.">Distributed files with DB2 Multisystem</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relconcepts"><strong>Related concepts</strong><br />
|
||
|
|
<div><a href="nodework.htm" title="A node group is a system object (*NODGRP), which is stored on the system on which it was created.">How node groups work with DB2 Multisystem</a></div>
|
||
|
|
<div><a href="dspng.htm" title="The Display Node Group (DSPNODGRP) command displays the nodes (systems) in a node group.">Display Node Group (DSPNODGRP) command</a></div>
|
||
|
|
<div><a href="chgnga.htm" title="The Change Node Group Attributes (CHGNODGRPA) command changes the data partitioning attributes for a node group.">Change Node Group Attributes (CHGNODGRPA) command</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|