Adjust tablespace and Index space size for estimated rows.

Document ID : KB000052475
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

I am comparing two databases. One is on Development and the other on Test environments. While the application system has been in development the number of data rows have not been large but now that it's time to start system testing the planned number of rows will increase to a defined number that will stress test the application.

How do I tell RC/Migrator to resize the tablespaces and indexes? I have already migrated an initial copy of the development objects to test and have been doing comparisons at intervals to update the test objects. Do I have to migrate the database from scratch and drop the test database?

RC/Migrator's Space Calculator feature is able to adjust the Primary and Secondary quantities for tablespaces and indexes. The specific number of expected data rows are provided for this calculation by the user.

Solution:

No, you don't have to migrate the database again or drop the test database.

This resizing can be done either during a migration or compare in a similar fashion.

Let's assume you know for a fact how many records will be loaded to each test table. This is already decided and all that has to be done is for the primary and secondary quantities of those tablespaces and indexes to be adjusted accordingly.

Resizing in a MIGRATION strategy can be done with these steps:

For Tablespaces

  1. Update the Migration Strategy.

  2. Place an "S" in the "TS" column to get a list of tablespaces.

  3. Pick the tablespace in question and "E" edit it.

  4. Place an "S" in the "CMD" line of the partition.

  5. This is space calculator. Enter the QUANTITY value of rows for each table in the tablespace. This will calculate a PRIQTY and SECQTY accordingly. Type the TOTALS command and look at the new Implicit PRIQTY and SECQTY. This will now be used in the generated DDL. Other items can be adjusted here too like SEGSIZE if desired or PCTFREE and others.

  6. PF3 out back one screen and you can see the changed values.

For Indexes

  1. Update the Migration Strategy.

  2. Place an "S" in the "IX" column to get a list of Indexes.

  3. Pick the Index in question and "E" edit it.

  4. Place an "S" on the "CMD" column of the Index partition to get to the Index Space Calculation.

  5. Change the Est. Data Rows to the correct number.

  6. PF3 out back one screen and you can see the changed values of PRI and SEC QTY.

When finished PF3 back to Strategy Services and perform the Analysis. The adjusted quantities will be generated in the DDL for the tablespace and/or indexes.

Resizing in a COMPARE strategy can be done with these steps:

For Tablespaces

  1. Update the Compare Strategy.

  2. Place an "X" in the "EXPL" column to go to the All Type Exclude Options screen.

  3. Enter "Y" in the Display all Dependent Objects field to list the objects.

  4. Pick the tablespace in question and "E" edit it.

  5. Place an "S" in the "CMD" line of the partition.

  6. This is space calculator. Enter the QUANTITY value of rows for each table in the tablespace. This will calculate a PRIQTY and SECQTY accordingly. Type the TOTALS command and look at the new Implicit PRIQTY and SECQTY. This will now be used in the generated DDL. Other items can be adjusted here too like FREEPAGE if desired or PCTFREE and others.

  7. PF3 out back one screen and you can see the changed values.

For Indexes

  1. Update the Compare Strategy.

  2. Place an "X" in the "EXPL" column to go to the All Type Exclude Options screen.

  3. Enter "Y" in the Display all Dependent Objects field to list the objects.

  4. Pick the Index in question and "E" edit it.

  5. Place an "S" in the "CMD" line of the partition.

  6. Enter the Est. Data Rows value of rows for the index. This will calculate a PRIQTY and SECQTY accordingly.

  7. PF3 out back one screen and you can see the changed values.

When finished PF3 back to Strategy Services and perform the Analysis. The adjusted quantities will be generated in the DDL for the tablespace and/or indexes.