Universal Cache User Guide : Tools and utilities : SQL stored procedures for data aging and refresh : Using the Refresh stored procedure
  
Using the Refresh stored procedure
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 number of inserts per commit depends on the value that is set with the IBM InfoSphere CDC system parameter refresh_commit_after_max_operations. See System parameters for IBM InfoSphere CDC for solidDB®.
Default value is 0.
Limitations
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 solsql to run the script, as shown in the following example:
solsql -f "C:\solidDB®\procedures\create_refresh_package.sql" "tcp 2315" dba dba
3 Define the login data for the Access Server using the IBM InfoSphere CDC for solidDB® command dmsetaccessserverpararms.
The syntax for the dmsetaccessserverpararms command is:
dmsetaccessserverparams [-u <username>] [-p <password>] [-H <hostname>] [-P <port>]
For example:
dmsetaccessserverparams -u dba -p dba - H 192.167.3.3 -P 10101
Running the Refresh procedure
To run the Refresh procedure:
1 Ensure that there is Mirroring (continuous) ongoing in a frontend to backend subscription.
2 Call the Refresh procedure with the following syntax:
CALL ts_refresh_cdc_subscription (’subscription_name’);
For example:
CALL ts_refresh_cdc_subscription (’current_invoices’);
Monitoring the status of refresh
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.
See also
SQL stored procedures for data aging and refresh