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. You can also use jndiparams or a ConnParams file
 

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/plugin/jndi, specifying connParams dir/foo causes the server to look in $SQLSTREAM_HOME/plugin/jndi/dir/foo.properties.

Note: $SQLSTREAM_HOME refers to the installation directory for s-Server, 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.

Defining a Server Using Jndiparams

The server takes Jndiparams as a parameter specifying the location of the property file. The JNDI parameters property file should be called <filename>.properties and should be located in $SQLSTREAM_HOME/plugin/jndi

If the name of a property specified in that JndiParams property file exactly matches an option specified in the server or stream options, the value specified in the JndiParams property file takes precedence. Any server or stream options that match will use the JndiParams property file value instead, including streams using server options as defaults. Any stream specifying the option JndiPrefix causes the specified prefix to be used instead of using the <stream name> as a prefix.

CREATE OR REPLACE SERVER "Postgres_TableReader"

    FOREIGN DATA WRAPPER "TableReader"

    OPTIONS (JNDIPARAMS 'test/params');

Sample contents of the Jndiparams 'test/params'

 SampleDataStream.LOG_PATH=testcases/logfile/logtypes/variable_log

 SampleDataStream.PREPROCESS_COMMAND=unitsql/plugins/appendDummy.sh

 SampleDataStream.POSTPROCESS_COMMAND=unitsql/plugins/deleteProcessed.sh

 SampleDataStream.SLEEP_INTERVAL=1000

 

Defining a Server Using Connection Parameters defined in ConnParams files

The connection properties indicated above (URI, DRIVER, CONNPARAMPREFIX, dbConn_databaseName, dbConn_user, dbConn_password, dbconn_applicationName) can be placed in a file named <filename>.properties.

Here, you change the code along the following lines. The properties file itself appears below the code:

$SQLSTREAM_HOME/jndi/mycompany/poc/postgres.properties

CREATE OR REPLACE SERVER "Postgres_TableReader"

FOREIGN DATA WRAPPER "TableReader"

OPTIONS (

   connParams 'mycompany/poc/postgres',

   sqlDialect 'Postgres 8.x',

   stateTable 'SQLS_TableReader_State',

   pollingMillis '5000')

DESCRIPTION 'Postgres database with sample data';

 

An example properties file that works with Postgres for the code above would look as follows:

URI=jdbc:postgresql://localhost/nswrta_renderer_dev

DRIVER=org.postgresql.Driver

CONNPARAMPREFIX=dbConn_

dbConn_databaseName=mydatabase

dbConn_user=SQLstream

dbConn_password=mypassword

dbConn_applicationName=SQLstream TableReader Adapter

 

For more information on the Define Server options available, see the Server Options Table below.

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.