Configuring Foreign Server and Foreign Stream

<< Click to Display Table of Contents >>

Navigation:  »No topics above this level«

Configuring Foreign Server and Foreign Stream

Return to chapter overview

The tables below show the options for the SQL/MED server ("foreign server") and foreign streams.

SQL/MED server ("foreign server") options

The table below shows the options specifiable in the CREATE SERVER command.
 

Option

Description

Possible Values

(default in bold)

URI

JDBC connect string

See "Accepted values" below.

DRIVER

JDBC driver class

See "Accepted values" below.

connParamPrefix

Prefix for connection parameter options (case-insensitive)

CONN_

connParams

You can specify connection parameters in options, as shown in this example; or by using a file (e.g., foo.properties) in which you list the options. connParams tells where you have put this file, e.g., connParams dir/foo. Since the default path connParams is $SQLSTREAM_HOME/s-Server/plugin/jndi, specifying connParams dir/foo causes the server to look in $SQLSTREAM_HOME/s-Server/plugin/jndi/dir/foo.properties.

Note: $SQLSTREAM_HOME refers to the installation directory, such as /opt/sqlstream/4.0.XXX.

string

user

Login user for source database table

No default

password

Login password for source database table

No default

databasename

Database name of source database

No default

[connParamPrefix] + applicationName

Connection property; value is displayed by management tools in SQL-Server so that dba can see which program is connected to database. With no value, blank shows up.

Optional: No default

[connParamPrefix] + clientProgramName

Connection property

 

Optional: No default

sqlDialect

Supported values listed below

Database vendor and version

stateTable

TableReader state table, usually "SQLS_TableReader_State"

No default: Must be supplied

pollingMillis

Description: Interval at which TableReader checks its queue for new rows. If no data is received in that time, pollingMillis is doubled, but never greater than pollingMillisMax.

10000L;

10s

pollingMillisMax

Description: Maximum interval at which TableReader checks its queue for new rows.

300000L

5 min.

sqlDialect options

The correct usage options appear below.

For Postgres and Postgres 8.x

URI

jdbc:postgresql://host:port/database

DRIVER

org.postgresql.Driver

Default port

5432

Dialect

"Postgres" or

"Postgres 8.x"

"PostgreSQL"

"PostgreSQL 8.x"

"Vectorwise"

For MySQL and MySQL 5.x

URI

jdbc:mysql://host:port/database

DRIVER

com.mysql.jdbc.Driver

Default port

3306

Dialect

"MySQL" or "MySQL 5.x"

SQLserver

SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2000/2005, Microsoft SQL Server, Microsoft SQL Server 2000, Microsoft SQL Server 2005, Microsoft SQL Server 2000/2005

URI

jdbc:sqlserver://host:port

DRIVER

com.microsoft.sqlserver.jdbc.SQLServerDriver

Default port

1433

Dialect

"Microsoft SQL Server 2000/2005/2008" or

"Microsoft SQL Server"

"Microsoft SQL Server 2000"

"Microsoft SQL Server 2005"

"Microsoft SQL Server 2008"

"SQL Server 2000/2005/2008"

"SQL Server"

"SQL Server 2000"

"SQL Server 2005"

"SQL Server 2008"

For Oracle

URI

jdbc:oracle:thin:@localhost:1521/SGA

or

jdbc:oracle:thin:@localhost:1521:XE

DRIVER

oracle.jdbc.OracleDriver

Default port

1521

Dialect

"Oracle"

"Oracle 10.x",

Generically, the Oracle connection URL is jdbc:oracle:thin:@<host>:<port>:<SID> or jdbc:oracle:thin://@<host>:<port>/<SERVICE_NAME>

For Paraccel

URI

jdbc:paraccel://localhost:5439

DRIVER

com.paraccel.Driver

Default port

5439

Dialect

"Paraccel"

Generically, the Paraccel connection URL is jdbc:paraccel://<host>:<port>

For Vectorwise

URI

jdbc:ingres://localhost:GG7/hcl

DRIVER

com.ingres.jdbc.IngresDriver

Default port

 

See http://www.actian.com/kb/article/400153 for more details.

SQL/MED foreign stream options

The DDL for each TableReader foreign stream must specify (in the eventsTable option) the external database table to which that stream corresponds. The rowtype of the foreign stream must match that of its corresponding table.

For each target table being tailed, there is one row in the state table. TableReader maintains its high-water mark for a target table's queryCol by either numeric record ID (BIGINT) or by record time (DATETIME or TIMESTAMP, depending on the database). TableReader automatically uses the target table's primary key to determine which type to use for the high-water mark tracking.

Since table entries from multiple insertion sources can arrive without being in strict queryCol order, a newly arriving row can have a queryCol value higher than a prior row, even though both are less than the latest high-water mark. If those earlier rows with lower queryCol values were already streamed, this new arriving row (with its higher queryCol value) would be seen as "out of order," and thus discarded.

To minimize such data loss, TableReader enables you to specify a querytSort parameter establishing an interval (in the queryCol values) during which you expect potential out-of-order queryCol values in the incoming rows. Rows with queryCol values in that interval are not yet streamed. The querytSort value you choose enables the streaming of rows whose queryCol values are lower than "high-water mark minus querytSort." As incoming rows establish a new high-water mark, earlier rows outside that new interval can then be streamed.

The following table describes the querytSort and other server options.

Option

Description

Possible Values

(default in bold)

TYPE

This is used when defining a stream as a control stream or a tableEvents stream. For a tableEvents stream, this table shows additional options.

"tableEvents"

or

"Control"

MASTER

This should only be set to true on one foreign stream which will then be the master. Nothing will flow until a select is done on the master.

true

false

eventsTable

Name of target table being read

No default value

"name"

"schema.name"

"db.schema.name"

DEFERSTART

Come up in suspended mode. No data read until a resume is done.

true

false

queryCol

Description: Name of column with unique ascending value or timestamp column:

--Must be an integer or date-time type.

--If, however, the contents of queryCol can be non-unique,

then to be selected the contents must be less than the current-maximum-value for queryCol.

--Put another way, if queryCol is id, then "id < max(id)" is made part of the query.

[column name]

queryColGroupRange

Number of distinct queryCol values to retrieve during each poll.

--QueryColGroupRange specifies what range of query column keys to query for.

--For a timestamp-based key column, units are in seconds.

--For an integral-based key column, units are absolute.

--The table reader query will be restricted to a range

whose starting point is the previous high-water mark.

--The end point of the range is calculated with QueryColGroupRange.

--The number of queries with no results, plus one, is multiplied by QueryColGroupRange.

--The end point of the range for the table reader query is the

sum of that result and the previous high-water mark.

0

integer

querytSort

Allow timestamps to be inserted out of order by x seconds. --If something is out of order by more than x seconds, then it will not be streamed.

-- For example: if id were a timestamp column, then setting querytSort '10' would cause a wait of 10 seconds before accepting rows, accepting them in sorted order.

Specifies the value X that determines the interval "high-water mark minus X" for queryCol entries. Arriving rows with queryCol values in that inclusive interval will not be streamed until the high-water mark rises by X. This criterion enables entries for queryCol to be inserted into the table out of order but within that range.

For integer queryCol entries, specifying X for querytSort means that a queryCol entry whose numeric difference from the current high-water mark is less than or equal to X will not be streamed.
For timestamp queryCol entries, X means that a queryCol entry whose timestamp difference from the current high-water mark is less than or equal to X seconds will not be streamed.
For example: if id were a timestamp column, then setting querytSort '10' would cause a wait of 10 seconds (in the queryCol values) from the high-water mark before streaming selected rows in sorted order.

" " (empty string)

string

If an integer is not specified, no querytSort-interval is used.

 

For timestamp values in queryCol, querytSort represents "how many seconds" for the interval: see description below.

SKIPVALIDATION

Description: Debug development tool; should not be used in production.

--SKIPVALIDATION true means skip doing column type validation, which can be useful during development: foreign stream and target stream rowtypes are not compared and validated.

--SKIPVALIDATION false or omitted means a connect is required so that the table and stream can be compared, such as the foreign stream rowtime for read table.

true

false

customPredicate

Description: SQL expression to filter event query results.

The specified expression is ANDed with the WHERE clause of the event query, and must be valid for the specified sqlDialect.