This section describes how to access only a certain section of records in a table using the Poll from Database adapter.
Normally, the query specified in the Query property will retrieve all the records in the table, or all records that meet your specific criteria if you use a WHERE clause. If the table is large, then retrieving all the rows may consume a lot of resources.
To save resources, the Poll From DB adapter allows you to specify that you want to read only the records that were added since the last time that the query was run. To do this, you need to tell the adapter which column to look at to determine which records are new. This column must be either a date/time column (for example, the date and time at which a stock trade was executed) or some type of counter or sequence number that is assigned in ascending order by the data source.
For example, suppose you are querying a table that contains stock information, in which each row has a column named TradeTime that indicates the time at which the stock trade took place. Suppose also that the previous execution of the query was at 10:00 July 1, 2006, and at 10:05 you want to retrieve just the stock trades that were executed since 10:00. Conceptually, you could do this by modifying your WHERE clause to look similar to:
...WHERE... TradeTime > TO_TIMESTAMP("2006-07-01 10:00:00")...
Each time that the query is run, the value "2006-07-01 10:00:00" must be replaced with the time of the query's most recent previous run. Since the adapter reads the rows from the external table, the adapter itself can keep track of the highest value read so far and plug that in each time that the query is executed. You must specify which column contains the timestamp or counter.
To specify which column to use, enter a column name into either of two adapter properties, named Timestamp Field and Counter Field. You must also modify your query to refer to a variable stored by the adapter itself; this variable holds the highest timestamp or the highest counter read so far. You must also include an ORDER BY clause that refers to the column name you entered into the Timestamp Field or Counter Field property.
The name of the column you enter must be the name of the column in the CCL schema, in the external table. For example, if you decide to use the TradeTime column of the external table, and if the TradeTime column of the external table is read into the TimeOfTrade column in the CCL stream, then you would specify TimeOfTrade in the Timestamp Field property of this Poll From DB adapter.
Using our example of a table of stock trades, the SQL Query would look like:
SELECT ... FROM StockTable WHERE TradeTime > ?C8_TIMESTAMP_FIELD ... ORDER BY TradeTime ...;
The Timestamp Field (or the Counter Field, if you use a Counter Field) must be a column that is always higher for newer records. For example, if you were reading from a table of employees, you would want to use a timestamp that indicated each employee's hiring date and time, not birthday. If you used birthdays, then the timestamps in new rows would not necessarily be greater than the timestamps in the most recently read row, and thus a query that retrieved only rows with newer timestamps would not necessarily retrieve all of the new employees.
If the external table does not have an appropriate timestamp/datetime column, you may be able to use a column that acts like a counter or a sequence number that has values that are unique and that ascend over time. In some cases, the table's primary key column may meet these requirements.
For example, suppose that you ship packages to customers, and each package is given a unique serial number. Serial numbers increase over time, and rows are inserted into the table in order by serial number. The serial number is in a column named "SerialNum" and the column name is the same in the external table and the Sybase CEP stream. In that case, set the adapter's property named Counter Field to "SerialNum", and write your query to look similar to:
SELECT ... FROM Shipments WHERE SerialNum > ?C8_COUNTER_FIELD ... ORDER BY SerialNum ...;
The variable ?C8_COUNTER_FIELD will be replaced with the most recent value of the Counter Field column that was read during the previous Poll From DB operation. For example, if the previous operation read rows with serial numbers 200-220, then the WHERE clause above would be sent to the external database server as
WHERE SerialNum > 220 ... ;
The server uses the value of the Timestamp Field or the Counter Field in the most recently read row, not the largest value seen so far. Therefore, you must include an ORDER BY clause to ensure that the values are returned in order and thus that the most recently read row contains the largest value.
If you are reading rows incrementally rather than reading all rows every time the query executes, you must decide whether the very first read should get all rows in the table or only the rows since a particular cutoff point. The adapter properties named "Timestamp Field Initial Value" and "Counter Field Initial Value" allow you to specify this.
If you want to read all of the rows in the table, set the ...Initial Value property to a value lower than the lowest value in the table.
If you want the first execution of the query to return only the rows that were added to the table after a particular cutoff point, then enter that cutoff point into the ...Initial Value property.
Note that the ... Initial Value must be set to a value LOWER than, not equal to, the first value you want to retrieve. For example, if you want to retrieve all rows with a timestamp at or greater than 9:00 AM July 1, 2006, then the initial value should be "2006-07-01 08:59:59.999999", not "2006-07-01 09:00:00.000000".
The first time you query the table, you probably want to get all of the records in the table, in which case you should set the "...InitialValue" to a value lower than the lowest value in the table.
The following is a complete example:
The table named Shipments has a column named TimeOfShipment which has a data type compatible with 's TIMESTAMP data type. The corresponding column in the stream is named ShippingTime and is of type TIMESTAMP. Each time that a shipment is made, a new record is added to the table, and the TimeOfShipment field of that record is set to the actual time that the item was shipped. The table has data going back as far as January 1, 1998, but you only want to use data data from the year 2000 onward. To access this table with the Poll From DB adapter, you set the following:
Set the adapter property Timestamp Field to the stream column name ShippingTime.
Set the adapter property Timestamp Field Initial Value to "1999-12-31 23:59:59.999999").
Set the query's WHERE clause to include:
"... TimeOfShipment > ?C8_TIMESTAMP_FIELD..."
Incremental retrievals work well when the only changes to a table are INSERTs. If there are any UPDATE and DELETE operations, they may require special handling. For example, suppose that you are using incremental retrievals on a table that contains stock transactions, and the Timestamp Field for that table is the TradeTime column, in other words, the time at which the trade was executed. If the record is updated to correct the name of the purchaser, for example, the adapter will not see that change unless the TradeTime is also updated, which would be inappropriate if the TradeTime itself was correct. The adapter therefore would not see the new value and would still cache the old value for that particular stock trade. Similarly, if a row were deleted, there might not be any indication of that sent to Sybase CEP Server. If the database that you connect to implements UPDATE as a DELETE plus an INSERT, then you will see the new/updated values, but you won't know that the old values have been deleted. Thus Sybase CEP's local cache of the external table's contents has both the original value and the new value. For this reason, use incremental retrievals only with tables that have INSERTs, not UDPATEs or DELETEs.
The dates from the external database server are assumed to be in UTC/GMT not local time.
If you use incremental retrievals, then since the values in the Counter Field or Timestamp Field must be assigned in ascending order chronologically, the values must not include any NULL values.
In addition to the C8_ROWCOUNT and C8_TIMESTAMP variables, there is also a C8_INVOCATIONS variable that stores the number of times that the adapter has invoked the query. This is useful in debugging, or if you want the adapter to run for only a finite amount of time or a finite number of executions.