Db2: reducing table and index size

Review the information for how to reduce the size of some database tables, and which files must be extended.

About this task

In nonsharded upgrade mode, you have the option of reducing the size of some database tables by converting CHAR/NOT NULL columns to VARCHAR/NULL columns. Whether or not you plan to convert these columns, you must extend the following files and edit the corresponding XML files:

  • SMCF_85_attribute_default_extn.xml.sample
  • SMCF_920_attribute_default_extn.xml.sample
  • YCD_920_tables_modifications.xml.sample

You cannot convert these columns in a sharded environment. Thus, if you want these columns converted and plan to move to a sharded environment in the future, you should perform this conversion as part of the nonsharded migration to Release 10.0.

If you are migrating from 9.3 (or later) to 10.0 on Db2, perform the following migration:

Note: If you upgraded to 9.3 (or later) and are now migrating to 10.0, the SMCF_85_attribute_default_extn.xml.sample file has already been extended and the corresponding XML file modified. To maintain these changes, use the earlier release’s extended file. However, if you want to make additional changes, such as converting CHAR columns that were not converted earlier, re-extend the SMCF_85_attribute_default_extn.xml.sample file, as described in the following procedure.

Procedure

  1. In the <INSTALL_DIR>/repository/entity/extensions directory, perform the following file updates:
    • copy SMCF_85_attribute_default_extn.xml.sample and save the file as SMCF_85_attribute_default_extn.xml.
    • copy SMCF_920_attribute_default_extn.xml.sample and save the file as SMCF_920_attribute_default_extn.xml.
    • copy the YCD_920_tables_modifications.xml.sample file and save the file as YCD_920_tables_modifications.xml.
  2. Remove the VirtualDefaultValue attributes from the following files:
    • SMCF_85_attribute_default_extn.xml
    • SMCF_920_attribute_default_extn.xml
  3. Migrate your history and transaction data.
  4. Delete the following files:
    • SMCF_85_attribute_default_extn.xml
    • SMCF_920_attribute_default_extn.xml
    • YCD_920_tables_modifications.xml
  5. Perform the following file updates again:
    • copy SMCF_85_attribute_default_extn.xml.sample and save the file as SMCF_85_attribute_default_extn.xml.
    • copy SMCF_920_attribute_default_extn.xml.sample and save the file as SMCF_920_attribute_default_extn.xml.
    • copy the YCD_920_tables_modifications.xml.sample file and save the file as YCD_920_tables_modifications.xml.
  6. In the SMCF_85_attribute_default_extn.xml file, the SMCF_920_attribute_default_extn.xml file, and the YCD_920_tables_modifications.xml file, perform either of the following edits:
    • To upgrade columns to nullable, remove the Nullable and DefaultValue attributes in the XML files, but do not remove the VirtualDefaultValue attributes, or
    • To prevent columns from converting to nullable, remove the VirtualDefaultValue attributes, but do not remove the Nullable and DefaultValue attributes.
    Note: If converting to NULL, all NOT NULL columns specified in the sample files must be converted to NULL. You cannot convert only some of the NOT NULL columns in these files to NULL.
  7. In the SMCF_85_attribute_default_extn.xml file, make either of the following changes:
    • To convert CHAR columns to VARCHAR, remove the DataType attributes, or
    • To maintain CHAR columns, do not remove the DataType attributes.
    Note: If converting to VARCHAR, convert all columns in the SMCF_85_attribute_default_extn.xml.sample file to VARCHAR. You cannot convert only some of the CHAR columns in this file to VARCHAR.
  8. Run the dbverify script to generate the alter scripts. To prevent dbverify from generating "IndexAdds" SQLs for dropped indexes, ensure that the dropped indexes are listed in the <INSTALL_DIR>/extensions/schemagenerator/indexes_not_created.txt file. Or, if the INDEXES_NOT_CREATED property in the <INSTALL_DIR>/properties/sandbox.cfg file is configured, add the list of dropped indexes to the file referenced by the property.
  9. Execute the SQLs generated by the dbverify script in your database. This command updates columns to VARCHAR and NULL.
  10. Perform a REORG on all modified tables. You can also optionally enable value compression on all newly nullified columns.
  11. Repeat steps 9 and 10 until all columns are modified.