Descriptions of asnoqapp parameters

These descriptions provide detail on the asnoqapp parameters, their defaults, and why you might want to change the default in your environment.

apply_server

The apply_server parameter identifies the database where a Q Apply program runs, and where its control tables are stored. The control tables contain information about targets, Q subscriptions, IBM® MQ queues, and user preferences. The Q Apply server must be the same database that contains the targets.

apply_schema

Default: apply_schema=ASN

The apply_schema parameter lets you distinguish between multiple instances of the Q Apply program on a Q Apply server.

The schema identifies one Q Apply program and its control tables. Two Q Apply programs with the same schema cannot run on a server.

A single Q Apply program can create multiple browser threads. Each browser thread reads messages from a single receive queue. Because of this, you do not need to create multiple instances of the Q Apply program on a server to divide the flow of data that is being applied to targets.

apply_path

Default: None

Methods of changing: When Q Apply starts; IBMQREP_APPLYPARMS table

The apply_path parameter specifies the directory where a Q Apply program stores its work files and log file. By default, the path is the directory where you start the program. You can change this path.

If you start a Q Apply program as a Windows service, by default the program starts in the \SQLLIB\bin directory.

applycmd_interval

Default: applycmd_interval=3000 milliseconds

Method of changing: When Q Apply starts, IBMQREP_APPLYPARMS table

The applycmd_interval parameter specifies how often the Q Apply program reads the IBMQREP_APPLYCMD table to look for inserts that prompt the running Q Apply program to execute a specific command. The minimum value is 1000 milliseconds.

applydelay

Default: applydelay=0 seconds

Method of changing: When Q Apply starts

The applydelay parameter controls the amount of time in seconds that the Q Apply program waits before replaying each transaction at the target. The delay is based on the source commit time of the transaction. Q Apply delays applying transactions until the current time reaches or exceeds the source transaction commit time plus the value of applydelay. Changes at the source database are captured and sent to the receive queue, where they wait during the delay period.

This parameter can be used, for example, to maintain multiple copies of a source database at different points in time for failover in case of problems at the source system. For example, if a user accidentally deletes data at the primary system, a copy of the database exists where the data is still available.

The applydelay parameter has no effect on the applyupto or autostop parameters.

Important: If you plan to use the applydelay parameter, ensure that the receive queue has enough space to hold messages that accumulate during the delay period.

applyupto

Default: None

Method of changing: When Q Apply starts

The applyupto parameter identifies a timestamp that instructs the Q Apply program to stop after processing transactions that were committed at the source on or before one of the following times:

A specific timestamp that you provide
The CURRENT_TIMESTAMP keyword, which signifies the time that the Q Apply program started

You can optionally specify the WAIT or NOWAIT keywords to control when Q Apply stops:

WAIT (default): Q Apply does not stop until it receives and processes all transactions up to the specified GMT timestamp or the value of CURRENT_TIMESTAMP, even if the receive queue becomes empty.
NOWAIT: Q Apply stops after it processes all transactions on the receive queue, even if it has not seen a transaction with a commit timestamp that matches or exceeds the specified GMT timestamp or the value of CURRENT_TIMESTAMP.

The applyupto parameter applies to all browser threads of a Q Apply instance. Each browser thread stops when it reads a message on its receive queue with a source commit timestamp that matches or exceeds the specified time. The Q Apply program stops when all of its browser threads determine that all transactions with a source commit timestamp prior to and including the applyupto timestamp have been applied. All transactions with a source commit time greater than the specified GMT timestamp stay on the receive queue and are processed the next time the Q Apply program runs.

The timestamp must be specified in Greenwich mean time (GMT) in a full or partial timestamp format. The full timestamp uses the following format: YYYY-MM-DD-HH.MM.SS.mmmmmm. For example, 2007-04-10-10.35.30.555555 is the GMT timestamp for April 10th, 2007, 10:35 AM, 30 seconds, and 555555 microseconds.

You can specify the partial timestamp in one of the following formats:

YYYY-MM-DD-HH.MM.SS: For example, 2007-04-10-23.35.30 is the partial GMT timestamp for April 10th, 2007, 11:35 PM, 30 seconds.
YYYY-MM-DD-HH.MM: For example, 2007-04-10-14.30 is the partial GMT timestamp for April 10th, 2007, 2:30 PM.
YYYY-MM-DD-HH: For example, 2007-04-10-01 is the partial GMT timestamp for April 10th, 2007, 1:00 AM.
HH.MM: For example, 14:55 is the partial GMT timestamp for today at 2:55 PM.
HH: For example, 14 is the partial GMT timestamp for today at 2 PM.

The partial timestamp could be used to specify a time in the format HH.MM. This format could be helpful if you schedule a task to start the Q Apply program every day at 1 AM Pacific Standard Time (PST) and you want to stop the program after processing the transactions that were committed at the source with a GMT timestamp on or before 4 AM PST. For example, run the following task at 1 AM PST and set the applyupto parameter to end the task at 4 AM PST:

asnqapp apply_server=MYTESTSERVER apply_schema=ASN applyupto=12.00

During daylight saving time, the difference between GMT and local time might change depending on your location. For example, the Pacific timezone is GMT-8 hours during the fall and winter. During the daylight saving time in the spring and summer, the Pacific timezone is GMT-7 hours.

Restriction: You cannot specify both the autostop parameter and the applyupto parameter.

You might want to set the heartbeat interval to a value greater than zero so that the Q Apply program can tell if the time value specified in the applyupto parameter has passed.

autostop

Default: autostop=n

Methods of changing: When Q Apply starts; while Q Apply is running; IBMQREP_APPLYPARMS table

The autostop parameter lets you direct a Q Apply program to automatically stop when there are no transactions to apply. By default (autostop=n), a Q Apply program keeps running when queues are empty and waits for transactions to arrive.

Typically, the Q Apply program is run as a continuous process whenever the target database is active, so in most cases you would keep the default (autostop=n). Set autostop=y only for scenarios where the Q Apply program is run at set intervals, such as when you synchronize infrequently connected systems, or in test scenarios.

If you set autostop=y, the Q Apply program shuts down after all receive queues are emptied once. When the browser thread for each receive queue detects that the queue has no messages, the thread stops reading from the queue. After all threads stop, the Q Apply program stops. Messages might continue to arrive on queues for which the browser thread has stopped, but the messages will collect until you start the Q Apply program again.

Restriction: You cannot specify both the autostop parameter and the applyupto parameter.

buffered_inserts

Default: buffered_inserts=n

Method of changing: When Q Apply starts

Linux, UNIX, Windows: The buffered_inserts parameter specifies whether the Q Apply program uses buffered inserts, which can improve performance in some partitioned databases that are dominated by INSERT operations. If you specify buffered_inserts=y, Q Apply internally binds appropriate files with the INSERT BUF option. This bind option enables the coordinator node in a partitioned database to accumulate inserted rows in buffers rather than forwarding them immediately to their destination partitions. When a buffer is filled, or when another SQL statement such as an UPDATE, DELETE, or INSERT to a different table, or COMMIT/ROLLBACK are encountered, all the rows in the buffer are sent together to the destination partition.

You might see additional performance gains by combining the use of buffered inserts with the commit_count parameter.

When buffered inserts are enabled, Q Apply does not perform exception handling. Any conflict or error prompts Q Apply to stop reading from the queue. To recover past the point of an exception, you must start message processing on the queue and start Q Apply with buffered_inserts=n.

commit_count

Default: commit_count=1

Method of changing: When Q Apply starts

The commit_count parameter specifies the number of transactions that each Q Apply agent thread applies to the target table within a commit scope. By default, the agent threads commit after each transaction that they apply.

By increasing commit_count and grouping more transactions within the commit scope, you might see improved performance.

Recommendation: Use a higher value for commit_count only with row-level locking. This parameter requires careful tuning when used with a large number of agent threads because it could cause lock escalation resulting in lock timeouts and deadlock retries.

commit_count_unit

Default: commit_count_unit=t

Methods of changing: When Q Apply starts; IBMQREP_APPLYPARMS table

The commit_count_unit parameter specifies whether the commit_count parameter uses the number of transactions applied or the number of rows applied as the unit for determining the size of a commit scope.

By default (commit_count_unit=t), Q Apply agent threads use the number of transactions that were applied to the target table to determine the size of a batch (when to commit). If you specify commit_count_unit=r, the agent threads count the number of rows to commit within a commit scope. The agent threads track the total number of rows for all uncommitted transactions. When this number reaches or exceeds the value of COMMIT_COUNT, the agent threads commit all pending transactions within the commit scope. With commit_count_unit=r, Q Apply agents count all rows that they try to apply, even if a row is not applied because of a conflict or if a row is diverted to a spill queue.

deadlock_retries

Default: deadlock_retries=3

Methods of changing: When Q Apply starts; while Q Apply is running; IBMQREP_APPLYPARMS table

The deadlock_retries parameter specifies how many times the Q Apply program tries to reapply changes to target tables when it encounters an SQL deadlock or lock timeout. The default is three tries. This parameter also controls the number of times that the Q Apply program tries to insert, update, or delete rows from its control tables after an SQL deadlock.

After the limit is reached, if deadlocks persist the browser thread stops. You might want to set a higher value for deadlock_retries if applications are updating the target database frequently and you are experiencing a high level of contention. Or, if you have a large number of receive queues and corresponding browser threads, a higher value for deadlock_retries might help resolve possible contention in peer-to-peer and other multidirectional replication environments, as well as at control tables such as the IBMQREP_DONEMSG table.

Restriction: You cannot lower the default value for deadlock_retries.

dftmodelq

Default: None

Method of changing: When Q Apply starts

By default, the Q Apply program uses IBMQREP.SPILL.MODELQ as the name for the model queue that it uses to create spill queues for the loading process. To specify a different default model queue name, specify the dftmodelq parameter. The following list summarizes the behavior of the parameter:

If you specify dftmodelq when you start Q Apply

For each Q subscription, Q Apply will check to see if you specified a model queue name for the Q subscription by looking at the value of the MODELQ column in the IBMQREP_TARGETS control table:

If the value is NULL or IBMQREP.SPILL.MODELQ, then Q Apply will use the value that you specify for the dftmodelq parameter.
If the column contains any other non-NULL value, then Q Apply will use the value in the MODELQ column and will ignore the value that you specify for the dftmodelq parameter.

If you do not specify dftmodelq when you start Q Apply

Q Apply will use the value of the MODELQ column in the IBMQREP_TARGETS table. If the value is NULL, Q Apply will default to IBMQREP.SPILL.MODELQ.

ignbaddata

Default: None

Methods of changing: When Q Apply starts; IBMQREP_APPLYPARMS table

Note: This parameter applies only if the Q Apply program uses International Components for Unicode (ICU) for code page conversion (if the code page of the source database and the code page that Q Apply uses are different).

The ignbaddata parameter specifies whether the Q Apply program checks for illegal characters in data from the source and continues processing even if it finds illegal characters.

If you specify ignbaddata=y, Q Apply checks for illegal characters and takes the following actions if any are found:

Does not apply the row with the illegal characters.
Inserts a row into the IBMQREP_EXCEPTIONS table that contains a hexadecimal representation of the illegal characters.
Continues processing the next row and does not follow the error action that is specified for the Q subscription.

A value of n prompts Q Apply to not check for illegal characters and not report exceptions for illegal characters. With this option, the row might be applied to the target table if DB2® does not reject the data. If the row is applied, Q Apply continues processing the next row. If the bad data prompts an SQL error, Q Apply follows the error action that is specified for the Q subscription and reports an exception.

logmarkertz

Default: logmarkertz=gmt

Methods of changing: When Q Apply starts

The logmarkertz parameter controls the time zone that the Q Apply program uses when it inserts source commit data into the IBMSNAP_LOGMARKER column of consistent-change data (CCD) tables or point-in-time (PIT) tables. By default (logmarkertz=gmt), Q Apply inserts a timestamp in Greenwich mean time (GMT) to record when the data was committed at the source. If the Q Capture and Q Apply servers are in the same time zone with the same Daylight Savings Time or other time change in effect, you can specify logmarkertz=local and Q Apply inserts a timestamp in the local time of the Q Apply server.

Note: Because the value in the IBMSNAP_LOGMARKER column records the time that a row was committed at the source database, specifying logmarkertz=local is useful only when the Q Capture and Q Apply servers are in the same time zone with the same Daylight Savings Time or other time change in effect.

Existing rows in CCD or PIT targets that were generated before the use of logmarkertz=local are not converted by Q Apply and remain in GMT unless you manually convert them.

The logmarkertz parameter does not affect stored procedure targets. The src_commit_timestamp IN parameter for stored procedure targets always uses GMT-based timestamps.

logreuse

Default: logreuse=n

Methods of changing: When Q Apply starts; while Q Apply is running; IBMQREP_APPLYPARMS table

Each Q Apply program keeps a log file that tracks its work history, such as when it starts and stops reading from queues, changes parameter values, prunes control tables, or encounters errors.

By default, the Q Apply program adds to the existing log file when the program restarts. This default lets you keep a history of the program's actions. If you don't want this history or want to save space, set logreuse=y. The Q Apply program clears the log file when it starts, then writes to the blank file.

The log is stored by default in the directory where the Q Apply program is started, or in a different location that you set using the apply_path parameter.

logstdout

Default: logstdout=n

Methods of changing: When Q Apply starts; while Q Apply is running; IBMQREP_APPLYPARMS table

By default, a Q Apply program writes its work history only to the log. You can change the logstdout parameter if you want to see program history on the standard output (stdout) in addition to the log.

Error messages and some log messages (initialization, stop, subscription activation, and subscription deactivation) go to both the standard output and the log file regardless of the setting for this parameter.

You can specify the logstdout parameter when you start a Q Apply program with the asnqapp command. If you use the Replication Center to start a Q Apply program, this parameter is not applicable.

max_parallel_loads

Default: max_parallel_loads=15

Methods of changing: When Q Apply starts; IBMQREP_APPLYPARMS table

The max_parallel_loads parameter specifies the maximum number of automatic load operations of target tables that Q Apply can start at the same time for a given receive queue.

monitor_interval

Default: monitor_interval=30000 milliseconds (30 seconds)

Methods of changing: When Q Apply starts; while Q Apply is running; IBMQREP_APPLYPARMS table

The monitor_interval parameter tells a Q Apply program how often to insert performance statistics into the IBMQREP_APPLYMON table. You can view these statistics by using the Q Apply Throughput and Latency windows.

You can adjust the monitor_interval based on your needs:

If you want to monitor a Q Apply program's activity at a more granular level, shorten the monitor interval: For example, you might want to see the statistics for the number of messages on queues broken down by each 10 seconds rather than one-minute intervals.
Lengthen the monitor interval to view Q Apply performance statistics over longer periods: For example, if you view latency statistics for a large number of one-minute periods, you might want to average the results to get a broader view of performance. Seeing the results averaged for each half hour or hour might be more useful in your replication environment.

Important for Q Replication Dashboard users: When possible, you should synchronize the Q Apply monitor_interval parameter with the dashboard refresh interval (how often the dashboard retrieves performance information from the Q Capture and Q Apply monitor tables). The default refresh interval for the dashboard is 10 seconds (10000 milliseconds). If the value of monitor_interval is higher than the dashboard refresh interval, the dashboard refreshes when no new monitor data is available.

monitor_limit

Default: monitor_limit=10080 minutes (7 days)

Methods of changing: When Q Apply starts; while Q Apply is running; IBMQREP_APPLYPARMS table

The monitor_limit parameter specifies how old the rows must be in the IBMQREP_APPLYMON table before the rows are eligible for pruning.

By default, rows that are older than 10080 minutes (7 days) are pruned. The IBMQREP_APPLYMON table provides statistics about a Q Apply program's activity. A row is inserted at each monitor interval. You can adjust the monitor limit based on your needs:

Increase the monitor limit to keep statistics: If you want to keep records of the Q Apply program's activity beyond one week, set a higher monitor limit.
Lower the monitor limit if you look at statistics frequently.: If you monitor the Q Apply program's activity on a regular basis, you probably do not need to keep one week of statistics and can set a lower monitor limit.

You can set the monitor_limit parameter when you start the Q Apply program or while the program is running. You can also change its saved value in the IBMQREP_APPLYPARMS table.

multi_row_insert

Default: multi_row_insert=n

Methods of changing: When Q Apply starts; IBMQREP_APPLYPARMS table

The multi_row_insert parameter controls whether the Q Apply program uses multi-row insert SQL statements on target tables.

Inserting replicated rows in batches of 100 or less can reduce CPU consumption at the target server and increase throughput. Performance improvements might be greater for tables with fewer columns, smaller row size, and fewer indexes than tables with more columns, larger row size, and more indexes. All of the rows that are part of a multi-row insert must be continuous insert statements against the same table and in the same transaction.

If an insert fails for any row in the rowset, the database management system rolls back all of the changes in the rowset. Q Apply then switches to single-row insert mode to process all of the rows in the rowset that caused an error. The error is retried and handled with the error action and conflict action that are specified for the Q subscription.

You can allocate additional memory for Q Apply to use in performing multi-row inserts by increasing the value of the MRI_MEMORY_LIMIT column in the IBMQREP_RECVQUEUES table. The default value is 1024 KB per agent thread. A larger value for this parameter can enable the Q Apply program to group more rows into each multi-row insert operation. The allocation for MRI_MEMORY_LIMIT is separate from the overall memory that Q Apply uses, which is set in the MEMORY_LIMIT column in IBMQREP_RECVQUEUES.

Note: Unsubscribed TIMESTAMP columns with a default value of CURRENT TIMESTAMP are likely to be given identical values in multi-row insert mode. To use the multi-row-insert option, you should subscribe to the TIMESTAMP columns with default values.

oracle_empty_str

Default: oracle_empty_str=y

Methods of changing: When Q Apply starts (not recommended)

The oracle_empty_str parameter specifies whether the Q Apply program replaces an empty string in VARCHAR columns with a space.

DB2 allows empty strings in VARCHAR columns. When a source DB2 VARCHAR column is mapped to an Oracle target, the empty string is converted to a NULL value. The operation fails when the target column is defined with NOT NULL semantics.

With the default of oracle_empty_str=y, Q Apply replaces the NULL value with a one-character space in the application code page just before applying the data to the target and after any code page conversion.

You can also specify oracle_empty_str=t and Q Apply replaces an empty string with a space only if the target column is defined as NOT NULL.

Important: When replicating to native Oracle targets, use the default value. A value of n will result in SQL errors when the Q Apply program encounters empty string values and does not convert them.

prune_batch_size

Default: prune_batch_size=1000

Methods of changing: When Q Apply starts; IBMQREP_APPLYPARMS table

The prune_batch_size parameter specifies the number of rows that are deleted from the IBMQREP_DONEMSG table (and corresponding messages deleted from the receive queue) in one commit scope. The default is 1000 rows. The minimum value is 2.

The IBMQREP_DONEMSG table is an internal table used by the Q Apply program to record all transaction or administrative messages that are received. The records in this table help ensure that messages are not processed more than once (for example in the case of a system failure) before they are deleted. During regular execution, Q Apply follows the value of prune_batch_size when it deletes rows from the table and receive queue.

Q Apply follows the value set for this parameter regardless of the setting for the prune_method parameter.

prune_interval

Default: prune_interval=300 seconds (5 minutes)

Methods of changing: When Q Apply starts; while Q Apply is running; IBMQREP_APPLYPARMS table

The prune_interval parameter determines how often a Q Apply program looks for old rows to delete from the IBMQREP_APPLYMON and IBMQREP_APPLYTRACE tables. By default, a Q Apply program looks for rows to prune every 300 seconds (5 minutes).

Your pruning frequency depends on how quickly these control tables grow, and what you intend to use them for:

Shorten the prune interval to manage monitor tables: A shorter prune interval might be necessary if the IBMQREP_APPLYMON table is growing too quickly because of a shortened monitor interval. If this table is not pruned often enough, it can exceed its table space limit, which forces a Q Apply program to stop. However, if the table is pruned too often or during peak times, pruning can interfere with application programs that run on the same system.
Lengthen the prune interval for record keeping: You might want to keep a longer history of a Q Apply program's performance by pruning the IBMQREP_APPLYTRACE and IBMQREP_APPLYMON tables less frequently.

The prune interval works in conjunction with the trace_limit and monitor_limit parameters, which determine when data is old enough to prune. For example, if the prune_interval is 300 seconds and the trace_limit is 10080 seconds, a Q Apply program will try to prune every 300 seconds. If the Q Apply program finds any rows in the IBMQREP_APPLYTRACE table that are older than 10080 minutes (7 days), it prunes them.

The prune_interval parameter does not affect pruning of the IBMQREP_DONEMSG table. Pruning of this table is controlled by the prune_method and prune_batch_size parameters.

prune_method

Default: prune_method=2

Methods of changing: When Q Apply starts; IBMQREP_APPLYPARMS table

The prune_method parameter specifies the method that the Q Apply program uses to delete unneeded rows from the IBMQREP_DONEMSG table. By default, (prune_method=2), Q Apply prunes groups of rows based on the prune_batch_size value. A separate prune thread records which messages were applied, and then issues a single range-based DELETE.

When you specify prune_method=1, Q Apply prunes rows from the IBMQREP_DONESG table one at a time. First Q Apply queries the table to see if data from a message was applied, then it deletes the message from the receive queue, and then prunes the corresponding row from IBMQREP_DONEMSG by issuing an individual SQL statement.

pwdfile

Default: pwdfile=apply_path/asnpwd.aut

Methods of changing: When Q Apply starts; IBMQREP_APPLYPARMS table

The pwdfile parameter specifies the name of the encrypted password file that the Q Apply program uses to connect to the Q Capture server. This connection is required only when a Q subscription specifies an automatic load that uses the EXPORT utility. When you use the asnpwd command to create the password file, the default file name is asnpwd.aut. If you create the password file with a different name or change the name, you must change the pwdfile parameter to match. The Q Apply program looks for the password file in the directory specified by the apply_path parameter.

z/OS®: No password file is required.

You can set the pwdfile parameter when you start the Q Apply program, and you can change its saved value in the IBMQREP_APPLYPARMS table. You cannot change the value while the Q Apply program is running.

report_exception

Default: report_exception=y

Methods of changing: When Q Apply starts

The report_exception parameter controls whether the Q Apply program inserts data into the IBMQREP_EXCEPTIONS table when a conflict or SQL error occurs at the target table but the row is applied to the target anyway because the conflict action that was specified for the Q subscription was F (force). By default, (report_exception=y), Q Apply inserts details in the IBMQREP_EXCEPTIONS table for each row that causes a conflict or SQL error at the target, regardless of whether the row was applied or not. You can specify report_exception=n and Q Apply will not insert data into the IBMQREP_EXCEPTIONS table when a row causes a conflict but is applied. With report_exception=n, Q Apply continues to insert data about rows that were not applied.

When report_exception=n, the Q Apply program also tolerates codepage conversion errors when writing SQL text into the IBMQREP_EXCEPTIONS table and continues normal processing.

richklvl

Default: richklvl=2

Methods of changing: When Q Apply starts; IBMQREP_APPLYPARMS table

The richklvl parameter specifies the level of referential integrity checking. By default (richklvl=2), the Q Apply program checks for RI-based dependencies between transactions to ensure that dependent rows are applied in the correct order.

If you specify richklvl=5, Q Apply checks for RI-based dependencies when a key value is updated in the parent table, a row is updated in the parent table, or a row is deleted from the parent table.

A value of 0 prompts Q Apply to not check for RI-based dependencies.

When a transaction cannot be applied because of a referential integrity violation, the Q Apply program automatically retries the transaction until it is applied in the same order that it was committed at the source table.

spill_commit_count

Default: spill_commit_count=10

Methods of changing: When Q Apply starts; IBMQREP_APPLYPARMS table

The spill_commit_count parameter specifies how many rows are grouped together in a commit scope by the Q Apply spill agents that apply data that was replicated during a load operation. Increasing the number of rows that are applied before a COMMIT is issued can improve performance by reducing the I/O resources that are associated with frequent commits. Balance the potential for improvement with the possibility that fewer commits might cause lock contention at the target table and the IBMQREP_SPILLEDROW control table.

skiptrans

Default: None

Method of changing: When Q Apply starts

The skiptrans parameter specifies that the Q Apply program should not apply one or more transactions from one or more receive queues based on their transaction ID.

Stopping the program from applying transactions is useful in unplanned situations, for example:

Q Apply receives an error while applying a row of a transaction and either stops or stops reading from the receive queue. On startup, you might want Q Apply to ignore the entire transaction in error.
After the failover from a disaster recovery situation, you might want to ignore a range of transactions on the receive queue from the failover node to the fallback node.

You can also prompt the Q Capture program to ignore transactions. This action would be more typical when you can plan which transactions do not need to be replicated.

Note: Ignoring a transaction that was committed at the source server typically causes divergence between tables at the source and target. You might need use the asntdiff and asntrep utilities to synchronize the tables.

sqlldr_expression

Default: sqlldr_expression=n

Methods of changing: When Q Apply starts

The sqlldr_expression parameter specifies whether the Oracle target database or Db2 source database evaluates expressions in a Q subscription during the target table loading process.

By default (sqlldr_expression=n), Db2 evaluates expressions during the loading process and then the Oracle SQL*Loader utility loads the resulting values into the target table. During normal replication after the loading process, the Oracle target database evaluates expressions and the Q Apply program applies the resulting values into the target table.

If you specify sqlldr_expression=y, the Oracle target database evaluates expressions both during the loading process and during normal replication.

If you create a Q subscription with an expression that contains functions that are only defined in Oracle, SQL errors can occur during the initial load and the Q subscription is not activated. Specifying sqlldr_expression=y avoids these errors.

When Db2 evaluates expressions during the load and Oracle evaluates the same expressions during normal replication, data inconsistencies can occur. However, this configuration can work satisfactorily depending on the type of expressions used.

sqlldr_options

Default: sqlldr_options="ERRORS=0"

Methods of changing: When Q Apply starts

The sqlldr_options parameter specifies options that you want the Oracle SQL*Loader utility to use when it loads target tables. Q Apply calls SQL*Loader when you specify LOAD_TYPE 4 in the IBMQREP_TARGETS table. The options that you specify when you use this parameter are added to the OPTIONS clause of the SQL*Loader invocation. You can specify multiple options separated by commas.

For example, to specify that SQL*Loader should use a direct path load and should suppress all messages that would normally be sent to the screen, you would specify SQLLDR_OPTIONS="DIRECT=TRUE, SILENT=(ALL)".

By default, Q Apply calls SQL*Loader with the ERRORS=0 option, meaning that the loading process terminates after any insert error at the target table.

sqlldr_truncate

Default: sqlldr_truncate=n

Methods of changing: When Q Apply starts

The sqlldr_truncate parameter specifies whether Q Apply calls the Oracle SQL*Loader utility with the TRUNCATE option to delete the contents of the target table before starting the loading process.

By default (sqlldr_truncate=n), Q Apply calls SQL*Loader with the REPLACE option when LOAD_TYPE=4 in the IBMQREP_TARGETS table. You can set sqlldr_truncate=y to specify that SQL*Loader be invoked with the TRUNCATE option, which can improve performance.

startallq

Default: startallq=y

Methods of changing: When Q Apply starts; IBMQREP_APPLYPARMS table

The startallq parameter specifies how Q Apply processes receive queues when it starts. With startallq=y, Q Apply puts all receive queues in active state and begins reading from them when it starts. When you specify startallq=n, Q Apply processes only the active receive queues when it starts.

You can use startallq=y to avoid having to issue the startq command for inactive receive queues after the Q Apply program starts. You can use startallq=n to keep disabled queues inactive when you start Q Apply.

term

Default: term=y

The term parameter controls whether a Q Apply program keeps running when the connection to the Oracle database is lost or the target queue manager is unavailable. By default (term=y), Q Apply stops when the connection is lost or the queue manager is unavailable. If you specify term=n, Q Apply shuts down all program threads except for its main thread, which waits for Oracle or the queue manager to come back.

If you are using automatic rerouting on the Oracle target with Oracle real application clusters (RAC), you must set LOAD_BALANCE to OFF in the tnsnames.ora file because all Q Apply program threads must be forced to connect to the same RAC instance. The following example shows an entry in the tnsnames.ora file for an Oracle RAC target to support Q Apply term=n:

RAC =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521))
 (ADDRESS = (PROTOCOL = TCP)(HOST = rac2)(PORT = 1521))
 (LOAD_BALANCE = OFF)
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = rac)))

In this example, the Oracle client first tries to connect to the Oracle RAC instance rac1. If this instance is unavailable the client then routes connections to rac2.

trace_ddl

Default: trace_ddl=n

Methods of changing: When Q Apply starts; IBMQREP_APPLYPARMS table

The trace_ddl parameter specifies whether, when DDL operations at the source database are replicated, the SQL text of the operation that the Q Apply program performs at the target database is logged. By default (trace_ddl=n), Q Apply does not log the SQL text. If you specify trace_ddl=y, Q Apply issues an ASN message to the Q Apply log file, standard output, and IBMQREP_APPLYTRACE table with the text of the SQL statement. The SQL text is truncated to 1024 characters.

trace_limit

Default: trace_limit=10080 minutes (7 days)

Methods of changing: When Q Apply starts; while Q Apply is running; IBMQREP_APPLYPARMS table

The trace_limit parameter specifies how long rows remain in the IBMQREP_APPLYTRACE table before the rows can be pruned.

The Q Apply program inserts all informational, warning, and error messages into the IBMQREP_APPLYTRACE table. By default, rows that are older than 10080 minutes (7 days) are pruned at each pruning interval. Modify the trace limit depending on your need for audit information.

use_applycmd_table

Default: use_applycmd_table=n

Methods of changing: When Q Apply starts; IBMQREP_APPLYPARMS table

The use_applycmd_table specifies whether the Q Apply program reads the IBMQREP_APPLYCMD table to look for user-inserted commands to be processed. If you specify use_applycmd_table=y, Q Apply reads the IBMQREP_APPLYCMD table every n milliseconds. The frequency is determined by the value of the applycmd_interval parameter. The Q Apply initial thread reads from this table and asynchronously executes any commands that were inserted by using the existing asnqacmd (Linux, UNIX, Windows) or MODIFY (z/OS) command processing infrastructure

warntxlatency

Default: warntxlatency=0

Methods of changing: When Q Apply starts; while Q Apply is running; IBMQREP_APPLYPARMS table

The warntxlatency parameter specifies whether the Q Apply program issues warning messages when the apply latency of a transaction exceeds a threshold. Apply latency is the time elapsed between getting a message from the receive queue and committing the transaction at the target table. You enable warnings and set the warning threshold by specifying an integer value greater than 0.

If you set warntxlatency to 10, for example, Q Apply would issue the ASN7878W and ASN7879W messages whenever the apply latency of any transaction exceeds 10 milliseconds. You use the warntxevts and warntxreset parameters to control the number of warnings and the reset interval for the warnings. Q Apply also issues summary messages at the end of each reset period that include transactions that exceeded the latency threshold.

By default (warntxlatency=0), warning messages for apply latency are not enabled.

warntxevts

Default: warntxevts=10

Methods of changing: When Q Apply starts; while Q Apply is running; IBMQREP_APPLYPARMS table

The warntxevts parameter specifies the maximum number of latency warning messages that the Q Apply program issues during a reset interval. By default ( warntxevts=10), Q Apply issues warning messages for the first 10 transactions that exceed the latency threshold. Any transactions beyond 10 that exceed the threshold do not prompt individual warning messages but are instead included in summary messages at the end of the reset period.

Allowed values range from 0 to the maximum integer value for Db2®. This option is ignored if WARNTXLATENCY is set to 0.

warntxreset

Default: warntxreset=300000 milliseconds (5 minutes)

Methods of changing: When Q Apply starts; while Q Apply is running; IBMQREP_APPLYPARMS table

The warntxreset parameter specifies a time interval in milliseconds that you set for warning messages about apply transaction latency. At the end of the interval the Q Apply program issues summary messages ASN7881W and ASN7882W and resets its latency counters if any transactions exceeded the latency threshold.

By default (warntxreset=300000), Q Apply resets the latency counters after 5 minutes if any transactions exceed the threshold.

Allowed values range from 60000 (1 minute) to the maximum integer value for Db2. This option is ignored if WARNTXLATENCY is set to 0. This option must have a value greater than WARNTXLATENCY.