The Refresh procedure TS_REFRESH_CDC_SUBSCRIPTION is an SQL stored procedure that initiates a Refresh on a subscription.
Overview of the Refresh procedure
To be able to initiate a refresh through the solidDB® connection, the Access Server login data must be set with an IBM InfoSphere CDC for solidDB® command dmsetaccessserverparams before the Refresh procedure is started.
When the procedure is called, it checks the existence and the refresh status of the subscription.
▪If the refresh can be started, the procedure call blocks until the refresh is finished. Depending on the size of the refreshed data, the call might block for a long time.
If the call does not return, normal timeouts apply.
▪If the refresh cannot be started, an error is returned.
The state of the refresh is maintained in a table called TS_REFRESH, which is created automatically by the IBM InfoSphere CDC for solidDB® when the instance is created. When the procedure is started, it changes the status to ‘1’ (Refresh in progress).After the refresh is finished, IBM InfoSphere CDC for solidDB® updates the state to ‘2’ (Refresh finished). If the refresh fails, IBM InfoSphere CDC for solidDB® reports the error in the table.
Column
Data type
Description
subscription_name
VARCHAR (20) PRIMARY KEY
The subscription name
state
INTEGER NOT NULL
The state of the refresh: v -1 — error v 0 — refresh requested v 1 — refresh in progress v 2 — refresh finished
error_description
VARCHAR(255)
The error description
▪Problem loading Access Server Parameters
▪Access Server username not set
▪Access Server password not set
▪Access Server host address not set
▪Access Server port number not set
▪Error creating connection to Access Server
▪Error connecting to Access Server
▪Connection to Access Server does not exist
▪Failed to get publishers
▪Failed to find a matching subscription
▪Subscription does not exist
▪Error polling on refresh
inserts_performed
BIG INT
Number of rows for committed inserts during refresh
The Refresh stored procedure does not support referential integrity. If your tables include foreign keys and you have set the IBM InfoSphere CDC for solidDB® system parameter refresh_with_referential_integrity to true, the Refresh stored procedure cannot start a refresh. Instead of using the Refresh stored procedure, you must initiate refreshes manually using the Management Console or with the dmrefresh command.
Creating the Refresh procedure
The solidDB® package includes an SQL script for creating the stored procedure. The script is available in the procedures directory under the solidDB® installation directory.
Script
Usage
create_refresh_package.sql
Creates the stored procedure
To create the Refresh procedure:
1 Ensure that you have created the subscriptions and your frontend and backend data servers and the IBM InfoSphere CDC components are running normally.
2 Create the Refresh procedure by running the script create_refresh_package.sql.
You can use solsqlto run the script, as shown in the following example:
You can check the progress of the refresh by viewing the TS_REFRESH table for the state of refresh and the number of refreshed rows (inserts_performed).
For example:
SELECT * from TS_REFRESH;
SUBSCRIPTION_NAME STATE ERROR_DESCRIPTION INSERTS_PERFORMED ----------------- ----- ----------------- ----------------- current_invoices 1
1 rows fetched.
Stopping the stored procedure
The procedure call blocks until the refresh executes successfully. If you want to stop the procedure, use the ADMIN COMMAND ’throwout’ to force an exit.
Normal timeouts apply also:
▪If a query timeout is set, the call will timeout on the query timeout. By default, there is no timeout.
For example:
– In ODBC, set the query timeout with the ODBC statement attribute SQL_ATTR_QUERY_TIMEOUT (in seconds).
– In JDBC, set the query timeout with the statement method setQueryTimeout() (in seconds).
▪If a connection timeout is set, the connection is lost after the timeout expires.
▪For details on the timeout behavior, see “Timeout controls” in the solidDB® Programmer Guide.