Partitioning

Partitioning allows for efficient use of DBMS utilities and placement of rows on different direct-access storage devices.
gen85
5463
Partitioning a tablespace is the process of dividing a table by rows into partitions that
Gen
can manage.
Creating Partitioned Tablespaces
Example
To create a partitioned tablespace, the database administrator must detail a tablespace or partition. During DDL generation, the following SQL language is built:
CREATE TABLESPACE CUSTBS IN DACUSDB USING STOGROUP DACUSSTG PRIQTY 500000 SECQTY 4 ERASE NO NUMPARTS 10 (PART 1 USING STOGROUP DANAMSTG PRIQTY 1000000 SECQTY 8, PART 2 USINAG STOGROUP DANAMSTG PRIQTY 1000000 SECQTY 8) ... LOCKSIZE PAGE BUFFERPOOL BPO CLOSE NO;
The only difference between a simple tablespace and a partitioned tablespace is the NUMPARTS parameter. Use NUMPARTS to establish the partitions. Each partition has its own data set. An index on the table, divides the partitions and manages rows.
After naming the tablespace (CUSTBS) and specifying its database (DACUSDB), the database administrator should specify the default storage group or VCAT. This storage group or VCAT assignment determines the placement of partitions not already assigned. This example has 10 partitions. The default storage group (DACUSSTG) contains partitions 3 through 10. Storage group DANAMSTG contains partitions 1 and 2.
Tablespace CUSTBS CUSTNUM TABLE PARTITION 1 >>> VSAM DATA SET 1 CUSTNUM TABLE PARTITION 2 >>> VSAM DATA SET 2 ... ... CUSTNUM TABLE PARTITION 10 >>> VSAM DATA SET 10
A partitioned tablespace requires a clustering index.
Creating Clustering Index
To create a clustering index, the database administrator must detail an indexspace or partition. During DDL generation, the following SQL language is built.
CREATE INDEX CUSTIDX ON DACUSDB.TCUST (CUSTNUM) SUBPAGES 4 CLUSTER (PART 1 VALUES(1000000) USING STOGROUP DANAMSTG PART 2 VALUES(1000000) USING STOGROUP DANAMSTG ... PART 10 VALUES(1000000) USING STOGROUP DAN10STG) BUFFERPOOL BPO CLOSE NO;
After naming the index (CUSTIDX) and specifying the index table (DACUSDB.TCUST), the database administrator should specify the index values. The CLUSTER parameter determines this index is clustered. The information in parentheses assigns the customer values to each index partition and the row values corresponding to the tablespace partition. Values can be partitioned in descending (DESC) or ascending (ASCEND) order.
The CREATE INDEX structure specifies the column that provides the index values and ranges of values for each partition row. The index is partitioned the same as the tablespace. This CREATE INDEX statement assigns each index partition to a VSAM data set in a storage group or VCAT. When loading tablespaces, the rows containing the value of each partition are placed in each corresponding tablespace partition.
Indexspace CUSTIDX CUSTIDX TABLE PARTITION 1 >>> VSAM DATA SET 90 CUSTIDX TABLE PARTITION 2 >>> VSAM DATA SET 91 ... ... CUSTIDX TABLE PARTITION 10 >>> VSAM DATA SET 100
Data Store List Detail Partition Actions
The following actions are in the Data Store List Detail Partitions:
Diagram
Saves the model, prints diagrams, details printer setup, and exits the diagram.
Edit
Adds and deletes selected partitions.
View
Controls how the database administrator displays the Data Store List Detail Partition diagram.
Detail
Specifies properties of tablespaces, indexes, indexspaces, and partitions.
Window
Arranges the display of all open secondary windows, maximizes the width and height of the active window, staggers and indents all open windows, and divides the work area into halves or fourths to display each open window in a part of the screen.
Options
Customizes user interface for multiple or single object add, changing fonts, and customizing pop-up menus.
Help
Describes general and specific help, explains contents and tasks that are performed in the application window, lists key assignments, and lists all help topics in alphabetical order.
Clustering Indexspace
A partition must have a clustering indexspace. Clustering stores related tables physically close together on the disk. Related tables are often used together; therefore, physical data clustering improves the performance of the data set.
During DDL generation, partitioning information is created for both the tablespace and indexspace in the following format.
Create Index
using and free block information (default)
(parent tablespace and indexspace detail)
PARTITION 1 Value / Key
"
"
"
PARTITION n Value / Key
using and free block information (optional)
(tablespace and indexspace detail)
Defaults For Tablespace And Indexspace Properties
You can specify dataset properties for tablespaces and indexspaces. The properties that you set can apply to all partitions, or the properties can apply only to the selected tablespace partitions or indexspace partitions.
If you want the tablespace properties to apply only to the selected tablespace partitions, access the Tablespace Properties dialog box and select the radio button Do not use values as default.
If you want the tablespace properties to apply to one or more partitions, access the Tablespace Properties dialog box and select the radio button Use following values as defaults.
The indexspace properties can be designated in the same manner. Use the Indexspace Properties dialog box and select one of the following radio buttons: Do not use values as defaults, or Use following values as defaults.
When the radio button Do not use values as defaults is selected, properties must be set up for each partition that is added.
When the radio button Use following values as defaults is selected, a partition can be set up with or without the default values.
To use the default tablespace values, access the Partition Tablespace Properties dialog box and select the radio button Use default values for partition.
To set up unique tablespace values for the partition, select the radio button Use following values for partition.
The indexspace properties can be designated in the same manner. Use the Partition Indexspace Properties dialog box and select one of the following radio buttons: Use default values for partition, or Use following values for partition.
A consistency check error occurs when no default values are set for a tablespace or indexspace, and the partition properties dialog box is set to use the default values.
For example, the consistency check error occurs when Do not use values as default, is selected in the Indexspace Properties dialog box and Use default values for partitioning is selected in the Partitioning Indexspace Properties dialog box.
Calculating Partition Size
The number of partitions determines the maximum partition size.
Partitions
Maximum Size in Gigabytes
1 to 16
4
17 to 32
2
33 to 64
1
The partition table size is the largest addressable number of bytes, not the actual number of bytes used or allocated. Each partition occupies one data set.
For example, a DBMS allows up to 64 1-gigabyte partitions, each in its own data set. Partitions can be as large as 4 GB each, but the total tablespace cannot exceed 64 GB.
Partitioning Tablespaces
Database administrators should consider partitioning any table with 1 million rows or more. For smaller tables, the advantages are probably not worth the effort that is needed to create the partitions. Use partitioning to allocate free space.
Partitioning places table rows that are accessed often on fast, direct-access storage devices. It also places table rows that are seldom accessed on slower devices. Customers collect and maintain recent data more often than the historical data. Therefore, when partitioning, the database administrator might move rows to a slower device as their information ages.
Partitioning Benefits
When using few indexspaces on the tablespace, the benefits of partitioning increases. When using many indexspaces on a tablespace, partitioning results in inefficient processing. An indexspace that contains the (required) clustering index for a partitioned tablespace is partitioned and requires minimal maintenance.
In Toolset, click Tools, Design, Data Structure List. A list of tables is displayed. Tables with partition are labeled as Has Partitioning.