Add DPR Registration (ADDDPRREG)
| Where allowed to run: All
environments (*ALL) Threadsafe: No |
Parameters Examples Error messages |
The Add DPR Registration command (ADDDPRREG) allows the user to register a source table for use with DataPropagator. The source table can be located on this system or on a remote system. If it is on a remote system, you must set up the registration to use remote journaling. You do this by using the SRCTBLRDB parameter to identify the remote relational database where the source table resides.
- Note: Do not precede an entry with an asterisk unless that entry is shown on the display itself or in the help information with an asterisk.
Error Messages for ADDDPRREG
*ESCAPE Messages
- ASN2051
- Not authorized to Registration Extension table.
- ASN2501
- Not authorized to register table.
- ASN3020
- Registration for source table completed.
- ASN3027
- Table not journaled with *BOTH images.
- ASN3036
- Column not found.
- ASN3055
- Cannot capture changes for table .
- ASN3069
- Relative record numbers cannot be captured.
- ASN3075
- Relative record numbers cannot be captured for an aggregate source table.
- ASN3115
- Relational database is not i5/OS V4R2 or greater.
- ASN3116
- Remote journal in library is not found.
- ASN3121
- The source table journal does not match the data from the RMTJRN parameter.
- ASN3122
- The SRCTBLRDB parameter cannot specify a remote souce table that is a logical file.
- ASN3125
- Local relational database is not i5/OS V4R2 or greater.
- ASN3213
- Base attributes not valid for base type.
- ASN3217
- CAPRRN(*YES) not valid with aggregate base type. A value of *YES on the CAPRRN parameter is not valid
- ASN3219
- You must specify *NO on the CAPRRN parameter when the CAPCOL parameter has a value of *NONE.
- ASN3230
- A Remote Journal Name cannot be specified if the Source Table RDB is *LOCAL.
- ASN3238
- The source table does not contain the correct columns for a source type.
- ASN3249
- Relative record number column IBMQSQ_RRN is not available.
- ASN3254
- Registration already exists for table .
- ASN3279
- View table has more columns than subselect string.
- ASN3280
- View subselect string missing correlation name.
- ASN3281
- View subselect string missing file name.
- ASN3284
- SQLFILE must not be *NONE when FROMGUI is *YES.
- ASN3285
- MSGFILE must not be *NONE when FROMGUI is *YES.
- ASN3286
- SQLFILE and MSGFILE names are the same.
- ASN3292
- Register table not found.
| Top |
Parameters
| Keyword | Description | Choices | Notes |
|---|---|---|---|
| SRCTBL | Source table | Qualified object name | Required, Positional 1 |
| Qualifier 1: Source table | Name | ||
| Qualifier 2: Library | Name | ||
| CAPCTLLIB | Capture control library | Name, ASN | Optional |
| CDLIB | Library for CD table | Name, *SRCTBL | Optional |
| CDNAME | Name of CD table | Name, *DEFAULT | Optional |
| SRCTYPE | Source table type | *USERTABLE, *POINTINTIME, *CCD, *BASEAGR, *CHANGEAGR, *REPLICA, *USERCOPY | Optional |
| REFRESH | Allow full refresh | *YES, *NO | Optional |
| TEXT | Text 'description' | Character value, *NONE | Optional |
| CAPCOL | Capture columns | Single values: *ALL, *NONE Other values (up to 300 repetitions): Character value |
Optional |
| CAPRRN | Capture relative record number | *YES, *NO | Optional |
| IMAGE | Record images | *AFTER, *BOTH | Optional |
| PREFIX | Prefix character | Character value, *DEFAULT, *NULL | Optional |
| CONDENSED | Condensed | *YES, *NO, *AGGREGATE | Optional |
| COMPLETE | Complete | *YES, *NO | Optional |
| FROMGUI | Command from GUI | *NO, *YES | Optional |
| SQLFILE | SQL output file | Character value, *NONE | Optional |
| MSGFILE | Message output file | Character value, *NONE | Optional |
| SRCTBLRDB | Source table RDB | Name, *LOCAL | Optional |
| RMTJRN | Remote journal | Qualified object name | Optional |
| Qualifier 1: Remote journal | Name, *SRCTBL | ||
| Qualifier 2: Library | Name | ||
| CONFLICT | Conflict level | *NONE, *STANDARD, *ENHANCED | Optional |
| UPDDELINS | Update using delete/insert | *NO, *YES | Optional |
| GENCDROW | Generate change data rows | *ALLCHG, *REGCOLCHG | Optional |
| RECAP | Recapture changes | *NO, *YES | Optional |
| STOPONERR | Stop on error | *NO, *YES | Optional |
| Top |
Source table (SRCTBL)
Identifies the table to register as a source table. This is a required parameter. This table can be any externally defined single format physical file or view in an i5/OS library or collection.
When SRCTYPE(*POINTINTIME) is specified, the table must have a unique key, and the data in the key field cannot be changed.
To capture changes, the table must also have journaling started with both images; that is, you must run the Start Journal Physical File (STRJRNPF) command with IMAGES(*BOTH) specified.
- library-name/table-name
- The qualified name of the table to be registered. If you are setting this source table up to be journaled using remote journaling, this is the source table's name on the remote system.
| Top |
Capture control library (CAPCTLLIB)
Specifies the library where the Capture control tables are located.
- ASN
- Specifies the default library of ASN containing the Capture control tables.
- library-name
- Name of the library containing the Capture control tables. This library should exist and have been created using the CRTDPRTBL command with the library specified in the CAPCTLLIB parameter.
| Top |
Library for CD table (CDLIB)
Specifies the name of the library in which the Change Data (CD) table is to be created.
- *SRCTBL
- Create the Change Data table in the library where the source table resides.
- library-name
- Create the Change Data table in the specified library. An error will be generated if the library does not exist.
| Top |
Name of CD table (CDNAME)
Specifies the name of the Change Data (CD) table. This table must not already exist. It will be created in the library specified on the CDLIB parameter.
- *DEFAULT
- Create the Change Data table using the default naming convention. The default name includes the date and time stamp to make it unique.
- cd-name
- Specifies the name of the Change Data table.
| Top |
Source table type (SRCTYPE)
Specifies the type of source table being registered. The point-in-time, base aggregate, change aggregate, replica, and CCD types are DataPropagator target tables that are optionally registered for the user when a subscription is created. When registering to a target table that already exists, the registration fails if the target table does not contain the IBMSNAP columns indicated for the specified source type.
- *USERTABLE
- The table is a user database table. This is the most common
type of table that you register. The table can not contain any
columns that start with the DataPropagator column identifier,
IBMSNAP.
Your source table must be this type if you are using remote journaling.
- *POINTINTIME
- The table is a point-in-time copy, whose content matches all or part of a source table, with an added system column that identifies the time when the particular row was inserted or updated at the source system. The table must contain the IBMSNAP_LOGMARKER TIMESTAMP column and can optionally have an INTEGER column called IBMQSQ_RRN.
- *BASEAGR
- The table is a base aggregate copy, which contains data aggregated from a user table or point-in-time table at intervals. The table must contain the IBMSNAP_HLOGMARKER and IBMSNAP_LLOGMARKER TIMESTAMP columns.
- *CHANGEAGR
- The table is a change aggregate copy, which contains data aggregations based on changes recorded for a source table. The table must contain the IBMSNAP_HLOGMARKER and IBMSNAP_LLOGMARKER TIMESTAMP columns.
- *CCD
- The table is a consistent copy, which contains transaction
consistent data from the source table. The table must contain the
following columns:
- IBMSNAP_LOGMARKER CHAR(10) FOR BIT DATA NOT NULL
- IBMSNAP_OPERATION CHAR(1) NOT NULL
- IBMSNAP_COMMITSEQ CHAR(10) FOR BIT DATA NOT NULL
- IBMSNAP_LOGMARKER TIMESTAMP NOT NULL
The table can optionally have an INTEGER column called IBMQSQ_RRN.
- *REPLICA
- The table is a target table for a replica subscription and is being registered so changes from the target table can be fed back to the original source table. The table also contains all of the columns from the original source table. The table can not contain any columns that start with the DataPropagator column identifier, IBMSNAP or IBMQSQ.
| Top |
Allow full refresh (REFRESH)
Determines whether the registration supports the full-refresh capability.
You can use this value to turn off the capability of the Apply process to perform a full refresh from the source table.
- *YES
- The source table can be used to fully refresh a target table.
- *NO
- The source table cannot be used to fully refresh a target table.
| Top |
Text 'description' (TEXT)
The textual description associated with the registration entry.
- *NONE
- No description is associated with the entry.
- description
- Specify up to 50 characters of text, enclosed in apostrophes.
| Top |
Capture columns (CAPCOL)
Specifies the columns for which changes are captured for this registration.
- *ALL
- Changes are captured for all columns.
- *NONE
- Changes are not captured for this table. Use this value to specify that you do not want change capture activities performed on the registration, and want the table registered for full refresh only. The Change Data (CD) table is not created with this registration, and the Capture process will not capture changes for the table.
- column-name
- Specifies up to 300 columns for which changes are captured.
| Top |
Capture relative record number (CAPRRN)
Specifies whether the relative record number of each changed record is captured.
- *NO
- The relative record number is not captured.
- *YES
- The relative record number is captured. An additional column
called IBMQSQ_RRN is created in the Change Data (CD) table.
If SRCTYPE(*USERTABLE) is specified, the IBMQSQ_RRN column must not exist in the source table. During capture activity, the IBMQSQ_RRN column is updated with the relative record number of each changed source table record.
If SRCTYPE(*USERTABLE) is not specified, the IBMQSQ_RRN column must be present in the source table. During capture activity, the IBMQSQ_RRN column in the CD table is updated using the value from the IBMQSQ_RRN column in the source table.
If SRCTYPE(*REPLICA) is specified, the CAPRRN parameter needs to be set to *NO.
| Top |
Record images (IMAGE)
Specifies whether the Change Data table contains both before and after images of the changes to the source table. This applies globally to all columns specified on the Capture columns (CAPCOL) parameter. The source table must be journaled with *BOTH images even if you specify *AFTER on this parameter.
This value is not valid when CAPCOL(*NONE) is specified.
- *AFTER
- Only after-images of the source table are kept in the Change Data table.
- *BOTH
- Both before- and after-images of the source table are kept in the Change Data table.
| Top |
Prefix character (PREFIX)
Use the prefix character to make the column names in the Change Data table unique when capturing before-images of the source table. This name can be used in the COLUMN parameter of the ADDDPRSUB or ADDDPRSUBM commands.
- *DEFAULT
- No prefix is specified. The default prefix (@) is used.
- *NULL
- When a null prefix is specified, the before-images from the source table will never be captured.
- character
- Any alphabetic character that is valid in an object name.
If the prefix character causes a before-image column name to be the same as an existing after-image column name, the command will terminate with an error.
| Top |
Condensed (CONDENSED)
Specifies whether or not the source table is condensed. This means that the table contains current data and there is no more than one row for each primary key value in the table.
- *YES
- The source table is condensed and contains only one row for each primary key value.
- *NO
- The source table is not condensed and contains multiple rows for each primary key value.
- *AGGREGATE
- The source table type is either *BASEAGR (base aggregate) or *CHANGEAGR (change aggregate). If this value is used, the COMPLETE parameter must be set to *NO.
| Top |
Complete (COMPLETE)
Specifies whether or not the source table is complete. This means that the table contains a row for every primary key value.
- *YES
- The source table is complete and contains a row for each primary key value.
- *NO
- The source table is not complete and does not contain a row for each primary key value.
| Top |
| Top |
| Top |
| Top |
Source table RDB (SRCTBLRDB)
Specifies the name of the relational database in which the source table resides. By specifying a remote relational database name, you are setting up a registration to a source table that uses remote journaling.
You can use remote journaling in conjunction with the Capture process to limit the system usage on the system where the source table resides. You do this by registering the source table (and running the ADDDPRREG command) on the system to which the source table is remote journaled.
In other words, if the source table is on the system from which you run this command, and the changes are journaled to a table on this system as well, you are not using remote journaling. If the source table is on a system other than the system from which you run this command, and the changes are journaled to this local system, you are using remote journaling.
- *LOCAL
- The source table is located on this system.
- relational-database-name
- Specifies the name of the relational database (RDB) where the
source table is located. The specified RDB must be for a system
running i5/OS Version 4 Release 2 or later.
If you are using remote journaling for this source table, specify the name of the RDB on the system where the source table is located.
If you are not using remote journaling for this registration, specify the name of the local RDB, or leave it at the default value of *LOCAL.
| Top |
Remote journal (RMTJRN)
If the source table is remote (that is, the source table is set up to be remotely journaled to this system), specify the name of the journal on this system that the changes to the remote source table are directed to.
If you are not using remote journaling, you must specify *SRCTBL.
- *SRCTBL
- The name of the journal is the same as the local journal that is associated with the source table. You must use this value if the source table is local (that is, the value of the SRCTBLRDB parameter is *LOCAL).
- library-name/journal-name
- Specifies the qualified name of the journal on this system that is used for journaling the remote source table. The library name does not need to be the same as the library where the source table journal resides. However, the journal name must be either *SRCTBL or the same as the source table's journal name.
| Top |
Conflict level (CONFLICT)
Specifies the level of conflict detection that is done during a update anywhere subscription.
- *NONE
- No conflict detection is done. This is the value needed for all tables that will not be using update anywhere.
- *STANDARD
- Moderate conflict detection in which the Apply program searches rows already captured in the replicas change data tables for conflicts.
- *ENHANCED
- Conflict detection that provides the best data integrity among all replicas and source tables
| Top |
Update using delete/insert (UPDDELINS)
Determines how updated data is handled. Data that is inserted into or deleted from the source table is not affected by this parameter. Specify UPDDELINS(*YES) when the key fields for the target table are being changed by the application.
- *NO
- Updates made to the source table are staged to the Change Data table. The changes are applied as updates
- *YES
- Updates made to the source table are captured as delete and insert pairs. Apply then deletes the existing row and inserts a new row.
| Top |
Generate change data rows (GENCDROW)
Specifies what changes from the journals should be copied into the Change Data table.
- *ALLCHG
- All insert, update, and delete records from the source table's journal are copied into the Change Data table.
- *REGCOLCHG
- Copy journal entries where registered columns have changed. If the changes are to columns that are not in the Change Data table, these changes will not be captured.
| Top |
Recapture changes (RECAP)
Specifies whether or not changes made to a table by Apply are re-captured by Capture.
- *YES
- Changes made to the source table by Apply are Captured and appear in the Change Data table.
- *NO
- Changes made to the source table by Apply are not Captured and do not appear in the Change Data table.
| Top |
Stop on error (STOPONERR)
Specifies whether or not Capture will stop when an error is encountered
- *NO
- Capture will continue processing after issuing error messages and deactivating the registration which caused the error.
- *YES
- Capture will issue an error message and completely stop processing when an error is encountered.
| Top |
Examples
None| Top |
Error messages
*ESCAPE Messages
- ASN2051
- Not authorized to Registration Extension table.
- ASN2501
- Not authorized to register table.
- ASN3020
- Registration for source table completed.
- ASN3027
- Table not journaled with *BOTH images.
- ASN3036
- Column not found.
- ASN3055
- Cannot capture changes for table .
- ASN3069
- Relative record numbers cannot be captured.
- ASN3075
- Relative record numbers cannot be captured for an aggregate source table.
- ASN3115
- Relational database is not i5/OS V4R2 or greater.
- ASN3116
- Remote journal in library is not found.
- ASN3121
- The source table journal does not match the data from the RMTJRN parameter.
- ASN3122
- The SRCTBLRDB parameter cannot specify a remote souce table that is a logical file.
- ASN3125
- Local relational database is not i5/OS V4R2 or greater.
- ASN3213
- Base attributes not valid for base type.
- ASN3217
- CAPRRN(*YES) not valid with aggregate base type. A value of *YES on the CAPRRN parameter is not valid
- ASN3219
- You must specify *NO on the CAPRRN parameter when the CAPCOL parameter has a value of *NONE.
- ASN3230
- A Remote Journal Name cannot be specified if the Source Table RDB is *LOCAL.
- ASN3238
- The source table does not contain the correct columns for a source type.
- ASN3249
- Relative record number column IBMQSQ_RRN is not available.
- ASN3254
- Registration already exists for table .
- ASN3279
- View table has more columns than subselect string.
- ASN3280
- View subselect string missing correlation name.
- ASN3281
- View subselect string missing file name.
- ASN3284
- SQLFILE must not be *NONE when FROMGUI is *YES.
- ASN3285
- MSGFILE must not be *NONE when FROMGUI is *YES.
- ASN3286
- SQLFILE and MSGFILE names are the same.
- ASN3292
- Register table not found.
| Top |