Striim 3.9.6 documentation

Database Writer

Writes to one of the following:

Drivers for Cassandra, the Cosmos DB Cassandra API, and PostgreSQL are included with the Striim server. For the others, the appropriate JDBC driver must be installed as described in Installing third-party drivers.

property

type

default value

notes

Batch Policy

java.lang. 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 -1.

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

Checkpoint Table

java.lang. 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

java.lang. 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

java.lang. 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 -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

java.lang. String

retryInterval=30, maxRetries=3

The connection retry policy includes retryInterval and maxRetries. 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

java.lang. String

  • for Cassandra: jdbc:cassandra://<host name>:<port>/<keyspace>?ssl=true (see Notes on writing to Cassandra with DatabaseWriter)

  • for HP NonStop SQL/MX: jdbc:t4sqlmx://<IP address>:<port> or jdbc:t4sqlmx://<IP address>:<port>/catalog=<catalog name>;schema=<schema 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:posgresql://<ip address>:<port>/<database name>

  • for SQL Server: jdbc:sqlserver://<ip address>:<port>;DatabaseName=<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

java.lang. String

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

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

Ignorable Exception Code

java.lang. 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.

For details of the individual ignored events, search striim.server.log for VendorExceptionCode (see Reading log files). Alternatively, to capture the ignored exceptions, Writing exceptions to a WActionStore.

Parallel Threads

java.lang. Integer

see Creating multiple writer instances

Enabling recovery for the application disables parallel threads.

Password

com. webaction. security. Password

The password for the specified user. See Encrypted passwords.

Preserve Source Transaction Boundary

java.lang. 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 a Cassandra or 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).

Tables

java.lang. 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.

If a specified target table does not exist, DatabaseWriter 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 wildcards for the table names, but not for the schema or database. For example:

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

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.

See Mapping columns for additional options.

Username

java.lang. String

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

Vendor Configuration

java.lang. String

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

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: 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 (
  header:yes
)
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',
  Username:'striim',
  Password:'******',
  Tables: 'mydb.testtable'
) INPUT FROM PosDataStream;