CREATE PUB command

Parameters

USING PUBQMAP qmapname

Specifies the publishing queue map that is used by all subsequent publications that are created by this command.

pubname-qmap-clause:

PUBNAME pubname

Specifies the name of the publication.

DESC "description"

Specifies a description of the publication.

PUBQMAP qmapname

Specifies the publishing queue map that is used by this publication. If you do not specify the USING PUBQMAP keyword, you must define the PUBQMAP keyword for every publication that you define.

src-clause:

source_owner

Specifies the schema of the source table.

source_name

Specifies the name of the source table.

SRC OWNER LIKE "predicate1"

Specify to choose all tables with a schema that matches the expression in the LIKE statement. The following examples show LIKE statements:

CREATE PUB USING PUBQMAP ABCDPUBQMAP 
(SRC OWNER LIKE "ASN%");

CREATE PUB USING PUBQMAP ABCDPUBQMAP 
(SRC OWNER LIKE "JDOE" SRC NAME LIKE "%TAB%");

SRC NAME LIKE "predicate2"

Specify to choose all tables with a name that matches the expression in the LIKE statement. The following example shows a LIKE statement:

CREATE PUB USING PUBQMAP ABCDPUBQMAP 
(SRC NAME LIKE "%4%") 

SRC ALL

Specify to choose all tables, with the exception of DB2® catalog views, that exist on the Q Capture server.

col-cause:

ALL

Specify to publish all columns in the source table.

INCLUDE (colname)

Specifies what columns to publish. You can specify multiple columns.

EXCLUDE (colname)

Specifies what columns not to publish. You can specify multiple columns.

ISKEY (colname)

Indicates whether the column is part of the key to use for publishing. Any column or set of columns that are unique at the source can be used. If no column is specified as a key, the Q Capture program looks for a primary key within the set of published columns, then for a unique constraint, and then for a unique index. If none of these exists, Q Capture will use all published, valid columns as key columns for publishing. (Some columns, such as LOB columns, cannot be used as keys.)

opt-clause:

SEARCH CONDITION "search_cond"

Specifies a search condition for filtering changes to publish. The change is not sent if the predicate is false. This is an annotated select WHERE clause, which requires a colon before the column names. The following example shows a WHERE clause:

CREATE PUB USING PUBQMAP ASNMAP
(PUBNAME mypubname ALLTYPE1 SEARCH CONDITION 
"WHERE :MYKEY > 1000")

ALL CHANGED ROWS

Specifies a data sending option.

Y

Send a row when any column in the source table changes.

N

Send a row only if a subscribed column in the source table changes.

CHANGE CONDITION "change_condition"

Specifies a predicate that uses log record variables for filtering changes to publish.

You can use the following log record variables:

$OPERATION	The DML operation. Valid values are I (insert), U (update), and D (delete).
$AUTHID	The authorization ID of a transaction.
$AUTHTOKEN	z/OS®: The authorization token (job name) of a transaction.
$PLANNAME	z/OS: The plan name of a transaction.

For example, the following predicate specifies that Q Capture only publish log records that were not committed by the user ASN:

$AUTHID <> 'ASN'

If a different predicate is specified by using the SEARCH CONDITION keyword, that predicate is combined with the CHANGE CONDITION predicate into a single predicate by using the AND operator. For more details on the format for CHANGE CONDITION, see Log record variables to filter rows.

BEFORE VALUES

For an update operation, this keyword indicates whether the Q Capture program sends the before values of non-key columns in addition to their after values. For a delete, this keyword indicates whether the Q Capture program sends the before values of non-key columns in addition to the before values of the key columns.

N

The Q Capture program does not send before values of nonkey columns that change. If a key column changes, the Q Capture program sends both its before and after values. For delete statements involving key columns, only before values are sent. This is the default.

Y

When there are changes to nonkey columns in the source table that are part of a publication, the Q Capture program sends both before and after values.

INCLUDE (colname)

Specifies the nonkey columns for which the Q Capture program sends both before and after values.

CHANGED COLS ONLY

This keyword indicates whether the Q Capture program publishes columns that are part of a publication only if they have changed. This field applies to update operations only.

Y

When the Q Capture program sends an updated row, it sends only the changed columns that are part of a publication. This is the default.

N

The Q Capture program sends all columns in a row that are part of a publication whenever any of them has changed.

HAS LOAD PHASE

Specifies whether the target table for the publication will be loaded with data from the source.

N

No load phase at the target. This is the default.

E

External load: Specifies a manual load by an application outside of replication. In this case, you insert the LOADDONE signal (by using the LOADDONE command) into the IBMQREP_SIGNAL table at the Q Capture server to inform the Q Capture program that the application is done loading.

SUPPRESS DELETES

Specifies whether to send rows that were deleted from the source table.

N

Send deleted rows.

Y

Do not send deleted rows.

TOPIC "topic"

Specifies the topic that will be included in the MQRFH2 message header and used by the publication. You must specify the HEADER MQRFH2 keywords when you create the publishing queue map that this publication uses.