How to: Configure the Transaction Set Job for an Oracle Publisher (Replication Transact-SQL Programming)

The Xactset job is an Oracle database job created by replication that runs at an Oracle Publisher to create transaction sets when the Log Reader Agent is not connected to the Publisher. You can enable and configure this job from the Distributor programmatically using replication stored procedures. For more information, see Performance Tuning for Oracle Publishers.

To enable the transaction set job

  1. At the Oracle Publisher, set the job_queue_processes initialization parameter to a sufficient value to allow the Xactset job run. For more information about this parameter, see the database documentation for the Oracle Publisher.

  2. At the Distributor, execute sp_publisherproperty (Transact-SQL). Specify the name of the Oracle Publisher for @publisher, a value of xactsetbatching for @propertyname, and a value of enabled for @propertyvalue.

  3. At the Distributor, execute sp_publisherproperty (Transact-SQL). Specify the name of the Oracle Publisher for @publisher, a value of xactsetjobinterval for @propertyname, and the job interval, in minutes, for @propertyvalue.

  4. At the Distributor, execute sp_publisherproperty (Transact-SQL). Specify the name of the Oracle Publisher for @publisher, a value of xactsetjob for @propertyname, and a value of enabled for @propertyvalue.

To configure the transaction set job

  1. (Optional) At the Distributor, execute sp_publisherproperty (Transact-SQL). Specify the name of the Oracle Publisher for @publisher. This returns properties of the Xactset job at the Publisher.

  2. At the Distributor, execute sp_publisherproperty (Transact-SQL). Specify the name of the Oracle Publisher for @publisher, the name of the Xactset job property being set for @propertyname, and new setting for @propertyvalue.

  3. (Optional) Repeat step 2 for each Xactset job property being set. When changing the xactsetjobinterval property, you must restart the job on the Oracle Publisher for the new interval to take effect.

To view properties of the transaction set job

  • At the Distributor, execute sp_helpxactsetjob. Specify the name of the Oracle Publisher for @publisher.

To disable the transaction set job

  • At the Distributor, execute sp_publisherproperty (Transact-SQL). Specify the name of the Oracle Publisher for @publisher, a value of xactsetjob for @propertyname, and a value of disabled for @propertyvalue.

Example

The following example enables the Xactset job and sets an interval of three minutes between runs.

-- This script uses sqlcmd scripting variables. They are in the form
-- $(MyVariable). For information about how to use scripting variables  
-- on the command line and in SQL Server Management Studio, see the 
-- "Executing Replication Scripts" section in the topic
-- "Programming Replication Using System Stored Procedures".

DECLARE @publisher AS sysname;
SET @publisher = $(Publisher);

-- Enable the creation of transaction sets
-- at the Oracle Publisher.
EXEC sp_publisherproperty 
  @publisher = @publisher, 
  @propertyname = N'xactsetbatching', 
  @propertyvalue = N'enabled';

-- Set the job interval before enabling
-- the job, otherwise the job must be restarted.
EXEC sp_publisherproperty 
  @publisher = @publisher, 
  @propertyname = N'xactsetjobinterval', 
  @propertyvalue = N'3';

-- Enable the transaction set job.
EXEC sp_publisherproperty 
  @publisher = @publisher, 
  @propertyname = N'xactsetjob', 
  @propertyvalue = N'enabled';
GO