(SM-2411) Sybase IQ / SAP ASE Storage Setup

Owned by SNP
Nov 20, 2024
2 min read

Sybase IQ / SAP ASE connector can communicate with SAP ASE and Sybase IQ databases.

Sybase IQ uses the same SQL syntax as Sybase ASE and is fully ANSI SQL compliant. 

Sybase IQ has a limitation for VARCHAR key field length, it is restricted to 225. During the Glue Table creation, the source text field length is multiplied by 3 to prevent issues transferring multi-byte characters. If the field definition length exceeds 255, it'll be restricted to 255.


Supported versions

The minimal supported version of Sybase IQ is 16.0.0.809.

The minimal supported version of SAP ASEIQ is 16.0.4.2.

Storage setup - Sybase IQ

  • Storage ID: Name of the storage.
  • Storage Type: Type of storage. Use storage type SM_TRS_SIQ for the Sybase IQ transparent storage.
  • Description: Short text description.
  • Connection mode:
    Native SQL (N) is supported by Sybase IQ storage.
    Open SQL (O) is obsolete and should not be used anymore.
  • DB Connection Name: Existing SAP database connection.
  • Load stripe size: Optional, see below the Performance improvements section.
  • Load stripe width: Optional, see below the Performance improvements section.
  • Enable update: Support of the direct update statements.
  • Connection mode switch after failure: Switch connection mode during reading/writing the data if it ends up with an error for the selected connection mode.
  • Read all clients: Read data from the database with all clients (relevant for Open SQL Connection mode).

Preview feature

Enable update functionality

Performance improvements

Load striping

Striping data across multiple disks is an essential technique for good performance.

Disk striping can be performed at different places in a system, often as part of RAID hardware or software. More information can be found under Disk striping.
Load striping parallelizes data inserts across multiple disc drives. To set up a stripe load, a database connection should be configured to support this feature.
Example configuration of DBCO:
SYBASE_SERVER=<server>.<domain>.<ext>
SYBASE_PORT=<port>   e.g. 34238
SYBASE_IQ_ENGINE=server_NLS_42
SYBASE_DBNAME=SAPIQDB
SYBASE_CONTYPE=IQ
SYBASE_IQ_LOAD=1
SYBASE_IQ_CESU-8=1
SYBASE_IQ_LOCKWAIT=600
SYBASE_IQ_BUFFER_SIZE=500000
SIQ_BULK_OP=1
Please refer to Sybase IQ documentation, page 75.

Example from the HD1 system, DBCO:
SYBASE_SERVER=vsks032
SYBASE_PORT=2648
SYBASE_IQ_ENGINE=vsks032_HD1
SYBASE_DBNAME=SAPHD1DB
SYBASE_IQ_LOCKWAIT=600
SYBASE_CONTYPE=IQ
SYBASE_IQ_CESU-8=1
SYBASE_IQ_LOAD=1
SYBASE_IQ_BUFFER_SIZE=500000

After DBCO is configured to support the stripe load, you should enable striping in the Storage management setup, as follows: (see above).
Settings:
  • Load stripe size: Size of a stripe on a disc, expressed as a multiple of SYBASE_IQ_BUFFER_SIZE value.
  • Load stripe width: Degree of parallelism.
More information on SAP Stripe load implementation.


Partitioning

Partitioning allows parallel inserts into a DB table.

To use partitioning, you must first create (register) partitioning on a table. To perform this call the method /DVD/SM_IF_TAB-ADD_TABLE_PARTITIONING.
Partitioning must be enabled on an empty table, ideally just after creating it (CREATE_TABLE).
Afterward create a partition, before inserting data, by calling the method /DVD/SM_IF_TAB-ADD_PARTITION. Otherwise, the INSERT statement will fail, because data cannot be assorted into partitions.
There are two corresponding methods to check if partitioning is enabled on a table: /DVD/SM_IF_TAB-PARTITIONING_EXISTS and to check if the partition with a given name exists on the table /DVD/SM_IF_TAB-PARTITION_EXISTS.