Publish allows you to specify which database tables you want to make available to SQDR clients for replication. The Publish feature provides centralized control that a database administrator can use to restrict specific database tables from being replicated to other destinations. When the requirePublication configuration parameter is set to true, only the database tables that have been published from the Capture Agent can be successfully subscribed to for replication. Note that publishing and unpublishing tables affects only new subscriptions that are created from the SQDR Replicator Manager. The Publications panel includes functions for displaying the published tables, and for selecting which tables to publish or unpublish.
Publishing is also the recommended method of granting access to Oracle source tables to the Agent User (SQDR). The default value of requirePublication for Oracle agents is true.
Open the Publications panel by selecting a database agent in the left panel and then selecting the Publications title bar in the right panel. A list of tables that have been published will be displayed. The tables are listed in the format schema.tablename, with a timestamp and a state of Initial, Subscribed, Published, or Failed. Initial indicates that the request to publish the table has been initiated. Subscribed means that the Capture Agent is setting up the change data table, and Published indicates that changes are being tracked and the source table can be subscribed to. If the table cannot be published the state shows Failed with additional text to explain the problem. An asterisk next to Published indicates that an exclusive lock was not obtained prior to starting to stage changed data.
To sort the display in ascending or descending order based on a particular field, click on the field name until the arrow adjacent to the field name is displayed in the desired direction.
Metadata
To display the metadata associated with a published table, select the checkbox next to one or more subscriptions and select View Metadata from the Resource menu. This option returns information about source table logging settings such as Oracle supplemental logging, IBM i journaling (IBM i), Microsoft SQL Server change tracking/change data capture, or Db2 LUW data capture settings (Y, L (Yes + long data), null).
No information is available for Informix or MySQL sources.
If you have a large number of publications, you can filter the display by entering a schema or table name at the top of the display. When entering a schema or table name, standard SQL wildcard operators apply - for instance, % character means match anything, and _ is a substitute for exactly one character. There is an implicit % at the end, and the display is updated immediately. For example, if you have tables ATAB, TAB1, TAB2, and TAB100, entering T in the Table field will immediately filter out ATAB and display only TAB1, TAB2, and TAB100. Entering TAB1 will display TAB1 and TAB100 (because of the implicit %). To eliminate the implicit % and terminate the string, enter a period. For example, entering TAB1. will display only TAB1 and not TAB100.
To filter by status, select the desired state (Initial, Subscribed, Published, Failed, or Published*) from the dropdown item. For example, you may be interested in displaying only the publications in a Failed status.
Open the Publications panel by selecting a database agent in the left panel and then selecting Publications in the right panel. Then select Add Publication from the Resource menu or select the + symbol at the top of the window:
For Db2 for i and Oracle agents, you will be prompted for the credentials of a user with sufficient authority to grant authorities to the Agent User (e.g. SQDR). We recommend using a SECOFR user (e.g. QSECOFR) for Db2 for i or a user with system authorities (e.g. SYSTEM) for Oracle. If you discover that the user you supplied does not have adequate authority, you will need to logoff from the SQDR Control Center (using the Logoff option in the upper right corner) before SQDR Control Center will prompt for the credentials again. Alternatively, you can use the following checkboxes to continue using a less-privileged user:
GRANT ACCESS TO SQDR checkbox:
For Db2 for i agents, you will be given the choice of whether or not to grant read authority to the published tables to SQDR agent user. Selecting the checkbox to grant authority may result in long delays (during which time the SQDR Control Center will appear unresponsive) if you are publishing a large number of tables and some of those tables are in use by other applications (i.e. table deadlocks could cause issues). In such a scenario, we recommend unselecting the checkbox and performing the grants manually e.g. with the GRTOBJAUT CL command or GRANT SELECT ON TABLE SQL statement.
For Oracle agents, the checkbox to grant or not is also present; the default behavior is checked. You may wish to uncheck it if the publishing user does not have sufficient authority to perform the GRANT (grant read access to the published table to the agent user) and ALTER (add supplemental logging for ALL columns).
Export to SQL script checkbox: If you select this checkbox, then no actions are performed. Instead, a SQL script is generated as a text file that can be supplied to a database administrator or other user with adequate privileges. After the DBA runs the script, the published tables will appear in the Publications panel.
Specify the schemas and names of the database tables you want to make available for SQDR users to subscribe to. You can use wildcards to specify a pattern for the schema and the name to publish multiple tables at once, as shown below:
After the Capture Agent creates a staging table for the published table(s), new subscriptions for replicating the source table data can be created from the SQDR Replicator Manager. Note that the Capture Agent publishing functions affect only new, not existing, SQDR incremental replication subscriptions.
Prior to SQDR+ v3.60, the Publish function required that an exclusive lock be acquired on a table before being considered published; this is no longer necessary. If an attempt to lock the table for shared update fails, the subscription will be published with a "*" character next to the state of Published to indicate that a lock was not obtained prior to starting to stage changed data. If the original more limiting behavior is desired, it can be configured by setting the keyword retryPublishLock=true in the configuration; in this case the publication will be displayed as Subscribed, and the lock operation will be retried every 10 minutes. Once successfully locked (and immediately released), the state will be updated to Published.
Importing Table Names from a Text File
If you have a large number of tables to publish, you can use the Import function to choose which tables to publish.
Create a text file containing a list of tables
in the following format:
<schema> <table_name> <object_type>
where <object_type>
is the word TABLE. The fields can be delimited with tab
or semicolon characters.
Note that this is the same export/import format as used by the Add
Subscription wizard of the Data Replicator Manager. So you can use
Export in Data Replicator Manager to create a list of tables
to be edited and then imported into Publications.
Select Add Publication from the Resource menu or the + symbol at the top of the window.
Select the Import button.
On the Import Table Names from Text File dialog, select Choose File.
Select the text file.
Select Submit to begin publishing.
On completion, the History panel at the bottom of the browser window will show how many tables were successfully published. If there were errors (for example, if a table does not exist or has already been published), look for detailed error messages in the jetty wrapper log (C:\ProgramData\StarQuest\sqdrplus\jetty\logs\wrapper.log or /var/sqdrplus/jetty/logs/wrapper.log).
In addition, it may be necessary to grant read access to the tables to the Agent user (e.g. SQDR).
After a table has been unpublished it cannot be subscribed to from the SQDR Replicator Manager. However, removing a table from the published list has no affect on any existing subscriptions to the table.
If you need to remove one or more database tables from the published list, open the Publications panel by selecting a database agent in the left panel and then selecting Publications in the right panel. Select the tables you want to unpublish, or use the checkbox on the heading bar to select all publications, and then select Delete Publication from the Resource menu or the X symbol at the top of the window.