Wednesday, April 06, 2005

A partitioned table created on an iSeries server is a database file with multiple members

Partitioned tables

Beginning in V5R3, DB2 UDB for iSeries will support partitioned tables using SQL. Partitioning allows for the data to be stored in more than one member, but the table appears as one object for data manipulation operations, such as queries, inserts, updates, and deletes. The partitions inherit the design characteristics of the table on which they are based, including the column names and types, constraints, and triggers.

Partitioning will allow you to have much more data in your tables. Without partitioning, there is a maximum of 4294967288 rows in a table, or a maximum size of 1.7 terabytes. A partitioned table, however, can have many partitions, with each partition able to have the maximum table size. For more information about maximum size for partitioned tables, refer to the DB2 UDB for iSeries White Papers .

Partitioning may also enhance the performance, recoverability, and manageability of your database. Each partition can be saved, restored, exported from, imported to, dropped, or reorganized independently of the other partitions. Additionally, partitioning allows for quickly deleting sets of records grouped in a partition, rather than processing individual rows of a non-partitioned table. Dropping a partition provides significantly better performance than deleting the same rows from a non-partitioned table.

A partitioned table created on an iSeries server is a database file with multiple members. A partition is the equivalent of a database file member. Therefore, most of the CL commands that are used for members are also valid for each partition of a partitioned table.

You must have DB2 Multisystem installed on your iSeries server in order to take advantage of partitioned tables support. There are, however, some important differences between DB2 Multisystem and partitioning. DB2 Multisystem provides two ways to partition your data:


You can create a distributed table to distribute your data across several iSeries systems or logical partitions.
You can create a partitioned table to partition your data into several members in the same database table on one system.
In both cases, you access the table as if it were not partitioned at all.

See the following topics in the this chapter for more information:


Creating partitioned tables
Altering existing tables
Using indexes with partitioned tables
Query performance and optimization
Save and restore considerations
Journaling
Native interface considerations
Restrictions

No comments:

Post a Comment