SAP Sybase Adaptive Server Enterprise Output Adapter

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

The SAP Sybase 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 SAP Sybase ASE 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 SAP Sybase ASE 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 SAP Sybase 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 SAP Sybase ASE 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 SAP Sybase ASE 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.

Prerequisites

Ensure that the default ASE database of the user specified in the service.xml file is the same ASE database in which the table specified in the table property is located.

Here is a sample service entry for the SAP Sybase ASE Output adapter to connect to the SAP Sybase ASE 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>

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

Property Label	Description
Database 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 SAP Sybase ASE 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 SAP Sybase ASE database. See the Configuration and Administration Guide for more information. No default value.
Target Database 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 SAP Sybase ASE database table to load data into. No default value.
Include Base Content	Property ID: outputBase Type: `boolean` (Optional) If set to true, the adapter outputs the initial stream contents in addition to stream updates. If this option is enabled and the adapter is running in GD mode, once the adapter has done a GD commit on the entire base data, the ESP Server does not redeliver the base data on adapter restart and only sends deltas that are saved for delivery. The 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. The 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 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. 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 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. ARCHIVE – updates and deletes become inserts. The opcode must be stored as a column in the database table. See the opcodeColumnName property. This column must be of characters type. The required length for the opcode column is one. Use this mode if you wish to store incoming data as a series of events. 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 SAP Sybase ASE table column name is provided, the time at which the record is added to the bulk array is stored in that column of the SAP Sybase ASE 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.
Operation Code Column Name	Property ID: opcodeColumnName Type: `string` (Required if adapter is running in the ARCHIVE data warehouse mode; advanced for other data warehouse modes) The name of the database column that the adapter uses to store the operation code. If specified for other modes, the opcode is written into the specified column.
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. The default value is GMT.
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.
Runs Adapter in GD Mode	Property ID: enableGDMode Type: `boolean` (Advanced) Specifies whether the adapter runs in guaranteed delivery (GD) mode. GD ensures that data continues to be processed in the case that the ESP Server fails, or the destination (third-party server) fails or does not respond for a long time. See Guaranteed Delivery in the Event Stream Processor Programmers Guide for details on enabling GD for your project. The default value is false.
Enable the Adapter Internal Cache Mode	Property ID: enableGDCache Type: `boolean` (Advanced) If set to true, only rows that can be recovered (that is, checkpointed) by the ESP Server on restart are sent to the database. Other rows are cached internally by the adapter. When this option is enabled, you may see a significant increase in latency depending on how frequently the attached stream delivers checkpoint messages. Streams support three modes related to GD: GD not supported, GD supported, and GD supported with checkpoint messages. This setting is ignored if the attached stream does not support GD and does not deliver checkpoint message. The default value is true.
GD Batch Size	Property ID: gdBatchSize Type: `int` (Advanced) Specify after how many message blocks (transactions or envelopes) the adapter issues a commit command to the database and a GD commit to the stream to which the adapter is attached. The default value is 10. Increasing this value improves performance at the expense of latency (specifically, how soon rows are available in the database to be consumed by other applications.) Increasing this value also increases memory consumption in the ESP Server because the uncommitted rows need to be preserved for redelivery in case of failure.

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.