SQL Guide : solidDB® SQL statements : CREATE [OR REPLACE] PUBLICATION
  
CREATE [OR REPLACE] PUBLICATION
"CREATE [OR REPLACE] 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
where
search_condition can reference parameter_definitions and/or columns of replica tables defined on previous (higher) levels.
Usage
The CREATE [OR REPLACE] PUBLICATION statement creates or replaces publications. Publications define the sets of data that can be REFRESHed from the master to the replica database. A publication is always transactionally consistent, that is, its data has been read from the master database in one transaction and the data is written to the replica database in one transaction.
Important: The data read from the publication is internally consistent unless the master is using the READ COMMITTED isolation level.
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. The tables of the publication can be independent or there can be relations between the tables. If there is a relation between tables, the result sets must be nested. 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.
If the relation between outer and inner result set of the publication is a N-1 relationship, the keyword DISTINCT must be used in the result set definition.
The replica_table_name can be different from the master_table_name. The publication definition provides the mapping between the master and replica tables. If you have multiple replicas, all the replicas should use the same name, even if that name is different from the name used in the master. Column names in the master and replica tables must be the same.
The initial download is always a full publication, which means that all data contained in the publication is sent to the replica database. Subsequent downloads (refreshes) for the same publication may be incremental publications, which means that they contain only the data that has been changed since the prior REFRESH. To enable usage of incremental publications, SYNCHISTORY has to be set ON for tables included in the publication in both the master and replica databases. For details, see ALTER TABLE ... SET SYNCHISTORY and DROP PUBLICATION REGISTRATION.
If the optional keywords “OR REPLACE” are used, and the publication already exists, it will be replaced with the new definition. Since the publication was not dropped and re-created, replicas do not need to reregister, and subsequent
REFRESHes from that publication can be incremental rather than full, depending upon exactly what changes were made to the publication.
To avoid having a replica refresh from a publication while you are updating that publication, you may temporarily set the catalog’s sync mode to Maintenance mode. However, using maintenance mode is not absolutely required when replacing a publication.
If you replace an existing publication, the new definition of the publication will be sent to each replica the next time that replica requests a refresh. The replica does not need to explicitly reregister itself to the publication.
When you replace an existing publication with a new definition, you can change the resultset definitions. You cannot change the parameters of the publication. The only way to change the parameters is to drop the publication and create a new one, which also means that the replicas must reregister and the replicas will get a full refresh rather than an incremental refresh the next time that they request a refresh.
When you replace an existing publication, the privileges related to that publication are left unchanged. You do not need to re-create them.
The CREATE OR REPLACE PUBLICATION command can be executed in any situation where it is valid to execute the CREATE PUBLICATION command.
Important: If you use CREATE OR REPLACE PUBLICATION to alter the contents of an existing advanced replication publication, you have to take care of removing invalid rows from Replica.
Usage in master
You define the publication in the master database to enable the replicas to get refreshes from it.
Usage in replica
There is no need to define the publications in the replicas. Publication subscription functionality depends on the definitions only at the master database. If the CREATE OR REPLACE PUBLICATION command is executed in a replica, it will store the publication definition to the replica, but the publication definition is not used for anything.
Note If a database is both a replica (for a master above it) and a master (to a replica below it), you might want to create a publication definition in the database.
Example 1
The following example publication retrieves data from the customer table using the area code of the customer as search criterion. For each customer, the orders and invoices of the customer (1-N relation) as well as the dedicated salesman of the customer (1-1 relation) are also retrieved.
"CREATE PUBLICATION PUB_CUSTOMERS_BY_AREA
      (IN_AREA_CODE VARCHAR)
BEGIN
   RESULT SET FOR CUSTOMER
   BEGIN
      SELECT * FROM CUSTOMER
      WHERE AREA_CODE = :IN_AREA_CODE;
      RESULT SET FOR CUST_ORDER
      BEGIN
         SELECT * FROM CUST_ORDER
         WHERE CUSTOMER_ID = CUSTOMER.ID;
      END
      RESULT SET FOR INVOICE
      BEGIN
         SELECT * FROM INVOICE
         WHERE CUSTOMER_ID = CUSTOMER.ID;
      END
      DISTINCT RESULT SET FOR SALESMAN
      BEGIN
         SELECT * FROM SALESMAN
         WHERE ID = CUSTOMER.SALESMAN_ID;
      END
   END
END";
Note The colon (:) in :IN_AREA_CODE is used to designate a reference to a publication parameter with the same name.
Example 2
Developers decided to add a new column C in table T, which is referenced in publication P. The modification must be made to the master database and all replica databases.
The tasks to execute in the master database are:
-- Prevent other users from doing concurrent synchronization
-- operations to this catalog.
SET SYNC MAINTENANCE MODE ON;
ALTER TABLE T ADD COLUMN C INTEGER;
COMMIT WORK;
CREATE OR REPLACE PUBLICATION P ...
      (column C added also to publication)
COMMIT WORK;
SET SYNC MAINTENANCE MODE OFF;
The tasks to execute in all replica databases are:
-- Prevent other users from doing concurrent synchronization
-- operations to this catalog.
SET SYNC MAINTENANCE MODE ON;
ALTER TABLE T ADD COLUMN C INTEGER;
COMMIT WORK;
SET SYNC MAINTENANCE MODE OFF;
Return values
Error code
Description
13047
No privilege for operation. You do not have the privileges required to drop this publication or create a publication.
13120
The name is too long for the publication
25015
Syntax error: error_message, line line_number
25021
Database is not master or replica database. Publications can be created only in a master or replica database. (As a practical matter, they should only be created in a master database.)
25033
Publication publication_name already exists
25049
Referenced table table_name not found in subscription hierarchy
25061
Where condition for table table_name must refer to an outer table of the publication
See also
solidDB® SQL statements