xDM

I removed an attribute named Gender from my Contact entity and re-deployed my model.

But the GENDER column still exists in all my %CONTACT tables.

Why is this and how do I remove it?

This is normal behavior. The deployment process is designed to never perform any destructive change, in order to prevent any loss of data. If these deprecated columns no longer exist as attributes in the model, then they will no longer be used by xDM. If you deploy the same model to a different data location, you will not have them.

You have two options for the existing data location:

• Delete this data location, re-deploy the model, and re-load the data.
• Manually drop these columns with your favorite SQL editor.

I created sql dynamic script to drop all the deleted attributes columns.

Hope this helps.:

do

 

DECLARE

vsql_delete text;

vcore_model text;

ventity_name text;

begin

 

vcore_model := 'core_data_model';

ventity_name := null;

 

with cte_edition as

(

SELECT max(en.editionid) AS editionid FROM semarchy_repository.mta_entity en

)

,cte_entities as

(

select me.editionid, me.name as entity_name, lower(me.phys_tabname) as table_name,me.entity_type

from

semarchy_repository.mta_entity me,

cte_edition ed

where 

me.editionid = ed.editionid

and  COALESCE(ventity_name,me.name) = me.name

--and me.entity_type = 'FUZZY_MATCHED'

),

cte_tables as

(

select cte.editionid, cte.entity_name,tab.table_name,entity_type

from

cte_entities cte

join information_schema.tables tab on tab.table_schema = vcore_model

and tab.table_name like ('%'||cte.table_name)

where 

1 = 1

and left(tab.table_name,2) not in ('sf','gf')

and left(tab.table_name,3) <> ('usr')

)

,cte_columns_to_delete as

(

select cte.editionid, cte.entity_name, cte.table_name, col.column_name--, MDM.phys_tabname, MDM.phys_colname, MDM.label

from 

cte_tables cte

join information_schema.columns col on col.table_schema = vcore_model

and col.table_name = cte.table_name

AND col.column_name not like ('b_%') 

AND col.column_name <> 'id'

left join (

       select cte.editionid, cte.entity_name, cte.table_name, me.phys_tabname, lower(coalesce (ma.phys_prefix || mac.phys_colname, ma.phys_colname)) as phys_colname, ma.label

       from

        cte_tables cte

           join semarchy_repository.mta_entity me on me.name = entity_name and me.editionid = cte.editionid

           join semarchy_repository.mta_attribute ma on me.uuid = ma.o_entity and me.editionid  = ma.editionid

           left join semarchy_repository.mta_attribute mac on mac.o_cplxtype = ma.r_cplxtype  and me.editionid  = mac.editionid

       union 

       select cte.editionid, cte.entity_name, cte.table_name,me.phys_tabname, lower(prefix || ma.phys_name), ma.label      

       from

           cte_tables cte

           join semarchy_repository.mta_entity me on me.name = entity_name and me.editionid = cte.editionid

           join semarchy_repository.mta_attribute ma on me.uuid = ma.r_entity and me.editionid  = ma.editionid

           ,( select 'F_'  as prefix UNION all select 'FD_' UNION all select 'FS_' UNION all select 'FP_') pf

       where 

           ma.classname = 'ForeignAttribute') MDM on MDM.table_name=col.table_name and MDM.phys_colname=col.column_name 

 where

 MDM.phys_tabname is null

order by entity_name, table_name

)

select 

string_agg(

'alter table '||vcore_model||'.'|| cte.table_name ||'  drop column '||cte.column_name||';' ,'

') into vsql_delete

from 

cte_columns_to_delete cte;

 

execute vsql_delete;

 

end;