Description
Use the create_ignore.sql utility script to prevent DML transactions from being replicated to the target system. This script creates a public procedure named SHAREPLEX_IGNORE_TRANS in the source database. When executed at the start of a transaction, the procedure makes the Capture process ignore DML operations that occur from the point of execution until the transaction is either committed or rolled back. Thus, the affected operations are not replicated.
Only DML operations are affected by the SHAREPLEX_IGNORE_TRANS procedure. It does not cause SharePlex to skip DDL operations, including TRUNCATE. DDL operations are implicitly committed by Oracle, so they render the procedure invalid.
Supported databases
Oracle on Unix and Windows
Run create_ignore.sql
Run the create_ignore.sql script from the util sub-directory in the SharePlex product directory. Run it as the SharePlex Oracle user so that the procedure is created in the SharePlex schema.
The script executes the following PL/SQL:
CREATE OR REPLACE PROCEDURE SHAREPLEX_IGNORE_TRANS AS
TNUM NUMBER;
BEGIN
INSERT INTO SHAREPLEX_TRANS (TRANS_NUM, QUE_SEQ_NO_1, QUE_SEQ_NO_2,
COMBO, OP_TYPE) VALUES (-999,0,0,'DUMMY',0);
DELETE FROM SHAREPLEX_TRANS WHERE TRANS_NUM=-999 AND COMBO='DUMMY'
AND OP_TYPE=0;
END;
/
GRANT EXECUTE ON SHAREPLEX_IGNORE_TRANS TO PUBLIC;
/
Execute SHAREPLEX_IGNORE_TRANS
Call SHAREPLEX_IGNORE_TRANS only at the beginning of a transaction containing operations that you do not want replicated. If it is called in the middle of a transaction, replicated operations preceding the start of the procedure will remain in the post queue indefinitely awaiting a COMMIT, because SharePlex does not release messages without one. The COMMIT will not arrive because Capture ignores all operations in the transaction after the procedure is called. The Read process will retain unwanted cache information on those records indefinitely.
- Log onto SQL*Plus as the SharePlex user.
-
Execute the SHAREPLEX_IGNORE_TRANS procedure at the beginning of the transaction that you want to skip.
execute SharePlex_schema.SHAREPLEX_IGNORE_TRANS;
- In one transaction, make the changes that you do not want replicated. They will be ignored by Capture.
- After the COMMIT or ROLLBACK, replication of subsequent transactions resumes normally.
Description
Use the hana_cleansp utility to remove the current replication state on a system where SharePlex is replicating to a HANA target.
Caution: The effects of hana_cleansp are not reversible. To stop replication without restoring it to an initial state, you might be able to use the abort config or purge config command.
Usage guidelines
What this utility does
hana_cleansp does the following on the target system:
- Truncates the SHAREPLEX_OPEN_TRANS internal table in the SharePlex schema and any other SharePlex-installed tables as applicable.
- Removes the following from the variable-data directory: the queue files, the process log files, the contents of the statusdb (but not the file), the contents of the dump and state directories, and all but one entry in the event log (the status entry for hana_cleansp)
hana_cleansp preserves the following:
- The SharePlex database, account, and password, and only cleans the data in the SharePlex tables.
- User-created files such as the paramdb and the target configuration settings
Supported databases
HANA on supported platforms
Run hana_cleansp
- Stop all SharePlex processes on the system.
- Shut down sp_cop.
-
Run the hana_cleansp utility from the bin sub-directory of the SharePlex product directory with the following syntax:
C:\users\splex\bin>hana_cleansp [port number ] database_name/user_name
where:
- port number is the port number of the SharePlex instance, if other than the default of 2100.
- database_name is the name of the SharePlex database.
- user_name is the name of the SharePlex user account.
-
Enter the password at the prompt.
Enter the password for the SharePlex User :
-
Type Y to confirm that you want to run cleanup for this SharePlex instance.
Are you sure you want to perform the clean for '/splex/vardir/var2200' and port 2200? [N] : Y
Description
Use the mss_cleansp utility to remove the current replication state on a SQL Server system.
Caution:
- Running mss_cleansp can be acceptable in a test or demonstration environment, but it rarely is appropriate in a production environment. The effects of the cleanup are not reversible.
- To fully remove the replication environment, run the mss_cleansp utility on the target system. If the source system is Oracle, run the ora_cleansp utility. To verify if and when mss_cleansp or ora_cleansp was run on a system, view the SharePlex event log on that system.
- If the SharePlex instance for which you are running mss_cleansp is configured to post to multiple databases, mss_cleansp will remove the replication configuration and supporting data for all of those databases.
mss_cleansp does the following on the target system:
- Truncates the SHAREPLEX_OPEN_TRANS internal table and any other SharePlex-installed tables as applicable.
- Removes the following from the variable-data directory: the queue files, the process log files, the contents of the statusdb (but not the file), the contents of the dump and state directories, and all but one entry in the event log (the status entry for mss_cleansp)
mss_cleansp preserves the following:
- The SharePlex database, account, and password, and only cleans the data in the SharePlex tables.
- User-created files such as the paramdb and the target configuration settings
Supported databases
Microsoft SQL Server on Windows
Run mss_cleansp
- Stop all SharePlex processes on the system.
- Shut down sp_cop.
-
Run the mss_cleansp utility from the bin sub-directory of the SharePlex product directory with the following syntax:
C:\users\splex\bin>mss_cleansp [port number ] database_name/user_name
where:
- port number is the port number of the SharePlex instance, if other than the default of 2100.
- database_name is the name of the SharePlex database.
- user_name is the name of the SharePlex user account.
-
Enter the password at the prompt.
Enter the password for the SharePlex User :
-
Type Y to confirm that you want to run cleanup for this SharePlex instance.
Are you sure you want to perform the clean for 'C:\splex\vardir\var2200' and port 2200? [N] :Y
Description
Use the mysql_cleansp utility to remove the current replication state on a MySQL system.
Caution: Running mysql_cleansp can be acceptable in a test or demonstration environment, but it rarely is appropriate in a production environment. The effects of the cleanup are not reversible.
Usage guidelines
What this utility does
mysql_cleansp does the following on the target system:
- Truncates the SHAREPLEX_OPEN_TRANS internal table and any other SharePlex-installed tables as applicable.
- Removes the following from the variable-data directory: the queue files, the process log files, the contents of the statusdb (but not the file), the contents of the dump and state directories, and all but one entry in the event log (the status entry for mysql_cleansp)
mysql_cleansp preserves the following:
- The SharePlex database, account, and password, and only cleans the data in the SharePlex tables.
- User-created files such as the paramdb and the target configuration settings
Supported databases
MySQL on supported UNIX platforms
Run mysql_cleansp
- Stop all SharePlex processes on the system.
- Shut down sp_cop.
-
Run the mysql_cleansp utility from the bin sub-directory of the SharePlex product directory with the following syntax:
C:\users\splex\bin>mysql_cleansp [port number ] database_name/user_name
where:
- port number is the port number of the SharePlex instance, if other than the default of 2100.
- database_name is the name of the SharePlex database.
- user_name is the name of the SharePlex user account.
-
Enter the password at the prompt.
Enter the password for the SharePlex User :
-
Type Y to confirm that you want to run cleanup for this SharePlex instance.
Are you sure you want to perform the clean for '/splex/vardir/var2200' and port 2200? [N] : Y
