Exporting and importing subscriptions

The advanced replication EXPORT SUBSCRIPTION command let you export a version of the data from a master database to a replica database or to a disk file. If the data is exported to a disk file, it can be imported into a replica database with the IMPORT command. These commands assume you have created subscriptions in your database and bookmarks to reference the state of the database you want to export.

The concept and procedures for using the EXPORT SUBSCRIPTION command are similar to refreshing from a publication. You use the EXPORT SUBSCRIPTION command instead of the MESSAGE APPEND REFRESH or REFRESH command in the following circumstances:

▪You need to create a large replica database from an existing master. This procedure requires that you export a subscription with or without data to a file first, then import the subscription to the replica. For details, see Creating a replica by exporting a subscription with data or Creating a replica by exporting a subscription without data.

Note the difference between using the EXPORT SUBSCRIPTION command vs. refreshing from a publication with MESSAGE APPEND REFRESH or REFRESH:

▪The export output is saved to a user specified file, whereas output of a REFRESH command is stored in a advanced replication reply message.

▪The export file is never incremental (for example, if the data for the export contains rows, all rows are included in the export file, as in a refresh based on a full publication).

▪The export file is based on a given bookmark; this means that export data is consistent up to a given bookmark and refreshes based on incremental publications are possible from that bookmark.

EXPORT SUBSCRIPTION COMMAND

There are two different ways to export data from master to replica database.

If you want to export data of a subscription from the master database to a file for later import to one or multiple replicas, use the following EXPORT SUBSCRIPTION syntax:

Once the export operation to a file has completed, the data of the file can be imported to a replica database with IMPORT command.

If you want to export data from a master database directly to specified existing replica database, use the following syntax:

Note that EXPORT SUBSCRIPTION TO REPLICA command does not use files as the means to transfer data from master to replica database. Instead, it writes the data to the replica database directly. Hence, no separate import step is needed. The replica database must exist and be available in the network.

The publication_name and bookmark_name are identifiers that must exist in the database. The filename represents a literal value enclosed in single quotation marks. An export file can contain more than one subscription. You can export subscriptions “WITH DATA” and “WITH NO DATA” options. If there is more than one publication specified, the exported file can have a combination of “WITH DATA” and “WITH NO DATA.”

For more details, including rules for usage, see the description of the SQL commands “EXPORT SUBSCRIPTION TO <file>” and “EXPORT SUBSCRIPTION TO REPLICA” in the solidDB^® SQL Guide. For the procedure to create a replica WITH DATA, see Creating a replica by exporting a subscription with data and to create a replica WITH NO DATA, see Creating a replica by exporting a subscription without data.

The IMPORT command is used on a replica database to import the data from a data file created by the EXPORT SUBSCRIPTION command.

The IMPORT command is created using the following syntax:

The filename represents a literal value enclosed in single quotation marks. The import command can accept a single filename only. All publication data for import to a replica must fit in a single file. However, you can use multiple import statements to import multiple files.

The #rows is an integer value used with the optional COMMITBLOCK clause to indicate the commitblock size.

The COMMITBLOCK clause indicates the number of rows processed before the data is committed. If COMMITBLOCK is not specified, the IMPORT command takes all rows in the publication as one transaction. If the file contains a large number of rows, the use of COMMITBLOCK is recommended.

For more details, including rules for usage, see the description of the “IMPORT” command in the solidDB^® SQL Guide.

When you have an existing (replica) database that needs a subset of the master’s data but does not yet have it, then you use the EXPORT SUBSCRIPTION command using the WITH DATA option to export data for the replica.

The following procedure requires that you include data in the export file(s) and load the data to the replica with the IMPORT command.

▪When using the EXPORT SUBSCRIPTION command, you can export data to the same file more than once. With each command, the data is appended to the file. If you plan to do this, be sure you have enough disk space to accommodate the exported data for each export command. If you run out of disk space in the middle of the export, the EXPORT SUBSCRIPTION command will fail with an error and the export file will not be usable.

Perform these steps in the master database:

1 Create a bookmark if one does not exist. If a bookmark already exists and
meets your needs, you can use it. See Managing data with synchronization bookmarks for information about creating bookmarks.

If a bookmark is associated with more than one publication at the master, then be sure to execute the EXPORT SUBSCRIPTION commands for each publication separately.

For each EXPORT SUBSCRIPTION command WITH DATA option, the metadata and versioned data corresponding to that publication and bookmark are added to the export file.

If you receive an error that you have run out of disk space, delete the previous file and execute the EXPORT SUBSCRIPTION command again with sufficient disk space.

Note You cannot suspend and resume an EXPORT SUBSCRIPTION command. If the execution did not complete for some reason, you need to execute the EXPORT SUBSCRIPTION command again.

Possible errors you may encounter include:

▪Error message 25067, indicating that the advanced replication bookmark could not be found. Check to see that you have entered the bookmark name correctly.

▪Error message 25068, indicating that the filename you specified in the EXPORT SUBSCRIPTION or IMPORT command cannot be opened or cannot be opened in the append mode. Check to see that you have entered the filename correctly and it is not currently in use.

Perform these steps at the replica site:

4 Import the file(s) you created using the EXPORT SUBSCRIPTION command. The procedure for import is described in Specifying a subscription for export.

The IMPORT command accepts only one file at a time. If you have multiple export files, execute a separate IMPORT command for each file. Remember that one file may include multiple exports.

You can import the same subscription to the same replica more than once as this has the same effect as subscribing with the FULL publication option.

Note You cannot suspend and resume an IMPORT command. If the execution did not complete for some reason, you need to execute the IMPORT command again.

5 REFRESH the publication(s) from the master database using advanced replication’s MESSAGE (for example, MESSAGE APPEND REFRESH) or REFRESH command.

In some cases, you might have a database that already contains all the appropriate data, but is not already configured to be a replica. For example, you might have a server that has a backup copy of an existing master, and you might want to convert that copy to a replica. If the database already has the appropriate data, then you do not want to have to discard and then refresh all that data. One solution is to export just the subscription metadata from the master, then import that into the copy of the master (i.e. into the database that you want to convert to a replica). You use the EXPORT SUBSCRIPTION command with the WITH NO DATA option to export the schema (the “metadata”).

CAUTION: Be sure valid data exists on the replica before you use this option, or you risk the consequence that an application accessing the replica will use the wrong set of data.

The following procedure requires that you export file(s) with no data and load the files containing the metadata and publication information to the replica with the IMPORT command.

Perform these steps in the master database:

Only one bookmark is required even if several publications are exported. You can export several publications to a single file by specifying the same file name.

For each EXPORT SUBSCRIPTION command WITH NO DATA option, the metadata and history data corresponding to that publication and bookmark are added to the export file.

Perform these steps at each applicable replica site:

2 Start the backup database and drop all replicas using the DROP REPLICA command and all publications using the DROP PUBLICATION command.

3 Change the node name using the SET SYNC NODE unique_node_name command (since the database is a backup of an existing master database and the original master is currently using that node name).

Note If you are switching the database to be a replica and the database is already a backup/copy of the master database, the backup is already set as a master database. If you execute the command SET SYNC REPLICA YES, then the database will be defined as a dual role (master and replica) database, instead of a dedicated replica-only database. If you want to make this database a replica exclusively, then you must execute SET SYNC MASTER NO as well as SET SYNC REPLICA YES.

6 Import the file(s) you created using the EXPORT SUBSCRIPTION command. The procedure for import is described in Specifying a subscription for import.

The newly created replica is ready for use. If this is the last replica you are creating, drop the bookmark from the master database as described inDropping bookmarks.