(SM-2102) Sybase IQ Transparent Storage
Sybase IQ is a column-based, petabyte scale, relational database system used for business intelligence, data warehousing and data marts.
Supported versions
The minimal supported version is 16.0.0.809.
Storage setup
- 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
Performance improvements
Load striping
"Striping data across multiple disks is an essential technique for good performance.
SYBASE_SERVER=<server>.<domain>.<ext>
SYBASE_PORT=<port>
e.g. 34238SYBASE_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
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
- Load stripe size: the size of a stripe on a disc, expressed as a multiple of SYBASE_IQ_BUFFER_SIZE value
- Load stripe width: a degree of parallelism
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 to 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
.