RC/Update : Table Partitioning Key Column maintenance on a Table Controlled Partitioning Table(TCP)
Article ID: 278254
Updated On:
Products
Issue/Introduction
The Column Selection & Key Maintenance screen can be used to maintain and and arrange the table partitioning columns being used by a Table.
The same Column Selection & Key Maintenance screen is available for use both on the "Table Partitioning Key Column Selection & Maintenance" and "Index Column Selection & Key Maintenance" screen.
Environment
DB2 for Z/OS
Release : R20
Resolution
The KEY command displays the "Table Partitioning Key Column Selection & Maintenance" screen. This screen allows for the creation, viewing or modifying of partitioning keys on the current Table.
If the Partitioning setting on the Create/Alter screen is set to NO, entering this command for a table whose tablespace is partitioned causes the Partitioning option to be automatically set to YES. If the tablespace is unknown or not partitioned, this command is not allowed unless this Partitioning setting is set to YES. This then would initiate the process of converting the tablespace/table to be a Table Controlled Partitioning(TCP) table.
Upon initiating an ALTER command on the table this screen can be seen when the table is in fact a TCP.
ROPTBAL ------------------- Table Alter ------------------ yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Option => A Object => T Mode => O ONLINE
Item Name => TABLE_UTS_PBR7_REL > Creator => authid1 > Where => N
SSID: ssid ------------------------------------------------------ authid1 >
Table => TABLE_UTS_PBR7_REL > Creator => authid1 > Comm/Lab => N
Database => DBPBRRPN Editproc => Data Cap => NONE
Tablespace => PBRRPN Validproc => OBID =>
Partitioning > YES (TS Parts: 7) Audit => NONE Volatile => N
Table Type => REGULAR Restrict => N CCSID => EBCDIC
Row Size => 1,158/-31,548 Forgn Key => N Chk Const > N
Append => N
CMD ### PS COLUMN NAME COLUMN TYPE SIZE N D FORDAT PK UK FK
___ 1 1 EMP_NO CHAR 9 N _ MIXED 1
___ 2 EMP_AGE SMALLINT 2 N _ _____ __ Y
___ 3 EMP_INT1 INTEGER 4 N _ _____ __
___ 4 EMP_INT2 INTEGER 4 N _ _____ __
___ 5 EMP_INT3 INTEGER 4 N _ _____ __
___ 6 EMP_INT4 INTEGER 4 N _ _____ __
___ 7 EMP_INT5 INTEGER 4 N _ _____ __
___ 8 EMP_INT6 INTEGER 4 N _ _____ __
___ 9 EMP_BIGINT BIGINT 8 N _ _____ __
___ 10 EMP_QUITDATE V9MIX_UDTTSTAMP 6 Y N _____ __
___ 11 EMP_LSTRING VARCHAR 1000 N _ MIXED __
___ 12 SQL_DESC VARCHAR 100 N _ MIXED __
******************************* BOTTOM OF DATA ********************************
On the screen above PARTITIONING is set to YES showing the number of parts in use. Down on the column list the PS column shows '1' next to the EMP_NO column.
PS is the Partitioning (key) Sequence of the column in the table's partitioning key, showing its numeric position within the key when the table is defined in a partitioned tablespace and it is using table-controlled partitioning.
If the column is not part of a partitioning key or if the table is not currently using table-controlled partitioning, the column will be blank. It is a display only field!!
Enter the KEY command in the COMMAND field.
ROPTBAL ------------------- Table Alter ------------------ yyyy/mm/dd hh:mm:ss
COMMAND ===> KEY SCROLL ===> CSR
Option => A Object => T Mode => O ONLINE
Item Name => TABLE_UTS_PBR7_REL > Creator => authid1 > Where => N
SSID: ssid ------------------------------------------------------ authid1 >
Table => TABLE_UTS_PBR7_REL > Creator => authid1 > Comm/Lab => N
Database => DBPBRRPN Editproc => Data Cap => NONE
Tablespace => PBRRPN Validproc => OBID =>
Partitioning > YES (TS Parts: 7) Audit => NONE Volatile => N
Table Type => REGULAR Restrict => N CCSID => EBCDIC
Row Size => 1,158/-31,548 Forgn Key => N Chk Const > N
Append => N
CMD ### PS COLUMN NAME COLUMN TYPE SIZE N D FORDAT PK UK FK
___ 1 1 EMP_NO CHAR 9 N _ MIXED 1
___ 2 EMP_AGE SMALLINT 2 N _ _____ __ Y
___ 3 EMP_INT1 INTEGER 4 N _ _____ __
___ 4 EMP_INT2 INTEGER 4 N _ _____ __
___ 5 EMP_INT3 INTEGER 4 N _ _____ __
___ 6 EMP_INT4 INTEGER 4 N _ _____ __
___ 7 EMP_INT5 INTEGER 4 N _ _____ __
___ 8 EMP_INT6 INTEGER 4 N _ _____ __
___ 9 EMP_BIGINT BIGINT 8 N _ _____ __
___ 10 EMP_QUITDATE V9MIX_UDTTSTAMP 6 Y N _____ __
___ 11 EMP_LSTRING VARCHAR 1000 N _ MIXED __
___ 12 SQL_DESC VARCHAR 100 N _ MIXED __
******************************* BOTTOM OF DATA ********************************
The next screen displayed is the Table Partitioning Key Col Selection & Maint screen. It shows a list of the available columns for partitioning in the first section and in the second section it shows the existing column currently in use by partitioning namely EMP_NO.
Note that the top section and the bottom section of the screen are both independently scrollable using PF7/PF8.
ROPTCOL1 - Table Partitioning Key Col Selection & Maint -- yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Select the columns you wish to insert by placing an S beside the column name.
You may optionally specify a block of columns by appending a number after
the S or by using the SS block command. Press HELP for more information.
Column Name Selection -------------------------- Unused Cols List - Hide ==> N
CMD COL# Column Name COLTYPE SRCTYPE SIZE N
___ 2 EMP_AGE SMALLINT 2 N
___ 3 EMP_INT1 INTEGER 4 N
___ 4 EMP_INT2 INTEGER 4 N
___ 5 EMP_INT3 INTEGER 4 N
___ 6 EMP_INT4 INTEGER 4 N
___ 7 EMP_INT5 INTEGER 4 N
___ 8 EMP_INT6 INTEGER 4 N
___ 9 EMP_BIGINT BIGINT 8 N
___ 10 EMP_QUITDATE V9MIX_UDTTSTAMP TIMESTMP 6 Y
___ 11 EMP_LSTRING VARCHAR 1000 N
___ 12 SQL_DESC VARCHAR 100 N
******************************* BOTTOM OF DATA ********************************
Arrange the columns in the key and set the ORDER of each column to either ASC
(ascending) or DESC (descending). Press PF3 when you are finished.
Table Partitioning Key --------------------------------------------------------
CMD SEQ# KEY-COLUMN-NAME ORDER COLTYPE SRCTYPE SIZE N
___ 1 EMP_NO ASC CHAR 9 N
******************************* BOTTOM OF DATA ********************************
Columns can be selected in order to be included into the table partitioning or the index key for an index object from the columns that are presented in the list at the top of the screen by using the S, Snn or SS line-commands. This first list contains all of the columns belonging to the table/index object that have not yet been selected for the key and that are also eligible for selection.
On the screen above "Unused Cols List" can be seen in the header indicating that this is a list of eligible columns.
Using the L primary command on this screen toggles between the two different screens showing the FULL list and the SELECTABLE list of columns.
The second list at the bottom of the screen displays the columns that compose the existing key if any......in this example only EMP_NO is currently in use. In this second list, indicate where the selected columns are to be inserted using the A or B line commands. Upon pressing the ENTER key, the columns that have been selected are removed from the first list at the top of the screen and they are automatically inserted into the key at the point marked with either A or B in the second list at the bottom of the screen..........just like ISPF does with the Move or Copy commands.
After the columns of the table key have been selected , further modifications can be made regarding the arrangement of the key columns as desired. Unwanted ones can be deleted or moved around using the M (Move) and D (Delete) line commands. The line commands that are available for use in the second list are: A, B, Dnn, Mnn, Snn, DD, MM, and SS.
Use of the HEADER command on the primary command line allows for toggling of the header portions of both lists from ON to OFF in order to provide additional space on the
screen for both lists.
Setting the Hide option, located in the upper right corner of the Column Name Selection List to Y will hide that list so as to provide the most space on the screen for the Table Partitioning or Index Key List. This makes it easier to manage larger and more complex compound keys.
The default ORDER is always ASC (ascending) when selecting or inserting new key-columns so this should be reviewed and set according to requirements. .
ROPTCOL1 - Table Partitioning Key Col Selection & Maint -- yyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Select the columns you wish to insert by placing an S beside the column name.
You may optionally specify a block of columns by appending a number after
the S or by using the SS block command. Press HELP for more information.
Column Name Selection -------------------------- Unused Cols List - Hide ==> N
CMD COL# Column Name COLTYPE SRCTYPE SIZE N
___ 3 EMP_INT1 INTEGER 4 N
___ 4 EMP_INT2 INTEGER 4 N
___ 5 EMP_INT3 INTEGER 4 N
___ 6 EMP_INT4 INTEGER 4 N
___ 7 EMP_INT5 INTEGER 4 N
___ 8 EMP_INT6 INTEGER 4 N
___ 9 EMP_BIGINT BIGINT 8 N
___ 11 EMP_LSTRING VARCHAR 1000 N
___ 12 SQL_DESC VARCHAR 100 N
******************************* BOTTOM OF DATA ********************************
Arrange the columns in the key and set the ORDER of each column to either ASC
(ascending) or DESC (descending). Press PF3 when you are finished.
Table Partitioning Key --------------------------------------------------------
CMD SEQ# KEY-COLUMN-NAME ORDER COLTYPE SRCTYPE SIZE N
___ 1 EMP_NO ASC CHAR 9 N
___ 2 EMP_AGE ASC SMALLINT 2 N
In the example above two new columns have been added to the key after EMP_NO by using the "S" command on the top list. The EMP_AGE and EMP_QUITDATE although EMP_QUITDATE can't be seen until PF8 with the cursor placed in this section on the column name line is used to scroll to it....
Table Partitioning Key --------------------------------------------------------
CMD SEQ# KEY-COLUMN-NAME ORDER COLTYPE SRCTYPE SIZE N
___ 3 EMP_QUITDATE ASC V9MIX_UDTTSTAMP TIMESTMP 10 Y
******************************* BOTTOM OF DATA ********************************
After the changes have been made, PF3 back to the ALTER TABLE screen....
ROPTBAL ------------------- Table Alter ------------------ yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Option => A Object => T Mode => O ONLINE
Item Name => TABLE_UTS_PBR7_REL > Creator => authid1 > Where => N
SSID: ssid ------------------------------------------------------ authid1 >
Table => TABLE_UTS_PBR7_REL > Creator => authid1 > Comm/Lab => N
Database => DBPBRRPN Editproc => Data Cap => NONE
Tablespace => PBRRPN Validproc => OBID =>
Partitioning > YES (TS Parts: 7) Audit => NONE Volatile => N
Table Type => REGULAR Restrict => N CCSID => EBCDIC
Row Size => 1,158/-31,548 Forgn Key => N Chk Const > N
Append => N
CMD ### PS COLUMN NAME COLUMN TYPE SIZE N D FORDAT PK UK FK
___ 1 1 EMP_NO CHAR 9 N _ MIXED 1
___ 2 2 EMP_AGE SMALLINT 2 N _ _____ __ Y
___ 3 EMP_INT1 INTEGER 4 N _ _____ __
___ 4 EMP_INT2 INTEGER 4 N _ _____ __
___ 5 EMP_INT3 INTEGER 4 N _ _____ __
___ 6 EMP_INT4 INTEGER 4 N _ _____ __
___ 7 EMP_INT5 INTEGER 4 N _ _____ __
___ 8 EMP_INT6 INTEGER 4 N _ _____ __
___ 9 EMP_BIGINT BIGINT 8 N _ _____ __
___ 10 3 EMP_QUITDATE V9MIX_UDTTSTAMP 6 Y N _____ __
___ 11 EMP_LSTRING VARCHAR 1000 N _ MIXED __
___ 12 SQL_DESC VARCHAR 100 N _ MIXED __
******************************* BOTTOM OF DATA ********************************
Now, viewing the screen above looking at the PS column , two new key columns can be seen added to sequence 2 and 3 in the key.
The last task to be done now for the two new key columns is to add a Limit Key Value using the LIMITS command on the above screen.
The LIMITS command displays the Table Partitioning & Limit Key Values screen.
Available functions:
1. Scroll through all partitions and their limit values
2. View the relationships between each partition's limit values
3. Make modifications as needed, including adding and deleting partitions, rotating partitions, and making modifications to each partition's limit values.
In summary, the KEY command manages the columns of the partitioning key and the LIMITS command manages the actual partitions and their limits keys.
Additional Information
Feedback
