Chat now with support
Chat with Support

SharePlex 8.6.6 - Reference Guide

About this guide Conventions used in this guide SharePlex commands SharePlex parameters SharePlex utilities Appendix B: SharePlex environment variables

SQL Server Capture Parameters

SharePlex parameters > Descriptions of user-configurable parameters

This section describes the user-configurable SharePlex parameters. Parameters not documented in this chapter are internal parameters that should only be modified under guidance of a Quest developer or Technical Support representative.

Parameters are grouped as follows:

Read Parameters

These parameters are used by the SharePlex Read process.

SP_ORD_BATCH_ENABLE

This parameter controls the enabling of the Batch Processing functionality. By default it is enabled. This allows the Read process to combine multiple identical records into a single record, or into a batch, for processing by the Post process.

 

Default: 1 (on)

Range of valid values: 0 or 1 (flag)

Takes effect: when Read is restarted

 

SP_ORD_BATCH_MAX

This parameter controls the maximum number of concurrent batch transactions.

 

Default: 50 (transactions)

Range of valid values: any integer between (and including) 5 and 250

Takes effect: when Read is restarted

 

SP_ORD_BATCH_MATCH_MIN

This parameter controls the minimum number of matched operations before processing a batch.

 

Default: 2

Range of valid values: any positive integer

Takes effect: when Read is restarted

 

SP_ORD_CDA_LIMIT

This parameter controls the number of cursors cached by each login of the Read process. You might need to increase its value if replication starts falling behind Oracle activity on the source system. An initial setting of 15 cursors is recommended if you have a large number of tables in replication.

 

Default: 5 cursors

Range of valid values: any positive integer

Takes effect: when Read is restarted

 

SP_ORD_DATE_ MSG

This parameter can be set so that the Read process prints a warning message to the Event Log and the Read log when it detects an invalid date column. A setting of 0 disables the parameter, and a setting of 1 activates it. The error message generated by Read is:

An oerr#1801 has occurred on record with rowid rowid, on object object_id. Rec skipped. It is usually caused by invalid column data of type DATE. Creating a unique index that doesn't include column of type DATE and reactivating same configuration may solve the problem.

 

Default: 0 (do not print warning message)

Range of valid values: 0 or 1 (flag)

Takes effect: immediately.

 

SP_ORD_DELAY_RECORDS

The Read process processes records in batches. The size of the batches depends on the number of records in the capture queue. If the number of records is large, Read ignores the value of this parameter, and the batches are kept as small as possible. If Read is keeping pace with Capture, the size of the batch is approximately the value set by this parameter.

Set this parameter low if SharePlex is generating rollback segment too old messages in the Event Log. It instructs SharePlex to pass the data along sooner. This parameter is of use where only minimal latency can be tolerated.

Important: Use this parameter with caution, because reducing the number of records increases the I/O that SharePlex must perform, which increases system overhead. It can also negatively impact smooth interaction between SharePlex processes.

 

Default: 200 records

Range of valid values: any positive integer

Takes effect: Immediately

 

SP_ORD_FIRST_FIND

This parameter controls how the Read process checks column conditions to determine whether or not a replicated row change satisfies them.

  • At the default of 1, when a row change satisfies a column condition, SharePlex does not check any other column conditions to see if that row change also satisfies any of them.
  • At a value of 0, SharePlex sends the data to all target systems where the column conditions are satisfied.

For more information about horizontal partitioning, see Chapter 5 of the SharePlex Administrator’s Guide.

 

Default: 1 (on)

Range of valid values: 0 or 1 (flag)

Takes effect: when Read is restarted

 

SP_ORD_HP_HASH

This parameter controls the number of slots in the hash table sued for Horizontal Partitioning. By default this parameter is set to 16 slots to minimize memory usage (usage is the number of slots x 32 bytes for every transaction with an insert). If the user’s system does a lot of insert operations followed by updates (in the same transaction) on a horizontally partitioned table then SharePlex will use this hash table a lot and this value may need to be increased for performance. Additionally, if the user system has a lot of long transactions with inserts on tables with horizontal partitioning the value of this parameter might need to be increased.

 

Default: 16 slots

Range of valid values: any positive integer

Takes effect: when Read is restarted

 

SP_ORD_HP_IN_SYNC

This parameter is used for horizontally partitioned replication to ensure that data is replicated properly when a value for a column in a column condition changes so that the row no longer satisfies the condition. It enables SharePlex to automatically correct the following:

  • UPDATEs that cause a row to meet a different column condition than the one created for that row, sending the changes to a different location. An example would be an UPDATE to a row for which the column condition is region=East that changes the value of the region column to WEST. Such operations will fail because the original INSERT statement for that row was replicated to the original location (the Eastern region), so the row does not exist in the new location (the Western region) when Post attempts the update there.
  • UPDATEs that cause a row to meet a column condition (and be replicated) when the row was not supposed to be replicated. An example would be when the region column is updated from the value of HEADQUARTERS (for which a row is not replicated) to the value of WEST. Such operations will fail because the original INSERT statement for that row (into the headquarters system) was not replicated to the Western region’s system, so Post cannot perform the update there.

  • UPDATEs that cause a row to no longer meet any column condition. An example would be when the region column is updated from the value of WEST to the value of HEADQUARTERS. The original INSERT statement was replicated to the Western region’s system, but the update to the new value is not replicated, because the new value does not meet a column condition (headquarters data is not shared). The rows are now out of synchronization, but there are no errors.

When this parameter is enabled, SharePlex automatically corrects rows for which UPDATEs cause the preceding conditions. SharePlex converts the UPDATE to a DELETE and, if needed, an INSERT.

To convert an UPDATE statement (which normally only uses the changed columns and the key) to an INSERT statement, SharePlex needs values for all of the columns. Enabling SP_ORD_HP_IN_SYNC directs SharePlex to send all of the columns in a row to the Post process when there is an UPDATE to a table using horizontally partitioned replication, so that an INSERT can be constructed.

Set this parameter on the source system before you activate the configuration. If replication is active, set the parameter and then reactivate the configuration so that SharePlex can rebuild its object cache.

If you know that the columns in column conditions for tables using horizontally partitioned replication will never change, leave this parameter set to 0, because using it incurs processing overhead.

 

Default: 0 (off)

Range of valid values: 0 or 1 (flag)

Takes effect: when Read is restarted

 

SP_ORD_LDA_ARRAY_SIZE

This parameter controls the number of logins made to the database for read consistency. If the Read process slows down, try increasing the value of this parameter. The maximum setting is determined by the MAX_PROCESSES parameter in the init_ora file.

 

Default: 5 logins

Range of valid values: any positive integer

Takes effect: when Read is restarted

 

SP_ORD_ONELINE_DEBUG

This parameter controls the oneline debug feature for the Read process.

The oneline debug feature enables you to perform debugging for out-of-sync errors without consuming a large amount of the system resources. The oneline debug logs just enough information in one line to detect where in the data stream an out-of-sync condition occurred. Once oneline debug identifies the process that is causing the problem, you can then enable regular debugging for that process.

To enable this parameter, specify the objects that you want to debug by their object ID, and separate each one by a comma. An example is:

sp_ctrl> set sp_ord_oneline_debug 230230, 351626

The following items are logged:

  • The state of the operation, which can be Dispatch (read from queue), processMessage (process the SQL code), or ExecSQL (apply to target)
  • The transaction identifier used by SharePlex
  • The type of DML or DDL operation that was affected
  • The owner and name of the target table
  • The object ID of the affected source table
  • The row identifier of the affected row.
  • The time when the operation occurred
  • The log sequence number and offset within the log of the affected operation
  • The Oracle SCN for the transaction
  • An internal SharePlex code that prevents redundant operations
  • The routing information

Note: This information is repeated for each target if the row is being routed to multiple targets.

 

Default: Disabled

Range of valid values: a string that forms a list of objects listed by object ID, separated by commas.

Takes effect: Immediately

 

SP_ORD_MSGS_CK_FREQ and SP_ORD_RCM_SKIP_RATIO

These parameters work together, so if one of them is set its default value of 0, then the other parameter, if set to a number other than 0, does not work. Both parameters support the automated process in which the Read process detects that too much time is being taken to process its queries, by checking the ratio of disk gets per executed queries. Once Read makes this determination, it gets rid of its existing read consistent views and replaces it with a new view.

The ORD_MSGS_CK_FREQ parameter is the frequency that the Read process checks if the ratio has been surpassed. For example, if you set this parameter to 100, the Read process will check every 100 queries to see if the ratio is still good. The recommended setting is 1,000 queries.

The ORD_RCM_SKIP_RATIO parameter specifies the number (the ratio of disk gets to executed queries) that when reached, causes the read consistent view to be replaced. The most sensitive setting is 1, which means one disk get per one executed query. The recommended setting is 2.

To turn off these parameters, set one of them to 0, which disables the other parameter.

 

Default:

SP_ORD_MSGS_CK_FREQ: 1,000

SP_ORD_RCM_SKIP_RATIO: 2

Range of valid values:

SP_ORD_MSGS_CK_FREQ: 0 to 100,000

SP_ORD_RCM_SKIP_RATIO: 0 to 1000 (not recommended to set this greater than 5)

Takes effect: Immediately

 

SP_ORD_RMSG_LIMIT

This parameter controls the frequency of the checkpoints performed by the Read process when it reads messages from the capture queue and determines the key values. A checkpoint saves the capture queue to a cache file, commits the outgoing queue messages (being passed to the export queue), and does a read release (delete) on the heldover records in the capture queue that have already been received by the export queue.

The higher the value of this parameter, the more records will be held in memory before the checkpoint is triggered. A very high value causes less I/O on the system and faster processing — but at the expense of increased memory usage and a longer recovery time should something unforeseen cause the Read process stop. A low value increases I/O, which increases the recovery speed but reduces throughput speed.

The default value of 1,000 records should establish a reasonable balance between the need for speed and the conservation of memory and process recovery, but you can adjust this parameter to suit your processing requirements. Adjustment options range from checkpointing after every record to holding as many records as the system and its memory can accommodate.

 

Default: 1,000 records

Range of valid values: any positive integer within system limitations

Takes effect: immediately

 

SP_ORD_ROLLBACK_TXNS_MAX

This parameter limits the number of entries the reader will keep in its transaction cache that indicate a transaction was completely rolled back

 

Default: 5,000 records

Range of valid values: any positive integer within system limitations

Takes effect: process restart

 

SP_ORD_SEND_DDL_TO_FIRST

This parameter helps improve Post performance when multiple post queues are in use and you are replicating DDL for objects that are not in the replication configuration (SP_OCT_REPLICATE_ALL_DDL=1).

This parameter directs Import to send DDL for objects that are not in the replication configuration to a specific post queue. Normally, DDL that is not related to objects in the replication configuration can be sent to any available queue. Large amounts of this DDL can block the DML of the objects that are in replication, causing data latency to increase.

This DDL, because it has no association with the objects in replication, does not have to be applied in any sequence relative to those objects. By routing it independently through a dedicated queue, you can free the other queues to process replicated data and its related DDL.

If you activate this parameter, you must specify the post queue through which to process the DDL for the non-replicating objects. This specification must be placed on the first line after the "Datasource:o.SID" line in the configuration file, as follows (the ! is a placeholder that replaces an actual object specification):

Datasource: o.dbprod

#Route for non-replication DDL

! sysmm:queddl1@o.dbprep

#The rest of the configuration entries

SCOTT.EMP SCOTT.EMP sysmm:que1@o.dbrep

SCOTT.FOO SCOTT.FOO sysmm:que2@o.dbrep

If you do not make an explicit designation in that manner, the DDL will be routed to the first route listed. For example, in the following configuration file, all DDL that is not associated with an object in replication will be sent to sysmm:que1@o.dbrep.

Datasource: o.dbprod

SCOTT.EMP SCOTT.EMP sysmm:que1@o.dbrep

SCOTT.FOO SCOTT.FOO sysmm:que2@o.dbrep

 

Default: 1 (on)

Range of valid values: 0 or 1 (flag)

Takes effect: immediately.

 

Export Parameters

These parameters are used by the SharePlex Export process.

SP_XPT_AES_KEY_LENGTH

This parameter controls the size of the AES encryption key that is generated by the create encryption key command. To configure SharePlex for AES encryption, see Encrypt data across the network.

 

Default: 128-bits

Range of valid values: 128, 192 or 256 bits

Takes effect: After restarting Export

 

SP_XPT_ALTERNATE_HOST

This parameter provides an alternate target IP address when you need to connect to a different target machine. The SP_XPT_ALTERNATE_HOST parameter allows you to change your target host without shutting down your active configuration and thus losing your data. The parameter can be used any time you want to change your target from the one specified in the current active configuration file. For example, if you are having difficulties with your predefined target host, you can set the parameter to the IP address of your alternate target machine, stop the Export process and then restart it.

 

Default: None

Range of valid values: a valid IP address

Takes effect: After restarting Export

 

SP_XPT_ENABLE_AES

This parameter controls whether Advanced Encryption Standard (AES) is enabled. The Export parameter SP_XPT_AES_KEY_LENGTH controls whether encryption is performed and the size of the key. Export communicates these factors to the Import process.

For more information, see SP_XPT_AES_KEY_LENGTH.

To configure SharePlex for AES encryption, see Encrypt data across the network in the SharePlex Administration Guide.

 

Default: 0 (disabled)

Range of valid values: 0 or 1 (enabled)

Takes effect: After restarting Export

 

SP_XPT_ENABLE_COMPRESSION

This parameter controls the compression of data across TCP/IP.

You can enable compression to reduce the amount of data that SharePlex sends across the network. SharePlex uses LZIP lossless compression. Enabling compression on the source SharePlex instance automatically enables compression to all targets of the source SharePlex instance.

By default compression is disabled. You can enable compression by itself or in conjunction with encryption. For more information about encryption, see Encrypt data across the network in the SharePlex Administration Guide.

 

Default: 0 (disabled)

Range of valid values: 0 or 1 (enabled)

Takes effect: After restarting Export

SP_XPT_PORT_OVERRIDE

This parameter works in conjunction with the SP_XPT_USE_LOCALHOST parameter to enable SharePlex to send data through a secure tunnel port created with SSH® Secure ShellTM software. This parameter sets the local connection, which overrides the default SharePlex port. From that port number, the SSH daemon directs the connection to a different system (the SharePlex target machine) using another port number and the SSH data encryption.

 

Default: 0 (disabled)

Range of valid values: 0 or any positive integer

Takes effect: when Export is restarted

 

SP_XPT_SO_SNDBUF

This parameter tunes the TCP/IP window size on the source machine. It works in conjunction with the SP_COP_SO_RCVBUF parameter on the target machine to establish the size of a packet sent across the network. SharePlex references both parameters when TCP/IP sockets are created at the startup of sp_cop and the Export process.

If SharePlex is replicating across a WAN and the export queue is continually backlogged, try adjusting both parameters. SP_XPT_SO_SNDBUF must be set equal to or greater than the value of SP_COP_SO_RCVBUF, in multiples of 1024 bytes. To size the parameters, determine the ping time between the source and target machines, then use the following formula for both parameters:

param_value / ping_time= bytes per second

For example, if ping time is 200 milliseconds, and the value for the two parameters is 64K, SharePlex will send five 64K-packets every second, totaling 320K per second.

Unless you observe a bandwidth problem, Quest recommends leaving both parameters set to their defaults, which use the system's setting. To change SP_XPT_SO_SNDBUF, set it on the source system, then stop and start Export on that system.

If transfer still is slow, try increasing the SP_IMP_WCMT_MSGCNT and SP_IMP_WCMT_TIMEOUT parameters on the target system. Set SP_IMP_WCMT_MSGCNT to at least 10,000.

 

Default: 0 (default is set by the operating system)

Range of valid values: positive integers, in bytes, using multiples of 1024. Maximum is set by the operating system.

Takes effect: when Export is restarted

 

SP_XPT_USE_LOCALHOST

This parameter enables SharePlex to send data through a secure tunnel port created with SSH® Secure ShellTM software. The Export process reads this parameter before making a TCP connection. If the parameter is enabled, the Export process connects to the local host through a local port number, where the SSH daemon directs the connection to a different system (the SharePlex target machine) using another port number and the SSH data encryption.

 

Default: 0 (disabled)

Range of valid values: 0 or 1 (flag)

Takes effect: when Export is restarted

 

Import Parameters

These parameters are used by the SharePlex Import process.

SP_IMP_QUEUE_PAUSE

  • This parameter pauses the writing of data to the post queue when that queue contains the specified number of messages. Post stores queue messages in shared memory until it issues a checkpoint, after which it releases the data from memory.
  • If the post queue runs out of shared memory, the read and write functions will start incurring file IO to free up the memory buffers. By pausing the queue writing, this parameter helps Post maintain its performance by avoiding the need for disk storage and the resultant slowdown in IO.
  • When Import is requested to write to a post queue, and the backlog for that queue is equal to or greater than SP_IMP_QUEUE_PAUSE, Import checkpoints with it associated Export and stops writing to any post queue. Import does, however, remain running. A warning is written to the event log and the status of the process in the show statusdb command output is shown as "paused." The status command shows "Paused" instead of "Running."

    Import resumes writing to the post queue(s) if Import is stopped and restarted or Import detects that the backlog is less than or equal to the SP_IMP_QUEUE_RESUME parameter.

    Use the SP_IMP_QUEUE_RESUME parameter to set the number of messages at which Import resumes writing to the post queue.

    To use this feature, both SP_IMP_QUEUE_PAUSE and SP_IMP_QUEUE_RESUME must be greater than zero, and SP_IMP_QUEUE_PAUSE must be greater than SP_IMP_QUEUE_RESUME.

     

    Default: 0 messages (disabled)

    Range of valid values: n thousand messages, where n is any positive integer

    Takes effect: immediately

     

    SP_IMP_QUEUE_RESUME

    This parameter works in conjunction with SP_IMP_QUEUE_PAUSE. If the number of messages in the post queue is lower or equal to the value set with this parameter, Import resumes writing to the post queue.

    To use this feature, both SP_IMP_QUEUE_PAUSE and SP_IMP_QUEUE_RESUME must be greater than zero, and SP_IMP_QUEUE_PAUSE must be greater than SP_IMP_QUEUE_RESUME.

     

    Default: 0 messages (disabled)

    Range of valid values: n thousand messages, where n is any positive integer

    Takes effect: immediately

     

    SP_IMP_WCMT_MSGCNT

    This parameter works in conjunction with the SP_IMP_WCMT_TIMEOUT parameter. It defines the number of messages that are processed before the Import process checkpoints. Checkpointing saves the state of the process in case it is needed for failure recovery.

    When Import checkpoints, it triggers the Export process to perform its own checkpoint. If the value for this parameter is reached before the value set for SP_IMP_WCMT_TIMEOUT, it triggers the checkpoint.

    In a WAN environment, you can increase this parameter to as many as 10,000 messages, with the understanding that increasing the message interval between checkpoints can negatively affect SharePlex's fault tolerance. If you are using SharePlex in an environment where the network continually fails, you can decrease this parameter.

     

    Default: 10,000 messages

    Range of valid values: any positive integer

    Takes effect: immediately

     

    SP_IMP_WCMT_TIMEOUT

    This parameter works in conjunction with the SP_IMP_WCMT_MSGCNT parameter. It defines the number of seconds that pass before the Import process checkpoints. Checkpointing saves the state of the process in case it is needed for failure recovery.

    When Import checkpoints, it triggers the Export process to perform its own checkpoint. If the value for this parameter is reached before the value set for SP_IMP_WCMT_MSGCNT, it triggers the checkpoint.

     

    Default: 30 seconds

    Range of valid values: any positive integer

    Takes effect: immediately

    Related Documents