CDC Guide for Linux, UNIX, and Windows

10.5
- 10.5.6
- 10.5.5
- 10.5.4
- 10.5.3
- 10.5.2
- 10.5.1
- 10.4.1
- 10.4.0 HotFix 1
- 10.4.0

Back Next

Changing the MULTIPUB Parameter Setting After Running Extractions

After you run change data extraction processing, you can change the MULTIPUB parameter setting in the MSQL CAPI_CONNECTION statement. You might need to do this task if you add or remove publication databases that include sources of CDC interest. To preserve data integrity, you must use the proper procedure.

The MULTIPUB parameter indicates whether you extract data for articles in a single publication database or in multiple publications. For a single publication database, Informatica recommends that you set MULTIPUB to N so that PowerExchange can use more efficient extraction processing. For multiple publications, you must set MULTIPUB to Y, the default setting. This parameter applies to real time extractions directly from the change stream, and PowerExchange Logger for Linux, UNIX, and Windows extractions in continuous extraction mode.

To switch the MULTIPUB setting from Y to N:

Use this procedure to switch the MULTIPUB from the default of Y to N. If you use the PowerExchange Logger for Linux, UNIX, and Windows, you must cold start it after making this change.

Stop extraction workflows that process the SQL Server distribution database and that are running in real-time extraction mode or continuous extraction mode.

If you use the PowerExchange Logger for Linux, UNIX, and Windows, stop the PowerExchange Logger.

In the dbmover configuration file, edit the MSQL CAPI_CONNECTION statement to switch the MULTIPUB parameter setting from Y to N.

Cold start the PowerExchange Logger.

Restart the extraction workflows.

The sequence tokens no longer include a timestamp.

To switch the MULTIPUB setting from N to Y:

Use this procedure to switch the MULTIPUB from N back to Y. If you use the PowerExchange Logger for Linux, UNIX, and Windows, you do not need to cold start it after making this change.

Stop DELETE, INSERT, and UPDATE activity on the SQL Server source tables.

Wait for the extraction workflows to reach the end of log and then stop them.

In the dbmover configuration file, edit the MSQL CAPI_CONNECTION statement to switch the MULTIPUB parameter setting from N to Y.

If you use the PowerExchange Logger for Linux, UNIX, and Windows, ensure that the DISTSRV and DISTDB parameters are specified in the PowerExchange Logger pwxccl.cfg configuration file. These parameters are required when MULTIPUB=Y.

To help avoid performance degradation, define the following index on the distribution database:

USE [distribution]
GO
/****** Object: Index [IX_MSrepl_transactions] Script Date: 03/31/2012 11:56:07 ******/
CREATE NONCLUSTERED INDEX [IX_MSrepl_transactions] ON [dbo].[MSrepl_transactions] 
(
[entry_time] ASC,
[publisher_database_id] ASC,
[xact_seqno] ASC,
[xact_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

To get the current restart tokens for the end of log, use one of the following methods:

Run the DTLUAPPL utility with the GENERATE RSTKKN option.

In the PowerExchange Navigator, perform a database row test with a SELECT CURRENT_RESTART SQL statement.

Specify the CURRENT_RESTART option on the RESTART1 and RESTART2 special override statements in the PWXPC restart token file. When the CDC session runs, PWXPC requests that PowerExchange provide restart tokens for the current EOL. PWXPC uses this restart information to locate the extraction start point.

Add the current restart tokens for the extractions to the restart token file.

Allow DELETE, INSERT, and UPDATE activity to resume on the SQL Server tables.

Cold start the extraction workflows.

PowerExchange adds a timestamp in the sequence token to combine the data from multiple publication databases during extraction processing.

Managing SQL Server CDC

Download Guide

Watch

Comments

Communities

Knowledge Base

Success Portal