How do I change the default schema for the attrep_changes files?

iPipeline — Sat, 04 Mar 2023 16:15:52 GMT

I would like to move the attrep_changes files out of the schema where the data is stored. Where do I make this change? Can I make the change in one place and have it affect all of the many tasks at the same time?

Thanks

Re: How do I change the default schema for the attrep_changes files?

john_wang — Sun, 05 Mar 2023 02:54:07 GMT

Hello @iPipeline ,

Thanks for reaching out.

If a task's "Change Processing Mode" set to "Batch optimized apply", Replicate utilize Net Change Tables if possible (tables name looks like "attrep_changes25A4554C056FC322") during CDC apply stage, it used to improve the apply performance. In general its schema is as same as the target table schema to avoid spanning out of target DBs which may lead extra cost.

Would you please let us know what's the target DB type and the reason? We'd love to look deeper into it for you.

Regards,

John.

Re: How do I change the default schema for the attrep_changes files?

Barb_Fill21 — Tue, 07 Mar 2023 15:07:20 GMT

@iPipeline

Also, for controlling the Replicate special Tables, you can go to Task settings, Metadata, and then Control Tables. There is a field there that allows you to put a Schema name>> Create target control tables in the schema:

If you put something in there, it will force a Schema name in the name. For example:

I typed in BARB and in my DB2 z/OS task it created the attrep_changes ( and my attrep_apply_exceptions tables) as the following:

2023-03-07T09:57:51:604305 [TARGET_APPLY ]T: Create table statement: CREATE TABLE [BARB].[attrep_changes14426005A56DB539] ( [seq] INT NOT NULL, [col1] NVARCHAR(260), [col2] NVARCHAR(128), [col3] NVARCHAR(40), [col4] NVARCHAR(37), [col5] NVARCHAR(35), [col6] NVARCHAR(32), [col7] NVARCHAR(25), [col8] NVARCHAR(20), [col9] NVARCHAR(20), [col10] NVARCHAR(20), [col11] NVARCHAR(20), [col12] NVARCHAR(20), [col13] NVARCHAR(20), [col14] NVARCHAR(20), [seg1] NVARCHAR(35), [seg2] NVARCHAR(20) ) (provider_syntax_manager.c:1774)

and eventually, my changes came across from the BARB.attrep_changes.

[TARGET_APPLY ]V: Execute: 'INSERT INTO [BARBARA].[CUSTOMER__ct] ([BARBHDR__change_mask],[BARBHDR__stream_position],[ADDRESS],[BARBHDR__timestamp],[BARBHDR__change_seq],[BARBHDR__transaction_id],[NAME],[PHONE],[BARBHDR__operation],[CUSTKEY],[NATIONKEY],[BARB],[JEFF],[BARBHDR__change_oper]) SELECT CONVERT ( varbinary(128) , [BARB].[attrep_changes14426005A56DB539].[col1] , 1 ) , CAST ( [BARB].[attrep_changes14426005A56DB539].[col2] as varchar(128)) COLLATE SQL_Latin1_General_CP1_CI_AS , CAST ( [BARB].[attrep_changes14426005A56DB539].[col3] as varchar(40)) COLLATE SQL_Latin1_General_CP1_CI_AS , CAST ( [BARB].[attrep_changes14426005A56DB539].[col4] as datetime2(6)) , CAST ( [BARB].[attrep_changes14426005A56DB539].[col5] as varchar(35)) COLLATE SQL_Latin1_General_CP1_CI_AS , CAST ( [BARB].[attrep_changes14426005A56DB539].[col6] as varchar(32)) COLLATE SQL_Latin1_General_CP1_CI_AS , CAST ( [BARB].[attrep_changes14426005A56DB539].[col7] as varchar(25)) COLLATE SQL_Latin1_General_CP1_CI_AS , CAST ( [BARB].[attrep_changes14426005A56DB539].[col8] as varchar(15)) COLLATE SQL_Latin1_General_CP1_CI_AS , CAST ( [BARB].[attrep_changes14426005A56DB539].[col9] as varchar(12)) COLLATE SQL_Latin1_General_CP1_CI_AS , CAST ( [BARB].[attrep_changes14426005A56DB539].[col10] as int) , CAST ( [BARB].[attrep_changes14426005A56DB539].[col11] as int) , CAST ( [BARB].[attrep_changes14426005A56DB539].[col12] as varchar(4)) COLLATE SQL_Latin1_General_CP1_CI_AS , CAST ( [BARB].[attrep_changes14426005A56DB539].[col13] as varchar(2)) COLLATE SQL_Latin1_General_CP1_CI_AS , CAST ( [BARB].[attrep_changes14426005A56DB539].[col14] as varchar(1)) COLLATE SQL_Latin1_General_CP1_CI_AS FROM [BARB].[attrep_changes14426005A56DB539] WHERE [BARB].[attrep_changes14426005A56DB539].[seq] >= ? and [BARB].[attrep_changes14426005A56DB539].[seq] <= ?' (ar_odbc_stmt.c:2729)

Hope this helps and please feel free to try it out.

Sincerely,

Barb

Re: How do I change the default schema for the attrep_changes files?

iPipeline — Tue, 07 Mar 2023 15:23:01 GMT

Thanks. That is what I needed.

topic Re: How do I change the default schema for the attrep_changes files? in Qlik Replicate

How do I change the default schema for the attrep_changes files?

Re: How do I change the default schema for the attrep_changes files?

Re: How do I change the default schema for the attrep_changes files?

Re: How do I change the default schema for the attrep_changes files?