SAP Sybase IQ Output Adapter

Adapter type: sybase_iq_out. The SAP Sybase IQ Output adapter reads data from Event Stream Processor and loads it into the SAP Sybase IQ database.

Prerequisite

Before running the adapters, create a directory for the primary file location and one for the overflow file location.

To be able to load data using the SAP Sybase IQ Output adapter, you need to:

Enable the allow_read_client_file option on the database into which the data is being loaded.
Grant READCLIENTFILE privileges to the user that the adapter uses to connect to the SAP Sybase IQ database.
Source the SAP Sybase IQ shell script (IQ-15_4.sh or IQ-15_4.csh) from your SAP Sybase IQ installation or SAP Sybase IQ client install before running the ESP Server. Then source the Event Stream Processor shell script ($ESP_HOME/../SYBASE.sh or $ESP_HOME/../SYBASE.csh).
On UNIX and Windows systems, install an SAP Sybase IQ ODBC driver using an SAP Sybase IQ client installation process.
On UNIX systems, you also need to install an ODBC driver manager. You can use the ODBC driver manager that is shipped with SAP Sybase IQ. See the SAP Sybase IQ documentation for more information.
On UNIX systems, Event Stream Processor expects your ODBC driver manager library to be called libodbc.so.1. Ensure that your driver manager library has this name, or create a symbolic link from libodbc.so.1 to your ODBC driver manager library.
On UNIX systems, SAP recommends that you upgrade to version 2.3.0 or later of unixODBC. If you are using a version earlier than 2.3.0, set a parameter for the driver that instructs the database manager not to synchronize database access. To do this, add a line that says “Threading = 0” for your driver in the odbcinst.ini file.

The adapter writes data to SAP Sybase IQ load files in the native SAP Sybase IQ binary format, and loads these files in sequence into the database. The adapter supports persisting Event Stream Processor insert, update, and delete records. To improve performance, the data warehousing mode allows you to configure the adapter to either ignore updates and deletes, or to treat updates as inserts and ignore deletes.

The adapter creates files in the primary or overflow file locations as rows are received from Event Stream Processor. Once a file is successfully loaded into SAP Sybase IQ, the data is visible within the database and the file is removed from the file system. If an error occurs while loading a file, an error is logged to the ESP Server log, but the file remains on the file system. Once the problem preventing the load is resolved, you can manually attempt to reload the file using the SQL statement provided in the Event Stream Processor logs.

You can track the progress of this adapter using Sybase Control Center for Event Stream Processor. The file activity report shows each of the files processed by this adapter and lists its current state. To view the file activity report, you must add a special table to the database into which the adapter is loading. See Enabling File Activity Monitoring for the SAP Sybase IQ Adapter. See Viewing File Activity for the SAP Sybase IQ Output Adapter in the SCC for Event Stream Processor online help for additional information on the file activity report.

When the adapter receives a shutdown request because the project is stopping, the ESP Server is shutting down, or the adapter itself is stopped, it continues processing data until certain conditions are satisfied. Any data the adapter receives before the shutdown request is written into the currently writing file. This file remains on the file system until you run the adapter again, at which time it is loaded into SAP Sybase IQ. If a file is being loaded into SAP Sybase IQ when the adapter receives the shutdown request, that file continues to load until it has completed; but no further files are loaded. When the adapter restarts, it resumes processing any files created, but not loaded, by a previous instance. The same process also occurs if the ESP Server or adapter terminates unexpectedly while a file is being loaded.

Note: Because the adapter continues to process data after it receives the shutdown signal, it may appear to be slow shutting down.

The adapter also supports schema discovery and permutations. Permutations allow mapping between a compatible Event Stream Processor schema and a database schema when the two schemas are not identical. If a permutation does not provide a mapping for a database column, that database column must be nullable. If the column is nullable, the adapter inserts NULL for each row into this column.

The default character set of the SAP Sybase IQ database must be either ASCII (if no international characters are to be loaded) or UTF-8.

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

Here is an example of a service entry for the SAP Sybase IQ Output adapter to connect to the SAP Sybase IQ database using an ODBC connection:

<Service  Name="IQ"  Type="DB">
     <Parameter Name="DriverLibrary">esp_db_odbc_lib</Parameter>
     <Parameter Name="DSN"><DataSourceName></Parameter>
     <Parameter Name="User">DBA</Parameter>
     <Parameter Name="Password">sql</Parameter>
    </Service>

Property Label	Description
DB Service Name	Property ID: service Type: `string` (Required for adapter operation and schema discovery) The name of the database service that represents the IQ database into which information will be loaded. Specify service entries in the Event Stream Processor service.xml file. You must specify an ODBC service for the database service. See the Administrators Guide. No default value.
Target DB Table Name	Property ID: table Type: `string` (Required for adapter operation; optional only if you are using schema discovery) A string value representing the name of the table in SAP Sybase IQ into which you wish to load data. 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.
Primary File Location	Property ID: primaryFileLocation Type: `directory` (Required for adapter operation; optional only if you are using schema discovery) Specify the directory in which SAP Sybase IQ load files are stored temporarily until they are loaded into the system. If this directory does not exist, create it before running the adapter. Because there is sensitive information being loaded into the SAP Sybase IQ database and anyone with access to the load files can change the data being loaded, you must secure this directory as you do your database. SAP recommends that only users with access to the database have access to this directory. No default value.
Overflow File Location	Property ID: overflowFileLocation Type: `directory` (Optional) Specify the directory for overflow files. If this directory does not exist, create it before running the adapter. If the primary file location does not contain enough space to write out load files, the files are created in the overflow location. Because there is sensitive information being loaded into the SAP Sybase IQ database and anyone with access to these files can change the data being loaded, you must secure this directory as you do your database. SAP recommends that only users with access to the database have access to this directory. No default value.
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.
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.
Recover Only	Property ID: recoverOnly Type: `boolean` (Advanced) If true, the adapter processes any files that have beencreated, but not loaded, by a previous instance of the adapter. After these files are processed, the adapter does not process further message flow. Default value is false.
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 a column name is provided, the time at which the record is written to the load file is stored in that column of the database record. If this property is empty, no timestamp is stored. The timestamp is always provided in UTC. No default value.
Target File Size	Property ID: targetFileSize Type: `uint` (in MB) (Advanced) Specifies the maximum size for an SAP Sybase IQ load file before it is loaded into SAP Sybase IQ. SAP Sybase IQ load performance is better for larger files, but latency increases with file size. Default value is 2000. Each SAP Sybase IQ load file is not necessarily this size. If the adapter is not in data warehousing mode, any updates or deletes received reduce the file size. The Idle Buffer Write Delay and Buffer Age Limit parameters may also cause smaller files to be generated.
Idle Buffer Write Delay	Property ID: idleBufferWriteDelay Type: `uint` (in seconds) (Advanced) If the adapter has not received a row during the number of seconds specified, the previously created file is loaded. This provides latency control in situations where message flow may be sporadic. Default value is 10.
Buffer Age Limit	Property ID: bufferAgeLimit Type: `uint` (Advanced) The maximum amount of time (in seconds) between the first and last record in an I/O buffer. This ensures that consistent but low volume message flow does not create very high latency. Default value is 600.
Disable Referential Integrity	Property ID: disableReferentialIntegrity Type: `boolean` (Advanced) Disables the referential integrity constraints on the table before the loading operation is performed. This can significantly improve performance of the load, but causes problems if the source does not maintain data integrity. Default value is false.
I/O Buffer Size	Property ID: ioBufferSizeMB Type: `uint` (in MB) (Advanced) Determines how much data is buffered in memory before it is written to disk. Increasing this value may increase write performance, but may also increase latency. Determine the amount of memory required by the adapter to buffer by multiplying this number by the number of I/O buffers. The target file size should be a multiple of this number. Default value is 20.
Number of I/O Buffers	Property ID: numIOBuffers Type: `uint` (in MB) (Advanced) The number of data buffers to maintain. This number should be sufficiently high so that a buffer is always available to write into. The slower the file system, the more I/O buffers required. Default value is 5.
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.
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 SAP Sybase IQ 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. This option helps determine how often the ESP Server gathers statistics.

Rows	Bytes	Average Rows Received/Loaded
Received in the last hour Received since midnight Loaded in the last hour Loaded since midnight Skippd* in the last hour Skipped* since midnight	Received since midnight Loaded since midnight Skipped since midnight	Over the last minute 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.