CREATE CONTROL TABLES FOR command

Use the CREATE CONTROL TABLES FOR command on Linux, UNIX, and Windows to set up Q Capture and Q Apply control tables. For event publishing, Q Apply control tables are not needed.

For bidirectional and peer-to-peer replication, run the SET MULTIDIR SCHEMA command before you use this command. The Q Capture and Q Apply programs must use the same schema on each server.

Note: When you are creating Q Capture or Q Apply control tables on z/OS, use the ASNQCTLZ sample job in the SASNSAMP data set rather than using this ASNCLP command.

Syntax

node-options

capparms-clause

table-space-options

queue-options

applyparms-clause

zos-ts-clause

uw-ts-clause

fed-ts-clause

prof-clause

zos-idx-clause

Parameters

CAPTURE SERVER: Specify to create Q Capture control tables.
REMOTE SOURCE SERVER: z/OS: Specify to create the IBMQREP_SIGNAL table on a remote source server when you are configuring the Q Capture program to run at a Db2® log read proxy server. To enable this command you must first specify SET SERVER REMOTE SOURCE.
APPLY SERVER: Specify to create Q Apply control tables.
NODE: Specify to generate a script for creating both Q Capture and Q Apply control tables with the same schema on one server in a multidirectional replication configuration.
Note: Use this option only in conjunction with the SET BIDI NODE command for specifying the servers that are involved in multidirectional replication.
CAPPARMS: Specify to set options for the Q Capture control tables.
APPARMS: Specify to set options for the Q Apply control tables.

proxy-clause:

AS PROXY WITH REMOTE SOURCE DBNAMEzdbname: Specify to create Q Capture control tables on a Db2 proxy log read server for a configuration in which the Q Capture program reads the log files of a Db2 for z/OS data sharing group remotely. The zdbname variable specifies the name of the remote source server. You must specify the RELEASE keyword with a minimum value of “11.4.0.”

capparms-clause:

STARTMODE

Specifies what kind of start the Q Capture program will perform.

WARMSI: Specify for the Q Capture program to perform a warm start. If the Q Capture program is starting for the first time, it will perform a cold start.
COLD: Specify for the Q Capture program to perform a cold start.
WARMNS: Specify for the Q Capture program to attempt a warm start if information is available. If the information is not available, the Q Capture program will stop.

MEMORY LIMIT limit

Specifies the maximum amount (in MB) of memory that the Q Capture program can use to build transactions.

AUTOSTOP

N: The Q Capture or Q Apply program does not stop after it reaches the end of the active log and finds no transactions.
Y: The Q Capture or Q Apply program stops after it reaches the end of the active log and finds no transactions.

MONITOR INTERVAL interval

Specifies how frequently (in milliseconds) the Q Capture program inserts rows into the IBMQREP_CAPMON table.

MONITOR LIMIT monlimit

Specifies how long (in minutes) a row can remain in the IBMQREP_CAPMON and IBMQREP_CAPQMON tables before it becomes eligible for pruning. All rows in these tables that are older than the specified value are pruned at the next pruning cycle.

TRACE LIMIT trclimit

Specifies how long (in minutes) a row can remain in the IBMQREP_CAPTRACE table before it becomes eligible for pruning. All rows that are older than the specified value are pruned at the next pruning cycle.

SIGNAL LIMIT siglimit

Specifies how long (in minutes) a row can remain in the IBMQREP_SIGNAL table before it becomes eligible for pruning. All rows that are older than the specified value are pruned at the next pruning cycle.

PRUNE INTERVAL prninterval

Specifies how frequently (in seconds) the IBMQREP_CAPMON, IBMQREP_CAPQMON, IBMQREP_CAPTRACE, and IBMQREP_SIGNAL tables are pruned.

SLEEP INTERVAL sleepinterval

Specifies the number of milliseconds that the Q Capture program sleeps when it finishes processing the active log and determines that the buffer is empty.

LOGREUSE

N: The Q Capture program appends messages to the log file, even after the Q Capture program restarts.
Y: The Q Capture program reuses the log file by first truncating the current log file and then starting a new log when the Q Capture program restarts.

LOGSTDOUT

N: The Q Capture program only sends messages to the log file.
Y: The Q Capture program sends messages to both the log file and the standard output (stdout).

TERM

Y: The Q Capture program terminates if Db2 is quiesced or stops. This value is the default.
N: The Q Capture program continues running if Db2 is quiesced or stops.

CAPTURE_PATH "capture_path"

Specifies the location of the work files that the Q Capture program uses. On z/OS® systems, the location can be an MVS™ data set high-level qualifier with //. The default is NULL.

RELEASE "capture_release"

Linux®, UNIX, and Windows: Specifies the release level of the control tables that you want to create. Enclose the value in double quotation marks ("). Specifying the release level enables newer replication and publishing function on an older Db2.

Restriction: Version 10.1 and newer control tables cannot be created on a downlevel Db2.

queue-options

MQDEFAULTS: Specifies that when creating the Q Capture control tables the ASNCLP program should use the default names for the Q Capture restart queue and administration queue that are generated by the CREATE MQ SCRIPT command.
RESTARTQ "rstqname": Specifies the restart queue that the Q Capture program uses.
ADMINQ "admqname": Specifies the administration queue that the Q Capture program uses.

appparms-clause:

ZOS

Specifies a z/OS system in which Q Apply control tables are created.

UW

Specifies a Linux, UNIX, or Windows system in which Q Apply control tables are created.

FEDERATED

Specifies a federated target, on which Q Apply control tables are created in an Oracle, Sybase, Informix®, Microsoft SQL Server, or Teradata database, and nicknames are created for these control tables in the Q Apply server. Some control tables are created in the Q Apply server.

MONITOR LIMIT monlimit

Specifies how long (in minutes) a row can remain in the IBMQREP_APPLYMON table before it becomes eligible for pruning. All rows that are older than the specified value are pruned at the next pruning cycle.

TRACE LIMIT trclimit

Specifies how long (in minutes) a row can remain in the IBMQREP_APPLYTRACE table before it becomes eligible for pruning. All rows that are older than the specified value are pruned at the next pruning cycle.

MONITOR INTERVAL interval

Specifies how frequently (in milliseconds) the Q Apply program inserts rows into the IBMQREP_APPLYMON table.

PRUNE INTERVAL prninterval

Specifies how frequently (in seconds) the IBMQREP_APPLYMON and IBMQREP_APPLYTRACE tables are pruned.

AUTOSTOP

N: The Q Apply program does not stop after all queues are emptied once.
Y: The Q Apply program stops after all queues are emptied once.

LOGREUSE

N: The Q Apply program appends messages to the log file, even after the Q Apply program is restarted.
Y: The Q Apply program reuses the log file by first truncating the current log file and then starting a new log when the Q Apply program is restarted.

LOGSTDOUT

N: The Q Apply program sends messages only to the log file.
Y: The Q Apply program sends messages to the log file and the standard output (stdout).

APPLY PATH "apply_path"

Specifies the location of the work files the Q Apply program uses. The default path is the directory where the asnqapp command was run.

RELEASE "apply_release"

Linux, UNIX, and Windows: Specifies the release level of the control tables that you want to create. Allowed values are 10.1, 9.7, 9.5, and 9.1. Enclose value in double quotation marks ("). Specifying the release level enables newer replication and publishing function on an older Db2.

Restriction: Version 10.1 and newer control tables cannot be created on a downlevel Db2.

TERM

Y: The Q Apply program stops if Db2 is quiesced or stops.
N: The Q Apply program continues running if Db2 is quiesced or stops.

PWDFILE "filename"

Specifies the name of the password file.

DEADLOCK RETRIES num

Specifies the number of retries for SQL deadlock errors.

table-space-options:

ZOS: Specifies a z/OS system on which to create Q Capture control tables.
UW: Specifies a Linux, UNIX, or Windows system on which to create Q Capture control tables.

zos-ts-clause:

PAGE LOCK: Specify for replication control tables that require page-level locking.
ROW LOCK: Specify for replication control tables that require row-level locking.
DB dbname: Specifies the name of the database that contains the table space where the control tables will be created.
tsname: Specifies the name of the table space for the z/OS control tables.
NAMING PREFIX prefix: Specifies a prefix to add to the name of the table space.

uw-ts-clause:

TBSPACE

tsname: Specifies the name of the table space that is used for the control tables on Linux, UNIX, or Windows. If the table space does not already exist, add the CREATE keyword from the prof-clause.
NAMING PREFIX prefix: Specifies a prefix to add to the name of the table space.

fed-ts-clause:

TBSPACE tsname: Specifies the name of an existing Oracle table space, Sybase segment, Informix dbspace, or Microsoft SQL Server file group that is used for the control tables. This parameter is not applicable for Teradata targets.
RMT SCHEMA: The remote schema that the Q Apply program uses to create control tables on the non-DB2 database. The default is the remote authorization ID.

prof-clause:

CREATE: Specify to create a table space. When this parameter is used without the USING PROFILE keyword, the table space is created with default attributes and the control tables are created in this table space.
USING PROFILE pname: Specifies the name of a profile to use to customize the table space attributes.

Q Apply control tables

To create Q Apply control tables and to specify a monitor limit of 3 minutes and a trace limit of 9 minutes:

CREATE CONTROL TABLES FOR APPLY SERVER USING MONITOR LIMIT 3 TRACE LIMIT 9

Q Capture control tables

To create Q Capture control tables:

CREATE CONTROL TABLES FOR CAPTURE SERVER USING 
RESTARTQ "ASN1.QM1.RESTARTQ" ADMINQ "ASN1.QM1.ADMINQ"

Specifying a table space

To specify a table space where the control tables are created:

CREATE CONTROL TABLES FOR APPLY SERVER using MONITOR LIMIT 100 IN UW TBSPACE ts2;

Replication to federated Oracle target

To create Q Apply control tables for replication to an Oracle target with a remote authorization ID of ORACLE_ID:

CREATE CONTROL TABLES FOR APPLY SERVER IN FEDERATED RMT SCHEMA ORACLE_ID

Creating control tables on a downlevel database

To create Version 9.7 Q Apply control tables on a Db2 Version 9.1 database:

CREATE CONTROL TABLES FOR APPLY SERVER USING RELEASE "9.7"

Setting up remote log read with Db2 proxy server

The first example creates the remote IBMQREP_SIGNAL table on the remote source server:

CREATE CONTROL TABLES FOR REMOTE SOURCE SERVER
IN ZOS ROW LOCK DB DEFCTLDB "TSALL2";

The next example creates Q Capture control tables on the proxy server:

CREATE CONTROL TABLES FOR CAPTURE SERVER AS PROXY WITH REMOTE SOURCE DBNAME "STLEC1"
USING RESTARTQ "QALLTYPE.EC23B.RESTARTQ" ADMINQ "QALLTYPE.EC23B.ADMINQ" RELEASE "11.4.0"
IN ZOS PAGE LOCK DB DEFCTLDB "TSALL1"
ROW LOCK DB DEFCTLDB "TSALL2";