Page Comparison

...

List the offending physical column names
Run the following SQL statement against the repository schema to identify the offending physical column names.
Code Block
language sql
title List the offending physical column names
select PHYS_NAMECOLNAME 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.

Code Block

language	sql
title	Update the physical column names in the model edition(s)

/* Matt: from my notes I needed PHYS_COLNAME rather than PHYS_NAME. Someone should review and either update the SQL or remove this comment based on the results. */
update MTA_ATTRIBUTE
set PHYS_NAMECOLNAME= :NEW_PREFIX || SUBSTR(PHYS_NAMECOLNAME, 2)
where PHYS_NAMECOLNAME 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).

Code Block

language	sql
title	Rename the columns in the data location(s)

/* OPTION Matt1: IYou recommendcan simply generatinggenerate 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>
    );
 
/* Take the results, review the statements, then execute them. */
 
/*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.

...

Versions Compared

Old Version 6

New Version 7

Key