ASNCLP - SQL Replication - CREATE REGISTRATION command

Use the CREATE REGISTRATION command to register one or more source tables, views, or nicknames for replication.

Syntax


>>-CREATE REGISTRATION--+-----------------+--------------------->
                        '-INCLUDE HISTORY-'   

      .-,--------------------------------------------------------------------.      
      V                                                                      |      
>--(----+-------------+--objname--+----------------------------------------+-+--)-->
        '-objowner--.-'           '-RMTJRN LIB--libname--NAME--journalname-'        

>--+-DIFFERENTIAL REFRESH--| diff-ref-clause |-+---------------><
   '-FULL REFRESH ONLY-------------------------'   

diff-ref-clause

|--+-----------------------------------------------+------------>
   '-STAGE--+--------------------+--cd_or_ccd_name-'   
            '-cd_or_ccd_owner--.-'                     

>--+--------------------+--+------------------------+----------->
   '-CONDENSED--+-ON--+-'  '-NONIBM--| fed-clause |-'   
                '-OFF-'                                 

>--+--------------------------------------------------------------+-->
   '-IN--+----------+--+-tsname----------------+--| prof-clause |-'   
         '-DB--name-'  '-NAMING PREFIX--prefix-'                      

>--+-------------------------+--+-------------------------+-----|
   '-COLS--| capcol-clause |-'  '-OPTIONS--| opt-clause |-'   

fed-clause

|--+-------------------+--remoteccdname-------------------------|
   '-remoteccdowner--.-'                  

prof-clause

|--+-----------------------------+------------------------------|
   +-CREATE USING PROFILE--pname-+   
   '-REUSE-----------------------'   

capcol-clause

|--+-ALL--IMAGE--+-AFTER--+----------------------------------------+-+-+-->
   |             |        |                          .-,------.    | | |   
   |             |        |                          V        |    | | |   
   |             |        '-INCLUDE IMAGE BEFORE--(----colnam-+--)-' | |   
   |             '-BOTH----------------------------------------------' |   
   |    .-,-----------------------------.                              |   
   |    V                               |                              |   
   '-(----colname--+------------------+-+--)---------------------------'   
                   '-IMAGE--+-AFTER-+-'                                    
                            '-BOTH--'                                      

>--+------------------------+-----------------------------------|
   '-PREFIX--+-X----------+-'   
             '-befimgpref-'     

opt-clause

|--+------------------------+----------------------------------->
   '-CONFLICT--+-NONE-----+-'   
               +-STANDARD-+     
               '-ENHANCED-'     

>--+----------------------------------+------------------------->
   '-UPDATE AS DELETE INSERT--+-OFF-+-'   
                              '-ON--'     

>--+----------------------+--+---------------------+------------>
   '-CAPTURE--+-ALL-----+-'  '-FORWARDING--+-OFF-+-'   
              '-CHANGES-'                  '-ON--'     

>--+-----------------------+--+----------------------------+----|
   '-FULL REFRESH--+-ON--+-'  '-STOP-- ON-- ERROR--+-ON--+-'   
                   '-OFF-'                         '-OFF-'

Parameters

INCLUDE HISTORY: Specifies that you are registering a temporal table on DB2® 10 for z/OS® or later and you also want to register the associated history table.
objowner: Specifies the owner of the source object (table, view, or nickname) to register. You can specify multiple objects.
objname: Specifies the name of the source object (table, view, or nickname) to register. You can specify multiple objects.
LIB libname: Specifies the System i® library name.
NAME journalname: Specifies the System i journal name.
DIFFERENTIAL REFRESH: Specify to update the target table periodically as the source object changes.
FULL REFRESH ONLY: Specify to do a full refresh only, instead of applying changes.

diff-ref-clause:

STAGE cd_or_ccd_owner.cd_or_ccd_name

Specifies the CD table owner and name. For non-DB2 sources, specifies the CCD table owner and name.

Note: If the object name is a view, then there can be multiple CD table names. Do not include this parameter because the command will generate view names for you. In this case, the ASNCLP program ignores any values you specify for this parameter.

CONDENSED

ON: Specify to retain the most current data value.
OFF: Specify to retain a history of data.

Note:

Must be set to OFF if the source is non-DB2.
This parameter is ignored for a CD table; CD tables are always noncondensed.

NONIBM

Specifies the non-DB2 options.

remoteccdowner.

Specifies the CCD table owner in the non-DB2 database.

remoteccdname

Specifies the CCD table name in the non-DB2 database.

IN

Specifies the CD or CCD table space. If you do not specify the IN clause, the command uses the DB2 defaults for table spaces.

DB name

Specifies the name of an existing database where the CD or CCD table will be created. You must specify the database name, even if you set the database name in the profile.

tsname

Specifies the table space name. For z/OS, the name includes the database name (for example, "dbname.tsname"). You can specify a heterogeneous segment or table space name, but it must already exist.

NAMING PREFIX prefix

Specifies a naming prefix for the control tables.

prof-clause:

CREATE USING PROFILE pname

Specify to create the registration by using a profile.

REUSE

Specify to reuse the current table space or index. You must issue the CREATE USING PROFILE parameter before you can use the REUSE parameter. When you specify the REUSE parameter, the ASNCLP program checks if the table space or index exists for the tsname:

If the table space or index exists, the ASNCLP program resets the flags and passes the fully populated object to the API.
If the table space or index does not exist, the ASNCLP program displays a syntax error saying that the CREATE USING PROFILE parameter is expected.

COLS

Specifies the columns that you want to register.

Note: This command only applies if the object is a table. If the object is a view, you cannot register a subset of the columns.

capcol-clause:

ALL

Specifies that you want to register all columns. This is the default.

IMAGE AFTER

Specify to register only after-image columns.

INCLUDE IMAGE BEFORE: Specify to register before images along with after images for the listed columns.
colname: Specifies a list of the columns for which you want to register before images.

IMAGE BOTH

Specify to register both after-image and before-image columns.

colname

Specifies a list of the columns that you want to register.

PREFIX

If you specify IMAGE AFTER, the prefix will be null and the source will not allow any before-image columns.
If you specify IMAGE BOTH or IMAGE BEFORE and do not specify PREFIX, a default value of X is used as a prefix for the before images. If you specify a PREFIX, that value is used.

You cannot alter an existing before-image prefix by using the ALTER REGISTRATION ROW command. However, you can add that prefix to a new before-image column. If the existing before-image prefix is null and you want to add a before-image column to the existing registration, you can specify the before-image prefix by using the ALTER REGISTRATION ADD command. If you do not specify the prefix, the ASNCLP program sets it to a default value of X.

opt-clause:

CONFLICT

Specifies the conflict-detection level.

NONE: No conflict detection. Conflicting updates between the master table and the replica table will not be detected. This option is not recommended for update-anywhere replication. This is the default.
STANDARD: Moderate conflict detection. During each Apply cycle, the Apply program compares the key values in the master's CD table with those in the replica's CD table. If the same key value exists in both CD tables, it is a conflict. In case of a conflict, the Apply program will undo the transaction that was previously committed at the replica by reading from the replica's CD table and keeping only the changes that originated at the master.
ENHANCED: Conflict detection that provides the best data integrity among the master and its replicas. As with standard detection, the Apply program compares the key values in the master's CD table with those in the replica's CD table during each Apply cycle. If the same key value exists in both CD tables, it is a conflict. However, with enhanced detection, the Apply program waits for all inflight transactions to commit before checking for conflicts. To ensure that it catches all inflight transactions, the Apply program locks all target tables in the subscription set against further transactions and begins conflict detection after all changes are captured in the CD table. In case of a conflict, the Apply program will undo the transaction that was previously committed at the replica by reading from the replica's CD table and keeping only the changes that originated at the master.

UPDATE AS DELETE INSERT

ON: Specify to capture updates as delete-insert pairs.
OFF: Specify to capture updates as updates. This is the default.

CAPTURE

ALL: Specify to capture everything. This is the default.
CHANGES: Specify to capture only changes.

FORWARDING

OFF: Specify not to forward changes from this source. This is the default.
ON: Specify to forward changes from this source.

FULL REFRESH

ON: Specify to allow full refreshes for this source. This is the default.
OFF: Specify not to allow full refreshes for this source.

STOP ON ERROR

ON: Specify not to stop the Capture program if it detects an error for this registration. This is the default.
OFF: Specify to stop the Capture program if it detects an error for this registration.

Usage notes

If multiple objects are registered at one time:

The CD table or CCD table object owner and name clause is ignored; the command generates its own defaults.
The table space specifications apply to all registrations.
The OPTIONS values are common across all registrations.
If the source object is view, the command decides whether the source can be registered as differential or full refresh and the user input will be ignored.

Example 1

To create a registration for DB2ADMIN.STAFF that only does full refreshes:

CREATE REGISTRATION (DB2ADMIN.STAFF) FULL REFRESH ONLY

Example 2

To create a registration for DB2ADMIN.STAFF that updates the target table as the source objects change, registers after-image columns C002 and C003, and registers both after-image and before-image columns C000 and C001:

CREATE REGISTRATION (DB2ADMIN.STAFF) DIFFERENTIAL REFRESH STAGE CDSTAFF 
COLS (C000 IMAGE BOTH, C001 IMAGE BOTH, C002 IMAGE AFTER, C003 IMAGE AFTER) PREFIX X

Example 3

To create a registration for DB2ADMIN.EMPLOYEE that updates the target table as the source objects change, registers after-images for all of the columns in the source table, and also registers before images for the SALARY and BONUS columns:

CREATE REGISTRATION (DB2ADMIN.EMPLOYEE) DIFFERENTIAL REFRESH
COLS ALL IMAGE AFTER INCLUDE IMAGE BEFORE(SALARY,BONUS)PREFIX X;