Database Reader

Returns data from a JDBC query against one or more tables in one of the following:

HP NonStop SQL/MX (and SQL/MP via aliases in SQL/MX)
MariaDB (including Amazon RDS for MariaDB, MariaDB Galera Cluster, MariaDB Xpand, and SkySQL)
MySQL (including Amazon Aurora for MySQL, Amazon RDS for MySQL, Azure Database for MySQL, and Google Cloud SQL for MySQL)
Oracle (including Amazon RDS for Oracle)
PostgreSQL (including Amazon Aurora with PostgreSQL compatibility, Amazon RDS for PostgreSQL, Azure Database for PostgreSQL, Azure Database for PostgreSQL - Flexible Server, Google AlloyDB for PostgreSQL, and Google Cloud SQL for PostgreSQL)
SQL Server (including Amazon RDS for SQL Server and Azure SQL Database)
Sybase
Teradata

Note

Sources supported by Database Reader are also supported by Incremental Batch Reader.

If the connection to the database is interrupted, the application will halt.

For all databases, when this adapter is deployed to a Forwarding Agent, the appropriate JDBC driver must be installed as described in Installing third-party drivers in the Forwarding Agent.

If you are using Database Reader to perform an initial load before running an application using a CDC reader, enable CDC on the source and perform the setup tasks for the CDC reader before starting the initial load.

Database Reader properties

property	type	default value	notes
Connection URL	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>` To use an Azure private endpoint to connect to Azure Database for MySQL, see Specifying Azure private endpoints in sources and targets. 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>`; if one or more source tables contain LONG or LONG RAW columns, append `?useFetchSizeWithLongColumn=true` for PostgreSQL, `jdbc:postgresql://<ip address>:<port>/<database name>` for SQL Server: `jdbc:sqlserver://<ip address>:<port>;DatabaseName=<database name>` or `jdbc:sqlserver://<ip address>\\<instance name>:<port>;DatabaseName=<database name>` for Sybase: `jdbc:jtds:sybase:<ip address>:<port>/<database name>` for Teradata: `jdbc:teradata://<ip address>/DBS_PORT=<port>,DATABASE=<database name>`
Database Provider Type	String	Default	Controls which icon appears in the Flow Designer.
Excluded Tables	String		If `Tables` uses a wildcard, data from any tables specified here will be omitted. Multiple table names (separated by semicolons) and wildcards may be used exactly as for `Tables`.
Fetch Size	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)
JAAS Configuration	String		This is not supported in Striim Cloud.
Password	encrypted password		The password for the specified user. See Encrypted passwords.
Query	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. The query may not contain single-line quotes (of the format `--comment`). It may contain multi-line comments (of the format `/* comment */`).
Quiesce on IL Completion	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 initial load application and then start another application for continuous replication of new data.Console commands Note Set to True only if all targets in the application support auto-quiesce (see Writers overview).Writers overview
Return DateTime As	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.
SSL Config	String		If the source is Oracle and it uses SSL, specify the required SSL properties (see the notes on SSL Config in Oracle Reader properties).
Tables	String		The table(s) or view(s) to be read. MySQL, Oracle, and PostgreSQL names are case-sensitive, SQL Server names are not. Specify names as `<database name>.<table name>` for MySQL, `<schema name>.<table name>` for Oracle and PostgresQL, and `<database name>.<schema name>.<table name>` for SQL Server. You may specify multiple tables and views as a list separated by semicolons or with the `%` wildcard. For example, `HR%` would read all the tables whose names start with HR. You may use the `%` wildcard only for tables, not for schemas or databases. The wildcard is allowed only at the end of the string: for example, `mydb.prefix%` is valid, but `mydb.%suffix` is not. If you are using the Query property, specify QUERY as the table name.
Username	String		the DBMS user name the adapter will use to log in to the server specified in ConnectionURL For all databases, this user must have SELECT permission or privileges on the tables specified in the Tables property. For Oracle, this user must also have SELECT privileges on DBA_TAB_COLS and ALL_COLL_TYPES.

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.

Database Reader sample code

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	notes
Types.ARRAY	java.lang.String
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	For MySQL, if the source tables contain columns of this type, append `?tinyInt1isBit=false` to the connection URL (`jdbc:mysql://<ip address>:<port>/<database name>?tinyInt1isBit=false`).
Types.VARCHARCHAR	java.lang.String
other types	java.lang.String

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

Sample Database ReaderWAEvent

For the following row:

id  first_name  last_name  phone  street          city          state  zip_code
1   Deborah     Burks      NULL   9273 Thorne AV  Orchard Park  NY     14127

The WAEvent would be similar to:

WAEvent{
  data: [1,"Deborah","Burks",null,"9273 Thorne AV","Orchard Park","NY","14127"]
  metadata: {"TableName":"BikeStores.sales.customers","ColumnCount":8,
    "OperationName":"SELECT","OPERATION_TS":1681412863364}
  userdata: null
  before: null
  dataPresenceBitMap: "fwM="
  beforePresenceBitMap: "AAA="
  typeUUID: {"uuidstring":"01edda2e-77f7-9b21-83c2-8e859085da65"}
};

The operation name for Database Reader WAEvents is always SELECT.

Database Reader

Note

Database Reader properties

Note

Note

Database Reader sample code

DatabaseReader data type support and correspondence

Sample Database ReaderWAEvent

Search results