Adding a partition to a Table Controlled Partitioning (TCP) table using RC/Update
Article ID: 25867
Updated On:
Products
Issue/Introduction
Trying to add a partition using the ALTER TABLESPACE command generates a strategy in which the NUMPARTS parameter displays the correct
number of partitions but the CREATE TABLE statement does not display the new partition and its' limit value using RC/Update for Db2 for z/OS (RCU).
Resolution
Adding a partition to a Table Controlled Partitioning table must be done using the ALTER TABLE command and not the ALTER TABLESPACE command.
RQTL --------------- RC/Q Table List --------------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> T Option ===> L Where => N
Table Name ===> TABLE_UTS_PBR7% > Creator ===> authid1 >
Qualifier ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: ssid ----------authid1 - LINE 1 OF 1 >
CMD TABLE NAME CREATOR DATABASE TBLSPACE COL_CNT NUMBER OF ROWS
ALTER___ TABLE_UTS_PBR7_RE> authid1 xxxxxxxx xxxxxxxx 12 N/A
******************************* BOTTOM OF DATA ********************************
ROPTBAL ------------------- Table Alter ------------------ yyyy/mm/dd mm:mm:ss
COMMAND ===> LIMITS 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 => xxxxxxxx Editproc => Data Cap => NONE
Tablespace => xxxxxxxx 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
When using the Table Alter screen, enter the LIMITS command and the Table Partitioning and Limit Key Values screen will display. Enter the I (Insert)
command on the CMND column and then provide the limit value for the key column for the new partition. This will generate the required Alter Table command.
ROPTLIM1 ----- Table Partitioning & Limit Key Values ---- yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Modify your Partitioning and Limit Key Values. A constant must be present for
the first key column of each partition. Unspecified columns will be def-
aulted by DB2. Enter END after you have completed your changes.
------------------------------------------- Maintenance Mode => PARTITIONS
CMND PART POL_NBR POL_EFF_DT MODU_NBR ACTY_SEQ_NBR
CMND PART EMP_NO
____ 1 AAAAAAAAA
____ 2 BBBBBBBBB
____ 3 CCCCCCCCC
____ 4 DDDDDDDDD
____ 5 EEEEEEEEE
____ 6 FFFFFFFFF
i___ 7 GGGGGGGGG
******************************* BOTTOM OF DATA *******************************
CMND PART EMP_NO
____ 1 AAAAAAAAA
____ 2 BBBBBBBBB
____ 3 CCCCCCCCC
____ 4 DDDDDDDDD
____ 5 EEEEEEEEE
____ 6 FFFFFFFFF
____ 7 GGGGGGGGG
____ 8 HHHHHHHHH <-------------new one added.
Add the part limit keys required and PF3 and then PF3 out of the Table Alter screen too.
The result is:
-- authid1.TABLE_UTS_PBR7_RELATIVE_ADDR WILL BE ALTERED VIA THESE NATIVE
-- DB2 COMMANDS.
------------------------------------------------------------------------
-- Warning... --
------------------------------------------------------------------------
ALTER TABLE authid1.TABLE_UTS_PBR7_RELATIVE_ADDR
ADD PARTITION
ENDING AT ( 'HHHHHHHHH' )
;
Note: You may have to enter command ‘Partitions’ to change maintenance mode of screen to PARTITIONS.
Maintenance Mode can be PARTITIONS or MAXVALUES or VALUES ONLY.
Use the online help with a ? and then another ? on the PARTITIONS , MAXVALUES or VALUES commands to find out how they work.
Additional Information
Feedback
