Striim 3.9.8 documentation

Database Reader

Returns data from a JDBC query against one or more tables in a supported database. If the connection to the database is interrupted, the application will crash.

Warning

Except for PostgreSQL, the JDBC driver for the DBMS must be installed as described in Installing database drivers.

property

type

default value

notes

Connection URL

java.lang.String

The following databases are supported.

  • for HP NonStop SQL/MX: jdbc:t4sqlmx://<IP address>:<port> or jdbc:t4sqlmx://<IP address>:<port>/catalog=<catalog name>;schema=<schema name>

  • for MariaDB: jdbc:mariadb://<ip address>:<port>/<database name>

  • for MariaDB Galera Cluster: specify the IP address and port for each server in the cluster, separated by commas: jdbc:mariadb://<IP address>:<port>,<IP address>:<port>,...; optionally, append /<database name>

  • for MySQL: jdbc:mysql://<ip address>:<port>/<database name>

  • for Oracle: jdbc:oracle:thin:@<hostname>:<port>:<SID> (using Oracle 12c with PDB, use the SID for the PDB service) or jdbc:oracle:thin:@<hostname>:<port>/<service name>

  • for PostgreSQL, jdbc:postgresql://<ip address>:<port>/<database name>

  • for SQL Server: jdbc:sqlserver://<ip address>:<port>;DatabaseName=<database name>

    You may use Active Directory authentication with Azure SQL Database (see Supporting Active Directory authentication for Azure) or, when Striim or a Forwarding Agent is running in Windows, with SQL Server (see Supporting Active Directory authentication for SQL Server).

  • for Teradata: jdbc:teradata://<ip address>/DBS_PORT=<port>,DATABASE=<database name>

Database Provider Type

java.lang.String

Default

Controls which icon appears in the Flow Designer.

Excluded Tables

java.lang.String

When a wildcard is specified for Tables, you may specify here any tables you wish to exclude from the query. Specify the value as for Tables. For example, to include data from HR_EMPLOYEES and HR_DEPTS but not HRMASTER when reading from SQL Server (since you cannot specify a literal underscore in the Tables string):

Tables='HR%',
ExcludedTables='HRMASTER'

Fetch Size

java.lang.Integer

100

maximum number of records to be fetched from the database in a single JDBC method execution (see the discussion of fetchsize in the documentation for the your JDBC driver)

Password

com.webaction. security.Password

The password for the specified user. See Encrypted passwords.

Query

java.lang.String

SQL statement specifying the data to return. You may query tables, aliases, synonyms, and views.

When Query is specified and Tables is not, the WAEvent TableName metadata field value will be QUERY. When both Query and Tables are specified, the data specified by Query will be returned, and the Tables setting will be used only to populate the TableName field.

If the query includes a synonym containing a period, it must be enclosed in escaped quotes. For example: select * from \"synonym.name\"

If using a query when the output of a DatabaseReader source is the input of a DatabaseWriter target, specify the target table name as the value of DatabaseReader's Tables field.

Quiesce on IL Completion

java.lang.Boolean

False

With the default value of False, you must stop the application manually after all data has been read.

Set to True to automatically quiesce the application after all data has been read (see discussion of QUIESCE in Console commands). When you see on the Apps page that the application is in the Quiescing state, it means that all the data that existed when the query was submitted has been read and that the target(s) are writing it. When you see that the application is in the Quiesced state, you know that all the data has been written to the target(s). At that point, you can undeploy the Database Reader application and then start another application for continuous replication of new data.

Note

Set to True only if all targets in the application support auto-quiesce (see Writers overview).

Return DateTime As

java.lang.String

Joda

Set to String to return timestamp values as strings rather than Joda timestamps. The primary purpose of this option is to avoid losing precision when microsecond timestamps are converted to Joda milliseconds. The format of the string is yyyy-mm-dd hh:mm:ss.ffffff.

Tables

java.lang.String

The table(s) or view(s) to be read. MySQL and Oracle names are case-sensitive, SQL Server names are not. Specify names as <schema name>.<table name> for MySQL and Oracle and as <database name>.<schema name>.<table name> for SQL Server.

If you are using the Query property, specify QUERY as the table name.

For Oracle and SQL Server, you may specify multiple tables and views as a list separated by semicolons or with the following wildcards:

  • %: any series of characters

  • \_: any single character in MySQL or Oracle (note that the underscore does not need to be escaped when using OracleReader)

  • _: any single character in SQL Server

For example, HR.% would read all tables in the HR schema.

When reading from Oracle, _ is a literal underscore, so, for example, HR_% would include HR_EMPLOYEES and HR_DEPTS but not HRMASTER. (Note that when using OracleReader the opposite is the case: _ is the wildcard and \_ is a literal underscore.)

When reading from SQL Server, there is no way to specify a literal underscore.

Username

java.lang.String

the DBMS user name the adapter will use to log in to the server specified in ConnectionURL

Vendor Config

java.lang.string

If the source is Oracle and it uses SSL, specify the required SSL properties (see the notes on SSL Config in OracleReader properties).

The output type is WAevent.

Note

To read from tables in both Oracle CDB and PDB databases, you must create two instances of DatabaseReader, one for each.

The following example creates a cache of data retrieved from a MySQL table:

CREATE TYPE RackType(
  rack_id String KEY,
  datacenter_id String,
  rack_aisle java.lang.Integer,
  rack_row java.lang.Integer,
  slot_count java.lang.Integer
);
CREATE CACHE ConfiguredRacks USING DatabaseReader (
  ConnectionURL:'jdbc:mysql://10.1.10.149/datacenter',
  Username:'username',
  Password:'passwd',
  Query: "SELECT rack_id,datacenter_id,rack_aisle,rack_row,slot_count FROM RackList"
)
QUERY (keytomap:'rack_id') OF RackType;
The following example creates a cache of data retrieved from an Oracle table:
CREATE TYPE CustomerType (
  IPAddress  String KEY,
  RouterId  String,
  ConnectionMode  String,
  CustomerId  String,
  CustomerName  String
);
CREATE CACHE Customers USING DatabaseReader (
  Password: 'password',
  Username: 'striim',
  ConnectionURL: 'jdbc:oracle:thin:@node05.example.com:1521:test5',
  Query: 'SELECT ip_address, router_id, connection_mode, customer_id, customer_name FROM customers',
  FetchSize: 1000
)
QUERY (keytomap:'IPAddress') OF CustomerType;
DatabaseReader data type support and correspondence

JDBC column type

TQL type

Types.BIGINT

java.lang.Long

Types.BIT

java.lang.Boolean

Types.CHAR

java.lang.String

Types.DATE

org.joda.time.LocalDate

Types.DECIMAL

java.lang.String

Types.DOUBLE

java.lang.Double

Types.FLOAT

java.lang.Double

Types.INTEGER

java.lang.Integer

Types.NUMERIC

java.lang.String

Types.REAL

java.lang.Float

Types.SMALLINT

java.lang.Short

Types.TIMESTAMP

org.joda.time.DateTime

Types.TINYINT

java.lang.Short

Types.VARCHARCHAR

java.lang.String

other types

java.lang.String

DatabaseReader can not read Oracle RAW or LONG RAW columns (Oracle Reader can).

In this section: