Sybase DC38133-01-0902-01 manual Tuning extraction performance for shared rows

Triggers at the	The values in the subscription-list column are maintained by triggers. These
consolidated database	triggers fire at the consolidated database when the triggering inserts or
only	updates are applied by the Message Agent. The triggers must be excluded
	from the remote databases, as they maintain a column that does not exist.
	You can use the sp_user_extraction_hook procedure to exclude only
	certain triggers from a remote database on extraction. The procedure is
	called as the final part of an extraction. By default, it is empty.

❖To customize the extraction procedure to omit certain triggers

1. Ensure the quoted_identifier option is set to ON:

set quoted_identifier on go

2.Any temporary tables referenced in the procedure must exist, or the CREATE PROCEDURE statement will fail. The temporary tables referenced in the following procedure are available in the ssremote.sql script. Copy any required table definitions from the script and execute the CREATE TABLE statements, so they exist on the current connection, before creating the procedure.

3.Create the following procedure:

CREATE PROCEDURE sp_user_extraction_hook

AS

BEGIN

--We do not want to extract the INSERT and

--DELETE triggers created on the Policy table

--that maintain the subscription_list

--column, since we do not include that

--column in the publication.

--If these objects were extracted the

--INSERTs would fail on the remote database

--since they reference a column

--( subscription_list ) that does not exist. DELETE FROM #systrigger

WHERE table_id = object_id( ’Policy’ )

--Do not create any procedures

DELETE FROM #sysprocedure

WHERE proc_name = ’SubscribeCustomer’

END go

Tuning extraction performance for shared rows

When extracting or synchronizing a user, the subscription-listcolumn can cause performance problems as it necessitates a full table scan.

162