Table space will be represented as partitions. partition is a storage unit used to store the table rows. only one table can be stored for partition. each partition can contain one data set. one data set contains only one table information. Max 64 partitions can be stored for the table. NUMPART will represent the partitions during the table space creation. if NUMPART is coded during the declaration, then the table space is partitioned table space.
A partitioned table space stores data pages for a single table. Db2 divides the table space into partitions. Non-UTS table spaces for base tables are deprecated and likely to be unsupported in the future.
Non-UTS table spaces for base tables are deprecated and likely to be unsupported in the future. Convert existing partitioned spaces to partition-by-range table spaces as soon as possible, as described in Converting partitioned table spaces to partition-by-range universal table spaces.
Definition of partitioned tablespaces
If you create a table space by specifying NUMPARTS without specifying the SEGSIZE or MAXPARTITIONS options, Db2 creates a partitioned table space. The default table space SEGSIZE value is 32.
Table partitioning is a data organization scheme in which table data is divided across multiple storage objects called data partitions, based on values in one or more columns. A partitioned table can contain significantly more data than an ordinary table; yet, by taking advantage of a process known as partition elimination, queries run against partitioned tables typically execute faster and require less disk I/O than when they are run against nonpartitioned tables. In this column, I’ll show you how to create partitioned tables, and I’ll show you how a partitioned table’s definition determines where individual records are stored.
The tablespace alter option provides all the functions of the DB2 ALTER TABLESPACE command. You can also alter the tablespace name, database, partition information, and segment information. DB2 permits only certain alterations to be made to a DB2 object using the DB2 ALTER command. To make a non-DB2 supported change, the tablespace must be dropped and recreated with the specified changes. This capability is transparent to the end user. An impact analysis report can be reviewed to determine whether to implement the alteration.
The Tablespace Alter panels enable you to alter any type of DB2 tablespace (segmented, partitioned, or universal) that is supported by the version of DB2 and mode on which you are running or for which DDL is to be generated and subsequently executed. If the requested changes can be made via DB2 ALTER TABLESPACE statements, a confirmation screen appears. Accept, edit, or reject the DDL to be used to make the alterations. If the tablespace, If the requested changes cannot be made through a DB2 ALTER TABLESPACE statement, RC/Alter must be used. RC/Alter must be used if the tablespace name, database, partition information, segment information are changed.
Note − The Primary (PQTY) and Secondary (SQTY) Quantity alterations are done through RC/Alter even though a DB2 ALTER could partially do them. If an attempt is made to change these quantities with a DB2 ALTER statement, the change will not be complete in any usable way until a REORG or DROP/CREATE is performed. For this reason, PQTY and SQTY are not included in the previous list.
These changes are made by dropping and recreating the tablespace. All dependents, data, and authorizations are automatically restored and all changes are automatically propagated to any dependent object types. If the tablespace must be dropped and re-created, the message Change Requires Drop/Recreate appears when F3 (End) is pressed to process the screen. This means that RC/Alter must be used to alter the tablespace, and the RC/Alter Specification Screen displays. Provide the necessary information about the data set to which the DDL will be written. The DDL must then be executed in batch mode.
The Tablespace Alter screen allows changes to the values used to create the tablespace. It is similar to the Tablespace Create and Tablespace Template screens. If partition information is entered, change the tablespace to non-partitioned. All lines except the first are deleted. If the tablespace is changed back to a partitioned tablespace, the old partition values are returned.
The Tablespace Alter screen also allows changes to multiple partitions on one screen without having to issue separate ALTER TABLESPACE statements. To change the Free Page or Percent Free for partitions in DB2, a separate ALTER TABLESPACE command must be issued for each partition. If the tablespace is partitioned (range-partitioned or partition-by-growth universal tablespace, or LARGE tablespace), the Partitions value must be set to YES.
Edit the information as in a Tablespace Create screen. Once the changes have been made, press F3 (End) to process the alter. When altering a tablespace, the TS Type option cannot be changed from REGULAR, GROWTH, RANGE, or LARGE to LOB, or vice-versa. The tablespace can only be altered between TS Types: REGULAR, GROWTH, RANGE, and LARGE.
Characteristics of partitioned tablespaces
Partitioned table spaces have the following characteristics -
You can plan for growth. When you define a partitioned table space, Db2 usually distributes the data evenly across the partitions. Over time, the distribution of the data might become uneven as inserts and deletes occur. You can rebalance data among the partitions by redefining partition boundaries with no impact to availability. You can also add a partition to the table and to each partitioned index on the table; the new partition becomes available immediately. You can spread a large table over several Db2 storage groups or data sets. The partitions of the table do not all need to use the same storage group.
Partitioned table spaces let a utility job work on part of the data while allowing other applications to concurrently access data on other partitions. In that way, several concurrent utility jobs can, for example, load all partitions of a table space concurrently. Because you can work on part of your data, some of your operations on the data might require less time.
You can use separate jobs for mass update, delete, or insert operations instead of using one large job; each smaller job can work on a different partition. Separating the large job into several smaller jobs that run concurrently can reduce the elapsed time for the whole task. If your table space uses nonpartitioned indexes, you might need to modify the size of data sets in the indexes to avoid I/O contention among concurrently running jobs. Use the PIECESIZE parameter of the CREATE INDEX or the ALTER INDEX statement to modify the sizes of the index data sets.
Table partitioning and other organization schemes
Table partitioning in DB2 9 can be used in isolation or in combination with other data organization schemes. Each clause of the CREATE TABLE statement includes an algorithm to indicate how the data should be organized. The following three clauses demonstrate the levels of data organization that can be used together in any combination -
DISTRIBUTE BY − Spreads data evenly across database partitions. Use this clause to enable intraquery parallelism and distribute the load across each database partition. This concept is known as database partitioning and is enabled using the Database Partitioning Feature (DPF) in DB2.
PARTITION BY − Groups rows with similar values of a single dimension in the same data partition. This concept is known as table partitioning.
ORGANIZE BY − Groups rows with similar values on multiple dimensions in the same table extent. This concept is known as multidimensional clustering (MDC).
This syntax allows for consistency between the clauses as well as allowing for future algorithms of data organization. Combining the DISTRIBUTE BY and PARTITION BY clauses of the CREATE TABLE statement allows data to be spread across database partitions spanning multiple table spaces.
DB2 9 is the first data server to support all three methods of grouping data at the same time. This is a major innovation in improving data management and information availability.