Troubleshooting
Problem
Attempts to run drop.sql script leads to the error ORA-12991 due to missing column in IBM Sterling Order Management 9.5
Symptom
The below error message is seen during Data Migration steps while executing oracle\transaction\drops.sql as part of upgrade
Commands:
----------------------
ALTER TABLE YFS_ORDER_LINE DROP COLUMN ALLOCATION_LEAD_TIME;
ALTER TABLE YFS_ORDER_LINE DROP COLUMN ALLOCATE_DATE;
ALTER TABLE YFS_SHIPMENT DROP COLUMN IS_PICKTICKET_PRINTED;
ALTER TABLE YFS_SHIPMENT DROP COLUMN IS_BACKROOM_PICK_REQUIRED;
ALTER TABLE YFS_SHIP_NODE DROP COLUMN IS_MODEL_STORE;
ALTER TABLE YFS_SHIP_NODE DROP COLUMN MODEL_STORE_NO;
ALTER TABLE YFS_SHIPMENT_LINE DROP COLUMN BACKROOM_PICKED_QUANTITY;
----------------------
These are failing because these columns contains data.
Error:
----------------------
ALTER TABLE YFS_SHIPMENT_LINE DROP COLUMN BACKROOM_PICKED_QUANTITY
Error report -
SQL Error: ORA-12991: column is referenced in a multi-column constraint
12991. 00000 - "column is referenced in a multi-column constraint"
*Cause: An attempt was made to drop a column referenced by some
constraints.
*Action: Drop all constraints referencing the dropped column or
specify CASCADE CONSTRAINTS in statement.
----------------------
Cause
All the components of Sterling Order Management are not yet installed.
Diagnosing The Problem
drops.sql got generated automatically while we executed below command as part of 9.5 upgrade, data migration step.
- $ANT_HOME/bin/ant -Druntime=$INSTALL_DIR -Druntime.old=$INSTALL_DIR_OLD -f buildmigration.xml -logfile $INSTALL_LOGS/initupgrade-migrate.log -Dtarget=initupgrade migrate
Resolving The Problem
If you have any pca application like com/store/sim installed, then you need to install the same before the migration so that pca added columns are considered while generating database scripts.
The sequence of steps to be followed is:
- Upgrade to 9.5
- Install COM and SIM PCA application
- Data migration.
Was this topic helpful?
Document Information
Modified date:
11 February 2020
UID
swg22001977