Proper way to remove columns

[Former Member]

What is the recommended way to remove unneeded columns -

When the project started we extended an OOTB type to hold some new attributes. We have now received new requirements/change order which will require changing our extended type - as part of this these attributes are no longer required.

Each of the columns in question reference a custom type which will be removed.

Removing these values will not affect any of the existing records currently in production.

[Former Member]

What option did you end up using? We're having the same requirement here.

Thanks!

[Former Member · Accepted solution]

Although this is probably not "the proper" way to remove unneeded columns this may help if you need to remove hundreds of them: we built an update task (can be run from hac update page) which generates a sql script which:

• remove the attribute definition for any attribute unreferenced in the deployed typesystem

• drops the referenced column

You can download the source code below. Place in your extension src folder and fix the @SystemSetup annotation adding your valid extension name. If you are not using annotations to configure your spring beans you will have to add a reference to your spring xml configuration. After a build you will find a configurable task in your extension:

---

Activating this task will not run anything on your db but it will generate a sql script you can test and review (the script will be written directly into the standard log). Below you can find a sample script generated for a single unneeded field:

 -- Table: genericitems
 DELETE FROM props WHERE ITEMPK IN (select PK from attributedescriptors WHERE lower(columnName) = 'p_orderminimun' and EnclosingTypePK in (select PK from composedtypes where itemtypecode in (select typecode from ydeployments WHERE lower(tablename) = 'genericitems'))  and PersistenceTypePK IS NOT NULL);
 DELETE FROM aclentries WHERE ItemPK IN (select PK from attributedescriptors WHERE lower(columnName) = 'p_orderminimun' and EnclosingTypePK in (select PK from composedtypes where itemtypecode in (select typecode from ydeployments WHERE lower(tablename) = 'genericitems'))  and PersistenceTypePK IS NOT NULL);
 DELETE FROM attributedescriptorslp WHERE ITEMPK IN (select PK from attributedescriptors WHERE lower(columnName) = 'p_orderminimun' and EnclosingTypePK in (select PK from composedtypes where itemtypecode in (select typecode from ydeployments WHERE lower(tablename) = 'genericitems'))  and PersistenceTypePK IS NOT NULL);
 DELETE FROM attributedescriptors WHERE lower(columnName) = 'p_orderminimun' and EnclosingTypePK in (select PK from composedtypes where itemtypecode in (select typecode from ydeployments WHERE lower(tablename) = 'genericitems')) and PersistenceTypePK IS NOT NULL;
 
 ALTER TABLE genericitems DROP COLUMN p_orderminimun;

---

Check the script and be careful on applying it. Before running it you need to run a "cleanup type system" in the hac maintenance section. Shut down hybris when running.

The way columns to be removed are detected should be pretty "safe", since the scripts actually computes the full hybris schema and compares it to the existing one. The scripts for the removal of attributes from the type configuration however have been build by tracing queries produced from hmc and may not cover any use case. If you only need to remove a few attributes you may want to remove attributes manually from hmc.

We used this script successfully to remove more than 250 unreferenced column in a project based on hybris 5.1. However be careful, this script will drop columns and modify the hybris type configuration in an unsupported way, and it may destroy your data. Take a backup and test carefully before running it on a production system

---

You can download the source code here:

http://goo.gl/Lp123x

---

HTH, fabrizio

[Former Member · Accepted solution]

Instead of two last DELETE statements one can execute one impex:

 REMOVE AttributeDescriptor;qualifier[unique=true];enclosingType(code)[unique=true]
 ; your_attribute_name ; your_item_with_removed_attribute

[Former Member]

Hi, you can remove the columns by performing the following steps:
1) Remove the attribute from your extensions core items.xml and do the build
2) go to HAC, remove columns manually (alter table table_name drop column column_name)
3) go to HMC, System->types-> search for the type -> properties-> remove the attributes/columns which are not needed
4) Go to HAC, do a platform update by selecting "core" and your extensions core

The columns should be removed by now

[Former Member]

step 1 and step 3 is sufficient for removing column.

step2 and 4 are redundant and it have no effect.

Step2: when execute sql drop command says " is not valid DQL or DML language ". Step4: When i perform update by selecting core and it will not remove orphan attribute.

[vladteodorescu]

This doesn't work, as hybris doesn't accept "alter table" commands.

[Former Member]

Removed Types can be removed via the HAC

https://wiki.hybris.com/display/release5/Cleanup+Type+System

This won't delete the columns though. However if nothing is referring to these columns you should be safe to drop them if you would like to keep the schema clean.

[Former Member]

@Richard

I removed the referencing types from the -items.xml, I then removed the attributes from the extended type.

Ran update and the referencing types were removed.

I then went into HAC and entered in a ALTER command in the SQL tab but I get an error saying that I entered in a invalid DML or DQL statement.