Note Applies to only solidDB Advanced Replication configurations.
CREATE [ORREPLACE] PUBLICATIONpublication‑name [(parameter‑definition [,parameter‑definition …])] BEGIN result‑set‑definition … END
where:
result‑set‑definition::= RESULTSETFORreplica‑table‑name BEGIN SELECTselect‑list FROMmaster‑table‑name [WHEREsearch‑condition] ; [[DISTINCT] result‑set‑definition …] END
Access requirements
In master database: Valid master user who has full access to the tables of the publication.
Usage
Use the CREATE [OR REPLACE] PUBLICATION statement to create or replace publications, see Creating publications.
▪ In master database: You define the publication in the master database to enable the replicas to get refreshes from it.
▪ In replica database: There is no need to define the publications in replica databases. Publication subscription functionality depends only on the definitions in the master database. If the CREATE OR REPLACE PUBLICATION statement is executed in a replica database, the publication definition is stored in the replica database, but the publication definition is not used for anything (unless the database is both a replica database and a master database).
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 can 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.
Parameters, clauses, keywords, and variables
▪ WHERE search‑condition:
– Publications can contain data from multiple tables. The tables of the publication can be independent or there can be a relationship between the tables. If there is a relationship between tables, the result sets must be nested, and the WHERE clause of the SELECT statement of the inner result set of the publication must refer to a column of the table in the outer result set.
– search‑condition can contain input arguments of the publication as parameters. The parameter name must have a colon as a prefix.
▪ DISTINCT: If the relationship between outer and inner result set of the publication is an N-1 relationship, the keyword DISTINCT must be used in the result set definition.
▪ replica‑table‑name and master‑table‑name: If you have multiple replica databases, all the replica databases should use a common table name. However, the table name that is used by replica databases can be different from the master table name. Column names in the master and replica tables must be the same.
▪ OR REPLACE: If the publication already exists, use the optional keywords OR REPLACE to replace the publication with a new definition. Since the publication is not dropped and re-created, replicas do not need to re‑register, and subsequent REFRESHes from that publication can be incremental rather than full, depending upon exactly what changes were made to the publication.
To avoid a replica database refreshing from a publication while you are updating that publication, you can temporarily set the catalog sync mode to Maintenance mode (see SET SYNC MODE). However, using Maintenance mode is not mandated when you replace a publication.
If you replace an existing publication, the new definition of the publication is sent to each replica database the next time that the replica database requests a refresh. The replica database does not need to explicitly re‑register with the publication.
When you replace an existing publication with a new definition, you can change the result set definitions but 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 re‑register with the publication and then get a full refresh of the data 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 the privileges.
The CREATE OR REPLACE PUBLICATION statement can be executed in any situation where it is valid to execute the CREATE PUBLICATION statement.
Important If you use the CREATE OR REPLACE PUBLICATION statement to alter the contents of an existing publication, the operation does not remove rows (that do not match the new publication definition) from the replica database.
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
Examples
Retrieving data by searching on area code
The following example publication retrieves data from the customer table by using the area code of the customer as the search criterion. For each customer, the orders and invoices of the customer (1-N relationship) as well as the dedicated salesman of the customer (1-1 relationship) 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.
Adding a new column to table
In this scenario, a new column C has been added to 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;