IBMQREP_APPLYPARMS table

The IBMQREP_APPLYPARMS table contains parameters that you can modify to control the operation of the Q Apply program. For example, you can specify the name of the queue manager that the Q Apply program works with, or how long the Q Apply program retains data in the IBMQREP_APPLYMON table before pruning. The Q Apply program reads changes to this table only during startup.

Server: Q Apply server

Default schema: ASN

Unique index: QMGR

This table contains information that you can update by using SQL.

The IBMQREP_APPLYPARMS table contains a single row. If this table has no row, or more than one row, the Q Apply program will not run.

Table 1 provides a brief description of the columns in the IBMQREP_APPLYPARMS table.

Table 1. Columns in the IBMQREP_APPLYPARMS table
Column name	Description
QMGR	Data type: VARCHAR(48); Nullable: No The name of the IBM® MQ queue manager that the Q Apply program works with.
MONITOR_LIMIT	Data type: INTEGER; Nullable: No, with default The number of minutes that rows remain in the IBMQREP_APPLYMON table before they are eligible for pruning. At each pruning interval, rows in the IBMQREP_APPLYMON table are pruned if they are older than this limit based on the current timestamp. Default: 10080
TRACE_LIMIT	Data type: INTEGER; Nullable: No, with default The number of minutes that rows remain in the IBMQREP_APPLYTRACE table before they are eligible for pruning. At each pruning interval, rows in the IBMQREP_APPLYTRACE table are pruned if they are older than this limit based on the current timestamp. Default: 10080
MONITOR_INTERVAL	Data type: INTEGER; Nullable: No, with default How often, in milliseconds, the Q Apply program adds a row to the IBMQREP_APPLYMON table. Default: 60000 milliseconds (1 minute) on z/OS®; 30000 milliseconds (30 seconds) on Linux®, UNIX, and Windows
PRUNE_INTERVAL	Data type: INTEGER; Nullable: No, with default How often, in seconds, the Q Apply program automatically prunes rows in the IBMQREP_APPLYMON, IBMQREP_APPLYTRACE, and IBMQREP_APPEVENTS tables. Default: 300
AUTOSTOP	Data type: CHAR(1); Nullable: No, with default A flag that tells the Q Apply program whether to stop when all receive queues have been emptied once. N (default) The Q Apply program continues running after all receive queues have been emptied once. Y The Q Apply program stops when all receive queues have been emptied once.
LOGREUSE	Data type: CHAR(1); Nullable: No, with default A flag that indicates whether the Q Apply program reuses the Q Apply log file or appends to it. N (default) The Q Apply program appends new information to an existing Q Apply log file when it restarts. Y On restart, the Q Apply program reuses its log file by clearing the file then writing to the blank file.
LOGSTDOUT	Data type: CHAR(1); Nullable: No, with default A flag that indicates whether the Q Apply program sends log messages to outputs other than its log file. N (default) The Q Apply program directs most log messages to the log file only. Y The Q Apply program sends log messages to both the log file and the console (stdout). Initialization, stop, and subscription activation and deactivation messages go to both the console (stdout) and the log file regardless of the setting for this parameter.
APPLY_PATH	Data type: VARCHAR(1040); Nullable: Yes, with default The path where files created by the Q Apply program are stored. By default, this is the directory where the Q Apply program is started. Default: NULL
ARCH_LEVEL	Data type: CHAR(4); Nullable: No, with default The version of the control tables. Valid values are 1140, 1021, 1001, 0973, and 0907. Important: When updating the IBMQREP_APPLYPARMS table, do not change the value in this column.
TERM	Data type: CHAR(1); Nullable: No, with default A flag that indicates whether the Q Apply program stops if the target Db2® or queue manager are unavailable. Y (default) The Q Apply program stops if Db2 or the queue manager are unavailable. N The Q Apply program continues running if Db2 or the queue manager are unavailable. When Db2 or the queue manager are available, Q Apply begins applying transactions where it left off without requiring you to restart the program. Restriction: The value N for the TERM column is not supported for federated targets.
PWDFILE	Data type: VARCHAR(48); Nullable: Yes, with default The name of the encrypted password file that the Q Apply program uses to connect to the Q Capture program if the Q subscription calls for an internal load of the target. The asnpwd command creates this file by default in the directory specified in the APPLY_PATH column. Default: NULL
DEADLOCK_RETRIES	Data type: INTEGER; Nullable: No, with default The number of times the Q Apply program tries to reapply changes to target tables, or make inserts into its control tables, after SQL deadlocks. If the deadlock occurs at a target table, the Q Apply program keeps trying until it reaches the limit that you set. After the limit is reached, if deadlocks persist the browser thread stops. Default: 3 tries.
SQL_CAP_SCHEMA	Data type: VARCHAR(128); Nullable: Yes, with default The schema of the Capture control tables that the Q Apply program uses to manage CCD target tables that are registered as SQL replication sources. This column must contain a value in for the Q Apply program to manage data distribution (fan-out) configurations. Default: NULL
LOADCOPY_PATH	Data type: VARCHAR(1040); Nullable: Yes, with default Specifies the path where the Db2 LOAD utility creates a copy of loaded data on the primary server for a configuration that involves the Db2 High Availability Disaster Recovery (HADR) utility. Setting this parameter prompts Q Apply to start the LOAD utility with the option to create the copy when Q Apply loads the target table. The secondary server in the HADR configuration then looks for the copied data in this path. Default: NULL
NICKNAME_COMMIT_CT	Data type: INTEGER; Nullable: Yes, with default Specifies the number of rows after which the Db2 IMPORT utility commits changes to nicknames that reference the target table during the loading process. This parameter applies only to automatic loads for federated targets that use the IMPORT utility. The default is nickname_commit_ct=10. This parameter can be used to tune the performance of the Db2 IMPORT utility by reducing the number of commits for federated targets.
SPILL_COMMIT_COUNT	Data type: INTEGER; Nullable: Yes, with default 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. The default is spill_commit_count=10. 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.
LOAD_DATA_BUFF_SZ	Data type: INTEGER; Nullable: Yes, with default Specifies the number of 4KB pages for the Db2 LOAD utility to use as buffered space for transferring data within the utility during the initial loading of the target table. This parameter applies only to automatic loads using the Db2 LOAD utility. By default, the Q Apply program starts the utility with the option to use a buffer of 8 pages. Load performance for multidimensional clustering (MDC) tables that are replication targets can be significantly improved by specifying a much higher number of pages.
MAX_PARALLEL_LOADS	Data type: INTEGER; Nullable: Yes, with default 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. The default differs depending on the platform of the target server: z/OS On z/OS the default is one load at a time because of potential issues with the DSNUTILU or DSNUTILS (deprecated) stored procedure that Q Apply uses to call the Db2 LOAD utility. If you plan to set values higher than max_parallel_loads=1, ensure that your Workload Manager (WLM) policy allows more than one load at a time. Linux, UNIX, Windows On Linux, UNIX, and Windows the default is 15 parallel loads.
COMMIT_COUNT	Data type: INTEGER; Nullable: Yes, with default Specifies the number of transactions or rows that each Q Apply agent thread applies to the target table within a commit scope. By default (COMMIT_COUNT=1), the agent threads commit after each transaction or row that they apply. By increasing COMMIT_COUNT and grouping more transactions or rows within the commit scope, you might see improved performance. The COMMIT_COUNT_UNIT parameter determines whether transactions or rows are counted. 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	Data type: CHAR(1); Nullable: No, with default A flag that specifies whether the COMMIT_COUNT parameter uses the number of transactions that were applied or the number of rows that were applied within a commit scope. T (default) Q Apply agent threads count the number of transactions to commit within a commit scope. R Q Apply 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.
INSERT_BIDI_SIGNAL	Data type: CHAR(1); Nullable: No, with default Whether the Q Capture and Q Apply programs use P2PNORECAPTURE signal inserts to prevent recapture of transactions in bidirectional replication. Y (default) The Q Apply program inserts P2PNORECAPTURE signals into the IBMQREP_SIGNAL table to instruct the Q Capture program at its same server not to recapture applied transactions at this server. N The Q Apply program does not insert P2PNORECAPTURE signals. Instead, you insert Q Apply's AUTHTKN information into the IBMQREP_IGNTRAN table, which instructs the Q Capture program at the same server to not capture any transactions that originated from the Q Apply program, except for inserts into the IBMQREP_SIGNAL table. Tables with triggers: If you specify INSERT_BIDI_SIGNAL=Y and a source table at one server has a trigger that updates the target table at another server, the trigger updates are not sent back to the source server. But if the trigger updates other tables at the target server, those trigger changes are captured and sent back to the source server. You can avoid this situation by setting INSERT_BIDI_SIGNAL=N, in which case all transactions that were triggered by the Q Apply program at the target server are ignored, and so triggered updates to tables that were not part of the source transaction are also ignored. For bidirectional replication, it is recommended that you keep the source and target triggers to be the same.
APPLY_ALIAS	Data type: VARCHAR(8); Nullable: Yes, with default The alias name for the database or subsystem that is used as the Q Apply server. This is the alias as cataloged on the system where the replication administration tools run and used to connect to the target database or subsystem to create Q Apply control tables. This column is populated by the Replication Center or ASNCLP command-line program when control tables are created. Default: NULL
STARTALLQ	Data type: CHAR(1); Nullable: No, with default A flag that tells the Q Apply program whether to activate all receive queues that are not already in active state when Q Apply starts. Receive queues that are already active are always processed when Q Apply starts. Y When the Q Apply program starts, it activates all receive queues that are not already in active (A) state. N When the Q Apply program starts, it does not activate receive queues that are in inactive (I) state. The default value for this column is N for z/OS and Y for Linux, UNIX, and Windows.
PRUNE_BATCH_SIZE	Data type: INTEGER; Nullable: Yes, with default The number of rows that are deleted from the IBMQREP_DONEMSG table in one commit scope when PRUNE_METHOD is 2. The default is 1000 rows. The minimum value is 2 rows.
PRUNE_METHOD	Data type: INTEGER; Nullable: Yes, with default The method that the Q Apply program uses to delete unneeded rows from the IBMQREP_DONEMSG table. 1 Q Apply deletes a message from the receive queue, queries the IBMQREP_DONESG table to see if data from the message was applied, and then prunes the corresponding row from IBMQREP_DONEMSG by issuing an individual SQL statement. 2 (default) Q Apply prunes groups of rows based on the PRUNE_BATCH_SIZE value. A separate prune thread records which messages have been applied, then issues a single range-based DELETE against the IBMQREP_DONEMSG table.
IGNBADDATA	Data type: CHAR(1); Nullable: No, with default Note: This column 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). Whether the Q Apply program checks for illegal characters in data from the source and continues processing even if it finds illegal characters. Y Q Apply checks for illegal characters. N (default) Q Apply does not check for illegal characters and does not report exceptions for illegal characters.
P2P_2NODES	Data type: CHAR(1); Nullable: No, with default Whether he Q Apply program records conflicting DELETE operations in the IBMQREP_DELTOMB table. Y (default) The Q Apply program does not use the IBMQREP_DELTOMB table to record conflicting DELETE operations in peer-to-peer replication with two active servers. This setting is recommended for a two-server peer-to-peer configuration. N The Q Apply program records conflicting DELETE operations in the IBMQREP_DELTOMB table.
RICHKLVL	Data type: INTEGER; Nullable: Yes, with default The level of referential integrity checking. By default, the Q Apply program checks for RI-based dependencies between transactions to ensure that dependent rows are applied in the correct order. 0 Q Apply does not check for RI-based dependencies. 2 (default) Q Apply checks for RI-based dependencies when a key value is updated in the parent table or a row is deleted from the parent table. 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.
NMI_ENABLE	Data type: CHAR(1); Nullable: No, with default z/OS: A flag that indicates whether the Q Apply program is enabled to provide a Network Management Interface (NMI) for monitoring Q Replication statistics from IBM Tivoli® NetView Monitoring for GDPS®. The NMI client application must be on the same z/OS system as the Q Apply program. N (default) Monitoring is not enabled. Y The NMI interface for monitoring is enabled.
NMI_SOCKET_NAME	Data type: VARCHAR(256); Nullable: Yes z/OS: The name of the AF_UNIX socket where the Q Apply program listens for requests for statistical information from NMI client applications. You can use this column to change the name that Q Apply automatically generates. The socket file is generated in the directory /var/sock. The socket name is constructed by combining the file path, group attach name, Q Apply schema name, and the program name (asnqapp). An example socket name is /var/sock/V91A_ASN_asnqapp. You can use the status show details parameter of the MODIFY command with the Q Apply job name to list the name of the current NMI file socket and all clients that are connected. For example, to get socket information for the myqapp job name: `f myqapp,status show details`
TRACE_DDL	Data type: CHAR(1); Nullable: No, with default When DDL operations at the source database are replicated, this column indicates whether the SQL text of the operation that the Q Apply program performs at the target database is logged. N (default) Replicated DDL operations are not logged. Y The Q Apply program issues an ASN message to its log file, standard output, and IBMQREP_APPLYTRACE table with the text of the SQL statement that was used to replicate the source DDL. The SQL text is truncated to 1024 characters.
REPORT_EXCEPTIONS	Data type: CHAR(1); Nullable: No, with default A flag that 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 anyway because the conflict action that was specified for the Q subscription was F (force). Y (default) Q Apply inserts data into the IBMQREP_EXCEPTIONS table whether or not the row that caused the exception is applied. N Q Apply does not insert data into the IBMQREP_EXCEPTIONS table when the row that caused an exception is applied; data is inserted only when the row is not applied. With this setting, Q Apply also tolerates codepage conversion errors when writing SQL text into the IBMQREP_EXCEPTIONS table and continues normal processing.
ORACLE_EMPTY_STR	Data type: CHAR(1); Nullable: No, with default A flag that 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, or to a Db2 server that is running with Oracle compatibility mode, the empty string is converted to a NULL value. The operation fails when the target column is defined with NOT NULL semantics. Y (need to manually set to Y for Oracle targets) Q Apply replaces the empty string with a one-character space in the application code page just before applying the data to the target and after any code page conversion. N (default on Db2 targets on Linux, UNIX, Windows, and z/OS) Q Apply applies the empty string even though Oracle inserts a NULL value. This action can result in an error if the target column does not accept nulls. This is the only valid value on z/OS. T Q Apply replaces the empty string with a space only if the target column is defined as NOT NULL.
LOGMARKERTZ	Data type: CHAR(8); Nullable: No, with default A value that determines 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. GMT (default) Q Apply records the source commit timestamp in Greenwich mean time. LOCAL Q Apply inserts the source commit timestamp in the local time of the Q Capture server
MULTI_ROW_INSERT	Data type: CHAR(1); Nullable: Yes, with default A flag that indicates whether the Q Apply program uses multi-row insert SQL statements to apply rows to target tables. Y (default) Q Apply inserts rows in groups of 100 or less. N Q Apply inserts one row at a time into target tables.
EVENT_LIMIT	Data type: INTEGER; Nullable: No, with default The number of minutes that rows remain in the IBMQREP_APPEVENTS table before they are eligible for pruning. At each pruning interval, rows are removed if they are older than this limit based on the current timestamp. Default: 10080
EVENT_GEN	Data type: CHAR(1); Nullable: No, with default A flag that tells the Q Apply program whether to generate events that are sent to the Event Interface Facility (EIF), console, or IBMQREP_APPEVENTS table. N (default) Q Apply does not generate events. Y Q Apply creates an event thread that connects to Db2 and generates events that are based on user-defined thresholds. Note: If EVENT_GEN=Y, you must also run with TERM=Y.
EVENT_INTERVAL	Data type: INTEGER; Nullable: No, with default How often, in milliseconds, the Q Apply program collects latency values for generating events. The default is 3000 milliseconds (3 seconds). The minimum value is 1000 milliseconds (1 second).
EIF_HBINT	Data type: INTEGER; Nullable: No, with default z/OS: How often, in milliseconds, the Q Apply program sends EIF messages. The default and minimum values are 10000 milliseconds (10 secs).
EIF_CONN1	Data type: VARCHAR(291); Nullable: Yes z/OS: Specifies a connection to the primary EIF event server. The value is either the host name or the IPv4 address with the port number in parentheses. For example, 9.123.59.123(22008) or stlmvs1.svl.ibm.com(25009).
EIF_CONN2	Data type: VARCHAR(291); Nullable: Yes z/OS: Specifies a connection to the backup EIF event server. The value is either the host name or the IPv4 address with the port number in parentheses.
USE_APPLYCMD_TABLE	Data type: CHAR(1); Nullable: No, with default A flag that tells the Q Apply program whether to read the IBMQREP_APPLYCMD table for any commands to be processed. N (default) The Q Apply program skips reading from the IBMQREP_APPLYCMD table. Y The Q Apply program reads the IBMQREP_APPLYCMD table every `n` milliseconds. The frequency is determined by the value in the APPLYCMD_INTERVAL column.
APPLYCMD_INTERVAL	Data type: INTEGER; Nullable: No, with default How frequently the Q Apply program reads the IBMQREP_APPLYCMD table. The default is 3000 milliseconds and the minimum value is 1000 milliseconds.
POSSIBLE_LEVEL	Data type: VARCHAR(10); Nullable: Yes, with default z/OS: The maximum functional level that can be set for the installed Q Apply program. Default: NULL
CURRENT_LEVEL	Data type: VARCHAR(10); Nullable: Yes, with default z/OS: The current level of function that is used by Q Apply. Default: 1140.0
CONTROL_TABLES_LEVEL	Data type: VARCHAR(10); Nullable: Yes, with default z/OS: The current level of the Q Apply control tables. Default: 1140.0
WARNTXLATENCY	Data type: INTEGER; Nullable: No, with default A threshold that triggers warning messages when apply transaction latency exceeds the limit that you set. By specifying an integer in milliseconds for this parameter, you enable warnings. Apply latency is the time elapsed between getting a message from the receive queue and committing the transaction at the target table. The default value of 0 means that no warning messages are issued.
WARNTXEVTS	Data type: INTEGER; Nullable: No, with default The maximum number of apply latency warning messages that are issued before a reset. The default value of 10, for example, means that a maximum of 10 warning messages are issued within the reset interval that is set by WARNTXRESET. Allowed values range from 0 to the maximum integer value for Db2. This option is ignored if WARNTXLATENCY is set to 0.
WARNTXRESET	Data type: INTEGER; Nullable: No, with default 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 a summary message and resets its latency counters if any transactions exceeded the latency threshold. The default is 300000 milliseconds (5 minutes). 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.
ARCHIVE_DELETE	Data type: CHAR(1); Nullable: Yes, with default z/OS: A flag that tells the Q Apply program whether to prevent the replication of a DELETE operation from an archive history table. This parameter is only valid if you are replicating an archive base table and its associated archive history table and the Db2 SYSIBMADM.MOVE_TO_ARCHIVE global variable is not set to N at the target database. Y (default) When a DELETE operation is replicated from an archive base table at the source database, Db2 automatically deletes the row from the archive history table at the target if SYSIBMADM.MOVE_TO_ARCHIVE is set to Y or E. When a delete occurs at the source, the deletes are inserted to the archive history table. Those deletes are replicated to the target history table as inserts. When Q Apply also replicates the DELETE operation at the target, Db2 tries to insert into the target archive history table and Q Apply can get an SQL -803 error and issue the ASN0552E error message because the rows that Db2 tries to insert into the target history table already exist. N Q Apply sets SYSIBMADM.MOVE_TO_ARCHIVE='N' to prevent the DELETE from the base target table from being replicated to the target archive history table.