Tablespace Alter Option

The tablespace alter option provides all the functions of the DB2 ALTER TABLESPACE command. You can also alter the tablespace name, database, partition information, and segment information.
carcudb2
The tablespace alter option provides all the functions of the DB2 ALTER TABLESPACE command. You can also alter the tablespace name, database, partition information, and segment information.
DB2 permits only certain alterations to be made to a DB2 object using the DB2 ALTER command. To make a non-DB2 supported change, the tablespace must be dropped and recreated with the specified changes. This capability is transparent to the end user. An impact analysis report can be reviewed to determine whether to implement the alteration.
The Tablespace Alter panels enable you to alter any type of DB2 tablespace (segmented, partitioned, or universal) that is supported by the version of DB2 and mode on which you are running or for which DDL is to be generated and subsequently executed.
If the requested changes can be made via DB2 ALTER TABLESPACE statements, a confirmation screen appears. Accept, edit, or reject the DDL to be used to make the alterations. If the tablespace
If the requested changes cannot be made through a DB2 ALTER TABLESPACE statement, RC/Alter must be used. RC/Alter must be used if the tablespace name, database, partition information, segment information are changed.
The Primary (PQTY) and Secondary (SQTY) Quantity alterations are done through RC/Alter even though a DB2 ALTER could partially do them. If an attempt is made to change these quantities with a DB2 ALTER statement, the change will not be complete in any usable way until a REORG or DROP/CREATE is performed. For this reason, PQTY and SQTY are not included in the previous list.
These changes are made by dropping and recreating the tablespace. All dependents, data, and authorizations are automatically restored and all changes are automatically propagated to any dependent object types.
If the tablespace must be dropped and re-created, the message Change Requires Drop/Recreate appears when F3 (End) is pressed to process the screen. This means that RC/Alter must be used to alter the tablespace, and the RC/Alter Specification Screen displays. Provide the necessary information about the data set to which the DDL will be written. The DDL must then be executed in batch mode.
The Tablespace Alter screen allows changes to the values used to create the tablespace. It is similar to the Tablespace Create and Tablespace Template screens. If partition information is entered, change the tablespace to non-partitioned. All lines except the first are deleted. If the tablespace is changed back to a partitioned tablespace, the old partition values are returned.
The Tablespace Alter screen also allows changes to multiple partitions on one screen without having to issue separate ALTER TABLESPACE statements. To change the Free Page or Percent Free for partitions in DB2, a separate ALTER TABLESPACE command must be issued for each partition.
If the tablespace is partitioned (range-partitioned or partition-by-growth universal tablespace, or LARGE tablespace), the Partitions value must be set to YES. 
Edit the information as in a Tablespace Create screen. Once the changes have been made, press F3 (End) to process the alter.
When altering a tablespace, the TS Type option cannot be changed from REGULAR, GROWTH, RANGE, or LARGE to LOB, or vice-versa. The tablespace can only be altered between TS Types: REGULAR, GROWTH, RANGE, and LARGE.
3
3
Change Tablespace Type to Segmented
You can change the tablespace type from simple to segmented by changing the value of the Segment Size field on the Tablespace Alter screen.
To change tablespace type from simple to segmented, change the Segment Size field from 0 to segment size.
Change Tablespace Type to Partitioned
You can change the tablespace type from simple to partitioned and segmented to partitioned.
After performing the conversion, you need to alter the index on the table that exists in the tablespace. If the table in the tablespace does not have an index, you need to create the index (see Step 3).
Follow these steps:
  1. Complete fields on the Tablespace Alter screen as follows:
    1. Change the value in the Partitioned field from NO to 
      YES
      .
    2. Change the value in the Segment Size field to 
      0
      . This step is not required for changing from simple to partitioned.
    Press Enter.
    Your settings are applied to the tablespace. A PART field appears in the Partition Information portion of the panel.
  2. (Optional) Perform the following steps as many times as needed to produce additional partitions:
    1. Enter 
      in the CMD line next to a partition to repeat the partition.
    2. Change the PRIQTY, SECQTY, Freepage, and PCTfree fields for the partition, if necessary, then press Enter.
    The partition is added according to your specifications.
  3. When you perform any of the following conversions, you also need to alter the index on the table that exists in the tablespace:
    • Changing the tablespace from simple to partitioned
    • Changing the tablespace from segmented to partitioned
    If the table in the tablespace does not have an index, you must create the index. 
    1. Enter 
      YES 
      in the Partitioned field on the Table Alter screen.
      The Table Partitioning Key Col Selection & Maint screen appears.
    2. Set up your key columns according to instructions on the screen, then press the END key.
      The table is converted to a partitioned table, and the Table Alter screen appears.
    3. Enter 
      LIMITS 
      on the command line.
      The Table Partitioning & Limit Key Values screen appears.
    4. Specify limit keys for each partition, then press the END key.
      The index is altered.
Change Tablespace Type to Range-Partitioned
You can change the tablespace type from simple, partitioned, or segmented to range-partitioned.
When the range-partitioned tablespace is defined with relative page numbering, the partitions for the tablespace can be defined with DSSIZE values that are different than the tablespace DSSIZE value. You can change the partition level DSSIZE values through the tablespace level DSSIZE field in the header portion of the panel.
When a tablespace is changed to be range-partitioned, any existing index-controlled partitioned table within the tablespace is converted to a table-controlled partitioned (TCP) table. However, simple and segmented tablespaces do 
not 
contain a partitioned table that can be converted. Therefore, you must convert the existing non-partitioned table to a TCP table after performing
either
of the following tasks:
  • Changing a tablespace from simple to range-partitioned
  • Changing a tablespace from segmented to range-partitioned
For more information about converting an existing non-partitioned table to TCP, see step 4.
Follow these steps:
  1. Complete fields on the Tablespace Alter screen as follows:
    1. Change the value in the Partitioned field from NO to 
      YES
      .
    2. Change the value in the TS Type field to 
      RANGE
      .
    3. Specify a non-zero value in the Segment Size field.
    Press Enter.
    Your settings are applied to the tablespace. A PART field appears in the Partition Information portion of the screen.
    If an index-controlled partitioned table exists in the tablespace, the table is automatically converted to table-controlled partitioning.
  2. (Optional) Define partitions for a range-partitioned tablespace with relative page numbering with DSSIZE values that are different than the tablespace DSSIZE value.
    1. Change the partition level DSSIZE values through the tablespace level DSSIZE field in the header portion of the panel.
      When you change this field, a pop-up window displays automatically when a partition level DSSIZE value is different than the new DSSIZE.
      +------- Tablespace DSSIZE does not match partition DSSIZE(s) -------+ |                                                                    | | The new global DSSIZE specified for this PBR2 tablespace does not  | | match one or more of its individual partition DSSIZE values.       | |                                                                    | |   New tablespace DSSIZE: 8          Total partitions:     6        | |   Smallest DSSIZE found: 1          Largest DSSIZE found: 4        | |                                                                    | | Do you want to propagate the new DSSIZE into all parts?  N (Y,N)   | | Enter Y to have DSSIZE in all parts changed to match new DSSIZE.   | |                                                                    | | Enter / below to suppress this pop-up window for the remainder of  | | your RCM/RCU session:                                              | | _ Do this whenever I change the tablespace DSSIZE.                 | |                                                                    | |        Press HELP/PF1 for more information on this pop-up window.  | +-----------------------------------------------------------------   |
      Summary information is provided showing the new tablespace level DSSIZE, total partitions, and the smallest and largest partition DSSIZE values. 
      This pop-up window does not display for tablespaces with absolute page numbering or when the DSSIZE changes because the RESET, SOURCE, or TARGET primary commands were entered. Propagating the DSSIZE keyword DFLT, in effect, causes express DSSIZE values in all tablespace partitions to be removed.
    2. Confirm whether you want to propagate the new DSSIZE value into all partitions or preserve the existing DSSIZE values.
    3. Include a forward slash in Do this whenever I change the tablespace DSSIZE to suppress this pop-up on subsequent changes in the current product session.
  3. (Optional) Perform the following steps as many times as needed to produce additional partitions:
    1. Enter 
      in the CMD line next to a partition to repeat the partition.
    2. Change the PRIQTY, SECQTY, Freepage, and PCTfree fields for the partition, if necessary, then press Enter.
      The partition is added according to your specifications.
  4. (Optional) To convert a non-partitioned table to a TCP table:
    1. Enter 
      YES 
      in the Partitioning field on the Table Alter panel.
      For more information about accessing table functions and panels, see the Table documentation. The Table Partitioning Key Col Selection & Maint screen appears. You must define at least one partitioning key column. 
    2. Type 
      beside the name of each column that you want to include in the table's partitioning key, then press Enter. 
      The selected columns are inserted into the key.
    3. You can use line commands to arrange the columns in the key if needed.
    4. Press the END key.
      The Table Alter panel appears.
    5. Press the END key.
      The Table Partitioning & Limit Key Values panel appears.
    6. Enter a limit value for the first key column of each partition.
      Any values you entered are now reflected on the panel.
    7. (Optional) Specify additional limit values, then press the END key.
      The Alteration Strategy Services panel appears.
    8. Press the END key.
      The non-partitioned table is converted to a TCP table.
Change Tablespace Type to Partition-by-Growth
You can change the tablespace type from partitioned or segmented to partition-by-growth (PBG). You cannot create simple tablespaces in DB2 9 and above. However, simple tablespaces that you created with an earlier DB2 version can be changed from simple to partition-by-growth.
Follow these steps:
  1. Access the Tablespace Alter panel by doing one of the following:
    • Complete the fields on the CA RC/Update Main Menu.
    • Access tablespace functions from an alteration strategy, migration strategy, or comparison strategy in CA RC/Migrator.
    The Tablespace Alter panel appears.
  2. Complete the following fields and press Enter:
    • Type 
      GROWTH
       in the TS Type field.
    • Specify 
      YES
       in the Partitions field.
    • Enter a non-zero value
       
      in the Segment Size field.
      If you do not define a value, Segment Size defaults to 4. 
    Your settings are applied to the tablespace. The panel now contains the Max Parts field, which controls the maximum number of partitions to which a PBG tablespace can grow.
     If an index-controlled partitioned table exists in the tablespace, the table is automatically converted to table-controlled partitioning.
  3. Complete the following fields for defining partition characteristics of the PBG tablespace:
    • Max Parts
      Specifies the maximum number of partitions to which a PBG universal tablespace is allowed to grow. The value must be an integer in the range of 1 to 4096, depending on the corresponding value of DSSIZE and Buffer Pool. Any unacceptable value specified for Max Parts is automatically adjusted downward to the nearest acceptable value. When DSSIZE is blank, 4 (GB) is assumed.
      For information about the maximum allowable value for Max Parts in relation to the page size and DSSIZE value for the tablespace, see the IBM 
      DB2 for z/OS SQL Reference
       
    • DSSIZE
      Specifies a value, in gigabytes, that indicates the maximum size for each partition (or for each data set for LOB tablespaces). When DSSIZE is blank, 4 (GB) is assumed in determining the maximum value for Max Parts.
    • Buffer Pool
      Specifies the name of the tablespace's buffer pool, which also determines the page size of the tablespace. For 4 KB, 8 KB, 16 KB, and 32 KB page buffer pools, the page sizes are 4 KB, 8 KB, 16 KB, and 32 KB respectively. The specified name must identify an activated buffer pool. For an explanation of valid names, see the online help. You may also specify DEFAULT, indicating that you want DB2 to use the default buffer pool of the database for your tablespace.
    Press Enter.
    Partition information is configured for your PBG tablespace.
    Note:
    When changing a simple tablespace, a prompt appears for you to choose whether to create a partitioned index. Specify Y to create a partitioned index and complete the fields on the Index Create panel. Specify N to skip this processing.
  4. Press the END key.
    The Alteration Analysis panel appears.
  5. Complete the fields on the Alteration Analysis panel, and press Enter.
    Alteration analysis commences.
Processing Considerations for Tablespace Alterations
Note the following processing considerations:
  • A change to the CLOSE Rule is effective as soon as the DDL is executed.
  • A change to the BUFFER POOL is effective the next time the tablespace's data sets are opened.
  • A change to the LOCKSIZE will only apply to SQL statements that will be executed later. It has no effect on currently executing SQL statements. Application plans are only updated if they are rebound.
  • A change to the FREEPAGE or PCTFREE values will take effect when records are loaded into the tablespace or the tablespace is reorganized.
  • When you increase the partition-by-range tablespace DSSIZE for a tablespace with relative page numbering, an ALTER TABLESPACE DSSIZE statement is generated as an immediate change. When you decrease the tablespace DSSIZE, the alter is generated as a pending change. An online reorg utility must be generated in the analysis output. 
When the changes are being made through the DB2 ALTER TABLESPACE, CA RC/Update takes the following steps to make the alter:
  1. The tablespace is stopped.
  2. The ALTER statement is issued.
  3. The tablespace is started in UT (utility) mode.
  4. The REORG statement is issued.
  5. The tablespace is started in RW (read-write) mode.
LC Line Command
The LC line command lets you browse the LISTC Report and view space amounts for user-defined data sets. Use this command from the Tablespace Alter or Tablespace Template screen. Enter 
LC
 in the CMD field of the appropriate data set, and the IDCAMS System Services LISTC Report appears. For more information about the specifics of this report, see the IBM IDCAMS documentation.
Confirming the Alter
After all information has been entered, press F3 (End). Depending on the type of alter performed, one of the following results occurs:
  • If the changes can be made with a standard DB2 ALTER TABLE command, a confirmation screen appears, from which you can perform any of the following actions:
    • Accept the generated DDL.
    • Reject the generated DDL.
    • Make modifications to the generated DDL before accepting it.
  • If the changes require an CA RC/Migrator analysis or if the current operation mode of CA RC/Update is A, the RC/Alter screen appears, from which you can perform one of the following actions:
    • Enter the required information, press Enter, and then use the Batch Processor (BP) facility to execute the generated DDL.
    • Enter END to return to the Table Alter screen without invoking RC/Alter.
    • Enter CANCEL to exit RC/Alter and discard your changes.
  • If a CA RC/Migrator analysis is required to generate the DDL necessary to make your changes, an O or B Operation Mode setting is ignored.
  • The analysis phase generates all statements that are necessary to alter the table or to drop and re-create the table, data, dependents, and authorizations.