Altering table spaces
You can make changes to table spaces or partitions, such as changing the name or lock size or converting a table space to be a partitioned. Depending on the change that you want to make, use either the AL or ALT line command.
About this task
Use the AL line command to make table space changes that are supported by the ALTER TABLESPACE statement, such as changing the lock size or the maximum number of partitions.
Use the ALT line command to make table space changes that are not supported by the ALTER TABLESPACE statement, such as changing a non-partitioned table space to a partitioned table space. When you alter a table space with the ALT command, the table space is said to be redefined.
Altering a table space by using the AL line command
Procedure
To alter a table space by using the AL line command:
-
On the Alter Table Space (ADB21SA)
panel, change any
table space or partition attributes, and press Enter.
For partitioned table spaces, a detail line is displayed for each partition. You can alter any partition by updating an attribute, such as FP. To apply the same change to all partitions within the table space, provide a value on the All Part row.
ADB21SA n -------------------- DD1A Alter Table Space -------- Row 1 to 5 of 5 Command ===> Scroll ===> PAGE Line commands: D - Display Database I - Interpret ALTER TABLESPACE : DSN8DD1A.DSN8SD1E (PBR - Partition by Range) Buffer Pool . . . . BP0 Close Rule . . . NO Max Rows . . 255 Lock Size . . . . . PAGE Lock Part . . . . NO Lock Max . . SYSTEM Max Partitions . . . LOG . . . . . . . YES Insert Algo . 0 SEGSIZE . . . . . . 32 MEMBER CLUSTER . NO PAGENUM . . . E T S S Part Pqty Sqty FP PF PFU Cmp R M T VCAT Stogroup GBPCach DSSIZE * * * * * * * * * * * * * * - ------ <------- <------ --- -- --- --- - - - -------- -------> ------- ------ All Part -1 -1 0 5 0 YES N Y I DD1A DSN8GD10 CHANGED 1 -1 -1 0 5 0 YES N Y I DD1A DSN8GD10 CHANGED 2 -1 -1 0 5 0 YES N Y I DD1A DSN8GD10 CHANGED 3 -1 -1 0 5 0 NO N Y I DD1A DSN8GD10 CHANGED 4 -1 -1 0 5 0 YES N Y I DD1A DSN8GD10 CHANGED 5 -1 -1 0 5 0 YES N Y I DD1A DSN8GD10 CHANGED
Note:Insert
Algo is displayed only if you are running Db2 12 for z/OS®.If the statement execution prompt is not enabled, an SQL ALTER TABLESPACE statement is executed with the parameters that you specified. If the Statement Execution Prompt (ADB2PSTM) panel is displayed, follow the instructions on that panel to complete and run the SQL statement to alter the table space.
For changes to some parameters, such as PAGENUM, these changes are not immediately materialized; they are pending. You must reorganize the object for the change to take affect. For more information about which changes are pending, see Pending data definition changes (Db2 12 for z/OS).
For other parameter changes, you must stop and restart the associated object. In these cases, Db2 Admin Tool issues a STOP DATABASE command for the table space, index, or partition. If the object is stopped, Db2 Admin Tool executes the ALTER TABLESPACE statement with the parameters that you specified and then a START DATABASE command to restart the stopped object. If the object is not in a fully-stopped state after Db2 Admin Tool issued the STOP DATABASE command, the STOP Check - Action panel prompts you to for the action to take:
DB2 ADMIN ------------------- DD1A STOP Check - Action ----- Row 1 to 11 of 15 Option ===> Scroll ===> PAGE Object is not in a fully-stopped state (STATUS field has STOP), and must be in order for the pending actions to be successful. The current USE information is displayed below. What do you want to do now: 1 - Re-check and continue if in STOP state. Re-display USE if not 2 - Perform any pending actions, regardless of the object's state 3 - Exit and do not perform any pending actions ******************************************************************************* DSNT360I @ *********************************** DSNT361I @ * DISPLAY DATABASE SUMMARY * GLOBAL USE DSNT360I @ *********************************** DSNT362I @ DATABASE = DSN8D81A STATUS = RW DBD LENGTH = 16142 DSNT397I @ NAME TYPE PART STATUS CONNID CORRID USERID -------- ---- ---- ------------------ -------- ------------ -------- DSN8S81D TS STOPP TSO SYSADM SYSADM - MEMBER NAME V81A ******* DISPLAY OF DATABASE DSN8D81A ENDED ********************** DSN9022I @ DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION ******************************* Bottom of data ********************************If an object is not stopped when the ALTER TABLESPACE statement runs, such as when others are holding locks on the object, a -626 SQL code is returned.
Altering a table space by using the ALT command
Procedure
To alter a table space by using the ALT command:
-
On the Table Spaces (ADB21S)
panel, enter
the ALT line command against the table space that you
want to redefine, and press Enter:
ADB21S in ------------------ DB2X Table Spaces --------------- Row 1 to 5 of 5 Command ===> Scroll ===> CSR Commands: GRANT MIG DIS STA STO ALL DROP MOVETB Line commands: T - Tables D - Database A - Auth G - Storage group ICS - Image copy status DIS - Display table space STA - Start table space STO - Stop table space ? - Show all line commands C Select Name DB Name Parts Bpool L E S I C Tbls Act pages Segsz T L O * * * * * * * * * * * * * * * ------ -------- -------- ------ ------ - - - - - ----- ----------- ------ - - - ALT TSFGR DBFGR 0 BP0 A N A N Y 1 0 4 Y TSFGRPBR DBFGR 3 BP0 A N C N Y 1 0 4 R Y TSFGRRO1 DBFGRRO1 3 BP0 A N A N Y 1 0 64 R Y TSFGRRO2 DBFGRRO2 3 BP0 A N A N Y 1 0 64 R Y TSFGRROO DBFGRROO 2 BP0 A N T N Y 0 0 64 R Y
-
On the Redefine Table Space (ADB21SAR)
panel, change
the table space parameters as needed and enter NEXT
on the command line.
Tips:
- If
you want to add or insert a partition, change the NUMPARTS value
and issue the NEXT command. Then on the Alter Partitioned Table (ADB21TAV)
panel, use
the INS line command to insert any partitions and specify the
limit key values.
- If you want to change the table space type to partition-by-growth (PBG), use the MAKEPBG command.
- If you want to change the table space type to partition-by-range (PBR), use the MAKEPBR2 command or the MAKEPBR command. (MAKEPBR2 specifies relative page numbering; MAKEPBR specifies absolute page numbering.) On the subsequent Alter Table (ADB21TAP) panel, you can specify the partitioning key.
ADB21SAR ------------------ DB2X Redefine Table Space ------- Row 1 to 1 of 1 Command ===> Scroll ===> CSR Commands: NEXT ORIGINAL MAKEPBG MAKEPBR MAKEPBR2 Line commands: S - Split part R - Remove part O - Original data C - Clear data ? - Show all line commands CREATE TABLESPACE: TSFGR IN DBFGR Owner . . . . . . . RIVERAG > Owner type . . _ (U/R) Numparts . . . . . 0 LOB . . . . . NO Define . . . . . . YES LOG . . . . . YES Member Cluster . . NO SEGSIZE . . . . 4 CCSID . . . . EBCDIC Buffer Pool . . . . BP0 Close Rule . . YES Max Rows . . 255 Lock Size . . . . . ANY Lock Part . . . NO Lock Max . . SYSTEM Max Partitions . . 0 PAGENUM . . . . Insert Algo . 0 C E T S S Part Pqty Sqty FP PF PFU O R M T VCAT Stogroup GBPCach DSSIZE - ------ ---------- ------- --- -- --- - - - - -------- -------> ------- ------ 0 -1 -1 0 5 N N Y I DSNC SYSDEFLT CHANGED
Note:Insert
Algo is displayed only if you are running Db2 12 for z/OS.If you are converting a segmented table space to a partitioned table space, the Alter tablespace - Partitioning methods (ADB2CONF) panel is displayed where you can select the partitioning method:
ADB2CONF -- DB2X Alter tablespace - Partitioning methods --------- 19:28 Please choose partitioning method for the table space to be altered. Select a choice 1. Use table-controlled partitioning (recommended) 2. Use index-controlled partitioningRecommendation: Use table-controlled partitioning.If you select option 1, the Alter Table (ADB21TA) panel is displayed. Specify the partitioning key for defining the table partitions.
If the ALT - Index-controlled Partitioning (ADB21XAP) panel is displayed, you can re-define an existing non-partitioning index to a partitioning index. If the Create Partitioning Index (ADB21SAX) panel is displayed, you can create a partitioning index.