Database Writer
Writes to one of the following:
Db2 for z/OS versions 12 and 13
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)
SAP HANA
SingleStore (formerly MemSQL)
SQL Server (including Amazon RDS for SQL Server and Azure SQL Database)
Sybase
Yellowbrick
Note
The drivers for MariaDB, MySQL, Oracle, PostgreSQL, and SQL Server are bundled with Striim Platform. For all other databases, the appropriate JDBC driver must be installed as described in Installing third-party drivers in Striim Platform.
Target database setup for Database Writer
For Database Writer to function correctly, the following target database privileges / permissions must be granted to the user specified in the Username property:
Db2 for z/OS:
for DML: SELECT, INSERT, UPDATE, DELETE, REFERENCES on the tables to be written to
for schema evolution: CREATETAB on the target database, ALTER, DROP on the tables to be written to
MariaDB and MySQL:
for DML: SELECT, INSERT, UPDATE, DELETE, REFERENCES on the tables to be written to
for creating the target schema when the source is Database Reader with Create Schema enabled (see Performing initial loads with Database Reader): CREATE ON *.*
for schema evolution (CDDL): CREATE, ALTER, DROP on the target schema
Oracle:
for DML: SELECT, INSERT, UPDATE, DELETE, REFERENCES on the tables to be written to, CREATE SESSION to the user specified in Username
for creating the target schema when the source is Database Reader with Create Schema enabled (see Performing initial loads with Database Reader): CREATE SESSION, CREATE USER, CREATE ANY TABLE, CREATE ANY INDEX, UNLIMITED TABLESPACE
for schema evolution (CDDL): CREATE TABLE, CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE on the target schema, CREATE SESSION to the user specified in Username
PostgreSQL:
for DML: CONNECT, SELECT, INSERT, UPDATE, DELETE, REFERENCES on the tables to be written to
for creating the target schema when the source is Database Reader with Create Schema enabled (see Performing initial loads with Database Reader): CONNECT ON DATABASE and CREATE ON DATABASE on the target database
for schema evolution (CDDL): CONNECT on the target database, CREATE on the target schema, and must own the tables (to allow ALTER and DROP)
SQL Server:
for DML: SELECT, INSERT, UPDATE, DELETE, REFERENCES on the tables to be written to (unnecessary if the user has the db_owner role)
for creating the target schema when the source is Database Reader with Create Schema enabled (see Performing initial loads with Database Reader): enter the following command in a SQL Server client, replacing
<user name>with the name specified in the Username property:ALTER ROLE db_owner ADD MEMBER <user name>;for schema evolution (CDDL): CREATE, ALTER, CONTROL on the target schema (unnecessary if the user has the db_owner role)
Sybase:
for DML: SELECT, INSERT, UPDATE, DELETE on the tables to be written to; to perform any operation on the IDENTITY column, the user must either own the table or have the System Administrator role (sa_role)
Yellowbrick
for all operations: CONNECT on database to be written to
for DML: CONNECT, SELECT, INSERT, UPDATE, DELETE, REFERENCES on the tables to be written to
for creating the target schema when the source is Database Reader with Create Schema enabled (see Performing initial loads with Database Reader): CONNECT ON DATABASE and CREATE ON DATABASE on the target database
for schema evolution (CDDL): CONNECT on the target database, CREATE and USAGE on the target schema, and must own the tables (to allow ALTER and DROP)
Database Writer properties
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 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, If this value is blank, Striim will use the following escape characters for the specified target databases:
| |
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 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 ( |
Connection URL | String |
| |
Excluded Tables | String | If | |
Ignorable Exception Code | String | By default, if the target DBMS returns an error, DatabaseWriter terminates 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' Ignored exceptions will be written to the application's exception store (see CREATE EXCEPTIONSTORE). When replicating from MySQL/MariaDB, Oracle 12c, PostgreSQL, and SQL Server CDC readers, the following generic (that is, not corresponding to any database-specific error code) exceptions can be specified:
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). See also Switching from initial load to continuous replication. | |
JAAS Configuration | String | If you Enable Kerberos authentication, use this property to configure it. To authenticate Oracle using a Kerberos principal, specify For example, To authenticate Oracle using a Kerberos credential cache, specify For example, To authenticate PostgreSQL, specify For example, You must also append the appropriate application name from the specified ' | |
Parallel Threads | Integer | See Creating multiple writer instances (parallel threads). Enabling recovery for the application disables parallel threads. | |
Password | encrypted password | The password for the specified user. See Encrypted passwords. | |
Preserve Source Transaction Boundary | Boolean | False | Set to 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 This setting interacts with CommitPolicy as follows:
|
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 | Specify the name(s) of the table(s) to write to. If the source is Database Reader, its Create Schema property is True, and the target is MariaDB, MariaDB Xpand, MySQL, Oracle, PostgreSQL, or SQL Server, the specified schema(s) and table(s) will be created in the target (see the discussion of Create Schema in Database Reader properties for more details). Otherwise, the table(s) must exist in the target when the application is started, and if a specified target table does not exist, the application will terminate with an error. To skip writes to missing tables without terminating, specify TABLE_NOT_FOUND as an Ignorable Exception Code. MySQL, Oracle, and PostgreSQL names are case-sensitive, SQL Server names are not. Specify names as 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 source.emp,target.emp source.db1,target.db1;source.db2,target.db2 source.%,target.% source.mydatabase.emp%,target.mydb.% source1.%,target1.%;source2.%,target2.% Starting in Striim 4.2.0.4, you may use the If some of the source table names are mixed-case and the target database's table names are case-sensitive, put the wildcard for the target in double quotes, for example, In Db2 for z/OS, table and column names may not exceed 30 characters. See Mapping columns for additional options. | |
Username | String | Specify 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 terminate.
|
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.
Database Writer sample application
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;Creating the checkpoint table
When recovery is not enabled, there is no need to create the checkpoint table.
When recovery is enabled, DatabaseWriter uses the table specified by the CheckpointTable property to store information used to ensure that there are no missing or duplicate events after recovery (see Recovering applications). Before starting DatabaseWriter with recovery enabled, use the following DDL to create the table, and grant insert, update, and delete privileges to the user specified in the Username property. The table and column names are case-sensitive, do not change them.
Db2 for z/OS:
CREATE TABLE CHKPOINT ( ID VARCHAR(100) PRIMARY KEY NOT NULL, SOURCEPOSITION BLOB, PENDINGDDL DECIMAL(1), DDL CLOB);
HP NonStop SQL/MX (replace <catalog>.<schema> with the catalog and schema in which to create the table):
CREATE TABLE <catalog>.<schema>.CHKPOINT ( ID VARCHAR(100) NOT NULL NOT DROPPABLE PRIMARY KEY, SOURCEPOSITION VARCHAR(30400), PENDINGDDL NUMERIC(1), DDL VARCHAR(2000) ) ATTRIBUTES BLOCKSIZE 32768;
MySQL:
CREATE TABLE CHKPOINT ( id VARCHAR(100) PRIMARY KEY, sourceposition BLOB, pendingddl BIT(1), ddl LONGTEXT);
Oracle:
CREATE TABLE CHKPOINT ( ID VARCHAR2(100) PRIMARY KEY, SOURCEPOSITION BLOB, PENDINGDDL NUMBER(1), DDL CLOB);
PostgreSQL:
create table chkpoint ( id character varying(100) primary key, sourceposition bytea, pendingddl numeric(1), ddl text);
SQL Server:
CREATE TABLE CHKPOINT ( id NVARCHAR(100) PRIMARY KEY, sourceposition VARBINARY(MAX), pendingddl BIT, ddl VARCHAR(MAX));
Sybase
CREATE TABLE CHKPOINT ( id VARCHAR(100) PRIMARY KEY NOT NULL, sourceposition IMAGE, pendingddl NUMERIC, ddl TEXT);
Database Writer data type support and correspondence
Use the following when the input stream is of a user-defined type. (See the Change Data Capture Guide when the input is the output of a CDC or DatabaseReader source.)
Most Striim data types can map to any one of several column types in the target DBMS.
See also Data type support & mapping for schema conversion & evolution.
TQL type | Cassandra | Db2 for z/OS | MariaDB / MySQL | Oracle |
|---|---|---|---|---|
java. lang. Byte | blob |
|
|
|
java. lang. Double | double | DOUBLE |
|
|
java. lang. Float | float | REAL | FLOAT |
|
java. lang. Integer | int | INTEGER |
|
|
java. lang. Long | bigint | BIGINT |
|
|
java. lang. Short | int | SMALLINT |
|
|
java. lang. String | varchar |
|
|
|
org.joda. time. DateTime | timestamp |
|
|
|
TQL type | PostgreSQL | SAP HANA | SQL Server |
|---|---|---|---|
java. lang. Byte | not supported |
|
|
java. lang. Double | double precision |
| FLOAT |
java. lang. Float | float |
|
|
java. lang. Integer |
| INTEGER |
|
java. lang. Long |
| BIGINTEGER |
|
java. lang. Short |
| SMALLINT |
|
java. lang. String |
|
|
|
org.joda. time. DateTime |
|
|
|
Runtime considerations for Database Writer
In Db2 for z/OS, table and column names may not exceed 30 characters.