Suggest an Idea

[billmcevoy]

When a replicate task restarts the target tables will get dropped and recreated or truncated -- depending on the settings.

I suggest there be an option to run a SQL script before and after this point.  

When the tables are recreated by Replicate, new columns are brought over (which is great) but we lost post-creation settings such as compression.  

Truncating tables is an answer to that issue of course, but does not allow us to bring over new columns.

Having the ability to run a custom SQL script would give us the option to include post-create changes.

[john_wang]

Hello @billmcevoy and all,

For easy trace I'm adding the original article:

https://community.qlik.com/t5/Qlik-Replicate/Compressing-Tables-on-SQL-Server/m-p/2023986#M4666

thanks,

John.

[billmcevoy]

Thank you John.  Much appreciated!

[joseph_jbh]

Hi Bill;

The way we achieve compression on our MS-SQL targets, is by utilizing the target endpoint's internal param "$info.query_syntax.create_primary_key". 

ALTER TABLE ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC} ADD CONSTRAINT ${QO}${CONSTRAINT_NAME}${QC} PRIMARY KEY ( ${COLUMN_LIST} );ALTER INDEX ${QO}${CONSTRAINT_NAME}${QC} on ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC} REBUILD with (ONLINE = ON, DATA_COMPRESSION = PAGE)

It's not perfect, but it works for what we need it for!

[john_wang]

Hello @joseph_jbh , copy @billmcevoy ,

Thank you so much for your valuable idea! it helps a lot!!

The compression is enabled just after table creation and before the data load. Great idea!!

Besides enable compression on an INDEX it can also be enabled on TABLE Enable Compression on a Table or Index . below sample works for TABLE level as well:

ALTER TABLE ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC} ADD CONSTRAINT ${QO}${CONSTRAINT_NAME}${QC} PRIMARY KEY ( ${COLUMN_LIST} );ALTER TABLE ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC} REBUILD WITH (DATA_COMPRESSION = PAGE)

The only concern is that the table must have Primary Key (or Unique Index if no PK) otherwise the above SQL cannot be triggered. anyway, looks like it's the best workaround till now.

Best Regards,

John.

[Meghann_MacDonald]

From now on, please track this idea from the Ideation portal. 

Link to new idea

Meghann

NOTE: Upon clicking this link 2 tabs may open - please feel free to close the one with a login page. If you only see 1 tab with the login page, please try clicking this link first: Authenticate me! then try the link above again. Ensure pop-up blocker is off.