This chapter describes how to set up and operate the Capture and Apply programs for DB2 DataPropagator for AS/400 Version 7.
Be sure to read the following sections before reading the sections on operating the Capture and Apply programs for AS/400:
You cannot run Version 1 of DB2 DataPropagator for AS/400 concurrently with Version 7. If you currently use Version 1, or if you use Version 1 replication components in a Version 5 DB2 DataPropagator for AS/400 environment, you must either:
You should use DJRA for all replication administration tasks. However, both DJRA and the DB2 Control Center provide basic replication administration functions for defining replication sources and subscription sets. Only DJRA provides support for remote journals and the use of a relative record number (RRN) as a primary key.
Setting up the Capture and Apply programs consists of installing DB2 DataPropagator for AS/400 and tuning the Capture program for optimum productivity. This section describes how to set up the Capture and Apply programs.
You install DB2 DataPropagator for AS/400 in the same way that you install any other licensed program. Follow these steps for the regular installation:
If the window does not contain the product ID number (5769DP3) on the install screen, exit LICPGM and enter RSTLICPGM on the AS/400 command line, and then specify 5769DP3 for the product ID.
If the window does contain the ID number, type a 1 next to it and press the Enter key.
Before you connect to an AS/400 server from a DB2 for Windows NT or Windows 95 client, make sure that your workstation is set up correctly:
For example, use the following steps to connect to an AS/400 server from a DB2 for Windows NT workstation:
MYDBOS2 9.112.14.67
RCHASDPD RCHASDPD
DB2400E *LOCAL
RCHASLJN RCHASLJN
db2 catalog tcpip node server_name remote server_name server 446 system server_name ostype OS400 db2 catalog dcs database rdb_name AS rdb_name db2 catalog database rdb_name AS rdb_name at node server_name authentication dcs
Where server_name is the TCP/IP host name of the AS/400 system, and rdb_name is the name of the AS/400 relational database that you found in Step 1.
db2 terminate
CHGUSRPRF USRPRF (user) CCSID(37)
db2 connect to rdb_name user user_name using password
STRTCPSVR SERVER(*DDM)
You should install DB2 DataPropagator for AS/400 before using the replication administration tools, because the installation process issues the CRTDPRTBL command to automatically create the replication control tables. These tables are created in the DataPropagator Relational collection (named ASN), if they do not already exist.
The installation program also creates an SQL journal, an SQL journal
receiver for this library, and work management objects. Table 8 lists the work management objects that are
created.
Table 8. Work management objects
| Description | Object type | Name |
|---|---|---|
| Subsystem description | *SBSD | QDPR/QZSNDPR |
| Job queue | *JOBQ | QDPR/QZSNDPR |
| Job description | *JOBD | QDPR/QZSNDPR |
A note on work management: You can alter the default definitions or provide your own definitions. See OS/400 Work Management V4R3, SC41-5306 for more information about changing these definitions.
If your replication control tables are accidentally deleted or corrupted, you can create them manually using the Create DPR Tables (CRTDPRTBL) command. You must have *ALLOBJ authority to run this command.
Important: The CRTDPRTBL command is the only command that you should use to create AS/400 control tables. Do not use DJRA to create the control tables.
>>-CRTDPRTBL----+----------------------+-----------------------><
| .-7--. |
'-DPRVSN(--+-5--+---)--'
Table 9. CRTDPRTBL command parameter definitions for AS/400
| Parameter | Definition and prompts |
|---|---|
| DPRVSN | Specifies the version of the control tables to create.
|
To control the performance of the Capture program, you can adjust four tuning parameters on the server by changing the values of columns in the tuning parameters table.
To specify the tuning parameters, do one of the following tasks:
The Change DPR Capture Attributes (CHGDPRCAPA) command changes the global operating parameters in the tuning parameters table for the Capture program.
You can see the current values of the Capture program attributes if you issue the CHGDPRCAPA command with the F4 key to prompt on the command.
>>-CHGDPRCAPA----+---------------------------+------------------>
'-RETAIN(--+-*SAME---+---)--'
'-minutes-'
>-----+------------------------+-------------------------------->
'-LAG(--+-*SAME---+---)--'
'-minutes-'
>-----+---------------------------+----------------------------->
'-FRCFRQ(--+-*SAME---+---)--'
'-seconds-'
>-----+---------------------------+----------------------------><
'-CLNUPITV(--+-*SAME-+---)--'
'-1-100-'
Table 10. CHGDPRCAPA command parameter definitions for AS/400
| Parameter | Definition and prompts |
|---|---|
| RETAIN | Specifies the new retention limit, which is the number of minutes that
data is retained in the CD tables and the UOW table before it is
removed.
The value of this parameter works with the CLNUPITV parameter. When the CLNUPITV value is reached, data in the CD and UOW tables is removed if the UOW table row shows that the transaction is older than the value of this parameter. Ensure that the Apply intervals are set to copy changed information before the value on the RETAIN parameter is reached. This prevents your tables from becoming inconsistent. If they become inconsistent, the Apply program performs full refreshes.
|
| LAG | Specifies the new lag limit, which is the number of minutes that the
Capture program can fall behind before clearing out the CD tables and starting
over with change capture. When the lag limit is reached (that is, when
the timestamp of the journal entry is older than the current time minus the
lag limit), the Capture program assumes that it is too far behind to catch
up. It then initiates a cold start for the tables that it is processing
for that journal. The Apply program then performs a full refresh to
provide the Capture program with a new starting point. Users typically
set this value high so that it has no effect.
|
| FRCFRQ | Specifies approximately how often the Capture program writes changes to
the CD and UOW tables.
The Capture program makes the changes available to the Apply program either when the buffers are filled or when this time has expired, whichever is sooner. Use this parameter to make source table changes more readily available for the Apply program on servers with a low rate of source table changes. This is a global value, and is used for all defined source tables. Setting this value at a lower number can affect processor usage.
|
| CLNUPITV | Specifies the maximum length of time before the Capture program prunes
old records from the CD tables and the UOW table, if it exists. This
parameter works with the RETAIN parameter.
The value of this parameter is converted from hours to seconds and stored in the PRUNE_INTERVAL column of the tuning parameters table. If the PRUNE_INTERVAL column is changed manually (not using the CHGDPRCAPA command), you might see changes due to rounding when you prompt using the F4 key.
|
This section describes the commands available for granting and revoking authority to the replication control tables: Granting authority and Revoking authority.
The Grant DPR Authority (GRTDPRAUT) command authorizes a list of users to the replication control tables, so that the users can run the Capture and Apply programs. For example, the authority requirements for the user who is running the Capture and Apply programs might differ from the authority requirements for the user who defines replication sources and targets.
You must have *ALLOBJ authority to grant authorities.
>>-GRTDPRAUT---USER(--+---user-name---+---)--------------------->
'-*PUBLIC-------'
.-7--.
>----AUT(--+-*REGISTRAR--+---)---DPRVSN(--+-5--+---)------------>
+-*SUBSCRIBER-+
+-*CAPTURE----+
'-*APPLY------'
.-*ALL------------.
>----APYQUAL(--+-*USER-----------+---)-------------------------><
'-apply-qualifier-'
Table 11. GRTDPRAUT command parameter definitions for AS/400
| Parameter | Definition and prompts |
|---|---|
| USER | Specifies the users who have authority.
|
| AUT | Specifies the type of authority being granted.
|
| DPRVSN | Specifies the version of DB2 DataPropagator for AS/400.
|
| APYQUAL | Specifies the Apply qualifier to be used by the user specified with the
USER parameter. This parameter is used only when AUT(*APPLY) or
AUT(*SUBSCRIBER) is specified.
|
You cannot use the GRTDPRAUT command while the Capture or Apply programs are running, or when applications that use the source tables are active because authorizations cannot be changed on files that are in use.
To authorize user USER1 to define and modify replication sources:
GRTDPRAUT USER(USER1) AUT(*REGISTRAR) DPRVSN(7)
To authorize user USER1 to define and modify subscriptions:
GRTDPRAUT USER(USER1) AUT(*SUBSCRIBER) DPRVSN(7)
To authorize user USER1 to define and modify existing subscriptions associated with Apply qualifier A1:
GRTDPRAUT USER(USER1) AUT(*SUBSCRIBER) DPRVSN(7) APYQUAL(A1)
To authorize a user to run the Apply program on the control server system for all subscriptions associated with Apply qualifier A1, where the target server is the same as the control server:
GRTDPRAUT USER(USER1) AUT(*APPLY) DPRVSN(7) APYQUAL(A1)
GRTDPRAUT USER(USER1) AUT(*APPLY) DPRVSN(7) APYQUAL(A1)
If the application server job on the source server used by the Apply program runs under a different user profile; for example, QUSER, the command is:
GRTDPRAUT USER(QUSER) AUT(*APPLY) DPRVSN(7) APYQUAL(A1)
The following tables list the authorities granted when you specify:
on the GRTDPRAUT command.
The following table lists the authorities granted when you specify the
AUT(*REGISTRAR) parameter on the GRTDPRAUT command:
Table 12. Authorities granted with GRTDPRAUT AUT(*REGISTRAR)
| Library | Object | Type | Version | Authorizations |
|---|---|---|---|---|
| QSYS | ASN | *LIB | 5 7 | *USE, *ADD |
| ASN | QSQJRN | *JRN | 5 7 | *OBJOPR, *OBJMGT |
| ASN | IBMSNAP_REGISTER | *FILE | 7 | *OBJOPR, *READ, *ADD, *UPD, *DLT |
| ASN | IBMSNAP_REGISTERX | *FILE | 7 | *OBJOPR, *READ, *ADD, *UPD, *DLT |
| ASN | IBMSNAP_REG_EXT | *FILE | 5 7 | *OBJOPR, *OBJMGT, *READ, *ADD, *UPD, *DLT |
| ASN | IBMSNAP_REG_EXTX | *FILE | 5 7 | *OBJOPR, *OBJMGT, *READ, *ADD, *UPD, *DLT |
| ASN | IBMSNAP_UOW | *FILE | 5 7 | *OBJOPR, *OBJMGT, *READ, *ADD *DLT |
| ASN | IBMSNAP_UOW_IDX | *FILE | 5 7 | *OBJOPR, *OBJMGT, *READ, *ADD, *DLT |
| ASN | IBMSNAP_PRUNCNTL | *FILE | 7 | *OBJOPR, *READ |
| ASN | IBMSNAP_CCPPARMS | *FILE | 5 7 | *OBJOPR, *READ, *UPD |
| ASN | QZSNCTLBLK | *USRSPC | 5 7 | *CHANGE |
| ASN | ASN4B* | *SQLPKG | 7 | *USE |
| ASN | ASN4C* | *SQLPKG | 7 | *USE |
| QSYS | Source library | *LIB | 5 7 | *USE |
| Source library | Source table | *FILE | 5 7 | *OBJOPR, *READ |
| QSYS | Control library | *LIB | 5 7 | *USE, *ADD |
| Control library | CDtimestamp - CD table | *FILE | 7 | *USE, *OBJMGT, *OBJEXIST |
The following table lists the authorities granted when you specify the
AUT(*SUBSCRIBER) parameter on the GRTDPRAUT command:
Table 13. Authorities granted with GRTDPRAUT AUT(*SUBSCRIBER)
| Library | Object | Type | Version | Authorizations |
|---|---|---|---|---|
| QSYS | ASN | *LIB | 7 | *USE, *ADD |
| QSYS | IBMSNAP_SUBS_SET | *FILE | 7 | *CHANGE |
| ASN | IBMSNAP_APPLYTRAIL | *FILE | 7 | *CHANGE |
| ASN | IBMSNAP_SUBS_COL | *FILE | 7 | *CHANGE |
| ASN | IBMSNAP_SUBS_EVENT | *FILE | 7 | *CHANGE |
| ASN | IBMSNAP_SUBS_STMTS | *FILE | 7 | *CHANGE |
| ASN | IBMSNAP_SUBS_MEMBR | *FILE | 7 | *CHANGE |
| ASN | IBMSNAP_REGISTER | *FILE | 7 | *USE, *UPD |
| ASN | IBMSNAP_REG_EXT | *FILE | 5 7 | *USE, *UPD |
| ASN | IBMSNAP_PRUNCNTL | *FILE | 7 | *USE, *ADD, *DLT |
| ASN | ASN4U* | *SQLPKG | 7 | *USE |
| ASN | ASN4A* | *SQLPKG | 7 | *USE |
| QSYS | Source library | *LIB | 5 7 | *USE |
| Source library | Source table | *FILE | 5 7 | *OBJOPR, *READ |
| QSYS | Control library | *LIB | 7 | *USE |
| Control library | ASNtimestampPC - pruning control table | *LIB | 7 | *USE |
| Control library | CD table | *FILE | 5 7 | *OBJOPR, *READ |
| Control library | Internal CCD table | *FILE | 5 7 | *OBJOPR, *READ |
| QSYS | Target library | *LIB | 7 | *USE, *ADD |
| Target library | Target table | *FILE | 7 | *USE, *OBJMGT, *OBJEXIST |
The following table lists the authorities granted when you specify the
AUT(*CAPTURE) parameter on the GRTDPRAUT command:
Table 14. Authorities granted with GRTDPRAUT AUT(*CAPTURE)
| Library | Object | Type | Version | Authorizations |
|---|---|---|---|---|
| QSYS | ASN | *LIB | 5 7 | *USE, *OBJMGT |
| ASN | IBMSNAP_REGISTER | *FILE | 5 7 | *USE, *UPD |
| ASN | IBMSNAP_REG_EXT | *FILE | 5 7 | *USE, *UPD |
| QSYS | Control library | *LIB | 5 7 | *USE |
| Control library | CD table | *FILE | 5 7 | *OBJOPR, *OBJMGT, *READ, *UPD, *DLT, *ADD |
| Control library | CD table | *FILE | 5 7 | *OBJOPR, *OBJMGT, *READ, *UPD, *DLT, *ADD |
| ASN | IBMSNAP_PRUNCNTL | *FILE | 7 | *USE, *UPD |
| ASN | IBMSNAP_CRITSEC | *FILE | 7 | *USE |
| ASN | IBMSNAP_CCPPARMS | *FILE | 5 7 | *USE |
| ASN | IBMSNAP_UOW | *FILE | 5 7 | *CHANGE |
| ASN | IBMSNAP_TRACE | *FILE | 7 | *CHANGE |
| ASN | IBMSNAP_WARM_START | *FILE | 7 | *CHANGE |
| ASN | IBMSNAP_AUTHTKN | *FILE | 7 | *CHANGE |
| ASN | QZSBCTKBLK | *USRSPC | 5 7 | *CHANGE |
| ASN | ASNB* | SQLPKG | 7 | *USE |
| ASN | ASNC* | SQLPKG | 7 | *USE |
The following table lists the authorities granted when you specify the
AUT(*APPLY) parameter on the GRTDPRAUT command:
Table 15. Authorities granted with GRTDPRAUT AUT(*APPLY)
| Library | Object | Type | Version | Authorizations |
|---|---|---|---|---|
| QSYS | ASN | *LIB | 5 7 | *USE |
| ASN | IBMSNAP_SUBS_SET | *FILE | 7 | *CHANGE |
| ASN | IBMSNAP_APPLYTRAIL | *FILE | 7 | *CHANGE |
| ASN | IBMSNAP_SUBS_COLS | *FILE | 7 | *USE |
| ASN | IBMSNAP_SUBS_EVENT | *FILE | 7 | *USE |
| ASN | IBMSNAP_SUBS_STMTS | *FILE | 7 | *USE |
| ASN | IBMSNAP_SUBS_MEMBR | *FILE | 7 | *USE |
| ASN | ASNA* | *SQLPKG | 7 | *USE |
| ASN | ASNU* | *SQLPKG | 7 | *USE |
| ASN | IBMSNAP_REGISTER | *FILE | 7 | *USE, *UPD |
| ASN | IBMSNAP_REG_EXT | *FILE | 5 7 | *USE, *UPD |
| ASN | IBMSNAP_UOW | *FILE | 5 7 | *USE, *UPD |
| ASN | IBMSNAP_PRUNCNTL | *FILE | 7 | *USE, *UPD, *ADD |
| ASN | IBMSNAP_CRITSEC | *FILE | 7 | *USE, *ADD |
| ASN | IBMSNAP_AUTHTKN | *FILE | 7 | *USE, *ADD |
| QSYS | Control library | *LIB | 5 7 | *USE |
| Control library | CD table | *FILE | 5 7 | *USE |
| QSYS | Target library | *LIB | 7 | *USE |
| Target library | Target table | *FILE | 7 | *CHANGE, *OBJMGT |
The Revoke DPR Authority (RVKDPRAUT) command revokes authority to the replication control tables so that users can no longer define or modify replication sources and subscriptions.
.----------------.
V | .-7--.
>>-RVKDPRAUT--USER(--+------user-name----+-+---)---DPRVSN(--+-5--+---)-->
'-*PUBLIC-------------'
>--------------------------------------------------------------><
The command returns an error message if any of the following conditions exist:
Table 16. RVKDPRAUT command parameter definitions for AS/400
| Parameter | Definition and prompts |
|---|---|
| USER | Specifies the users whose authority is revoked.
|
| DPRVSN | Specifies the version of DB2 DataPropagator for AS/400.
|
To revoke authorities to the control tables:
RVKDPRAUT USER(user-name) DPRVSN(7)
If you perform any of the following actions while a Capture journal job is running, it terminates with message ASN2301 or ASN2201 in the job log:
To keep the Capture program running, use the following guidelines:
When conditions make capturing data for a particular source table impossible, the Capture program changes the state of the source table from capturing changes to needing a full refresh. (See Table 20 for a list of such conditions.) Other conditions that prevent data capturing for a source table are:
When you need to perform an ALTER TABLE on the source table, ensure that you remove the subscription and define the source table again. Or you can use the List or Change replication sources action in DJRA to fix the changed data table. If you defined targets, you can also use the List members or add a column to target table action in DJRA to alter the target tables.
DB2 DataPropagator for AS/400 uses the information that it receives from the journals about changes to the data to populate the CD and UOW tables for replication.
DB2 DataPropagator for AS/400 runs under commitment control for most operations and therefore requires journaling on the control tables. (The QSQJRN journal is created when the CRTDPRTBL command creates a collection.)
Administrators must manually create the QSQJRN journal in both the library that contains the replication source control tables and the library that contains the target tables. Administrators must also ensure that all the source tables are journaled correctly.
In previous versions of DB2 DataPropagator for AS/400, replication source definitions (including the control tables associated with a source) and the Capture program always resided on the same system. The remote journal function makes it possible to move the replication source definitions and the Capture program and its control tables away from the system on which the source tables reside, leaving more resources available on that system. With the remote journal function, processor usage can be reduced, DASD can be saved, and performance can be improved significantly.
Important: The intention of this type of setup is to have the replication source definitions on the same AS/400 system as the replication target.
A replication source definition that refers to a remote source table cannot be subscribed to by other platforms such as the Apply program for OS/390 or the Apply program for UNIX.
To define a replication source with remote journals, select Define One Table as a Replication Source from the DJRA main window, select an AS/400 source table, then select the AS/400 policies tab. From this tab, select the remote journal checkbox, and enter the remote journal library, remote journal name, and Capture server.
For more information about the remote journal function, see AS/400 Remote Journal Function for High Availability and Data Replication, SG24-5189.
To set up the source table journals, you must have the authority to create journals and journal receivers for the source tables to be defined.
Important: Use a different journal for the source tables than one of those created by DB2 DataPropagator for AS/400 (QSQJRN journals) in the ASN library, the source library, the control library, or the target library.
To create a source table journal:
CRTJRNRCV JRNRCV(JRNLIB/RCV0001)
THRESHOLD(50000)
TEXT('DataPropagator Journal Receiver')
Be sure to:
CRTJRN JRN(JRNLIB/DJRN1)
JRNRCV(JRNLIB/RCV0001)
MNGRCV(*SYSTEM) DLTRCV(*YES)
TEXT('DataPropagator Journal')
Be sure to:
You can use two values on the RCVSIZOPT parameter of the CRTJRN command (*RMVINTENT and *MINFIXLEN) to optimize your storage availability and system performance. See the AS/400 Programming: Performance Tools Guide for more information.
STRJRNPF FILE(library/file)
JRN(JRNLIB/DJRN1)
OMTJRNE(*OPNCLO)
IMAGES(*BOTH)
Specify the name of the journal that you created in step 2. The
Capture program requires a value of *BOTH for the IMAGES parameter.
The Capture program uses the Receive Journal Entry (RCVJRNE) command to receive journals.
It is recommended that you let the AS/400 system manage the changing of journal receivers. This is called system change journal management. Specify MNGRCV(*SYSTEM) when you create the journal, or change the journal to that value. If you use system change journal management support, you must create a journal receiver that specifies the threshold at which you want the system to change journal receivers. The threshold must be at least 5000 KB, and should be based on the number of transactions on your system. The system automatically detaches the receiver when it reaches the threshold size and creates and attaches a new journal receiver, if it can.
If you specify MNGRCV(*USER) when you create the journal (meaning you want to manage changing your own journal receivers), a message is sent to the journal's message queue when the journal receiver reaches a storage threshold, if one was specified for the receiver.
Use the CHGJRN command to detach the old journal receiver and attach a new one. This command prevents Entry not journaled error conditions and limits the amount of storage space that the journal uses. To avoid affecting performance, do this at a time when the system is not at maximum use.
You can switch journal receiver management back to the system by specifying CHGJRN MNGRCV(*SYSTEM).
You should regularly detach the current journal receiver and attach a new one for two reasons:
The default message queue for a journal is QSYSOPR. If you have a large volume of messages in the QSYSOPR message queue, you might want to associate a different message queue, such as DPRUSRMSG, with the journal. You can use a message handling program to monitor the DPRUSRMSG message queue. For an explanation of messages that can be sent to the journal message queue, see OS/400 Backup and Recovery.
When you install DB2 DataPropagator for AS/400 on a V4R2 (or later) system, a delete journal receiver exit routine (DLTJRNRCV) is registered automatically. This exit routine is called any time a journal receiver is deleted, whether or not it is used for journaling the source tables. This exit routine determines whether or not a journal receiver can be deleted. (You no longer need to do this manually. Nor do you need to use the ANZDPRJRN command to delete old receivers.)
To take advantage of the delete journal receiver exit routine and leave journal management to the system, specify DLTRCV(*YES) and MNGRCV(*SYSTEM) on the CHGJRN or CRTJRN command.
If the journal that the receiver is associated with has no association with any of the source tables, this exit routine approves the deletion of the receiver.
If the journal receiver is used by one or more source tables, this exit routine makes sure that the receiver being deleted does not contain entries that have not been processed by the Capture program. The exit routine disapproves the deletion of the receiver if the Capture program still needs to process entries on that receiver.
If you must delete a journal receiver and the delete journal receiver exit routine does not approve the deletion, specify DLTJRNRCV DLTOPT(*IGNEXITPGM) to override the exit routine.
If you want to handle the deletion of journal receivers manually, you can remove the delete journal receiver exit routine by issuing the following command:
RMVEXITPGM EXITPNT (QIBM_QJO_DLT_JRNRCV)
FORMAT(DRVC0100)
PGMNBR(value)
To determine the PGMNBR value for the RMVEXITPGM command:
If the 5769DP3 version of DB2 DataPropagator for AS/400 was installed on V4R1 and the operating system was upgraded to V4R2 or V4R3 without reinstalling the product, you must register the exit routine with this command:
ADDEXITPGM EXITPNT(QIBM_QJO_DLT_JRNRCV)
FORMAT(DRCV0100)
PGMNBR(value *LOW)
CRTEXITPNT(*NO)
PGM(QDPR/QZSNDREP)
Before you define source tables as replication sources with DB2 DataPropagator for AS/400, you must be authorized to the control tables.
There are no commands in DB2 DataPropagator for AS/400 for defining replication sources and subscription sets. Use the DB2 DataJoiner Replication Administration tool (DJRA) to define replication sources and subscription sets. Before you define a table as a replication source, the source table must be journaled for both before-images and after-images, and the library where the CD table is created must have a QSQJRN journal.
In DB2 DataPropagator for AS/400, the tables you define as replication sources can contain approximately 2000 columns; the exact number of columns depends on the length of the name of each column. This limitation exists because character strings supplied by the Apply program for AS/400 cannot exceed 32K.
When you define tables as replication sources, the CCSID attributes of CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC columns in the CD table must be the same as the CCSID column attributes of the source table.
In DB2 DataPropagator for AS/400, many source tables do not have a column that can be identified as a primary key column. DB2 DataPropagator requires primary key columns for the Apply program to track which updates are applied to which target table rows. To meet this requirement, DB2 DataPropagator for AS/400 supports the use of relative record numbers (RRNs) of source table rows as primary key columns. Both the CD table and the target table have an extra column, IBMQSQ_RRN, of type INTEGER. This column has the RRN of the source table row.
Because the RRN of a source table row does not change unless the source table is reorganized, the RRN value can be used as a primary key for the source table row if a source table is not reorganized. Any time that you reorganize a source table (to compress deleted rows, for example), DB2 DataPropagator for AS/400 performs a full refresh of all the target tables.
Important: Only the Apply program for AS/400 can be used to maintain copies that contain RRN columns, whether these copies are on an AS/400 or other target DB2 platforms.
To define a replication source with a RRN column, select Define One Table as a Replication Source from the DJRA main window, select an AS/400 source table, then select the AS/400 policies tab. From this tab, select the RRN checkbox.
The replication administrator user ID and users who have *CAPTURE authority can use the commands in this section to perform the following Capture for AS/400 tasks:
This section also describes how the Capture program handles warm and cold starts, in Warm and cold starts.
Use the Start DPR Capture (STRDPRCAP) command to start capturing changes to AS/400 database tables. Because this command processes all replication sources in the register table, make sure that the user running this command has the proper authority.
After you start the Capture program, it runs continuously until you stop it or it detects an unrecoverable error.
>>-STRDPRCAP----+--------------------------+-------------------->
| .-*YES--. |
'-RESTART(--+-*NO---+---)--'
>-----+---------------------------------------------------+----->
| .-*LIBL/QZSNDPR---------------------. |
'-JOBD(--+-library-name/job-description-name-+---)--'
>-----+----------------------+---+-----------------------+------>
| .-7--. | | .-120---. |
'-DPRVSN(--+-5--+---)--' '-WAIT(--+-value-+---)--'
>-----+--------------------------------------------------+------>
| .-*DPRVSN-------. .-*IMMED---. |
'-CLNUPITV(--+-*GLOBAL-------+---+-*DELAYED-+---)--'
'-hours-to-wait-' '-*NO------'
>-----+-------------------------------------------------+------->
| .-*ALL-----------------------------. |
| | .-----------------------------. | |
| | V | | |
'-JRN(--+----library-name/journal-name---+-+---)--'
>-----+--------------------------------+-----------------------><
| .-*ALLCHG----. |
'-GENCDROW(--+-*REGCOLCHG-+---)--'
Table 17. STRDPRCAP command parameter definitions for AS/400
You can run the STRDPRCAP command manually, or you can automatically run the command as a part of the initial program load (IPL startup program). For information about including the STRDPRCAP command in a startup program, see OS/400 Work Management V4R3, SC41-5306.
If the job description specified with the JOBD parameter uses job queue QDPR/QZSNDPR, and the DB2 DataPropagator for AS/400 subsystem is not active, the STRDPRCAP command starts the subsystem. If the job description is defined to use a different job queue and subsystem, you must start this subsystem manually with the Start Subsystem (STRSBS) command either before or after running the STRDPRCAP command:
STRSBS QDPR/QZSNDPR
You can set up the system to start the subsystem automatically by adding the STRSBS command to the program that is referred to in the QSTRUPPGM system value on your system.
To determine the progress of the Capture program, you must either determine how much work remains between the last Capture process that was performed and the last Apply process, or use the DJRA Replication Monitor.
If the Capture program has ended, you can determine its progress by inspecting the warm start table. There is one row for each journal used by the source tables. The LOGMARKER column provides the timestamp of the last journal entry processed successfully. The SEQNBR column provides the journal entry sequence number of that entry.
If the Capture program is still running, you can determine its progress by completing the following tasks:
DSPJRN FILE(JRNLIB/DJRN1)
RCVRNG(*CURCHAIN)
FROMTIME(timestamp)
TOTIME(*LAST)
JRNCDE(J F R C)
OUTPUT(*OUTFILE)
ENTDTALEN(1) OUTFILE(library/outfile)
In the example, timestamp is the timestamp that you identified in step 4.
The number of records in the output file is the approximate number of journal entries that remain to be processed by the Capture program.
Use the SBMJOB command to schedule the start of the Capture program on AS/400:
SBMJOB CMD('STRDPRCAP...') SCDDATE(...) SCDTIME(...)
Use the End DPR Capture (ENDDPRCAP) command to end the Capture program.
Use this command to end the Capture program before shutting down the system. You might also want to end the program during periods of peak system use to increase the performance of other programs that run on the system.
>>-ENDDPRCAP----+----------------------------+------------------>
| .-*CNTRLD--. |
'-OPTION(--+-*IMMED---+---)--'
>-----+----------------------+---------------------------------><
| .-7--. |
'-DPRVSN(--+-5--+---)--'
Table 18. ENDDPRCAP command parameter definitions for AS/400
| Parameter | Definition and prompts |
|---|---|
| OPTION | Specifies how to end the Capture program.
|
| DPRVSN | Specifies the version of the Capture program to end.
|
If you use the ENDJOB command, temporary objects might be left in the QDPR library. These objects have the types *DTAQ and *USRSPC, and are named QDPRnnnnnn, where nnnnnn is the job number of the job that used them. You can delete these objects when the job that used them (identified by the job number in the object name) is not active.
If the job QDPRCTL5 does not end long after issuing this command, use the ENDJOB command with *IMMED option to end this job and all the journal jobs running in the DB2 DataPropagator for AS/400 subsystem. Do not end Apply jobs running in the same subsystem if you want to end only the Capture program.
In rare cases when the job QDPRCTL5 ends abnormally, the journal jobs created by QDPRCTL5 might still be left running. The only way to end these jobs is to use the ENDJOB command with either the *IMMED or *CNTRLD option.
The Initialize DPR Capture (INZDPRCAP) command initializes the Capture program by directing the Capture program to work with an updated list of source tables.
Source tables under the control of the program can change while the Capture program is running. Use the INZDPRCAP command to ensure that the Capture program processes the most up-to-date replication sources.
If you change the values of the tuning parameters while the Capture program is running, enter the INZDPRCAP command to reinitialize the program using the new values.
The Capture program must be running before you run this command.
>>-INZDPRCAP----+----------------------+------------------------>
| .-7--. |
'-DPRVSN(--+-5--+---)--'
>-----+-------------------------------------------------+------><
| .-*ALL-----------------------------. |
| | .-----------------------------. | |
| | V | | |
'-JRN(--+----library-name/journal-name---+-+---)--'
Table 19. INZDPRCAP command parameter definitions for AS/400
| Parameter | Definition and prompts |
|---|---|
| DPRVSN | Specifies the version of the Capture program to initialize.
|
| JRN | Specifies a subset of up to 50 journals that you want the Capture program
to work with. The Capture program will start processing all the source
tables that are currently journaled to this journal.
|
The CLNUPITV parameter on the STRDPRCAP command specifies the maximum number of hours that the Capture program waits before pruning old records from the CD tables and the UOW table. For more information about the CLNUPITV parameter, see Starting Capture for AS/400.
Pruning does not recover DASD for you. You have to frequently issue RGZPFM (Reorganize Physical File Member) commands against the CD tables and the UOW tables to recover the DASD. The RGZPFM command reclaims deleted space by moving active rows forward. It requires an EXCLRD lock of the file and member, and you must schedule it when Capture and Apply are not running.
Important: Issue RGZPFM commands once a week.
The value of the RESTART parameter on the STRDPRCAP command controls how the Capture program handles warm and cold starts.
Warm start information is saved in most cases. Occasionally, warm start information is not saved. In this case, the Capture program uses the CD tables, UOW table, or the pruning control table to resynchronize to the time that it was stopped.
Sometimes the Capture program automatically switches to a cold start, even if you specified a warm start. On AS/400 systems, cold starts work on a journal-by-journal basis. So, for example, if a journal exceeds the lag limit, all replication sources using that journal are cold-started, whereas replication sources using a different journal are not cold started.
For more information about how the Capture program processes different journal entry types, see Table 20.
The following table describes how the Capture program processes different
journal entry types.
Table 20. Capture program processing by journal entry
| Journal code3 | Entry type | Description | Processing |
|---|---|---|---|
| C | CM | Set of record changes committed | Insert a record in the UOW table. |
| C | RB | Rollback | No UOW row inserted. |
| F | AY | Journaled changes applied to physical file member | Issue an ASN2004 message and full refresh of file. |
| F | CE | Change end of data for physical file | Issue an ASN2004 message and full refresh of file. |
| F | CR | Physical file member cleared | Issue an ASN2004 message and full refresh of file. |
| F | EJ | Journaling for physical file member ended | Issue an ASN2004 message and full refresh of file. |
| F | IZ | Physical file member initialized | Issue an ASN2004 message and full refresh of file. |
| F | MD | Member removed from physical file (DLTLIB, DLTF, or RMVM) | Issue an ASN2004 message and attempt a full refresh. |
| F | MF | Storage for physical file member freed | Issue an ASN2004 message and full refresh of file. |
| F | MM | Physical file containing member moved (Rename Object (RNMOBJ) of library, Move Object (MOVOBJ) of file) | Issue an ASN200A message and attempt a full refresh. |
| F | MN | Physical file containing member renamed (RNMOBJ of file, Rename Member (RNMM)) | Issue an ASN200A message and attempt a full refresh. |
| F | MR | Physical file member restored | Issue an ASN2004 message and full refresh of file. |
| F | RC | Journaled changes removed from physical file member | Issue an ASN2004 message and full refresh of file. |
| F | RG | Physical file member reorganized | If the RRN of the source table is being used as the replication key, issue an ASN2004 message and full refresh of file. |
| J | NR | Identifier for next journal receivers | Reset the Capture program. |
| J | PR | Identifier for previous journal receivers | Increment the unique sequence number counter. |
| R | DL | Record deleted from physical file member | Insert a DLT record in the CD table. |
| R | DR | Record deleted for rollback | Insert a DLT record in the CD table. |
| R | PT | Record added to physical file member | Insert an ADD record in the CD table. |
| R | PX | Record added directly to physical file member | Insert an ADD record in the CD table. |
| R | UB | Before-image of record updated in physical file member | See note 1. |
| R | UP | After-image of record updated in physical file member | See note 1. |
| R | BR | Before-image of record updated for rollback | See note 2. |
| R | UR | After-image of record updated for rollback | See note 2. |
|
Notes:
| |||
All other journal entry types are ignored by the Capture program.
A replication administrator user ID and users who have *APPLY authority can use the commands in this section to perform the following Apply for AS/400 tasks:
This section also describes two additional Apply program operations:
SQL and DPR packages can be created to use with remote systems. You can create these packages by issuing the following commands:
>>-CRTDPRPKG----+---------------------+------------------------->
| .-7--. |
'-DPRVSN--+-5--+---)--'
>-----+-----------------------+---+------------------------+---><
| .-*ALL---. | | .-*ALL-----. |
'-TYPE--+-*APPLY-+---)--' '-RDB--+-rdb-name-+---)--'
'-*ADMIN-'
Table 21. CRTDPRPKG command parameter definitions for AS/400
The packages are created using the ASN qualifier. They are created in the ASN library for DB2 UDB for AS/400 platforms. For other platforms, the authorization ID ASN is used.
After creating the DB2 DataPropagator for AS/400 packages, this command grants *PUBLIC authority to the packages to allow them to be used by users of DB2 DataPropagator for AS/400.
The system also produces a spool file that contains the SQL messages associated with each attempt to create a package.
Before you start the Apply program, ensure that:
Important: The primary key index is built for you when you define a subscription set. Do not delete it accidentally.
The Start DPR Apply (STRDPRAPY) command starts an instance of the Apply program on the local system. The Apply program continues running until you stop it or an unrecoverable error occurs.
>>-STRDPRAPY----+---------------------------+------------------->
| .-*CURRENT--. |
'-USER(--+-*JOBD-----+---)--'
'-user-name-'
>-----+---------------------------------------------------+----->
| .-*LIBL/QZSNDPR---------------------. |
'-JOBD(--+-library-name/job-description-name-+---)--'
'-*LIBL/job-description-name--------'
>-----+----------------------+---------------------------------->
| .-7--. |
'-DPRVSN(--+-5--+---)--'
>-----+------------------------------------+-------------------->
| .-*USER-----------. |
'-APYQUAL(--+-apply-qualifier-+---)--'
>-----+----------------------------+---------------------------->
| .-*LOCAL---. |
'-CTLSVR(--+-rdb-name-+---)--'
>-----+-------------------------+------------------------------->
| .-*NONE--. |
'-TRACE(--+-*ERROR-+---)--'
+-*ALL---+
'-*PRF---'
>-----+-------------------------------------------------+------->
| .-*NONE---------------------. |
'-FULLREFPGM(--+-library-name/program-name-+---)--'
>-----+------------------------------------------------+-------->
| .-*NONE---------------------. |
'-SUBNFYPGM(--+-library-name/program-name-+---)--'
>-----+---------------------------+----------------------------->
| .-*YES--. |
'-INACTMSG(--+-*NO---+---)--'
>-----+---------------------------+----------------------------->
| .-*YES--. |
'-ALWINACT(--+-*NO---+---)--'
>-----+-----------------------------+--------------------------->
| .-6----------. |
'-DELAY(--+-delay-time-+---)--'
>-----+------------------------------------+-------------------><
| .-300-------------. |
'-RTYWAIT(--+-retry-wait-time-+---)--'
Table 22. STRDPRAPY command parameter definitions for AS/400
| Parameter | Definition and prompts |
|---|---|
| USER | Specifies the name of the user ID for which the Apply program is
started. When you run this command, you must be authorized (have *USE
rights) to the specified user profile.
The Apply program runs under the specified user profile. The control tables (in ASN) are located on the relational database specified with the CTLSVR parameter. The same control tables are used regardless of the value specified on the USER parameter.
|
| JOBD | Specifies the name of the job description to use when submitting the
Apply program.
|
| DPRVSN | Specifies the version of the Apply program to start.
|
| APYQUAL | Specifies that an Apply qualifier be used by an Apply program
instance. All subscriptions that are grouped together with this Apply
qualifier will be run by this Apply program instance.
|
| CTLSVR | Specifies the control server where the common control tables are
located.
|
| TRACE | Specifies whether the Apply program should generate a trace. If
the Apply program generates a trace, the trace is output to a spool file
called QPZSNATRC.
|
| FULLREFPGM | Specifies whether the Apply program should invoke an exit routine to
initialize a target table. When the Apply program determines that a
target table needs to be full-refreshed, it invokes the specified exit routine
rather than doing the full refresh itself.
When a full-refresh exit routine is used by the Apply program, the value of the ASNLOAD column in the Apply trail table is Y. For examples and more information, see Refreshing target tables with the ASNLOAD exit routine for AS/400.
|
| SUBNFYPGM | Specifies whether the Apply program is to invoke an exit routine when it
finishes processing a subscription set. Input to the exit routine
consists of the set name, Apply qualifier, completion status, and statistics
including the number of rejects.
The notify program allows you to examine the UOW table to determine the transactions that have been rejected and then allows you to take further actions such as issuing a message or generating an event. For more information, see Using the ASNDONE exit routine for AS/400.
|
| INACTMSG | Specifies whether the Apply program should generate a message whenever it
completes its work and becomes inactive for a period of time.
|
| ALWINACT | Specifies whether the Apply program is able to run in an inactive state
(sleep).
|
| DELAY | Specifies the delay time (in seconds) at the end of each Apply program
cycle when continuous replication is used.
|
| RTYWAIT | Specifies in seconds how long the Apply program should wait after it
encounters an error before it retries the operation that failed.
|
You can set up the system to automatically start the subsystem by adding the command that is referred to in the QSTRUPPGM value on your system. If you will use the QDPR/QZSNDPR subsystem, it will be started as part of the STRDPRAPY command processing.
If the relational database (RDB) specified with the CTLSVR parameter is a DB2 UDB for AS/400 database, the tables on the server are found in the ASN library. If the RDB is not a DB2 UDB for AS/400 database, you can access the tables using ASN as the qualifier.
The STRDPRAPY command issues an error message if any of the following conditions occur:
An Apply instance must be started for each unique Apply qualifier in every subscription set table. You can start multiple Apply processes by specifying a different Apply qualifier each time that you issue the STRDPRAPY command. These Apply processes will run under the same user profile.
Each Apply process is identified using both the Apply qualifier and the control server names. When run, the job started for the Apply process does not have sufficient external attributes to correctly identify which Apply process is associated with a particular Apply qualifier and control server combination. Therefore, the job is identified in the following way:
In general, you can identify the correct Apply program job by looking at the list of jobs running in the QZSNDPR subsystem if both:
Use the ADDJOBSCDE command to start the Apply program at a specific time.
The End DPR Apply (ENDDPRAPY) command ends an instance of the Apply program on the local system.
You should end the Apply program before any planned system down time. You might also want to end the Apply program during periods of peak system activity.
>>-ENDDPRAPY----+---------------------------+------------------->
| .-*CURRENT--. |
'-USER(--+-user-name-+---)--'
>-----+----------------------------+---+----------------------+->
| .-*CNTRLD--. | | .-7--. |
'-OPTION(--+-*IMMED---+---)--' '-DPRVSN(--+-5--+---)--'
>-----+------------------------------------+-------------------->
| .-*USER-----------. |
'-APYQUAL(--+-apply-qualifier-+---)--'
>-----+----------------------------+---------------------------><
| .-*LOCAL---. |
'-CTLSVR(--+-rdb-name-+---)--'
Table 23. ENDDPRAPY command parameter definitions for AS/400
The ENDDPRAPY command uses the value of the APYQUAL and CTLSVR parameters to search the Apply job table for the job name, job number, and job user for the referenced Apply program, and ends that job.
ENDDPRAPY issues an error message if the following conditions occur:
This section provides information about performing two additional Apply program functions: using the ASNDONE exit routine and refreshing target tables with the ASNLOAD exit routine.
The ASNDONE exit routine is a program that the Apply program can optionally call after subscription processing completes, regardless of success or failure. A separate subscription notify program can be provided for each Apply qualifier. For general information about the ASNDONE exit routine, see Using the ASNDONE exit routine.
This section provides information about customizing the ASNDONE routine for an AS/400 environment.
When creating your subscription notify program, consider these activation group concerns:
If the program is created to run with a new activation group: the Apply program and the subscription notify program will not share SQL resources, such as RDB connections and open cursors. The activation handling code in the AS/400 operating system frees any resources allocated by the subscription notify program before control is returned to the Apply program. Additional resource is used every time that the Apply program calls the subscription notify program.
If the program is created to run in the caller's activation group: it shares SQL resources with the Apply program. Design the program so that you minimize its impact on the Apply program. For example, the program might cause unexpected Apply program processing if it changes the current relational database (RDB) connection.
If the program is created to run in a named activation group: it does not share resources with the Apply program. Using a named activation group will avoid the activation group overhead every time the subscription notify program is called. Run time data structures and SQL resources can be shared between invocations. Application cleanup processing is not performed until the Apply program is ended, so the subscription notify program must be designed to ensure that it does not cause lock contention with the Apply program by leaving source tables, target tables, or control tables locked when control is returned to the Apply program.
When you start the Apply program, specify the name of the subscription notify program using the parameter SUBNFYPGM on the STRDPRAPY command. For example, if the program is named ASNDONE_1 and resides in library APPLIB, use the command:
SUBNFYPGM(APPLIB/ASNDONE_1).
The ASNLOAD full-refresh exit routine is called by the Apply program:
When a full refresh of a subscription set is necessary, the Apply program calls the exit routine. The program then performs a full refresh of the target table (if necessary), or of each target table listed in the subscription set.
You can use an exit routine instead of the Apply program to perform a full refresh more efficiently. For example, if you are copying every row and every column from a source table to a target table, you can design a full-refresh exit routine that uses a Distributed Data Management (DDM) file and the Copy File (CPYF) CL command to copy the entire file from the source table to the target table.
If the exit routine returns a non-zero return code, the current subscription set being processed by the Apply program fails. Processing of the remainder of the subscription set is discontinued until the next iteration.
The source for sample exit routines is included with DB2 DataPropagator for
AS/400. The samples for the C, COBOL, and RPG languages are:
| Compiler language | Library name | Source file name | Member name |
|---|---|---|---|
| C | QDPR | QCSRC | ASNLOAD |
| COBOL | QDPR | QCBLLESRC | ASNLOAD |
| RPG | QDPR | QRPGLESRC | ASNLOAD |
You cannot direct the Apply program to use another program unless you end the Apply program and start it again with another STRDPRAPY command.
If you plan to use ASNLOAD, be aware of the following items:
The exit routine should either explicitly commit changes, or it should be compiled to implicitly commit changes when it completes. Any uncommitted changes when the exit routine completes are not committed until either:
| Offset (Hex or Decimal) | Type | Field |
|---|---|---|
| 0 | BINARY(4) |
SQL statement length |
| 4 | Char(*) |
SQL select statement |
When the Apply program generates a trace, it prints to a spool file. If the exit routine is running in a separate activation group, the results print to a separate spool file. If the exit routine runs in the caller's activation group, the results print to the same spool file as the Apply trace.
The values for trace indicator are: