Reducing table size and index size

To improve system performance, Release 8.5 allows you to reduce the size of some database tables by converting some CHAR columns to VARCHAR, and NOT NULL columns to NULL. On some databases, this will improve index performance, as well. The Reducing Table Size and Index Size feature is supported on the Oracle and DB2® databases. Updating to NULL columns may be required on your database.

Depending on your database, this feature may add a significant amount of time to the upgrade process.

As part of your preparation tasks you may convert columns to VARCHAR/NULL. To do so, you must extend the SMCF_85_attribute_default_extn.xml.sample file in the <INSTALL_DIR>/repository/entity/extensions directory and edit the corresponding XML file.

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

The SMCF_85_attribute_default_extn.xml.sample file is structured identically to entity XMLs. The sample file only contains the Entities and Attributes that are affected by this upgrade. In addition to the ColumnName attribute that uniquely identifies the Attribute, there are up to four possible XML attributes: DefaultValue, Nullable, DataType, and VirtualDefaultValue. The DefaultValue and Nullable attributes contain the previous entity XML values required to make a column NOT NULL. DataType contains the previous entity XML values required to keep a column as CHAR, instead of VARCHAR. VirtualDefaultValue is a new attribute that 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.

Example: Updating to VARCHAR/NULL Columns

To update to VARCHAR/NULL columns, extend the SMCF_85_attribute_default_extn.xml.sample file and then edit the SMCF_85_attribute_default_extn.xml file, as described below:

How it affects you

Depending on your database, you can enable backwards compatibility to maintain CHAR columns or NOT NULL columns.

To enable backwards compatibility, you must extend the SMCF_85_attribute_default_extn.xml.sample file in the <INSTALL_DIR>/repository/entity/extensions directory and edit the corresponding XML file.

Example: Maintaining CHAR/NOT NULL Columns

If you do not want to update to VARCHAR/NULL columns, extend the SMCF_85_attribute_default_extn.xml.sample file, and then edit the SMCF_85_attribute_default_extn.xml file, as described below:

Example: Maintaining CHAR Columns and Updating to NULL Columns

If you want to update to NULL columns without updating to VARCHAR columns, extend the SMCF_85_attribute_default_extn.xml.sample file, and then edit the SMCF_85_attribute_default_extn.xml file, as described below:

Migration strategy

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

Automated migration processes

The automated migration process updates index and CHAR columns in the following tables.

Note: Due to DB2 limitations, if you are using DB2 these changes need to be made manually. Refer to the Manual Migration Processes section for more information.
Table 1. YFS_oRDER_hEADER table
Column To VARCHAR TO NULL
ALLOCATION_RULE_ID X X
BILL_TO_ID   X
BUYER_ORGANIZATION_CODE   x
CONTACT_KEY   x
CUSTOMER_EMAILID   x
DEFAULT_TEMPLATE   x
ENTERED_BY   x
EXCHANGE_TYPE   x
MARK_FOR_KEY   X
NOTIFICATION_REFERENCE   x
NOTIFICATION_TYPE   x
OPTIMIZATION_TYPE   x
ORDER_NO x  
ORDER_PURPOSE   x
PAYMENT_RULE_ID   x
PAYMENT_STATUS x  
PRICE_PROGRAM_KEY   x
RECEIVING_NODE   x
RETURN_OH_KEY_FOR_EXCHANGE   x
SHIP_NODE   x
SHIP_TO_ID   x
SOURCING_CLASSIFICATION   x
VENDOR_ID x x
Table 2. YFS_oRDER_lINE table
Column To VARCHAR TO NULL
CHAINED_FROM_ORDER_HEADER_KEY   X
CHAINED_FROM_ORDER_LINE_KEY   X
CURRENT_WORK_ORDER_KEY   X
DEPENDENCY_SHIPPING_RULE   X
DEPENDENCY_ON_LINE_KEY   X
DERIVED_FROM_ORDER_HEADER_KEY   X
DERIVED_FROM_ORDER_LINE_KEY   X
DERIVED_FROM_ORDER_RELEASE_KEY   X
DISTRIBUTION_RULE_ID X X
MARK_FOR_KEY   X
MERGE_NODE   X
ORIG_ORDER_LINE_KEY   X
PROCURE_FROM_NODE   X
RECEIVING_NODE   X
SHIP_TO_ID   X
SHIP_TO_KEY   X
SHIPMENT_CONSOL_GROUP_ID X  
SHIPNODE_KEY   X
SOURCE_FROM_ORGANIZATION   X
SUPPLIER_CODE X X
Table 3. YFS_sHIPMENT table
Column To VARCHAR TO NULL
AIRWAY_BILL_NO X  
APPOINTMENT_NO X X
BILL_TO_CUSTOMER_ID X  
BOL_NO X X
BUYER_ORGANIZATION_CODE   X
DELIVERY_PLAN_KEY   X
IT_NO X X
MANIFEST_KEY   X
MANIFEST_NO X X
MARK_FOR_KEY   X
MERGE_NODE   X
ORDER_HEADER_KEY   X
ORDER_NO X X
ORDER_RELEASE_KEY   X
PARENT_SHIPMENT_KEY   X
PICK_LIST_NO   X
PICKTICKET_NO X X
POD_NO X X
PRO_NO X X
RECEIVING_NODE   x
SEAL_NO X X
SHIPMENT_CONSOL_GROUP_ID X  
TRACKING_NO X X
TRAILER_NO X X
WORK_ORDER_APPT_KEY   X
Table 4. YFS_sHIPMENT_lINE table
Column To VARCHAR TO NULL
EXTERNAL_RELEASE_IDENTIFIER   X
MARK_FOR_KEY   X
ORDER_HEADER_KEY   X
ORDER_LINE_KEY   X
ORDER_NO X X
ORDER_RELEASE_KEY   X
REQUESTED_TAG_NUMBER   X
SHIPMENT_CONSOL_GROUP_ID X  
Table 5. YFS_aCTIVITY_dEMAND table
Column To VARCHAR TO NULL
ORDER_HEADER_KEY   X
ORDER_NO X X
Table 6. YFS_cONTAINER_dETAILS table
Column To VARCHAR TO NULL
ORDER_HEADER_KEY   X
ORDER_LINE_KEY   X
ORDER_RELEASE_KEY   X
Table 7. YFS_dOCK_aPPOINTMENT table
Column To VARCHAR TO NULL
APPOINTMENT_NO   X
BOL_NO X X
ORDER_HEADER_KEY   X
PRO_NO X X
Table 8. YFS_iNBOX table
Column To VARCHAR TO NULL
ORDER_HEADER_KEY   X
ORDER_NO X X
Table 9. YFS_lOAD table
Column To VARCHAR TO NULL
AIRWAY_BILL_NO X  
APPOINTMENT_NO X X
BOL_NO X X
BUYER_ORGANIZATION_CODE   X
DELIVERY_PLAY_KEY   X
MANIFEST_KEY   X
MARK_FOR_KEY   X
POD_NO X X
PRO_NO X X
SEAL_NO X X
TRAILER_NO X X
Table 10. YFS_lOADED_cONTAINER table
Column To VARCHAR TO NULL
TRAILER_NO X X
Table 11. YFS_lOAD_sHIPMENT table
Column To VARCHAR TO NULL
BOL_NO X  
PRO_NO X  
Table 12. YFS_lOAD_sTOP table
Column To VARCHAR TO NULL
APPOINTMENT_NO X X
Table 13. YFS_lOCN_iNVENTORY_aUDIT table
Column To VARCHAR TO NULL
BOL_NO X X
ORDER_NO X X
Table 14. YFS_mANIFEST table
Column To VARCHAR TO NULL
MANIFEST_NO X  
TRAILER_NO X X
Table 15. YFS_oRDER_iNVOICE table
Column To VARCHAR TO NULL
DERIVED_FROM_ORDER_HEADER_KEY   X
ORDER_NO X X
Table 16. YFS_oRDER_lINE_sCHEDULE table
Column To VARCHAR TO NULL
OVERRIDE_PRODUCT_CLASS   X
PROCURE_FROM_NODE   X
RECEIVING_NODE   X
SHIP_NODE   X
SOURCE_FROM_ORGANIZATION   X
Table 17. YFS_oRDER_rELEASE table
Column To VARCHAR TO NULL
BILL_TO_ID   X
BUYER_ORGANIZATION_CODE   X
MARK_FOR_KEY   X
MERGE_NODE   X
NOTIFICATION_REFERENCE   X
NOTIFICATION_TYPE   X
ORDER_HEADER_KEY   X
ORDER_NO X X
PICK_LIST_NO   X
RECEIVING_NODE   X
SHIP_TO_ID   X
SHIPMENT_CONSOL_GROUP_ID X  
SUPPLIER_CODE X X
WORK_ORDER_APPT_KEY   X
Table 18. YFS_oRDER_rELEASE_sTATUS table
Column To VARCHAR TO NULL
CHAINED_TO_ORDER_HEADER_KEY   X
CHAINED_TO_ORDER_LINE_KEY   X
ORDER_RELEASE_KEY   X
Table 19. YFS_pRODUCTIVITY table
Column To VARCHAR TO NULL
BOL_NO X X
MANIFEST_NO X X
ORDER_LINE_KEY   X
ORDER_NO X X
ORDER_RELEASE_KEY   x
TRAILER_NO X X
Table 20. YFS_rECEIPT_lINE table
Column To VARCHAR TO NULL
ORDER_HEADER_KEY   X
ORDER_LINE_KEY   X
ORDER_NO X X
ORDER_RELEASE_KEY   X
Table 21. YFS_rECEIVING_pREFERENCE table
Column To VARCHAR TO NULL
BUYER_ORGANIZATION_CODE   X
Table 22. YFS_rESPONSE table
Column To VARCHAR TO NULL
ENTERED_BY   X
Table 23. YFS_sHIPMENT_cONTAINER table
Column To VARCHAR TO NULL
MANIFEST_KEY   X
MANIFEST_NO X X
ORDER_HEADER_KEY   X
TRACKING_NO X X
Table 24. YFS_tASK table
Column To VARCHAR TO NULL
BOL_NO X X
MANIFEST_NO X X
ORDER_HEADER_KEY   X
ORDER_LINE_KEY   X
ORDER_NO X X
ORDER_RELEASE_KEY   X
Table 25. YFS_wORK_oRDER table
Column To VARCHAR TO NULL
BUYER_ORGANIZATION_CODE   X
ORDER_NO X X

Manual migration processes

To reduce table and index size in DB2, manually make nullable the columns listed in the Automated Migration Processes section and change the default value of these columns to NULL.