Striim Cloud4.1.0 documentation

Database Writer

Writes to one of the following:

• HP NonStop SQL/MX (and SQL/MP via aliases in SQL/MX)

• MemSQL

• Oracle

• PostgreSQL

• SAP HANA

• SQL Server

• Sybase

Note

property

type

default value

notes

Batch Policy

String

eventcount:1000, interval:60

The batch policy includes eventcount and interval (see Setting output names and rollover / upload policies for syntax). Events are buffered locally on the Striim server and sent as a batch to the target every time either of the specified values is exceeded. When the app is stopped, any remaining data in the buffer is discarded. To disable batching, set to BatchPolicy:'-1'.

With the default setting, events will be sent every 60 seconds or sooner if the buffer accumulates 1,000 events.

Bidirectional Marker Table

String

When performing bidirectional replication, the fully qualified name of the marker table (see Bidirectional replication). This setting is case-sensitive.

CDDL Action

String

Process

Checkpoint Table

String

CHKPOINT

The table where DatabaseWriter will store recovery information when recovery is enabled. See Creating the checkpoint table below for DDL to create the table. Multiple instances of DatabaseWriter may share the same table. If the table is not in the Oracle or SQL/MX schema being written to, or the same MySQL or SQL Server database specified in the connection URL, specify a fully qualified name.

Column Name Escape Sequence

String

When the input stream of the target is the output of a DatabaseReader, IncrementalBatchReader, or SQL CDC source, you may use this property to specify which characters Striim will use to escape column names that are on the List of reserved keywords. You may specify two characters to be added at the start and end of the name (for example, [] ), or one character to be added at both the start and end.

If this value is blank, Striim will use the following escape characters for the specified target databases:

• Oracle: " (ASCII / UTF-8 22)

• MySQL:  (ASCII / UTF-8 60)

• PostgreSQL: " (ASCII / UTF-8 22)

• SQL Server: []

Commit Policy

String

eventcount:1000, interval:60

The commit policy controls how often transactions are committed in the target database. The syntax is the same as for BatchPolicy. CommitPolicy values must always be equal to or greater than BatchPolicy values. To disable CommitPolicy, set to CommitPolicy:'-1'.

If BatchPolicy is disabled, each event is sent to the target database immediately and the transactions are committed as specified by CommitPolicy.

If BatchPolicy is enabled and CommitPolicy is disabled, each batch is committed as soon as it is received by the target database.

If BatchPolicy and CommitPolicy are both disabled, each event received by DatabaseWriter will be committed immediately. This may be useful in development and testing, but is inappropriate for a production environment.

Connection Retry Policy

String

retryInterval=30, maxRetries=3

With the default setting, if a connection attempt is unsuccessful, the adapter will try again in 30 seconds (retryInterval. If the second attempt is unsuccessful, in 30 seconds it will try a third time (maxRetries). If that is unsuccessful, the adapter will fail and log an exception. Negative values are not supported.

Connection URL

String

• 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 MemSQL: same as MySQL

• 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 SAP HANA: jdbc:sap://<ip address>:<port>/?databaseName=<database name>&currentSchema=<schema 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>

When writing to MySQL, performance may be improved by appending ?rewriteBatchedStatements=true to the connection URL (see Configuration Properties and MySQL and JDBC with rewriteBatchedStatements=true).

Excluded Tables

String

When a wildcard is specified for Tables, you may specify here any tables you wish to exclude from the query. Specify the value exactly as for Tables. For example, to include data from all tables whose names start with HR except HRMASTER:

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

Ignorable Exception Code

String

By default, if the target DBMS returns an error, DatabaseWriter crashes the application. Use this property to specify errors to ignore, separated by commas. For example, to ignore Oracle ORA-00001 and ORA-00002, you would specify:

IgnorableExceptionCode: '1,2'


When an ignorable exception occurs, Striim will write an "Ignoring VendorExceptionCode" message to the log, including the error number, and increment the "Number of exceptions ignored" value for the target.

To view the number of exceptions ignored in the web UI, go to the Monitor page, click the application name, click Targets, and click More Details next to the target.

Optionally, to capture the ignored exceptions, Writing exceptions to a WActionStore.

When replicating from MySQL/MariaDB, Oracle 12c, PostgreSQL, and SQL Server CDC readers, the following three generic (that is, not corresponding to any database-specific error code) exceptions can be specified:

• DUPLICATE_ROW_EXISTS: could not insert a row in the target because an identical row it already exists

• NO_OP_UPDATE: could not update a row in the target (typically because there was no corresponding primary key)

• NO_OP_PKUPDATE: could not update the primary key of a row in the target (typically because the "before" primary key could not be found); not supported when source is PostgreSQLReader

• NO_OP_DELETE: could not delete a row in the target (typically because there was no corresponding primary key)

These exceptions typically occur when other applications besides Striim are writing to the target database. The unwritten events will be captured to the application's exception store, if one exists (see CREATE EXCEPTIONSTORE).

Integer

Enabling recovery for the application disables parallel threads.

Preserve Source Transaction Boundary

Boolean

False

Set to True to ensure that all operations in each transaction are committed together.

When the target's input stream is the output of an HP NonStop source or when writing to an HP NonStop database, this setting must be False.

This setting interacts with CommitPolicy as follows:

When PreserveSourceTransactionBoundary is True and CommitPolicy is disabled, each transaction will be committed when all of its operations have been received. For example, if you have a series of three transactions containing 300, 400, and 700 operations, there will be three commits.

When PreserveSourceTransactionBoundary is True and CommitPolicy has a positive EventCount value, that value is the minimum number of operations included in each commit. For example, if CommitPolicy includes EventCount=1000 and you have a series of three transactions containing 300, 400, and 700 operations, there will be one commit, after the third transaction (because the first two transactions had a total of only 700 operations, less than the EventCount value).

SSL Config

String

When the target is Oracle and it uses SSL, specify the required SSL properties (see the notes on SSL Config in Oracle Reader properties).

Statement Cache Size

Integer

50

The number of prepared statements that Database Writer can cache. When the number of cached statements exceeds this number, the least recently used statement is dropped. When a DatabaseWriter Oracle target in the same application fails with the error "ORA-01000: maximum open cursors exceeded," increasing this value may resolve the problem.

Tables

String

The name(s) of the table(s) to write to. The table(s) must exist in the DBMS and the user specified in Username must have insert permission.

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, PostgresQL, and SQL Server (but see the note below about SQL Server source table names)..

If a specified target table does not exist, the application will crash with an error. To skip writes to missing tables without crashing, specify TABLE_NOT_FOUND as an Ignorable Exception Code.

When the target's input stream is a user-defined event, specify a single table.

When the input stream of the target is the output of a DatabaseReader, IncrementalBatchReader, or SQL CDC source (that is, when replicating data from one database to another), it can write to multiple tables. In this case, specify the names of both the source and target tables. You may use the % wildcard only for tables, not for schemas or databases. If the reader uses three-part names, you must use them here as well. Note that SQL Server source table names must be specified in three parts when the source is Database Reader or Incremental Batch Reader (database.schema.%,schema.%) but in two parts when the source is MS SQL Reader or MS Jet (schema.%,schema.%). Examples:

source.emp,target.emp
source.db1,target.db1;source.db2,target.db2
source.%,target.%
source.mydatabase.emp%,target.mydb.%
source1.%,target1.%;source2.%,target2.%


See Mapping columns for additional options.

String

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

Vendor Configuration

String

When the target is SQL Server, the following configuration options are supported. If the target table contains an identity, rowversion, or timestamp column and you do not specify the relevant option(s), the application will crash.

• enableidentityInsert=true: for insert operations only (not updates), replicate identity column values from the source to the target using identity inserts

• excludeColTypes={identity|rowversion|timestamp}: ignore any identity, rowversion, or timestamp values in the source and have the target database supply values; to specify multiple options, separate them with a comma, for example, exludeColTypes=identity,rowversion

• To combine both options, separate them with a semicolon. For example, enableidentityInsert=true; exludeColTypes=timestamp would replicate identity column values and have the target database supply timestamp values.

Note

PostgreSQL does not allow NULL (\0x00) character values (not to be confused with database NULLs) in text columns. If writing to PostgreSQL from a source that contains such values, Contact Striim support for a workaround.

The following example uses an input stream of a user-defined type. When the input is the output of a CDC or DatabaseReader source, see Replicating data from one Oracle instance to another.

The following TQL will write to a MySQL table created as follows in MySQL database mydb:

CREATE TABLE mydb.testtable (merchantId char(36), dateTime datetime, amount decimal(10,2), zip char(5));

The striim user must have insert permission on mydb.testtable.

CREATE SOURCE PosSource USING FileReader (
directory:'Samples/PosApp/AppData',
wildcard:'PosDataPreview.csv',
positionByEOF:false
)
PARSE USING DSVParser (
)
OUTPUT TO RawStream;

CREATE CQ CsvToPosData
INSERT INTO PosDataStream partition by merchantId
SELECT TO_STRING(data[1]) as merchantId,
TO_DATEF(data[4],'yyyyMMddHHmmss') as dateTime,
TO_DOUBLE(data[7]) as amount,
TO_STRING(data[9]) as zip
FROM RawStream;

CREATE TARGET WriteMySQL USING DatabaseWriter (
connectionurl: 'jdbc:mysql://192.168.1.75:3306/mydb',
) INPUT FROM PosDataStream;`