Replication Guide and Reference

Operating Apply for AS/400

A replication administrator user ID and users granted *APPLY authority can use the commands in this section to perform the following Apply for AS/400 tasks:

Creating DPROPR/400 packages to use with remote systems
Starting
Scheduling
Stopping

This section also describes two additional Apply program operations:

Creating DPROPR/400 packages to use with remote systems

You can use the Create DPR Packages (CRTDPRPKG) command to create the packages necessary to use the DPROPR/400 product with remote systems.

>>-CRTDPRPKG----+---------------------+------------------------->
                |         .-1--.      |
                '-DPRVSN--+-5--+---)--'
 
>-----+-----------------------+---+------------------------+---><
      |       .-*ALL---.      |   |      .-*ALL-----.      |
      '-TYPE--+-*APPLY-+---)--'   '-RDB--+-rdb-name-+---)--'
              '-*ADMIN-'

Table 24. CRTDPRPKG Command Parameter Definitions for AS/400

Parameter Definition and Prompts
DPRVSN Specifies the version of the DPROPR/400 package to use. You can specify one or both of the version levels:

1 (default)
Specifies packages for Version 1 of DPROPR/400.
5
Specifies packages for Version 5 of DPROPR/400.

TYPE Specifies which DPROPR/400 packages are created.

*ALL (default)
Specifies to create packages for all the DPROPR/400 programs that do remote SQL.
*APPLY
Specifies to create the packages for the programs used by the Apply program.
*ADMIN
Specifies to create the packages for the programs used by the CL commands.

RDB Specifies the relational database where the packages are created. If the RDB is on an AS/400 system and the ASN library does not exist on the remote system, the packages are not created. If the RDB is not on an AS/400 system and ASN is not defined as an authorization ID on that RDB, the packages are not created.

*ALL (default)
Specifies to create an SQL package on every RDB that is used as a source server or a target server by DPROPR/400.
rdb-name
Represents the name of the relational database. You can use the Work with RDB Directory Entries (WRKRDBDIRE) command to find this name.
When prompting on the CRTDPRPKG command, you can press the F4 key to choose from the list of databases in the RDB directory.

Parameter	Definition and Prompts
DPRVSN	Specifies the version of the DPROPR/400 package to use. You can specify one or both of the version levels: 1 (default) Specifies packages for Version 1 of DPROPR/400. 5 Specifies packages for Version 5 of DPROPR/400.
TYPE	Specifies which DPROPR/400 packages are created. ALL (default) Specifies to create packages for all the DPROPR/400 programs that do remote SQL. APPLY Specifies to create the packages for the programs used by the Apply program. *ADMIN Specifies to create the packages for the programs used by the CL commands.
RDB	Specifies the relational database where the packages are created. If the RDB is on an AS/400 system and the ASN library does not exist on the remote system, the packages are not created. If the RDB is not on an AS/400 system and ASN is not defined as an authorization ID on that RDB, the packages are not created. ALL (default) Specifies to create an SQL package on every RDB that is used as a source server or a target server by DPROPR/400. `rdb-name` Represents the name of the relational database. You can use the Work with RDB Directory Entries (WRKRDBDIRE) command to find this name. When prompting on the CRTDPRPKG* command, you can press the F4 key to choose from the list of databases in the RDB directory.

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 DPROPR/400 packages, this command grants *PUBLIC authority to the packages to allow them to be used by DPROPR/400 users.

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

Before you start the Apply program, ensure that:

The control tables have been created. If the tables do not exist, you can use the CRTDPRTBL command to create them. For information on the CRTDPRTBL command, see Creating the replication control tables.
You have the proper authorization to run the Apply program. See Granting authority for more information.
At least one subscription set is created and activated.
All target tables have a primary key index. Differential refresh performance is significantly affected if the primary key index is removed for a subscription.
Important: The primary key index is built for you when you define a subscription set. Do not delete it accidentally.
The Apply program package is created.
The Capture program is started on the source server before you start the Apply program for the first time. The Capture program updates the SYNCHTIME and SYNCHPOINT columns of the GLOBAL record in the register table before the Apply program is started. The Apply program assumes that if a GLOBAL record is present in the register table, the SYNCHTIME and SYNCHPOINT columns are not null.

Starting Apply for AS/400

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--------'
 
>-----+----------------------+---------------------------------->
      |          .-1--.      |
      '-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---+---)--'

Table 25. 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.

*CURRENT (default)
Specifies that the user ID associated with the current job is the user ID associated with this instance of the Apply program.
*JOBD
Represents the user ID specified in the job description associated with this instance of the Apply program. The job description cannot specify USER(*RQD).
user-name (default)
Specifies the user ID associated with this instance of the Apply program. The following IBM-supplied objects are not valid on this parameter: QDBSHR, QDFTOWN, QDOC, QLPAUTO, QLPINSTALL, QRJE, QSECOFR, QSPL, QSYS, or QTSTRQS.
When prompting on the STRDPRAPY command, you can press the F4 key to see a list of users who defined subscriptions.

JOBD Specifies the name of the job description to use when submitting the Apply program.

*LIBL/QZSNDPR (default)
Specifies the default job description provided with DPROPR/400.
library-name/job-description-name
Represents the name of the job description used for the Apply program.

DPRVSN Specifies the version of the Apply program to start. You can specify one or both of the version levels.

1 (default)
Start version 1 of the Apply program.
5
Start version 5 of the Apply program.

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.

*USER (default)
Specifies the user name on the USER parameter as the Apply qualifier.
apply_qualifier
Specifies the name used to group the subscriptions that are to be run by this Apply program instance. You can specify a maximum of 18 characters for the Apply qualifier name. This name follows the same naming conventions as an RDB name. The subscriptions to be run are identified by the records in the subscription set table with this value in the APPLY_QUAL column.
When prompting on the STRDPRAPY command, you can press the F4 key to see a list of Apply qualifier names with existing subscriptions.

CTLSVR Specifies the control server where the common control tables are located.

*LOCAL (default)
Specifies that the subscription control tables are located on the local relational database.
rdb-name
Represents the name of the relational database where the control tables are located. You can use the Work with RDB Directory Entries (WRKRDBDIRE) command to find this name.
When prompting on the STRDPRAPY command, you can press the F4 key to see a list of available RDB names.

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.

*NONE (default)
Specifies that no trace is generated.
*ERROR
Specifies that the trace should contain information for errors only.
*ALL
Specifies that the trace should contain information for errors, execution flow, and SQL statements issued by the Apply program.
*PRF
Specifies that the trace should contain information that can be used to analyze performance at different stages of the Apply program execution.

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.

*NONE (default)
Specifies that a full-refresh exit routine is not used.
library-name/program-name
Represents the qualified name of the program that is called when the Apply program determines that it is necessary to do a full refresh of a target table. For example, to call program ASNLOAD in library DATAPROP, the qualified name is DATAPROP/ASNLOAD.

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.

*NONE (default)
Specifies that an exit routine is not used.
library-name/program-name
Represents the qualified name of the program to be called when the Apply program completes processing a subscription set. For example, to call program APPLYDONE in library DATAPROP, the qualified name is DATAPROP/APPLYDONE.

INACTMSG Specifies whether the Apply program should generate a message whenever it completes its work and becomes inactive for a period of time.

*NO (default)
Specifies that no message is generated.
*YES
Specifies that the Apply program generate message ASN1044 before beginning a period of inactivity. Message ASN1044 indicates how long the Apply program will be inactive.

ALWINACT Specifies whether the Apply program is able to run in an inactive state (sleep).

*YES (default)
Specifies that the Apply program should sleep if there is nothing to process.
*NO
Specifies that if the Apply program has nothing to process, the job started for the Apply program should end.

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. CURRENT (default) Specifies that the user ID associated with the current job is the user ID associated with this instance of the Apply program. JOBD Represents the user ID specified in the job description associated with this instance of the Apply program. The job description cannot specify USER(RQD). `user-name` (default) Specifies the user ID associated with this instance of the Apply program. The following IBM-supplied objects are not valid on this parameter: QDBSHR, QDFTOWN, QDOC, QLPAUTO, QLPINSTALL, QRJE, QSECOFR, QSPL, QSYS, or QTSTRQS. When prompting on the STRDPRAPY command, you can press the F4 key to see a list of users who defined subscriptions.
JOBD	Specifies the name of the job description to use when submitting the Apply program. *LIBL/QZSNDPR (default) Specifies the default job description provided with DPROPR/400. `library-name/job-description-name` Represents the name of the job description used for the Apply program.
DPRVSN	Specifies the version of the Apply program to start. You can specify one or both of the version levels. 1 (default) Start version 1 of the Apply program. 5 Start version 5 of the Apply program.
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. USER (default) Specifies the user name on the USER parameter as the Apply qualifier. apply_qualifier* Specifies the name used to group the subscriptions that are to be run by this Apply program instance. You can specify a maximum of 18 characters for the Apply qualifier name. This name follows the same naming conventions as an RDB name. The subscriptions to be run are identified by the records in the subscription set table with this value in the APPLY_QUAL column. When prompting on the STRDPRAPY command, you can press the F4 key to see a list of Apply qualifier names with existing subscriptions.
CTLSVR	Specifies the control server where the common control tables are located. LOCAL (default) Specifies that the subscription control tables are located on the local relational database. `rdb-name` Represents the name of the relational database where the control tables are located. You can use the Work with RDB Directory Entries (WRKRDBDIRE) command to find this name. When prompting on the STRDPRAPY* command, you can press the F4 key to see a list of available RDB names.
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. NONE (default) Specifies that no trace is generated. ERROR Specifies that the trace should contain information for errors only. ALL Specifies that the trace should contain information for errors, execution flow, and SQL statements issued by the Apply program. PRF Specifies that the trace should contain information that can be used to analyze performance at different stages of the Apply program execution.
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. *NONE (default) Specifies that a full-refresh exit routine is not used. `library-name/program-name` Represents the qualified name of the program that is called when the Apply program determines that it is necessary to do a full refresh of a target table. For example, to call program ASNLOAD in library DATAPROP, the qualified name is DATAPROP/ASNLOAD.
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. *NONE (default) Specifies that an exit routine is not used. `library-name/program-name` Represents the qualified name of the program to be called when the Apply program completes processing a subscription set. For example, to call program APPLYDONE in library DATAPROP, the qualified name is DATAPROP/APPLYDONE.
INACTMSG	Specifies whether the Apply program should generate a message whenever it completes its work and becomes inactive for a period of time. NO (default) Specifies that no message is generated. YES Specifies that the Apply program generate message ASN1044 before beginning a period of inactivity. Message ASN1044 indicates how long the Apply program will be inactive.
ALWINACT	Specifies whether the Apply program is able to run in an inactive state (sleep). YES (default) Specifies that the Apply program should sleep if there is nothing to process. NO Specifies that if the Apply program has nothing to process, the job started for the Apply program should end.

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.

Understanding subscription control table information

You can use the CTLSVR parameter to identify the control server where the subscription control tables for this Apply qualifier are found. The following control tables can be found on the control server:

The subscription set and subscription-targets-member tables contain the information that the Apply program uses to determine which tables are copied, when they are copied, and the types of copies to make for each table.
The subscription columns table contains the information about the columns that are to be copied to the copy table.
The subscription statements table contains information about the SQL statements that are to be run before or after each subscription is processed.
The subscription events table contains information about the events that might trigger a subscription to be processed.
The Apply trail table contains information about the actions that were taken each time a subscription was processed.

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.

Error conditions when starting the Apply program

The STRDPRAPY command issues an error message if any of the following conditions occur:

If the user does not exist.
If the user running the command is not authorized to the user profile specified on the command or the job description.
If an instance of the Apply program is already active on the local system for this combination of Apply qualifier and control server.
If the RDB name specified with the CTLSVR parameter is not in the relational database directory.
If the control tables do not exist on the RDB specified with the CTLSVR parameter.
If there are no subscriptions defined for the Apply qualifier specified with the APYQUAL parameter.

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.

Identifying Apply program jobs

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:

The job is started under the user profile associated with the USER parameter.
The first 10 characters of the Apply qualifier are truncated and become the job name.
DPROPR/400 maintains an Apply job control table named IBMSNAP_APPLY_JOB in the ASN library on the local system. The table maps the Apply qualifier/control server values to the correct Apply program job.
You can view the job log. The Apply qualifier and control server names are used in the call to the Apply program.

In general, you can identify the correct Apply program job by looking at the list of jobs running in the QZSNDPR subsystem if both:

The first 10 characters of the Apply qualifier name are unique.
The Apply program is started for the local control server only.

Scheduling Apply for AS/400

Use the ADDJOBSCDE command to start the Apply program at a specific time.

Stopping Apply for AS/400

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--.      |   |          .-1--.      |
      '-OPTION(--+-*IMMED---+---)--'   '-DPRVSN(--+-5--+---)--'
 
>-----+------------------------------------+-------------------->
      |           .-*USER-----------.      |
      '-APYQUAL(--+-apply-qualifier-+---)--'
 
>-----+----------------------------+---------------------------><
      |          .-*LOCAL---.      |
      '-CTLSVR(--+-rdb-name-+---)--'

Table 26. ENDDPRAPY Command Parameter Definitions for AS/400

Parameter Definition and Prompts
USER This parameter is ignored unless the APYQUAL parameter has a value of *USER, in which case this is the Apply qualifier associated with the instance of Apply.

*CURRENT (default)
Specifies the Apply process of the user associated with the current job.
user-name
Specifies the Apply process of the specified user.
When prompting on the ENDDPRAPY command you can press the F4 key to see a list of users who defined subscriptions.

OPTION Specifies how to end the Apply process.

*CNTRLD (default)
Specifies that the Apply process complete all of its tasks before ending. These tasks might take a considerable period of time if the Apply program is completing a subscription.
*IMMED
Specifies that the Apply program complete all of its tasks with the ENDJOB OPTION(*IMMED) command. The tasks end immediately, without any cleanup. Use this option only after a controlled end is unsuccessful, because it can cause undesirable results. (Unless the Apply program was asleep when you issued the ENDDPRAPY command, you should verify the target table contents.)
If the Apply program was performing a full refresh to the target table, the target table might be empty as a result of ending the Apply program before the table was refreshed with the source table contents. If the target table is empty, you must force a full refresh for this replication target.
You might find that a subscription is considered IN USE (the STATUS column in the subscription set table has a value of 1). If it is, reset the value to 0 or -1. This allows the subscription to be run again by the Apply program.

DPRVSN Specifies the version of the Apply program to end. You can specify one or both of the version levels.

1 (default)
Specifies version 1 of the Apply program.
5
Specifies version 5 of the Apply program.

APYQUAL Specifies the Apply qualifier used by an instance of the Apply program. All subscriptions that are grouped together with this Apply qualifier are run by the instance.

*USER (default)
Specifies that the user name specified on the USER parameter is the Apply qualifier.
apply_qualifier
Specifies the name used to group the subscriptions that this Apply instance runs. You can specify a maximum of 18 characters for the Apply qualifier name. This name follows the same naming conventions as an RDB name. You identify the subscriptions being run by the records in the subscription set table with this value in the APPLY_QUAL column.
When prompting on the ENDDPRAPY command, you can press the F4 key to see a list of Apply qualifier names with existing subscriptions.

CTLSVR Specifies the name of the relational database where the Version 5 control tables are located.

*LOCAL (default)
Specifies that the control tables are located on the local relational database.
rdb-name
Specifies that the subscription control tables are located on this relational database. You can use the Work with RDB Directory Entries (WRKRDBDIRE) command to find this name.
When prompting on the ENDDPRAPY command, you can press the F4 key to choose from the list of databases in the RDB directory.

Parameter	Definition and Prompts
USER	This parameter is ignored unless the APYQUAL parameter has a value of USER, in which case this is the Apply qualifier associated with the instance of Apply. CURRENT (default) Specifies the Apply process of the user associated with the current job. `user-name` Specifies the Apply process of the specified user. When prompting on the ENDDPRAPY command you can press the F4 key to see a list of users who defined subscriptions.
OPTION	Specifies how to end the Apply process. CNTRLD (default) Specifies that the Apply process complete all of its tasks before ending. These tasks might take a considerable period of time if the Apply program is completing a subscription. IMMED Specifies that the Apply program complete all of its tasks with the *ENDJOB OPTION(IMMED) command. The tasks end immediately, without any cleanup. Use this option only after a controlled end is unsuccessful, because it can cause undesirable results. (Unless the Apply program was asleep when you issued the ENDDPRAPY** command, you should verify the target table contents.) If the Apply program was performing a full refresh to the target table, the target table might be empty as a result of ending the Apply program before the table was refreshed with the source table contents. If the target table is empty, you must force a full refresh for this replication target. You might find that a subscription is considered IN USE (the STATUS column in the subscription set table has a value of 1). If it is, reset the value to 0 or -1. This allows the subscription to be run again by the Apply program.
DPRVSN	Specifies the version of the Apply program to end. You can specify one or both of the version levels. 1 (default) Specifies version 1 of the Apply program. 5 Specifies version 5 of the Apply program.
APYQUAL	Specifies the Apply qualifier used by an instance of the Apply program. All subscriptions that are grouped together with this Apply qualifier are run by the instance. USER (default) Specifies that the user name specified on the USER parameter is the Apply qualifier. `apply_qualifier` Specifies the name used to group the subscriptions that this Apply instance runs. You can specify a maximum of 18 characters for the Apply qualifier name. This name follows the same naming conventions as an RDB name. You identify the subscriptions being run by the records in the subscription set table with this value in the APPLY_QUAL column. When prompting on the ENDDPRAPY* command, you can press the F4 key to see a list of Apply qualifier names with existing subscriptions.
CTLSVR	Specifies the name of the relational database where the Version 5 control tables are located. LOCAL (default) Specifies that the control tables are located on the local relational database. `rdb-name` Specifies that the subscription control tables are located on this relational database. You can use the Work with RDB Directory Entries (WRKRDBDIRE) command to find this name. When prompting on the ENDDPRAPY* command, you can press the F4 key to choose from the list of databases in the RDB directory.

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:

If the Apply job table does not exist or is corrupted.
If there is no record in the Apply job table for the Apply qualifier and control server name.
If the Apply job already ended.
If the user ID running the command is not authorized to end the Apply job.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]