Adapter type: sybase_iq_out. The Sybase IQ Output adapter reads data from Event Stream Processor and loads it into the Sybase IQ database.
Before running the adapters, create a directory for the primary file location and one for the overflow file location.
The adapter writes data to Sybase IQ load files in the native 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. 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 to improve performance.
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 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, and 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, the database into which the adapter is loading must contain a special table. See the Enabling File Activity Monitoring for the Sybase IQ Adapter topic for information on creating this table. See the Viewing File Activity for the Sybase IQ Output Adapter topic 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 out 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 Sybase IQ. If a file is being loaded into 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 starts up again, it resumes processing any files created, but not loaded, by a previous instance. This process also occurs if the ESP Server or adapter terminate unexpectedly while a file is being loaded.
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 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.
| Property Label | Property ID | Type | Description |
|---|---|---|---|
| DB Service Name | service | string | (Required) The name of the database service that represents
the IQ database into which information will be loaded. Specify
this in the cluster service.xml file. You
must specify an ODBC service for the database service. No default value. |
| Target DB Table Name | table | string | (Optional) The name of the table in Sybase IQ into which you
wish to load data. This parameter is optional only if you are
using schema discovery. It is required for the operation of the
adapter. No default value. |
| Include Base Content | outputBase | boolean | (Optional) Determines whether the base data of the window or stream to which this adapter is connected is output. If set to true, the adapter processes the base data of the window or stream to which it is attached. Default value is false. |
| Primary File Location | primaryFileLocation | directory |
(Optional) This parameter is optional only if you are using schema discovery. It is required for the operation of the adapter. Specify the directory in which 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 Sybase IQ database and anyone with access to these files can change the data being loaded, you must secure these directories as you do your database. Sybase recommends that only users with access to the database have access to these directories. No default value. |
| Overflow File Location | overflowFileLocation | 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 Sybase IQ database and anyone with access to these files can change the data being loaded, you must secure these directories as you do your database. Sybase recommends that only users with access to the database have access to these directories. No default value. |
| Field Mapping | permutation | permutation | (Advanced) The 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 match the database schema of the
destination table exactly. For example, it must have the same order,
same number of columns, and compatible datatypes. 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 | onlyBase | boolean | (Advanced) If set to 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 | recoverOnly | boolean |
(Advanced) If set to true, the adapter processes any files that were created, 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 | dataWarehouseMode | choice |
(Advanced) Specifies the type of data warehousing mode the adapter uses. Valid values are:
|
| Timestamp Column Name | timestampColumnName | 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, there is no
timestamp stored. The timestamp is always provided in UTC. No default value. |
| Target File Size | targetFileSize | uint (in MB) |
(Advanced) Specifies the maximum size for a Sybase IQ load file before it is loaded into Sybase IQ. Sybase IQ load performance is better for larger files, but latency increases with file size. Default value is 2000. Each Sybase IQ load file is not necessarily this size. If the adapter is not in data warehousing mode, any updates or deletes received cause the file size to be lower. The Idle Buffer Write Delay and Buffer Age Limit parameters may also cause smaller files to be generated. |
| Idle Buffer Write Delay | idleBufferWriteDelay | uint (in seconds) |
(Advanced) If the adapter has not received a row in this many seconds, the previously created file is loaded. This is a control for latency in situations where message flow may be sporadic. Default value is 10. |
| Buffer Age Limit | bufferAgeLimit | uint (in seconds) |
(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 | disableReferentialIntegrity | 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 the integrity of the data. Default value is false. |
| I/O Buffer Size | ioBufferSizeMB | uint (in MB) |
(Advanced) Data is buffered in memory before being written to disk. The I/O buffer size determines how much data is buffered before being written. Adjusting this higher can increase write performance, but may increase latency. The amount of memory required by the adapter to buffer can be determined 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 | numIOBuffers | 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 |
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. |
| Timezone For Statistics | timezoneForStats | 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 stats reset at midnight
GMT. This setting does not affect any other dates processed or output by this adapter. Default value is false. |
The 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 |
|---|---|---|
|
|
|
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.