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.
- For Oracle, updating to NULL columns is required.
Updating to VARCHAR columns is optional.
- For DB2, updating
to VARCHAR/NULL columns is optional. In either case, you must make
manual changes to these columns as described in this topic.
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:
- Convert CHAR columns to VARCHAR by removing the DataType attributes
in the XML file.
- Convert NOT NULL columns to NULL by removing the Nullable and DefaultValue attributes
in the XML file. Do not remove the VirtualDefaultValue attributes.
How it affects you
Depending
on your database, you can enable backwards compatibility to maintain
CHAR columns or NOT NULL columns.
- On DB2, you
have the following options for enabling backwards compatibility:
- Maintain CHAR and NOT NULL columns.
- Maintain NOT NULL columns, and convert CHAR columns
to VARCHAR columns.
- Maintain CHAR columns, and convert NOT NULL columns
to NULL columns.
- On Oracle, you can maintain CHAR columns; however,
converting NOT NULL columns to NULL is required.
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:
- Do not remove the DataType attributes
in the XML file. The CHAR columns are maintained as they exist in
the current version.
- Remove the VirtualDefaultValue attributes
in the XML file. Do not remove the Nullable and DefaultValue attributes.
The NOT NULL columns are maintained as they exist in the current version.
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:
- Do not remove the DataType attributes
in the XML file. The CHAR columns are maintained as they exist in
the current version.
- Remove the Nullable and DefaultValue attributes
in the XML file. Do not remove the VirtualDefaultValue attributes.
The NOT NULL columns are converted to NULL columns.
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.
- YFS_ORDER_HEADER Table
- YFS_ORDER_LINE Table
- YFS_SHIPMENT Table
- YFS_SHIPMENT_LINE Table
- YFS_ACTIVITY_DEMAND Table
- YFS_CONTAINER_DETAILS Table
- YFS_DOCK_APPOINTMENT Table
- YFS_INBOX Table
- YFS_LOAD Table
- YFS_LOADED_CONTAINER Table
- YFS_LOAD_SHIPMENT Table
- YFS_LOAD_STOP Table
- YFS_LOCN_INVENTORY_AUDIT Table
- YFS_MANIFEST Table
- YFS_ORDER_INVOICE Table
- YFS_ORDER_LINE_SCHEDULE Table
- YFS_ORDER_RELEASE Table
- YFS_ORDER_RELEASE_STATUS Table
- YFS_PRODUCTIVITY Table
- YFS_RECEIPT_LINE Table
- YFS_RECEIVING_PREFERENCE Table
- YFS_RESPONSE Table
- YFS_SHIPMENT_CONTAINER Table
- YFS_TASK Table
- YFS_WORK_ORDER Table
Table 1. YFS_oRDER_hEADER tableColumn |
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 tableColumn |
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 tableColumn |
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 tableColumn |
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 tableColumn |
To VARCHAR |
TO NULL |
ORDER_HEADER_KEY |
|
X |
ORDER_NO |
X |
X |
Table 6. YFS_cONTAINER_dETAILS tableColumn |
To VARCHAR |
TO NULL |
ORDER_HEADER_KEY |
|
X |
ORDER_LINE_KEY |
|
X |
ORDER_RELEASE_KEY |
|
X |
Table 7. YFS_dOCK_aPPOINTMENT tableColumn |
To VARCHAR |
TO NULL |
APPOINTMENT_NO |
|
X |
BOL_NO |
X |
X |
ORDER_HEADER_KEY |
|
X |
PRO_NO |
X |
X |
Table 8. YFS_iNBOX tableColumn |
To VARCHAR |
TO NULL |
ORDER_HEADER_KEY |
|
X |
ORDER_NO |
X |
X |
Table 9. YFS_lOAD tableColumn |
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 tableColumn |
To VARCHAR |
TO NULL |
TRAILER_NO |
X |
X |
Table 11. YFS_lOAD_sHIPMENT tableColumn |
To VARCHAR |
TO NULL |
BOL_NO |
X |
|
PRO_NO |
X |
|
Table 12. YFS_lOAD_sTOP tableColumn |
To VARCHAR |
TO NULL |
APPOINTMENT_NO |
X |
X |
Table 13. YFS_lOCN_iNVENTORY_aUDIT tableColumn |
To VARCHAR |
TO NULL |
BOL_NO |
X |
X |
ORDER_NO |
X |
X |
Table 14. YFS_mANIFEST tableColumn |
To VARCHAR |
TO NULL |
MANIFEST_NO |
X |
|
TRAILER_NO |
X |
X |
Table 15. YFS_oRDER_iNVOICE tableColumn |
To VARCHAR |
TO NULL |
DERIVED_FROM_ORDER_HEADER_KEY |
|
X |
ORDER_NO |
X |
X |
Table 16. YFS_oRDER_lINE_sCHEDULE tableColumn |
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 tableColumn |
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 tableColumn |
To VARCHAR |
TO NULL |
CHAINED_TO_ORDER_HEADER_KEY |
|
X |
CHAINED_TO_ORDER_LINE_KEY |
|
X |
ORDER_RELEASE_KEY |
|
X |
Table 19. YFS_pRODUCTIVITY tableColumn |
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 tableColumn |
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 tableColumn |
To VARCHAR |
TO NULL |
BUYER_ORGANIZATION_CODE |
|
X |
Table 22. YFS_rESPONSE tableColumn |
To VARCHAR |
TO NULL |
ENTERED_BY |
|
X |
Table 23. YFS_sHIPMENT_cONTAINER tableColumn |
To VARCHAR |
TO NULL |
MANIFEST_KEY |
|
X |
MANIFEST_NO |
X |
X |
ORDER_HEADER_KEY |
|
X |
TRACKING_NO |
X |
X |
Table 24. YFS_tASK tableColumn |
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 tableColumn |
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.