Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » MobiLink and SAP HANA Remote Data Sync - Client Administration » SQL Anywhere clients for MobiLink » Event hooks for SQL Anywhere clients

sp_hook_dbmlsync_download_log_ri_violation

Logs referential integrity violations in the download process.

Rows in #hook_dict table
Name Value Description

publication_n (in)

publication

Deprecated. Use subscription_n instead. The publications being synchronized, where n is an integer. There is one publication_n entry for each publication being synchronized. The numbering of n starts at zero.

MobiLink user (in)

MobiLink user name

The MobiLink user for which you are synchronizing.

foreign key table (in)

table name

The table containing the foreign key column for which the hook is being called.

primary key table (in)

table name

The table referenced by the foreign key for which the hook is being called.

role name (in)

role name

The role name of the foreign key for which the hook is being called.

script version (in)

script version name

The MobiLink script version to be used for the synchronization.

subscription_n (in) subscription name(s) The names of subscriptions being synchronized where n is an integer. This is one subscription_n entry for each subscription being synchronized. The numbering of n starts at zero.
Remarks

A download RI violation occurs when rows in the download violate foreign key relationships on the remote database. This hook allows you to log RI violations as they occur so that you can investigate their cause later.

After the download is complete, but before it is committed, dbmlsync checks for RI violations. If it finds any, it identifies a foreign key that has an RI violation and calls sp_hook_dbmlsync_download_log_ri_violation (if it is implemented). It then calls sp_hook_dbmlsync_download_ri_violation (if it is implemented). If there is still a conflict, dbmlsync deletes the rows that violate the foreign key constraint. This process is repeated for remaining foreign keys that have RI violations.

This hook is called only when there are RI violations involving tables that are currently being synchronized. If there are RI violations involving tables that are not being synchronized, this hook is not called and the synchronization fails.

This hook is called on a separate connection from the one that dbmlsync uses for the download. The connection used by the hook has an isolation level of 0 so that the hook can see the rows that have been applied from the download that are not yet committed. The actions of the hook are committed immediately after it completes so that changes made by this hook are preserved regardless of whether the download is committed or rolled back.

Since this hook executes on a separate connection you should use care when accessing tables that are being synchronized in your hook procedure because dbmlsync may have locks on these tables. These locks could cause operations in your hook to fail or to wait indefinitely.

Do not attempt to use this hook to correct RI violation problems. It should be used for logging only. Use sp_hook_dbmlsync_download_ri_violation to resolve RI violations.

Privileges

Hook procedures can be created by any user with the MANAGE REPLICATION system privilege. However, to ensure that the hook can access the #hook_dict table, which is used to pass information in and out of hooks, hooks must meet one of the following requirements:

  • Be owned by a user with the SELECT ANY TABLE and UPDATE ANY TABLE system privileges.

  • Be defined using the SQL SECURITY INVOKER clause of the CREATE PROCEDURE statement.

Example

Assume you use the following table to log referential integrity violations.

CREATE TABLE DBA.LogRIViolationTable
(
    entry_time  TIMESTAMP,
    pk_table  VARCHAR( 255 ),
    fk_table  VARCHAR( 255 ),
    role_name  VARCHAR( 255 )
);

The following example logs the foreign key table name, primary key table name, and role name when a referential integrity violation is detected on the remote database. The information is stored in LogRIViolationTable on the remote database.

CREATE PROCEDURE sp_hook_dbmlsync_download_log_ri_violation()
BEGIN
 INSERT INTO DBA.LogRIViolationTable VALUES( 
 CURRENT_TIMESTAMP,
 (SELECT value FROM #hook_dict WHERE name = 'Primary key table'),
 (SELECT value FROM #hook_dict WHERE name = 'Foreign key table'),
 (SELECT value FROM #hook_dict WHERE name = 'Role name' ) );  
END;