Partitioned tables

By using SQL, Db2® for i supports partitioned tables.

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.

With partitioning, you can have much more data in your tables. Without partitioning, there is a maximum of 4 294 967 288 rows in a table, or a maximum size of 1.7 TB (where TB equals 1 099 511 627 776 bytes). A partitioned table, however, can have many partitions, with each partition being able to have the maximum table size. For more information about the maximum size for partitioned tables, refer to the Db2 for i White Papers.

Partitioning can 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 nonpartitioned table. Dropping a partition provides significantly better performance than deleting the same rows from a nonpartitioned table.

A partitioned table is a database file with multiple members. A partitioned table 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 system 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 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.