SAP (Sybase) IQ - the hidden treasure ...

When it comes to the standard method to move historical data from the primary database (especially not only HANA) the nearline storage (NLS) implementation based on SAP IQ is the defacto standard for all our customers.

unfortunately in the past years the main capabilities of IQ were hide behind the "big HANA wall", so the majority of our SAP customers are not aware of the unique features which makes SAP (Sybase) IQ so special. the Maximum Limits of IQ also speak for themselves!

The most probably reason, why IQ is treated as a black box or an unknown stranger (remember the not so successful launch of dynamic tiering) is the fact, that the installation is still not based on the software provisioning manager (SWPM).

This mayor gap was solved a while ago with Q the easy Installer for SAP IQ (based on SAP Hostagent and is available for Linux SLES and Red Hat). the used components can be downloaded from here and can be used on Hyperscaler's or on-premise Linux VM's.

SAP IQ - Q - the easy SAP IQ Database Installer

SAP First Guidance - Q - the easy SAP IQ Database Installer

Q - the easy SAP IQ database installer

The IQ DB Installation routine can be also taken as "the defacto standard" for ILM and native IQ based storage as well, which makes it as THE alternative for existing Hyperscaler storage types like S3, Swift, HDFS, ADLS or Spark/Vora coming as well from the Vendor SAP.

The main Advantage here: You can implement the data storage on your own premises to be legal compliant while only the orchestration remains in the Hyperscaler, e.g. SAP Data Intelligence 3.0 based on Azure AKS.

SAP IQ - column based compression - nothing beats IQ

when it comes to compression of raw or already compressed data by classical databases like Oracle, DB6, MSSQL or our own SAP HANA database, IQ is still able to reduce the data footprint even more.

Fast data ingest: fully parallel bulk data loading

Industry leading query engine: rich SQL query processing on complex, ad hoc workloads

Data compression: pioneering compression and indexing technologies

Scale out: dynamic scale-out for complex analytics on petabyte-scale data volumes

Security: comprehensive security features

Low TCO: commodity blade hardware deployments, with low administrative overhead

Extensibility: in-database analytics framework

Elasticity: separation of storage and compute

SAP IQ - a complete platform for XLDB analytics

IQ - a complete platform for XLDB analytics

IQ - versatile application services for XLDB analytics

IQ - query engine scale out - distributed query processing

Massively parallel processing

Leader node:
Receives and initiates queries, including UDFs
Any node can be a leader, one leader per query, many concurrent leaders possible
Leader node may satisfy query within itself

Worker node:
Nodes picking up work units from leader
any worker nodes per query, same worker node can serve multiple queries
Worker nodes are enlisted only if leader cannot satisfy query on its own

SAP IQ - simplex server architecture fits all purpose

IQ simplex server architecture - SAP IQ database

SAP IQ - unstructured content - structured analysis

one of the main concepts of SAP IQ is the storage of huge amount of unstructured data and the capability to analyze this data in a structured way

SAP IQ Administration: Unstructured Data Analytics

The Unstructured Data Analytics Option extends the capabilities of SAP IQ to allow storage, retrieval, and full text searching of binary large objects (BLOBs) and character large objects (CLOBs) within the database.

As data volumes increase, the need to store large object (LOB) data in a relational database also increases. LOB data may be either:

Unstructured – the database simply stores and retrieves the data, or

Semi structured (for example, text) – the database supports the data structure and provides supporting functions (for example, string functions).

SAP IQ data load management - you decide and not the RDBMS

SAP IQ Administration: Load Management

SAP IQ offers you a choice of methods for adding, changing, or deleting data.

For efficient bulk loading of tables from flat files, use the SQL statement LOAD TABLE.

To insert specified values into a table row by row, use the SQL statement INSERT with the VALUES option.

To insert rows selected from a table (including a table residing in another database), use the SQL statement INSERT with a SELECT statement clause.

To remove specific rows from a table, use the DELETE statement.

To change existing rows in a table, use the UPDATE statement.

The IQ data extraction facility exports data in binary or ASCII format, which you can then load into another database. Use this facility for high-volume data movement, or when you need an output file that can be used for loads.

From Interactive SQL, you can export data to another database in a variety of formats, or produce a text file as output. You can also redirect the output of any command.

SAP IQ supports the loading of tables with Parquet format files - parquetjs

Parquet is an efficient, open-source, column-oriented format file designed for Apache Hadoop.
You can load tables in parquet format in the LOAD TABLE statement. See https://parquet.apache.org for more information on Parquet.

Note 2748429 - Feature request for support JSON in IQ 16x -- SAP IQ
Note 3213101 - Does SAP IQ support data extraction to Apache 'Parquet' format ? -- SAP IQ

You can easily build a Pipeline to convert JSON file into parquet format to load SAP IQ Instantly. See the Blog - SAP Data Intelligence Hub – connecting the Dots …

SAP IQ - the SAP IQdemo database

SAP Help - The IQdemo Database
Note 3156521 - Creation of the IQ demo database has failed with "Database server not found"

if you are not jet familiar with SAP IQ, the SAP IQdemo database is a good start to understand the concepts and usage of SAP IQ. By default, all sources to create the database are delivered with the SAP IQ binaries under the Directory $IQDIR16/demo

sq1adm@server:/usr/sap/SQ1/data/db> env | grep IQDIR16

IQDIR16=/usr/sap/SQ1/SIQ02/IQ-16_1

sq1adm@server:/usr/sap/SQ1/data/db> cd $IQDIR16/demo

sq1adm@server:/usr/sap/SQ1/SIQ02/IQ-16_1/demo

Despite to the SAP online help, the IQdemo database must be created first with the script mkiqdemo.sh (if already another IQ database is running on the server, make sure you specify the parameter -port to isolate the IQdemo from other resources)

sq1adm@server:/usr/sap/SQ1/SIQ02/IQ-16_1/demo> ./mkiqdemo.sh -dba dba -pwd changeit -demodir /usr/sap/SQ1/SIQ02/IQ-16_1/demo/ -port 34238 -share -y

sq1adm@server:/usr/sap/SQ1/SIQ02/IQ-16_1/demo>start_iq @iqdemo.cfg iqdemo.db

sq1adm@server:/usr/sap/SQ1/SIQ02/IQ-16_1/demo> iqdsn -y -w "IQdemo" -c "UID=DBA;PWD=changeit;ServerName=<server>_iqdemo;DBN=IQdemo; Host='<server>.domain.ext:34238'"

Configuration "IQdemo" written to file /usr/sap/SQ1/home/.odbc.ini

sq1adm@server:/usr/sap/SQ1/SIQ02/IQ-16_1/demo> dbping -d -c dsn=IQdemo

Ping database successful.

sq1adm@server:/usr/sap/SQ1/SIQ02/IQ-16_1/demo> dbisql -c dsn=IQdemo -nogui

(dba)>

check the IQdemo database with the command sp_iqlmconfig

(dba)> sp_iqlmconfig
Property                             Value
-------------------------------------------------------------
Licence Notice                       SAP License Key
Edition                              EE
License Type                         CP
Application Type                     IQ
IQ_CORE License Count in use         16 (CPU core based)
Optional license in use : IQ_UDA     No (Allowed)
Optional license in use : IQ_LOB     No (Allowed)
Optional license in use: IQ_SECURITY No (Allowed)
Optional license in use: IQ_MPXNODE  No
Optional license in use: IQ_VLDBMGMT No (Allowed)
IQ_VLDBMGMT License Count in use     0 (Max Allowed : 4)
Optional license in use: IQ_UDF      No (Allowed)
Optional license in use: IQ_IDA      No (Allowed)
Optional license in use: IQ_URIDA    No (Allowed)
Email Severity                       NONE
SMTP Host                            smtp
SMTP Port                            25
Email Sender                         <sid>adm@cloudapp.net
Email Recipients                     <sid>adm@cloudapp.net
(19 rows)
(dba)>

now you have a fully configured SAP IQ Database at hand.

SAP IQ trial

SAP IQ - not only a database, it's a multiple usage server

SAP IQ allows you to interact with your data in all kind of Formats, services or API's. one of the important features is to use it an an OData server in combination with HTTP/HTTPS build-in support.

How to set up an OData server in SAP IQ

To enable the additional server capabilities simply add them to the startup file SAPIQDB.cfg and restart the SAP IQ server.

test examples for OData, HTTP/S and for SQL Anywhere applications can be found in the following SAP IQ binary directories (you really want to find this ... ;-))

/sapmnt/<SID>/SIQ<nr>/IQ-16_1/samples/certificates
/sapmnt/<SID>/SIQ<nr>/IQ-16_1/samples/sqlanywhere/http
/sapmnt/<SID>/SIQ<nr>/IQ-16_1/sdk/php/examples

Furthermore the in-build SQL Anywhere server (also the sql interface for SAP IQ) gives you various options to use the additional functions. A good start to discover all these features can be found in the

SAP Help Section - HTTP web service examples - OData server samples - IQ spatial features

Picture Gallery based on HTTP/OData service

to show quickly within minutes the unstructured data capabilities is the Gallery Example, a little application to store and analyze pictures of all kind. Here you are using the database server as a web client. within that directory you will find several more sql scripts, e.g. a JSON example. you can run all these sql scripts from the interactive SQL client for IQ, See Installing SAP IQ Client Software.

Not all Examples are explained in the SAP online help, and the examples are easy to understand and to test instantly. use the SAP IQ Cockpit to check your compiled Objects and create your own services in a easier way than the SAP help description

SAP IQ Cockpit

DBACOCKPIT for IQ (SAP NetWeaver based)

this is just the start, find out more ...

there is so much more to find out how to use SAP IQ for your own purposes and include SAP IQ in hybrid scenarios like seen below.

Blog: Unified Data Integration for SAP
see how SAP Data Intelligence can orchestrate the use from and to SAP IQ for Big Data Challenges.

SAP Data Pyramid vs. Data Value Formula

SAP Intelligent Data Orchestration Platform

SAP online help: SAP Data Intelligence - Supported Connection Types - Table Producer V2
(new with DI 3.2)

SAP Data Itelligence Integration

E-3 Enterprise Ausgabe 2012/2013

Roland Kramer, SAP first-guidance
@RolandKramer
email Profile Image.jpg

“I have no special talent, I am only passionately curious.”