Adaptive Server Enterprise Output Adapter

Adapter type: SAP_ase_out. The Adaptive Server Enterprise (ASE) Output adapter reads data from an Event Stream Processor stream and writes it to the Adaptive Server.

The ASE Output Adapter is statically linked to the Open Client™ libraries, it uses the bulk insert API from the Open Client Bulk-Library to optimally load data into the Adaptive Server database. It supports parallel load for tables that are partitioned using round-robin partitioning, but does not support guaranteed delivery. Under normal operation of the system, all Event Stream Processor messages are stored in the database. In a production environment, the Adaptive Server database server runs on a separate machine than the Event Stream Processor engine. Network bandwidth can affect message flow throughput.

The stream or window attached to the ASE Output adapter might have a schema where the order or names of the columns are different from the order or names of the columns in the target Adaptive Server Enterprise table. In such a case, specify the optional permutation adapter parameter, which describes the mapping between the stream or window columns and the columns in the database. All non-nullable columns that do not have a default value defined in the target Adaptive Server database table must have a mapping in the permutation parameter. For nullable columns that do not have a mapping provided, a default value is used, if one is defined in the database; otherwise, a null value is used.

When the adapter gets a request to shutdown, it first processes the data it received before that request. The adapter gets the shutdown signal either when the project is stopping, the ESP server is shutting down, or the adapter itself is stopped. Because the amount of time it takes for the adapter to shutdown depends on the amount of data being processed, the adapter may appear to be slow shutting down.

By default, the adapter performs inserts, updates, and deletes. To perform only inserts, or to change all updates to inserts, use the optional dataWarehouseMode adapter parameter.

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

Here is a sample service entry for the ASE Output adapter to connect to the Adaptive Server database using an Open Client connection:

 <Service  Name="ASE"  Type="DB">
     <Parameter Name="DriverLibrary">esp_db_ocs_lib</Parameter>
     <Parameter Name="Host">raprh5dev</Parameter>
     <Parameter Name="Port">15000</Parameter>
     <Parameter Name="User">sa</Parameter>
     <Parameter Name="Password"  />
     <Parameter Name="AppName">ASEOutputAdapter</Parameter>
    </Service>

Property Label	Description
DB Service Name	Property ID: service Type: `string` (Required for adapter operation and schema discovery) The name of the service entry with connection information for the Adaptive Server database into which information is loaded. See above for a sample service entry for the adapter. Service entries are specified in the Event Stream Processor service.xml file. This adapter uses only Open Client drivers to connect to the Adaptive Server database. See the Administrators Guide for more information. No default value.
Target DB Table Name	Property ID: table Type: `tables` (Required for adapter operation; optional only if you are using schema discovery) A string value representing the name of the Adaptive Server database table to load data into. No default value.
Include Base Content	Property ID: outputBase Type: `boolean` (Optional) Determines whether to process the base data of the window or stream to which the adapter is connected. The adapter processes the base data of the window or stream to which it is attached. Default value is false.
Only Base Content	Property ID: onlyBase Type: `boolean` (Advanced) If true, the adapter processes only the base data of the window or stream to which it is attached. No further message flow is processed. Default value is false.
Field Mapping	Property ID: permutation Type: `permutation` (Advanced) The permutation list maps the Event Stream Processor column names to the database schema column names in the specified table. If you do not specify a permutation, ensure that the Event Stream Processor stream or window columns exactly match the database schema of the destination table. For example, both must have the same order, same number of columns, and compatible datatypes. If the Data Warehouse Mode property is OFF, and you want to specify a permutation, include a mapping for at least one column of the primary key of the ESP window attached to the adapter. Without mapping at least one primary key column, the adapter fails to start. The format for this property is: <esp_columnname>=<database_columnname>:<esp_columnname>=<database_columnname> Use a colon to separate mappings. No default value.
Bulk Batch Size	Property ID: bulkBatchSize Type: `int` (Advanced) Specifies the number of rows that can be inserted in a table partition before a commit occurs. Value must be a multiple of the bulkInsertArraySize parameter. Default value is 10000.
Bulk Insert Array Size	Property ID: bulkInsertArraySize Type: `int` (Advanced) Specifies the number of rows that are simultaneously inserted into a table partition. Default value is 1000.
Idle Buffer Write Delay in Msec	Property ID: idleBufferWriteDelayMSec Type: `int` (Advanced) Specifies the number of milliseconds that a database table partition may sit idle with uncommitted data available for insert. Default value is 1000.
Buffer Age Limit in Msec	Property ID: bufferAgeLimitMSec Type: `int` (Advanced) Forces the loading of any data that has been in existence longer than the time limit. Specify a value in milliseconds between 1 and 65535. Default value is 10000.
Data Warehouse Mode	Property ID: dataWarehouseMode Type: `choice` (Advanced) Specifies the type of data warehousing mode the adapter uses. Valid values are: ON – updates are converted to inserts, and deletes are ignored. INSERTONLY – only inserts are processed, and updates and deletes are ignored. OFF – (default) all inserts, updates and deletes are processed as such. If you want to specify a field mapping, or permutation, map at least one column of the primary key of the ESP window attached to the adapter. Without mapping of at least one primary key column, the adapter fails to start.
Timestamp Column Name	Property ID: timestampColumnName Type:`string` (Advanced) If an Adaptive Server table column name is provided, the time at which the record is added to the bulk array is stored in that column of the Adaptive Server record. If this property is empty, there is no timestamp stored. The timestamp is always in UTC rather than the ESP Server’s local time zone. No default value.
Timezone For Statistics	Property ID: timezoneForStats Type: `string` (Advanced) Controls the time zone in which midnight statistics for the adapter roll over. For example, if the adapter is left at its default value of GMT, midnight statistics reset at midnight GMT. This setting does not affect any other dates processed or returned by this adapter. Default value is GMT.

Custom Statistics

The Adaptive Server Enterprise Output adapter maintains custom statistics to show its status and to track its loading activities. Enable the time granularity option in the project configuration (ccr) file to get these statistics reported by the _ESP_Adapter_Statistics metadata stream:

Rows	Bytes	Average Rows
Received in the last hour Received since midnight Loaded in the last hour Loaded since midnight Skipped* in the last hour Skipped* since midnight	Received since midnight Loaded since midnight Skipped since midnight	Per second received over the last minute Per second received over the last hour

* A row is skipped when the opcode does not match the adapter's current warehousing mode. For example, if the data warehousing mode is INSERTONLY, and the adapter passes in a delete or an update, this results in a skipped row.

The "since midnight" statistics reset after midnight in the time zone specified by the timezoneForStats adapter property. If no value is set, the default behavior is reset at midnight GMT.