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.
To
convert a table space from partition-by-growth (PBG) to partition-by-range (PBR) by
using an ALTER statement in Db2®
13, you must use the
ALT line command or navigate to the table and use the AL line command. See Converting a table space from PBG to a PBR.
You can use
Db2 Admin Tool to alter
implicit table spaces by using either AL or ALT. However, Db2 restricts certain
alterations for implicit table spaces. For example, you cannot change the table
space name or owner. Any attributes that are restricted by Db2 for implicit table space
alters are not changeable on the Db2 Admin Tool panels when using AL or
ALT for implicit table spaces.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
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 13 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 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.
- If
you want to convert a PBG table space to a PBR table space by
using an ALTER statement in Db2®
13, use the MAKEPBR2 command and ensure that the high limit key
for the last partition is MAXVALUE (for ascending columns) or
MINVALUE (for descending columns).
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 ALTER TABLESPACE: TSFGR IN DBFGR Owner . . . . . . . RIVERAG > Owner type . . _ (U/R) Numparts . . . . . 0 LOB . . . . . NO 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
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.