(SM-1811) Sybase IQ transparent storage

Owned by SNP
Last updated: Dec 05, 2018 by Martina Pospisilova (Unlicensed)


Storage setup

  • Storage ID: name of the storage
  • Storage Type: type of storage, use storage type "SM_TRS_SIQ" for Sybase IQ transparent storage.
  • Description:  short text description


  • Connection mode: O - open connection, open SQL is used
  • DB Connection Name: existing SAP Database Connection.
  • Load stripe size: optional, described in performance improvement section
  • Load stripe width: optional, described in performance improvement section


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"
Source.
Load striping parallelize data inserts across multiple disc drives. In order to setup stripe load, database connection must be configured to support this feature.
DBCO example configuration:

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 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 stripe load, you enable striping in storage management setup (screenshot above).
Settings :
  • Load stripe size: size of stripe on disc, expressed as a multiple of SYBASE_IQ_BUFFER_SIZE value
  • Load stripe width: degree of parallelism
More info on SAP Stripe load implementation.


Partitioning

Partitioning will allow parallel insert into DB table.

To use partitioning, you must at first create (register) partitioning on table. You do this by calling method /DVD/SM_IF_TAB-ADD_TABLE_PARTITIONING. Partitioning must be enabled on empty table only, ideally just after CREATE_TABLE.

Afterwards you must create partition before inserting data by calling method  /DVD/SM_IF_TAB-ADD_PARTITION. Otherwise the INSERT statement will fail, because data cannot be assorted to partition(s).

There are two corresponding methods to check if partitioning is enabled on table: /DVD/SM_IF_TAB-PARTITIONING_EXISTS and to check if partition with given name exists on table /DVD/SM_IF_TAB-PARTITION_EXISTS.