Using the create publication statement

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

"CREATE PUBLICATION publication_name
  [( parameter_definition [,parameter_definition ...])]
BEGIN
  main_result_set_definition...
END";

main_result_set_definition ::=
RESULT SET FOR main_replica_table_name
  BEGIN
    SELECT select_list
    FROM master_table_name
  [ WHERE search_condition ] ;
    [[DISTINCT] result_set_definition...
  END

result_set_definition ::=
RESULT SET FOR replica_table_name
  BEGIN
    SELECT select_list
  FROM master_table_name
  [ WHERE search_condition ] ;
  [[DISTINCT] result_set_definition...]
  END

The CREATE PUBLICATION statement lets you specify publications for incremental “download” of new and changed data from the master 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.

CAUTION: The data read from the publication is internally consistent unless the master 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 information about the RefreshIsolationLevel parameter in the solidDB^® Administrator Guide.

By default, the publication data is also written to the replica database in one transaction to maintain that consistency. However, on the replica side you may override the default behavior and split the refresh into multiple transactions by using COMMITBLOCK. 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 can reference parameter_definitions and/or columns of replica tables defined on previous (higher) levels. Search conditions of a SELECT clause can contain input arguments of the publication as parameters. The parameter name must have a colon as a prefix.

Publications can contain data from multiple tables. These tables may be independent, or there may be relations between them. If there is a relation between tables, you must nest the result sets. The WHERE clause of the SELECT statement of the inner result set of the publication must refer to a column of the table of the outer result set.

Note that even if the publication contains multiple tables and there is a relation between tables, the number of tables that the data is written to (in the replica) is the same as the number of tables that the data was read from (in the master). Records from multiple tables in the master 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().

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;

▪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 may 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 once.

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 relation between 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:

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;

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 you cannot define dependencies between publications.

Do not use overlapping publication definitions in replicas. This includes publication definitions with overlapping tables and WHERE conditions. Publication definitions overlap if they can potentially produce overlapping subsets of the same table, i.e. 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. This will lead to conflict situations, for example, when dropping subscriptions to publications, resulting in the deletion of a subscription’s entire replica data, 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 may 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 at the master, then when the replica tries to subscribe to that publication, the system issues an error message stating that the publication does not exist.

Publication data is requested from the master database using the MESSAGE APPEND REFRESH publication_name statement.