Create a DB2 z/OS Table Partition › Add Columns to a Range Table Partition

Add Columns to a Range Table Partition

In a range table partition, the data in a table is divided into storage units depending upon the values of one or more table columns. Range partitions are referred to by numbers. The partition numbers are automatically assigned as each range is created.

A column or a set of columns can determine the rows of a table that will belong to a particular partition. For example, Date of Joining is the column to be used for dividing the table into table partition, all the rows of a table that belong to a particular year (say 2012) or month (say January) can belong to a single range partition. The rows where the Date of Joining is a different year or month will belong to a different range partition. Using Employee ID is another example. All employees with IDs in the range of 1000 to 10000 belong to one partition, 10001 to 20000 belong to the next partition, and so on. Similarly, for Employee Name, employees with the names starting with the alphabet A to the alphabet C belong to one range partition and the alphabet D to the alphabet F belong to another range partition.

Range table partition can be used in situations where ranges of data are added or removed from tables frequently. Ranges of data are added or removed in data warehousing environments where roll-in or roll-out operations are performed frequently.

To create a range partition, add one or more table columns to define the table partition. The CA Gen DB2 z/OS Table Partitioning Plug-in lets a maximum of three table columns in a range table partition.

Follow these steps:

1. Select the Range partition type in the CA Gen DB2 z/OS Table Partitioning Plug-in dialog.

   This option is selected by default.

2. Click Add Columns.

   The Add Table Partitioning Columns dialog opens. The columns that are not already part of the table partition are displayed in the Add Table Partitioning Columns dialog.

3. (Optional) To modify the sort order and the null value position for each table column, select the column and click Modify.

   The Modify Table Partitioning Column Values dialog opens. The Modify Table Partitioning Column Values dialog displays the name of the table column and contains the following fields:

   Direction

   Specifies the direction of sort order. You can select Ascending or Descending.

   Nulls Last

   Specifies the position of null value. By default, the null values are sorted first. Select this option if null values must be sorted last.

   Click OK to save and close the dialog. Else, click Cancel to exit the dialog.

4. To add columns, select up to three table columns from the list and click Add.

   The dialog closes and the columns are added to the partition. To exit this dialog without adding a column click Cancel.

5. (Optional) Select Append to indicate that the APPEND YES clause must be added to the generated DDL.
6. Click OK.

   The changes are saved in the model and the CA Gen DB2 z/OS Table Partitioning Plug-in is closed.

Note: Click Cancel to close the dialog without saving any changes in the data model. Clear All clears the table columns selected for a range table partition or the size specified for a size table partition.