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 1021, 1001, 0973, 0907, and 0905. Attention: 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 DSNUTILS stored procedure that Q Apply uses to call the DB2 LOAD utility. Depending on your environment you can experiment with values higher than max_parallel_loads=1. If errors occur, reset the value to 1. 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 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 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	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.
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 (default) 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 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. 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 z/OS: A flag that indicates whether the Q Apply program uses multi-row insert SQL statements to apply rows to DB2 for z/OS target tables. N (default) Q Apply inserts one row at a time into target tables. Y Q Apply inserts rows in groups of 100 or less.
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.