100 lines
6.0 KiB
HTML
100 lines
6.0 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="Creation of partitioned tables" />
|
||
|
|
<meta name="abstract" content="New partitioned tables can be created using the CREATE TABLE statement." />
|
||
|
|
<meta name="description" content="New partitioned tables can be created using the CREATE TABLE statement." />
|
||
|
|
<meta name="DC.subject" content="Partitioned tables, creating" />
|
||
|
|
<meta name="keywords" content="Partitioned tables, creating" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="partitionedtables.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="../db2/rbafzmsthctabl.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="addpart.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzaf3ptcheck.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="creatept" />
|
||
|
|
<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>Creation of partitioned tables</title>
|
||
|
|
</head>
|
||
|
|
<body id="creatept"><a name="creatept"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Creation of partitioned tables</h1>
|
||
|
|
<div><p>New partitioned tables can be created using the <samp class="codeph">CREATE
|
||
|
|
TABLE</samp> statement.</p>
|
||
|
|
<p>The table definition must include the table name and the names and attributes
|
||
|
|
of the columns in the table. The definition might also include other attributes
|
||
|
|
of the table, such as the primary key.</p>
|
||
|
|
<p>There are two methods available for partitioning: hash
|
||
|
|
partitioning and range partitioning. Hash partitioning places rows at random
|
||
|
|
intervals across a user-specified number of partitions and key columns. Range
|
||
|
|
partitioning divides the table based on user-specified ranges of column values.
|
||
|
|
Specify the type of partitioning you want to use with the <samp class="codeph">PARTITION
|
||
|
|
BY</samp> clause. For example, to partition table PAYROLL in library PRODLIB
|
||
|
|
with partitioning key EMPNUM into four partitions, use the following code:</p>
|
||
|
|
<pre>CREATE TABLE PRODLIB.PAYROLL
|
||
|
|
(EMPNUM INT,
|
||
|
|
FIRSTNAME CHAR(15),
|
||
|
|
LASTNAME CHAR(15),
|
||
|
|
SALARY INT)
|
||
|
|
PARTITION BY HASH(EMPNUM)
|
||
|
|
INTO 4 PARTITIONS</pre>
|
||
|
|
<p>Or, to partition PAYROLL by range, use the following code:</p>
|
||
|
|
<pre>CREATE TABLE PRODLIB.PAYROLL
|
||
|
|
(EMPNUM INT,
|
||
|
|
FIRSTNAME CHAR(15),
|
||
|
|
LASTNAME CHAR(15),
|
||
|
|
SALARY INT)
|
||
|
|
PARTITION BY RANGE(EMPNUM)
|
||
|
|
(STARTING FROM (MINVALUE) ENDING AT (500) INCLUSIVE,
|
||
|
|
STARTING FROM (501) ENDING AT (1000) INCLUSIVE,
|
||
|
|
STARTING FROM (1001) ENDING AT (MAXVALUE)</pre>
|
||
|
|
<p>This statement results in a table that contains three partitions. The first
|
||
|
|
partition contains all rows where EMPNUM is less than or equal to 500. The
|
||
|
|
second partition contains all rows where EMPNUM is between 501 and 1000 inclusive.
|
||
|
|
The third partition contains all rows where EMPNUM is greater than or equal
|
||
|
|
to 1001. The following figure shows a table with data partitioned according
|
||
|
|
to these values.</p>
|
||
|
|
<div class="fignone"><span class="figcap">Figure 1. Employee information partitioned</span></div>
|
||
|
|
<br /><img src="rbaf3501.gif" alt="This image shows a table of employee information partitioned by range " /><br /><p>When a partitioned table is created, a system-supplied check constraint
|
||
|
|
is added to each partition. This check constraint cannot be displayed, altered,
|
||
|
|
or removed by the user.</p>
|
||
|
|
<p>For range partitioning, this check constraint validates that the data is
|
||
|
|
in the proper range. Or, if the partition allows null values, the check constraint
|
||
|
|
validates that the data is null.</p>
|
||
|
|
<p>For hash partitioning, this check constraint validates that the data based
|
||
|
|
on the condition <samp class="codeph">Partition number = MOD(Hash(fields), Number of partitions)
|
||
|
|
+ 1</samp> where the Hash function returns a value between 0 and 1023. The
|
||
|
|
null values are always placed in the first partition.</p>
|
||
|
|
<p>See the CREATE TABLE statement in the SQL Reference topic for partitioning
|
||
|
|
clauses and syntax diagrams.</p>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="partitionedtables.htm" title="DB2 UDB for iSeries supports partitioned tables using SQL.">Partitioned tables</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relconcepts"><strong>Related concepts</strong><br />
|
||
|
|
<div><a href="addpart.htm" title="Use the ADD partitioning-clause of the ALTER TABLE statement to change a nonpartitioned table into a partitioned table. Altering an existing table to use partitions is similar to creating a new partitioned table.">From a nonpartitioned table to a partitioned table</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="reltasks"><strong>Related tasks</strong><br />
|
||
|
|
<div><a href="../db2/rbafzmsthctabl.htm">CREATE TABLE</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relref"><strong>Related reference</strong><br />
|
||
|
|
<div><a href="rzaf3ptcheck.htm" title="The optimizer uses check constraints (either user-added or the implicit check constraints added for the partition key) to reduce the partitions examined.">Check constraint optimization</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|