The primary RAPStore contains such a large amount of historical market data, that configuring a secondary RAPStore is impractical. If the data in the RAPStore becomes stale due to the loss of the data stream or Subscriber, the RAPStore must be loaded from data in the RAPCache using a manual procedure. This procedure to reload the RAPStore data from the RAPCache should be performed in the event of a failure that prevents the RAPStore from being loaded by the RAPStore Subscriber.
Perform the following steps for each table you are loading:
Determine the maximum key value currently in the RAPStore. The maximum key value is determined by date and time. Each market data message is stored in its corresponding database table with a date and timestamp value. Corresponding values for these key fields are a) the date today, and b) maximum timestamp value less 10 minutes. Since the messages may arrive out of order, Sybase recommends allowing a grace period of at least 10 minutes.
Create a view in the current RAPCache to select from the corresponding ASE table where key values are greater than the key values from RAPStore. For example, if you are transferring the STOCK_QUOTE table, you can create a view called STOCK_QUOTE_XFER using the following SQL statement:
create view RAP_USER.STOCK_QUOTE_XFER ( INSTRUMENT_ID, QUOTE_DATE, QUOTE_SEQ_NBR, TRADING_SYMBOL, QUOTE_TIME, ASK_PRICE, ASK_SIZE, BID_PRICE, BID_SIZE ) as select from STOCK_QUOTE where QUOTE_TIME > 'datetime key value from previous step'
Bulk copy out to a file from the view in current RAPCache, using the ASE bcp utility. Ensure that you use the following flags: -c -t , -r \\n
The -c flag tells bcp to output character data instead of binary data.
The -t , flag tells bcp to use the comma as a field terminator.
The -r \\n flag tells bcp to use a newline as a row terminator.
Here is the bcp command, using the example view STOCK_QUOTE_XFER:
bcp RAP_USER.STOCK_QUOTE_XFER out stock_quote_xfer.csv -c -t , -r \\n -URAP_USER -Prap_user -S<RAPCache Database Server Name>
For more information on the bcp utility, refer to the ASE 15.0 Utility Guide Chapter 4, “Using bcp to Transfer Data to and from Adaptive Server.”
Transfer the bulk copy files from the ASE RAPCache server to the Sybase IQ server hosting the RAPStore database.
On the Sybase IQ server, load the data into the RAPStore. Use dbisql to submit a LOAD TABLE command. Use the IGNORE CONSTRAINT UNIQUE 0 option to enable IQ to throw away any duplicate key values without terminating the load operation. For information on the LOAD TABLE command, refer to the Sybase IQ 12.7 System Administration Guide Chapter 7, “Moving Data In and Out of Databases.”
Continuing the example, the LOAD TABLE command looks like the following:
load table RAP_USER.STOCK_QUOTE ( INSTRUMENT_ID null (blanks,'NULL') , QUOTE_DATE null (blanks,'NULL') , QUOTE_SEQ_NBR null (blanks,'NULL') , TRADING_SYMBOL null (blanks,'NULL') , QUOTE_TIME null (blanks,'NULL') , ASK_PRICE null (blanks,'NULL') , ASK_SIZE null (blanks,'NULL') , BID_PRICE null (blanks,'NULL') , BID_SIZE '\x0a' ) from '<path>/stock_quote_xfer.csv' quotes off escapes off preview on ignore constraint unique 0 ; commit ;
Once the LOAD TABLE command completes for every table that must be reloaded, clean up the RAPStore before restarting the RAPStore Subscriber. The RAPStore loads data using files written by the RAPStore Subscriber.
Perform the following steps to clean up the RAPStore:
Go to the directory where the files are located. The files are located in the directory set as the PrimaryFileLocation in the RAPStore Subscriber rapstore.xml configuration file.
For each file in the directory, issue the following command using dbisql. This command removes the file from the RAP_WORK_FILE table.
delete from RAP_WORK_FILE where FILE_NAME='filename'
Delete all of the files in the directory.
