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

[iPipeline]

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

[john_wang]

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.

[Barb_Fill21 · Accepted solution]

@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

[iPipeline]

Thanks.  That is what I needed.