Using the CREATE PUBLICATION statement

When you create a publication, you specify which table (in the master database) the data should be read from, and which table (in the replica databases) the data should be written to.

To create a publication, execute the CREATE PUBLICATION statement in the master database, see CREATE [OR REPLACE] PUBLICATION.

The CREATE PUBLICATION statement lets you specify publications for incremental download of new and changed data from the master database to a replica database.

The data of a publication is always read from the master database in one transaction. This guarantees that the data read from the publication is internally consistent.

Important The data read from the publication is internally consistent unless the master database is using the READ COMMITTED transaction isolation level. The transaction isolation level for refreshes can be different from the system default. For more information, see the Synchronizer.RefreshIsolationLevel parameter in Synchronizer section.

By default, the publication data is also written to the replica database in one transaction to maintain that consistency. However, on the replica database side you can override the default behavior and split the refresh into multiple transactions by using the COMMITBLOCK option. If you use COMMITBLOCK, then you lose the guarantee of internal consistency. See Setting the commit block size for more details about COMMITBLOCK.

The search condition of the WHERE clause of the CREATE PUBLICATION statement can contain the input arguments of the publication as parameters. The parameter name must have a colon as a prefix.

If the tables of the publication have a relationship between them, the search condition of the inner result set can refer to a column of a table in the outer result set:

RESULT SET FOR OUTER_TABLE
BEGIN
   SELECT * FROM OUTER_TABLE
   WHERE COL1 = 1;
   RESULT SET FOR INNER_TABLE
   BEGIN
      SELECT * FROM INNER_TABLE
      WHERE COL1 = OUTER_TABLE.COL1;
   END
END

Note that even if the publication contains multiple tables and there is a relationship between tables, the number of tables that the data is written to (in the replica database) is the same as the number of tables that the data was read from (in the master database). Records from multiple tables in the master database are not summarized or joined into a single record in the replica the way you might join two tables into a single view, or summarize data by using an aggregate function such as SUM().

The following is an example of a typical publication:

CREATE PUBLICATION ORDERS_BY_SALESPERSON
(SALESPERSON_ID VARCHAR)
BEGIN
  RESULT SET FOR CUST_ORDER
  BEGIN
    SELECT * FROM CUST_ORDER
    WHERE SM_ID = :SALESPERSON_ID AND STATUS = 'ACTIVE';
    RESULT SET FOR ORDER_LINE
    BEGIN
      SELECT * FROM ORDER_LINE WHERE ORDER_ID = CUST_ORDER.ID;
    END
    DISTINCT RESULT SET FOR CUSTOMER
    BEGIN
      SELECT * FROM CUSTOMER WHERE ID = CUST_ORDER.CUSTOMER_ID ;
    END
  END
END;

This example retrieves data from three tables of the master database:

▪ The main table of the publication is CUST_ORDER. Rows are retrieved from the table using SALESPERSON_ID as a search criterion.

▪ For each order, rows from ORDER_LINE table are retrieved. The multiplicity between CUST_ORDER and ORDER_LINE is 1-N. The data is linked together using the ID column of CUST_ORDER table and ORDER_ID column of the ORDER_LINE table.

▪ For each order, also a row from the CUSTOMER table is retrieved. The multiplicity between CUST_ORDER and CUSTOMER tables is N-1; that is, a customer might have multiple orders. The data is linked together using the CUSTOMER_ID column of the CUST_ORDER table and ID column of the CUSTOMER table. The keyword DISTINCT ensures that the same customer information is brought to the replica database only one time.

You can make publications that have 1-N and N-1 relationships between the result sets. You can also nest the result sets. For example, a CUST_ORDER can have ORDER LINES (1-N) and each ORDER LINE can have a PRODUCT (N-1).

If the relationship between the outer and inner result set of the publication is a N-1 relationship, then the keyword DISTINCT must be used in the result set definition.

Each nested result set is internally treated as a join. The more tiers of result sets in the publication, the more complex the queries must be to retrieve the data. Therefore, for better performance, avoid extensive nesting of result sets. In the following examples, better performance is achieved by rewriting the CREATE PUBLICATION statement into an equivalent unnested version of the statement, as shown in the following examples:

Nested publication version

CREATE PUBLICATION NESTED (IN_ORDER_ID INTEGER)
BEGIN
  RESULT SET FOR CUST_ORDER
  BEGIN
    SELECT * FROM CUST_ORDER
      WHERE ID = :IN_ORDER_ID;
    RESULT SET FOR ORDER_LINE
    BEGIN
      SELECT * FROM ORDER_LINE WHERE ORDER_ID = CUST_ORDER.ID;
    END
  END
END;

Unnested publication version

CREATE PUBLICATION UNNESTED (IN_ORDER_ID INTEGER)
BEGIN
  RESULT SET FOR CUST_ORDER
  BEGIN
    SELECT * FROM CUST_ORDER WHERE ID = :IN_ORDER_ID;
  END
  RESULT SET FOR ORDER_LINE
  BEGIN
    SELECT * FROM ORDER_LINE WHERE ORDER_ID = :IN_ORDER_ID;
  END
END;

Each publication that you create is fully independent from every other publication. This means that you cannot define dependencies between publications.

Do not use overlapping publication definitions in replica databases. This includes publication definitions with overlapping tables and WHERE conditions. Publication definitions overlap if they can potentially produce overlapping subsets of the same table, (that is, some or all rows can simultaneously be in both subsets).

For example, if the publication ‘ORDERS_BY_SALESPERSON’ retrieves customer information, it is not advisable to have another publication, such as ‘CUSTOMERS_BY_AREA’, that can retrieve the same rows from the master database to the subscribing replica database. This will lead to conflict situations, for example, if you drop subscriptions to publications and that results in the deletion of all replica data for a subscription, regardless of whether another subscription is referring to those rows.

Also make sure that you are not doing overlapping refreshes of same publication within a replica. For example, if you start a new REFRESH before the reply message of the previous REFRESH operation has been processed, the replica database might contain incorrect data.

After using CREATE PUBLICATION, it is important to commit the transaction before any replica subscribes to the publication. If a transaction that defines a publication is uncommitted in the master database, then when the replica tries to subscribe to that publication, the system generates an error message stating that the publication does not exist.