Upgrading Models with Reserved Physical Column Names to Version 3.2 and Above

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.

Important Note

Refer to the Release Notes and Upgrade Guide available in the on-line documentation for all the required information when upgrading Convergence for MDM.

Fixing and Upgrading Models with Reserved Column Names

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.
List 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 the physical column names in the model edition(s)
```
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).

Rename the columns in the data location(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;
/

Upgrade the repository and data locations
When upgrading the data location, the certification and purge jobs are automatically re-generating and will take into account the new physical column names.
Validate the Data Model
If the model was correctly fixed, then it should not raise any validation error such as "Invalid physical name: <column_name> matches the reserved pattern B_*".
Update your Integration Layer
If you are using a data integration tool accessing the physical tables in the data location schema to publish or consume data, you should review and fix your data integration layer to take into account the column name changes.

Important Note

This process should be repeated in all environments (development, test, etc.) and all data locations to consistently reflect the column name changes.

Semarchy Knowledge Base

Upgrading Models with Reserved Physical Column Names to Version 3.2 and Above

Fixing and Upgrading Models with Reserved Column Names

Related articles