Adapter type: db_out. The Database Output adapter sends data to a database table.
You can truncate the table when the adapter starts using the truncateTable property. The required properties depend on the database type you are connecting to. The supported databases for JDBC are Adaptive Server Enterprise, Microsoft SQL Server, IBM DB2, Oracle, and KDB. The supported databases for ODBC are Adaptive Server Enterprise, Microsoft SQL Server, IBM DB2, Oracle, 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 Administrators Guide for more information on configuring database connections using 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.
If you use the CCL ATTACH ADAPTER statement to attach an adapter, you must supply the adapter type.
| Property Label | Property ID | Type | Description |
|---|---|---|---|
| Database Service | service | string | (Required) Name of database service as defined in the service.xml file. No default value. |
| Date Format | dateFormat | string | (Advanced) Format string for parsing date values. Default value is %Y-%m-%dT%H:%M:%S. |
| Timestamp Format | timestampFormat | string | (Advanced) Format string for parsing timestamp values. Default value is %Y-%m-%dT%H:%M:%S. |
| Field Mapping | permutation | permutation | (Advanced)
Maps the internal ESP fields to the external application or
display fields. No default value. For Oracle 11g and DB2
9.7, 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 Adaptive Server Enterprise 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 | onlyBase | boolean | (Advanced) Send only the initial contents of the stream, once. Default value is false. |
| Batch Limit | batchLimit | 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 | datalocation | string | (Advanced) Looks up properties in the project configuration. No default value. |
| Output Table Name (runtime) | table | tables | (Optional) Name of the table to push data to. No default value. |
| Include Base Content | outputBase | boolean | (Optional) Output initial stream contents in addition to stream updates. Default value is false. |
| Truncate the Database Table | truncateTable | boolean | (Optional) Truncate the database table, then populate with streaming data. Default value is false. |
PropertySet |
propertyset | string | (Advanced) Specifies the name of the property set from the project configuration 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. |