Prior to version 3.2, attributes' physical column names could take any name that was not a system column name.
Starting with version 3.2.0, it is no longer possible to define physical column names starting with B_, as these names may conflict with Semarchy's reserved column names.
Models with physical column names violating this rule no longer pass model validation after the upgrade and should be fixed before upgrading.
Refer to the Release Notes and Upgrade Guide available in the on-line documentation for all the required information when upgrading Convergence for MDM. |
In order to fix the model, you should follow the process described below:
List the offending physical column names
Run the following SQL statement against the repository schema to identify the offending physical column names.
select PHYS_COLNAME from MTA_ATTRIBUTE where SUBSTR(PHYS_COLNAME, 1, 2) = 'B_' and CLASSNAME='AtomicAttribute'; |
Update the physical column names in the model edition(s)
Run the following SQL statement against the repository schema to fix the offending physical column names in all the model editions.
This script requires the list of physical column names collected in the previous step. It replaces the B_ prefix by a new prefix of your choice.
update MTA_ATTRIBUTE set PHYS_COLNAME= :NEW_PREFIX || SUBSTR(PHYS_COLNAME, 2) where PHYS_COLNAME in ( -- <insert here the list of physical names from step 1 in the form 'physname1', 'physname2'...> ) and CLASSNAME='AtomicAttribute'; |
Rename the columns in the data location(s).
Run the following SQL statement against the data location schemas to rename the columns and reflect the changes applied to the model edition(s).
/* OPTION 1: You can simply generate the SQL for renaming the columns, then manually executing the statements. */ /* This is a one-time issue, so automating the update of your master data adds risk without much benefit. */ select 'alter table '||TABLE_NAME||' rename column '||COLUMN_NAME||' to '|| 'BB_' || SUBSTR(COLUMN_NAME, 2) stmt from USER_TAB_COLUMNS where TABLE_NAME in ( 'SD_' || 'PATON_ENTITY', 'SE_' || 'PATON_ENTITY', 'MI_' || 'PATON_ENTITY', 'GI_' || 'PATON_ENTITY', 'GE_' || 'PATON_ENTITY', 'MD_' || 'PATON_ENTITY', 'GD_' || 'PATON_ENTITY' ) and COLUMN_NAME in ( <replace by the list of physical names from step 1> ); /* OPTION 2: An alternative which automatically executes the statements follows below. */ declare CURSOR alter_stmts is select 'alter table '||TABLE_NAME||' rename column '||COLUMN_NAME||' to '||:NEW_PREFIX|| SUBSTR(COLUMN_NAME, 2) stmt from USER_TAB_COLUMNS where TABLE_NAME in ( 'SD_'||:ENTITY_PHYS_NAME, 'SE_'||:ENTITY_PHYS_NAME, 'MI_'||:ENTITY_PHYS_NAME, 'GI_'||:ENTITY_PHYS_NAME, 'GE_'||:ENTITY_PHYS_NAME, 'MD_'||:ENTITY_PHYS_NAME, 'GD_'||:ENTITY_PHYS_NAME ) and COLUMN_NAME in ( -- <insert here by the list of physical names from step 1> ); ; rec alter_stmts%rowtype; begin for rec in alter_stmts loop execute immediate rec.stmt; end loop; end; / |
This process should be repeated in all environments (development, test, etc.) and all data locations to consistently reflect the column name changes. |
Related articles appear here based on the labels you select. Click to edit the macro and add or change labels.
|