Reducing table size by converting columns to NULL

To improve system performance, Release 9.2 provides two XML files that allow you to reduce the size of some tables by converting NOT NULL columns to NULL:
Use the SMCF_920_attribute_default_extn.xml.sample file to reduce the size of the tables listed in Table 1 by converting the specified columns to NULL.
Table 1. Tables and columns in the SMCF_920_attribute_default_extn.xml.sample file
Table Column
YFS_PAYMENT CustomerAccountNo
  CreditCardNo
  SvcNo
YFS_CUSTOMER_PAYMENT_METHOD CustomerAccountNo
  CreditCardNo
  SvcNo

Use the YCD_920_tables_modifications.xml.sample file to reduce the size of the tables listed in Table 2 by converting the specified columns to NULL.

Table 2. Tables and columns in the YCD_920_tables_modifications.xml.sample file
Table Column
YFS_ORDER_HEADER AllAddressesVerified
  ComplGiftBoxQty
  NoOfAuthStrikes
  SourceIpAddress
  CustomerFirstName
  CustomerLastName
  CustomerPhoneNo
  CustomerZipCode
YFS_ORDER_LINE BackorderNotificationQty
  IsPriceMatched
YFS_SHIPMENT NotificationSent

On some databases, this will improve index performance but may add a significant amount of time to the upgrade process.

The Reducing Table Size By Converting Columns to NULL feature is supported on the Oracle and DB2® databases. For Oracle, updating to NULL columns is required. For DB2, updating to NULL columns is optional. In either case, you must extend the SMCF_920_attribute_default_extn.xml.sample and YCD_920_tables_modifications.xml.sample files in the <INSTALL_DIR>/repository/entity/extensions directory and edit the corresponding XML files.

The sample files are structured identically to entity XMLs. The sample files only contain the Entities and Attributes that are affected by this upgrade. In addition to the ColumnName attribute that uniquely identifies the Attribute, sample files can contain up to three possible XML attributes: DefaultValue, Nullable, and VirtualDefaultValue. The DefaultValue and Nullable attributes contain the previous entity XML values required to make a column NOT NULL. VirtualDefaultValue contains the same value as DefaultValue. However, this will only be used in memory and NULL will still be used in the database. Only the attributes applicable to an Attribute will be in the sample file.

Typically, columns with Null values are not returned; however, when you extend the SMCF_920_attribute_default_extn.xml.sample file, attributes made Nullable are returned in output XMLs because of the VirtualDefaultValue attribute.

How it affects you

On DB2, you have the option of enabling backwards compatibility to maintain NOT NULL columns.

Migration strategy

This section describes the automated and manual migration processes for this functionality.

Automated migration processes

None.

Manual migration processes

You convert NULL columns as part of your preparation tasks before migration.