esp_iqloader

Archives data from one or more Event Stream Processor streams into Sybase IQ.

The esp_iqloader command archives data from one or more Event Stream Processor streams into Sybase IQ, either in real-time mode or in batch mode.

esp_iqloader subscribes to the streams that require archiving, then writes the data to an intermediate file either in a delimited format suitable for bulk-loading for inserts, or as SQL DML statements for updates and deletes. esp_iqloader uses the LOAD TABLE statement feature of Sybase IQ to archive inserts, and archives updates and deletes via ODBC.

Note: When running esp_iqloader in data warehousing mode (-I argument), updates are treated as inserts, and deletes are ignored.

esp_iqloader closes if it encounters an error. If esp_iqloader, the Event Stream Processor, or Sybase IQ stop, on restart esp_iqloader begins archiving data from the last archived transaction. esp_iqloader tracks the most recent archived transaction.

Note: Ensure that every stream to be archived uses the Persistent Subscribe Pattern. Associate every stream with two additional streams. The first stream is a log stream, which contains the transaction logs for the stream to archive. The second stream is a control stream that is an input to the log stream, which serves as a gateway into the log stream to purge the archived transaction logs. Use the ESP Studio to create both the log and control streams that use the Persistent Subscribe Pattern.

The control stream and the log stream must be persisted to guarantee that no transaction is lost if the Event Stream Processor fails. To ensure that the data stream can regenerate itself, create at least one log store to store both the control stream and the log stream.

To stop a running esp_iqloader process, enter Ctrl+C on the command line.

Limitations and known issues:

When archiving updates and deletes, esp_iqloader may not be able to keep up with the Event Stream Processor, because individual update and delete SQL statements are applied via ODBC to archive the data.

Syntax

esp_iqloader -f configFile -p [<host>:]<port>/workspace-name/project-name

Required Arguments

-f configFile – (required) specifies the XML style configuration file that describes the streams to be archived, the connection information to Sybase IQ, the Sybase IQ LoadTable options, and other information.
-p [<host>:]<port>/workspace-name/project-name – (required) together, the host:<port>/<workspace name>/ <project name> arguments specify the URI to connect to the ESP Server (cluster manager). For example, if you have started your ESP cluster server in the port 19011, the host name is set as localhost, and you are running a project called prj1 in the default workspace, specify -p as: -p localhost:19011/default/prj1

Options

-b – (optional) sets byteswap mode. The server running esp_iqloader can connect to machines of differing byte order.
Note: You can use esp_iqloader to connect to machines with different byte order architectures, but not differing address sizes.
-B batchsize – (optional) specifies the commit batch size when archiving data using ODBC and SQL data manipulation statements. Choose a proper setting for this option. If the batch size is too small, many small intermediate files are generated, making archiving inefficient. A batch size that is too large may cause problems when committing the data into the database. The default value is 1000.
Note: batchsize does not modify the commit batch size of bulk loaded transactions. To do this, specify the appropriate option for the 'LoadStatement' in the configFile. The default batch size value for bulk-loaded transactions is 100,000.
-c user[:password] – (optional) passes authentication credentials to the Event Stream Processor. If the password is not given, you are prompted for it. If the Event Stream Proccessor successfully authenticates with these credentials, the connection is maintained. Otherwise, the Event Stream Proccessor immediately closes the connection. Ensure this argument corresponds to the type of authentication specified for the Event Streaming Processor when it was started.
-d – (optional) specifies the delimiter that esp_iqloader uses when it writes intermediate files. Do not choose a delimiter that appears as part of the data being archived. Doing so results in the archived data being corrupted or rejected by the bulk load executable. The default delimiter value is HEX 31.
-D – (optional) enables delta mode. If not specified, esp_iqloader archives all existing data for the specified streams and exits.
-e – (optional) enables encryption via OpenSSL sockets.
-k privateRsaKeyFile – (optional) authentication is performed using the RSA private key file mechanism instead of password authentication. The privateRsaKeyFile must specify the pathname of the private RSA key file.
Note: Ensure that the Server has been started with the -k option specifying the directory in which to store the RSA keys.
-l linedelimiter – (optional) specifies a single character delimiter for esp_iqloader; marks the end of the line when esp_iqloader writes intermediate files. By default, the esp_iqloader uses the new line character as the delimiter.
Note: Do not choose a delimiter that appears as part of the data being archived. Doing so results in the archived data being corrupted or rejected by the bulk load executable.
-p [<host>:]<port>/workspace-name/project-name – (required) together, the host:<port>/<workspace name>/ <project name> arguments specify the URI to connect to the ESP Server (cluster manager). For example, if you have started your ESP cluster server in the port 19011, the host name is set as localhost, and you are running a project called prj1 in the default workspace, specify -p as: -p localhost:19011/default/prj1
-P precision – specifies an integer value between 0 and 6 that defines how many decimal places to print after the decimal point for floating point numbers. The default value is 6.
-q queueSize – (optional) sets the maximum subscription buffer size. Set this parameter to a higher value if the data arrives in bursts, and if the Event Stream Proccessor reports the subscription buffer has filled up. The minimum value is 1001. The default value is 8000.
Note: When setting a maximum buffer size, ensure the Block Size parameter in the configuration file is set to no more than 12000.
-R – (optional) enables recovery mode. In this mode, esp_iqloader does not connect to the Event Stream Processor to get more data. Instead, it archives any data that was written out to disk previously but not archived because esp_iqloader was interrupted. It exits once the data has been archived.
Note: With recovery mode enabled, the -D argument is ignored.
-T interval – (optional) specifies approximately how often esp_iqloader should archive data deltas when archiving in real-time mode. The interval is from the time the last delta archive operation completed.
-W wait_time – (optional) specifies, in milliseconds, how long the Event Stream Processor should wait for esp_iqloader to consume the base data. The minimum value is 1000. The default value is 30000.
-v – (optional) prints the esp_iqloader utility version.