idea Add option to run a SQL script when a Replicate task is restarted in Suggest an Idea

Add option to run a SQL script when a Replicate task is restarted

billmcevoy — Thu, 12 Jan 2023 16:02:35 GMT

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.

Re: Add option to run a SQL script when a Replicate task is restarted

john_wang — Fri, 13 Jan 2023 09:21:38 GMT

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.

Re: Add option to run a SQL script when a Replicate task is restarted

billmcevoy — Fri, 13 Jan 2023 13:08:29 GMT

Thank you John. Much appreciated!

Re: Add option to run a SQL script when a Replicate task is restarted

joseph_jbh — Fri, 27 Jan 2023 15:12:14 GMT

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!

Re: Add option to run a SQL script when a Replicate task is restarted

john_wang — Sat, 28 Jan 2023 07:33:29 GMT

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.

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

Meghann_MacDonald — Wed, 02 Aug 2023 14:58:05 GMT

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.

Re: Add option to run a SQL script when a Replicate task is restarted - Status changed to: Closed - Archived

Ideation — Wed, 02 Aug 2023 14:58:07 GMT