Database Output Adapter

Adapter type: db_out. The Database Output adapter sends data to a database table.

When the adapter starts, you can truncate the table using the truncateTable property. The required properties depend on the database type you are connecting to. The supported databases for JDBC are SAP Sybase ASE, Microsoft SQL Server, IBM DB2, Oracle, and KDB. The supported databases for ODBC are SAP Sybase ASE, Microsoft SQL Server, IBM DB2, Oracle, SAP Sybase IQ, SQL Anywhere, TimesTen, MySQL 5.x, and PostgreSQL.

The service.xml file contains service definitions and the properties required for a database connection. For the service definition name, consult the person responsible for configuring and maintaining the service.xml file. See the Configuration and Administration Guide for more information on configuring database connections using the service.xml file.

Attention: The Oracle ODBC driver does not support SQL_C_SBIGINT/SQL_C_UBIGINT parameters, causing errors when the Database Output adapter tries to write long and interval Event Stream Processor types to bigint type columns. To successfully use the Oracle and the TimesTen ODBC drivers with the Database Output adapter, add this parameter "<Parameter Name ="WriteBigIntAsChar" > true < /Parameter >" to the service.xml file.

An example of specifying a different date format is when inserting a date column into an Oracle Date column. The default Oracle date format is: 04-Apr-1964 17:12:00, so you specify that the dateFormat parameter is d-%b-%Y %H:%M:%S.

Important: Enable the "Server side prepare" option in the ODBC configuration to ensure that the Database Output adapter writes successfully to the PostgreSQL database using the ODBC driver. For more information on this option, consult your ODBC documentation.

If you use the CCL ATTACH ADAPTER statement to attach an adapter, you must supply the adapter type.

Important: For an SAP Sybase ASE database, enable the ddl in tran option on the temporary database (tempdb) to discover all tables when using schema discovery. Then, update the ESP Server by performing a checkpoint on tempdb or restarting the database instance. For more information on the ddl in tran option, consult your SAP Sybase ASE documentation.

Property Label	Description
Database Service	Property ID: service Type: `string` (Required for adapter operation and schema discovery) Name of database service as defined in the service.xml file. No default value.
Date Format	Property ID: dateFormat Type: `string` (Advanced) Format string for parsing date values. Default value is `%Y-%m-%dT%H:%M:%S`.
Timestamp Format	Property ID: timestampFormat Type: `string` (Advanced) Format string for parsing timestamp values. Default value is `%Y-%m-%dT%H:%M:%S`.
Field Mapping	Property ID: permutation Type: `permutation` (Advanced) Maps the internal ESP fields to the external application or display fields. No default value. For Oracle 11g, DB2 9.7, and SAP HANA, the metadata services return results in uppercase, so ensure the database column key in the permutation is in uppercase. For example, in CCL, this command does not work: permutation= 'Subject=subject:c_string=c_string' but this one does: permutation= 'Subject=SUBJECT:c_string=C_STRING' For SAP Sybase ASE 15.5 and Microsoft SQL Server 2008, the metadata results are the same as the case in the defined column name, unless the name is modified in the SELECT statement.
Only Base Content	Property ID: onlyBase Type: `boolean` (Advanced) Send only the initial contents of the stream, once. Default value is false.
Batch Limit	Property ID: batchLimit Type: `uint` (Advanced) Number of records to process as a batch. Default value is 1. Using UPSERT with batch processing may negatively impact performance since this process may be terminated if the adapter receives a delete. The resolution of an UPSERT to either INSERT or UPDATE based on stream content gives you less control over the grouping of these operations. However, frequently changing between operations (INSERT, UPDATE, DELETE, and UPSET) reduces the optimization of using batch processing.
Data Location	Property ID: datalocation Type: `string` (Advanced) Looks up properties in the project configuration. No default value.
Output Table Name (runtime)	Property ID: table Type: `tables` (Optional) A string value representing the name of the table to push data to. No default value.
Include Base Content	Property ID: outputBase Type: `boolean` (Optional) Output initial stream contents in addition to stream updates. Default value is false.
Truncate the Database Table	Property ID: truncateTable Type: `boolean` (Optional) Truncate the database table, then populate with streaming data. Default value is false.
PropertySet	Property ID: propertyset Type: `string` (Advanced) Specifies the name of the property set. Property sets are reusable sets of properties that are stored in the project configuration file. Using these sets allows you to move adapter configuration properties out of the CCL file and into the CCR file. If you specify the same properties in the project configuration file and the ATTACH ADAPTER statement, the values in the property set override the values defined in the ATTACH ADAPTER statement. No default value.

The Database Output adapter has these limitations:

The output table must exist.
Each row translates to an SQL statement, therefore updates are slow.
If you are using a memory store, you can perform only UPSERT, UPDATE, and DELETE on data that is in the stream.
If a batch contains a bad row, the entire batch is discarded, the bad rows statistic is incremented, and an error message is logged to the ESP Server log file.