Script to Remove Table Compression

[former_member1144245]

Looking for assistance in creating a custom VB script that will remove the table compression option when it exists. This is for a DB2 .pdm file. Plan to make this mandatory by customizing the .xdb file but need to remove where this option already exists in order to avoid duplicate "compress yes" statements in the DDL. Any assistance would be greatly appreciated!

[GeorgeMcGeachie · Accepted solution]

Hello Monique

It's easy to change the table compression properties without using a script. Create a List Report that includes the relevant properties (e.g. "Compression"), select every row, then change the value of the offending property or properties. If you prefer, access the list of tables via the Model menu, as this is easier to filter.

You say that you're getting duplicate statements, which sounds like a bug - have you told SAP about this?

[former_member1144245 · Accepted solution]

Hello George,

Thanks for your message. I tried your suggestion. Unfortunately, it does not remove the compression statement from the DDL - which is the goal - it only changes the option from compress yes to compress no.

I want to customize the .xdb so that the compression statement is added automatically to every create table DDL. Previously this has been at the discretion of the data modeler but the DBAs want this specified in every case. I know how to change the .xdb to incude this statement, but because of the existing compression option, having this in the .xdb results in two compress yes statements for existing tables where compression was previously defined. I don't believe this would be a bug - just the result of this option being specified in two places.

Appreciate your help. If you have any further thoughts on this, let me know.

[GeorgeMcGeachie · Accepted solution]

My sample PDM uses the DB2 UDB 9.5 DBMS. PD appears to be inconsistent in how it handles compression statements:

• If I uncheck the 'Compression' property in the list of Tables, the compression statement disappears from the SQL preview
• If I uncheck the 'Compress' option on the table's "Physical Options (Common)" tab, the statement changes from 'compress yes' to 'compress no'
• If I delete the 'compress yes' statement via the table's 'Physical Options' tab, the compression statement disappears from the SQL preview

Is this the behaviour you're seeing?

[GeorgeMcGeachie · Accepted solution]

Actually, I think it's more complicated - the results seem to depend on the order you make the changes. That must be a bug, probably caused by SAP giving us too many ways of changing it.

If you don't want any of the 'physical options' , you could try including the 'Options' property in the list of tables, selecting all the rows, and deleting the property contents.

[GeorgeMcGeachie · Accepted solution]

I think the real solution for you is a model check that can automatically remove the compression option.

[former_member1144245 · Accepted solution]

Yes. I am seeing the same thing.

[former_member1144245 · Accepted solution]

I do need the physical options, so cannot remove that statement altogether. With your help, however, a solution was determined. Notice that there are two properties for compression in the list of tables:  Compress (which displays yes, no or is blank) and the Compression option (a check box). I can filter the list of tables on either one of these - but when I delete the 'yes' from the Compress filter - it removes the statemement altogether from the script - which is exactly what I want to achieve. After this is done, I can now customize the .xdb such that compress yes appears in the DDL for all tables existing and going forward and there will no longer be two compression statements in the case where this was previously specified for existing tables.

Thank you so much for your help! Problem solved 🙂