This chapter describes how to set up and operate the Capture and Apply programs on the following UNIX platforms:
Be sure to read the following sections before reading the sections on operating the Capture and Apply programs:
Before you set up the Capture and Apply programs, you must set up a UNIX user account to run the programs. Ensure that the user ID under which the Capture and Apply programs will run has the required privileges:
Setting up consists of configuring the source, target, and control servers. The following sections provide instructions for configuring each server as well as information about setting up end-user authentication at the source server.
db2 connect to database
where database is the source server database.
db2 update database configuration for database_alias using logretain on db2 backup database database_aliasor:
db2 update database configuration for database_alias using userexit on db2 backup database database_aliasYou might need to increase DBHEAP, APPLHEAPSZ, PCKCACHESZ, LOCKLIST, and LOGBUFSZ based on your installation requirements.
db2 bind @capture.lst isolation ur blocking all
where ur specifies the list in uncommitted read format for greater performance.
These commands create a list of packages, the names of which are in the file CAPTURE.LST.
Important: The Apply package is bound automatically during execution. Therefore, the following steps for binding the Apply package on UNIX are optional. If you want to specify options or check that all bind processes completed successfully, complete the following steps:
db2 connect to databasewhere database is the source, target, or control server .
| Note: | If the server database is cataloged as a remote database, you might need to
specify a user ID and password on the db2 connect to
command. For example:
db2 connect to database user userid using password |
db2 bind @applycs.lst isolation cs blocking all grant public
db2 bind @applyur.lst isolation ur blocking all grant publicwhere cs specifies the list in cursor stability format, and ur specifies the list in uncommitted read format.
Because the Apply program control tables use static SQL calls for the control tables, the Apply bind process searches for the control tables at each server that the Apply program is bound to, regardless of whether these control tables are used at a server.
These commands create a list of packages, the names of which are in the files APPLYCS.LST and APPLYUS.LST.
Ensure that the user ID from which the Capture and Apply programs are running has write privilege on the directories where you invoke the programs. Write privilege is necessary because both the Capture and Apply programs create files in the invocation directory.
The Capture program creates the following files in addition to the spill files:
The Apply program creates the following files:
For more information about configuration of UNIX-based components, see IBM DB2 Universal Database for UNIX Quick Beginnings.
In some cases you must provide a password file for end-user authentication to occur at the source server. The Apply program uses this file when connecting to the source server. Give read access to only the user ID that will run the Apply program. Environment-specific requirements include:
Creating a password file:
The password file must meet the following criteria:
Where applyqual is a case-sensitive string that must match the case and value of the Apply qualifier (APPLY_QUAL) in the subscription set table exactly.
For example: DATADIR.PWD
This naming convention is the same as the log file name (APPLYQUAL.app) and the spill file name (APPLYQUAL.nnn), but with a file extension of .pwd.
SERVER=server_name USER=userid PWD=password
Where:
If you do not create a password file:
The Apply program for UNIX must be able to issue an SQL CONNECT statement without specifying the user ID and password. If the Apply program needs to connect to an OS/390 database with SNA connectivity, these settings are necessary:
For more information about authentication and security, refer to the IBM DB2 Universal Database Administration Guide.
An administrator can use the commands in this section to operate the Capture program for UNIX platforms. Enter the commands or a key combination from a UNIX command line.
This section explains how to perform the following Capture program tasks:
This section also lists restrictions for running the Capture program.
Some actions cause the Capture program to terminate while it is running. Stop the Capture program before you take any of the following actions:
The Capture program cannot capture any changes made by DB2 utilities, because the utilities do not log the changes that they make.
Use the at command to start the Capture program at a specific time. For example, the following command starts the Capture program at 3:00 p.m. on Friday:
at 3pm Friday asnccp warmns noprune
You must set two environment variables before you start the Capture program. These variables must also be set when you use any of the following functions:
To set the environment variables:
export DB2INSTANCE=db2_instance_name
After you start the Capture program, it runs continuously until you stop it or it detects an unrecoverable error.
To start the Capture program for a UNIX platform:
export DB2INSTANCE=db2_instance_name
While the Capture program is running, a file with the name Database_instance_nameDatabase_name.ccp is created in the directory from which the Capture program is started. This file is a log file for the messages issued by the Capture program; these messages are also recorded in the trace table.
AIX example:
export LIBPATH=db2homedir/sqllib/lib:/usr/lib:/lib export LANG=en_US
HP-UX example:
export SHLIB_PATH=db2homedir/sqllib/lib:/usr/lib:/lib export LANG=en_US
Linux example:
export LD_LIBRARY_PATH=db2homedir/sqllib/lib:/usr/lib:/lib:/db2/linux/lib export LANG=en_US
NUMA-Q example:
export LD_LIBRARY_PATH=db2homedir/sqllib/lib:/opt/jse3.0/lib export LANG=en_US
Solaris example:
export LD_LIBRARY_PATH=db2homedir/sqllib/lib:/usr/lib:/lib export LANG=en_US export NLSPATH=/usr/lib/locale/%L/%N:/db2homedir/sqllib/msg/en_US/%N
>>-asnccp----+------------+---+--------+---+---------+---------->
'-src_server-' +-warm---+ +-prune---+
+-warmns-+ '-noprune-'
'-cold---'
>-----+---------+---+---------+---+----------+---+----------+--->
+-notrace-+ '-trcfile-' '-notrctbl-' '-autostop-'
'-trace---'
>-----+----------+---+-----------+---+---------+---------------><
'-logreuse-' '-logstdout-' +-allchg--+
'-chgonly-'
Table 27 defines the invocation parameters.
Table 27. ASNCCP invocation parameter definitions for UNIX platforms
| Parameter | Definition |
|---|---|
| src_server | Source server name must be the first parameter if entered. If not specified, the value from the DB2DBDFT environment variable is used. |
| warm (default) | The Capture program resumes processing where it ended in its previous run if warm start information is available. If the Capture program cannot warm start, it switches to a cold start. |
| warmns | The Capture program resumes processing where it ended in its previous run if warm start information is available. Otherwise, it issues a message and terminates. With warmns, the Capture program does not automatically switch to a cold start. The Capture program leaves the trace, UOW, CD, and warm start tables intact. In case of errors, the Capture program terminates instead of switching to a cold start as when warm is specified. |
| cold | The Capture program starts by deleting all rows in its CD table, UOW table, and trace table during initialization. All subscriptions to these replication sources will be fully refreshed during the next Apply processing cycle. A full refresh is not done if the target is a noncomplete CCD table (see Resolving gaps between source and target tables). |
| prune (default) | The Capture program automatically prunes the rows in the CD and UOW tables that the Apply program has copied, at the interval specified in the tuning parameters table. In addition, the Capture program also prunes the CD and UOW rows that are older than the retention limit even though they have not been copied during warm start. |
| noprune | Automatic pruning is disabled. The Capture program prunes the CD and the UOW tables only when you enter the prune command. |
| notrace (default) | No trace information is written. |
| trace | Writes trace messages to the standard output, stdout, unless trcfile is also specified. |
| trcfile | If both trcfile and trace are specified, the Capture program writes trace output to the trace file (*.trc). If you do not specify this option, the Capture program sends trace output to the standard output, stdout. |
| notrctbl | The Capture program messages are not logged in the trace table. |
| autostop | The Capture program terminates after it captures all transactions logged before the Capture program was started. |
| logreuse | The Capture program reuses the log file (*.ccp) by first deleting it and then recreating it when the Capture program is restarted. If you do not specify this option, the Capture program appends messages to the log file, even after the Capture program is restarted. |
| logstdout | The Capture program sends all messages to both the standard output (stdout) and the log file. |
| allchg (default) | Specifies that an entry is made to the CD table whenever any source table row changes. |
| chgonly | Specifies that an entry is made to the CD table when a source table row changes only if the columns defined for replication (CD table columns) change values. |
Use the stop command or a key combination to stop the Capture program in an orderly way and commit the log records that it processed up to that point. You must stop the Capture program before removing or modifying an existing replication source.
Before you stop the Capture program, ensure that the environment variables are set (see Setting environment variables for Capture for UNIX platforms). To stop the Capture program, enter the command:
If you set environment variable DB2DBDFT (see step 2):
>>-asncmd--stop------------------------------------------------><
or, if you did not set environment variable DB2DBDFT:
>>-asncmd--src_server--stop------------------------------------><
Use the suspend command to relinquish operating system resources to operational transactions during peak periods without destroying the Capture program environment. This command suspends the Capture program until you issue the resume command.
Before you suspend the Capture program, ensure that the environment variables are set (see Setting environment variables for Capture for UNIX platforms). To suspend the Capture program, enter the command:
If you set environment variable DB2DBDFT (see step 2):
>>-asncmd--suspend---------------------------------------------><
or, if you did not set environment variable DB2DBDFT:
>>-asncmd--src_server--suspend---------------------------------><
Important: Do not use the suspend command when canceling a replication source. Instead, stop the Capture program.
Use the resume command to restart the Capture program if you suspended it using the suspend command.
Before you resume the Capture program, ensure that the environment variables are set (see Setting environment variables for Capture for UNIX platforms). To resume the Capture program, enter the command:
If you set environment variable DB2DBDFT (see step 2):
>>-asncmd--resume----------------------------------------------><
or, if you did not set environment variable DB2DBDFT:
>>-asncmd--src_server--resume----------------------------------><
Use the reinit command to begin to capture changes from new source tables if you add a new replication source or ALTER ADD a column to a replication source and CD table while the Capture program is running. The reinit command tells the Capture program to obtain newly added replication sources from the register table.
reinit also rereads the tuning parameters table for any changes made to the tuning parameters.
Before you reinitialize the Capture program, ensure that the environment variables are set (see Setting environment variables for Capture for UNIX platforms). To reinitialize the Capture program, enter the command:
If you set environment variable DB2DBDFT (see step 2):
>>-asncmd--reinit----------------------------------------------><
or, if you did not set environment variable DB2DBDFT:
>>-asncmd--src_server--reinit----------------------------------><
Important: Do not use the reinit command to reinitialize the Capture program after canceling a replication source or dropping a replication source table while the Capture program is running. Instead, stop the Capture program and restart it using the WARM or WARMNS option.
Use the prune command to start pruning the CD and UOW tables.
This command prunes tables once.
Before you begin pruning tables, ensure that the environment variables are set (see Setting environment variables for Capture for UNIX platforms). To begin pruning tables, enter the command:
If you set environment variable DB2DBDFT (see step 2):
>>-asncmd--prune-----------------------------------------------><
or, if you did not set environment variable DB2DBDFT:
>>-asncmd--src_server--prune-----------------------------------><
The Capture program issues the message ASN0124I when the command is successfully queued.
If you stop or suspend the Capture program during pruning, you must enter the prune command again to resume pruning. Pruning does not resume after you enter the resume command.
Use the getlseq command to get the timestamp and current log sequence number. You can use this information to determine how far the Capture program has read the DB2 log.
Before you display captured log progress, ensure that the environment variables are set (see Setting environment variables for Capture for UNIX platforms). To display captured log progress, enter the command:
If you set environment variable DB2DBDFT (see step 2):
>>-asncmd--getlseq---------------------------------------------><
or, if you did not set environment variable DB2DBDFT:
>>-asncmd--src_server--getlseq---------------------------------><
| Tip: | You can use the DB2 UDB Find Log Sequence Number command (db2flsn) to identify the physical log file associated with the log sequence number. You can use this number to delete or archive log files no longer needed by the Capture program. For more information, see the IBM DB2 Universal Database Command Reference. |
An administrator can use the commands in the following sections to perform the following Apply program tasks:
Before you start the Apply program, ensure that:
After you start the Apply program, it runs continuously until:
To start the Apply program on a UNIX platform:
export DB2INSTANCE=db2_instance_name
AIX example:
export LIBPATH=db2homedir/sqllib/lib:/usr/lib:/lib export LANG=en_US
HP-UX example:
export SHLIB_PATH=db2homedir/sqllib/lib:/usr/lib:/lib export LANG=en_US
Linux example:
export LD_LIBRARY_PATH=db2homedir/sqllib/lib:/usr/lib:/lib:/db2/linux/lib export LANG=en_US
NUMA-Q example:
export LD_LIBRARY_PATH=db2homedir/sqllib/lib export LANG=en_US
Solaris example:
export LD_LIBRARY_PATH=db2homedir/sqllib/lib:/usr/lib:/lib export NLS_PATH= /usr/lib/locale/%L/%N:db2homedir/sqllib/msg/en_US/%N export LANG=en_US
>>-asnapply--apl_qual----+-----------+---+-----------+---------->
'-ctrl_serv-' +-loadxit---+
'-noloadxit-'
>-----+----------+---+---------+---+---------+---+----------+--->
+-inamsg---+ +-notrc---+ '-trcfile-' +-notify---+
'-noinamsg-' +-trcerr--+ '-nonotify-'
'-trcflow-'
>-----+---------+---+------------+---+----------+--------------->
+-sleep---+ +-delay(n)---+ '-copyonce-'
'-nosleep-' '-errwait(n)-'
>-----+----------+---+-----------+---+----------+--------------><
'-logreuse-' '-logstdout-' '-trlreuse-'
Table 28 defines the invocation parameters.
Table 28. ASNAPPLY invocation parameter definitions for UNIX platforms
| Parameter | Definition |
|---|---|
| apl_qual | Specifies the Apply qualifier that the Apply instance uses to identify the subscription sets to be served. The Apply qualifier is case sensitive and must match the value of APPLY_QUAL in the subscription set table. This must be the first parameter. |
| ctrl_serv | Specifies the name of the server where the replication control tables will reside. If you do not specify this parameter, the default is the default database or the value of DB2DBDFT. |
| loadxit | Specifies that the Apply program is to invoke ASNLOAD, an IBM-supplied exit routine that uses the export and load utilities to refresh target tables. |
| noloadxit (default) | Specifies that the Apply program will not invoke ASNLOAD. |
| inamsg (default) | Specifies that the Apply program is to issue a message when the Apply program is inactive. |
| noinamsg | Specifies that the Apply program will not issue this message. |
| notrc (default) | Specifies that the Apply program does not generate a trace. |
| trcerr | Specifies that the Apply program generates a trace that contains only error information. |
| trcflow | Specifies that the Apply program generates a trace that contains both error and execution flow information. |
| trcfile | If both trcfile and either trcerr or trcflow are specified, the Apply program writes trace output to the trace file (*.trc). If you do not specify this option, the Apply program sends trace output to the standard output, stdout. |
| notify | Specifies that the Apply program is to invoke ASNDONE, an exit routine that returns control to the user when the Apply program finishes copying a subscription set. |
| nonotify (default) | Specifies that the Apply program will not invoke ASNDONE. |
| sleep (default) | Specifies that the Apply program is to go to sleep if no new subscription sets are eligible for processing. |
| nosleep | Specifies that the Apply program is to stop if no new subscriptions are eligible for processing. |
| delay(n) | Specifies the delay time (in seconds) at the end of each Apply cycle when continuous replication is used, where n=0, 1, 2, 3, 4, 5, or 6. The default delay time is 6 seconds. |
| errwait(n) | Specifies the number of seconds that the Apply program waits before retrying after it encounters an error condition, where n is the number of seconds. Do not specify too small a number because the Apply program will run almost continuously and generate many rows in the Apply trail table. The default wait period is 300 seconds (5 minutes). |
| copyonce | The Apply program executes one copy cycle for each subscription set that
is eligible at the time the Apply program is invoked, and then the Apply
program terminates. An eligible subscription set is such that:
|
| logreuse | The Apply program reuses the log file (*.app) by first deleting it and then re-creating it when the Apply program is restarted. If you do not specify this option, the Apply program appends messages to the log file, even after the Apply program is restarted. |
| logstdout | The Apply program sends all messages to both the standard output (stdout) and the log file. |
| trlreuse | The Apply program empties the Apply trail table when the Apply program is started. |
Use the at command to start the Apply program at a specific time. For example, the following command starts the Apply program at 3:00 p.m. on Friday:
at 3pm Friday asnapply myqual
Use the asnastop command or a key combination to stop the Apply program in an orderly way.
To stop the Apply program, perform the following steps from a window where the Apply program is not running:
>>-asnastop--apply_qualifier-----------------------------------><