Skip to Content
Business Trends
Author's profile photo Roland Kramer

SAP (Sybase) IQ – the hidden treasure …

last changed: 23rd of August, 2021

SAP (Sybase) IQ – the hidden treasure …

Blog Content


 

the%20Data%20Value%20Formula

the Data Value Formula

 

Blog: The Next Generation of SAP Adaptive Server Enterprise (SAP ASE) and SAP IQ
Blog: The Next Chapter: Turning Data into a Superpower
SAP News: SAP ASE and SAP IQ: The Next Generation (November 2019)
LinkedIn: SAP’s Commitment to On-Premise SAP IQ Customers (May 2020)

SAP IQ trial

 

Sybase IQ was acquired in 2013 together with other Databases, e.g. ASE, the replication technology System Replication Server (REP) and the Event Streaming Platforms, which are incorporated in other SAP Products now, see the SAP IQ Overview below.

Getting%20Started%20With%20SAP%20IQ

Getting Started With SAP IQ


SAP IQ – main use cases


SAP Information Lifecycle Management (ILM) –
SAP ILM based on SAP IQ Database

SAP nearline storage for BW (NLS) – SAP-NLS Solution for SAP BW based on SAP IQ

SAP IQ Database as Internal Datalake


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
(please contact roland.kramer@sap.com for a preview copy)

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

a%20complete%20platform%20for%20XLDB%20analytics

IQ – a complete platform for XLDB analytics

 

IQ%20-%20versatile%20application%20services%20for%20XLDB%20analytics

IQ – versatile application services for XLDB analytics

 

IQ%20query%20engine%20scale%20out%20-%20distributed%20query%20processing

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 architectureSAP 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%20IQ%20Administration%3A%20Unstructured%20Data%20Analytics

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%20IQ%20Administration%3A%20Load%20Management

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 filesparquetjs

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

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 HelpThe IQdemo Database

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 – 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%20to%20set%20up%20an%20OData%20server%20in%20SAP%20IQ

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

-xs odata{port=1180}
-xs http{port=1080;MaxRequestSize=3m}
-xs https{port=1443;IDENTITY=/sapmnt/<SID>/SIQ<nr>/security/rsaserver.id;IDENTITY_PASSWORD=test;FIPS=yes}

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%20Gallery%20based%20on%20HTTP/OData%20service

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%20IQ%20Cockpit

SAP IQ Cockpit

 


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%20Data%20Pyramide%20vs.%20Data%20Value%20Formula

SAP Data Pyramid vs. Data Value Formula

SAP Intelligent Data Orchestration Platform

SAP online help: SAP Data Intelligence – Supported Connection Types

SAP Data Intelligence Integration

 


Roland Kramer, SAP Platform Architect for Intelligent Data & Analytics
@RolandKramer

 

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

Assigned tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Yoav Yahav
      Yoav Yahav

      Thanks for this article

      Could you elaborate on the parallel computing capabilities of IQ similar to spark when big data scenarios are the case?

      Thanks

      Yoav

      Author's profile photo Roland Kramer
      Roland Kramer
      Blog Post Author

      Hello Yoav
      I have added additional Information about the parallel computing capabilities of IQ.
      However I'm talking mainly about the simplex server architecture here, while the IQ multiplex server architecture is the underlying model of the SAP HANA Data Lake

      Best Regards Roland

      Author's profile photo Yoav Yahav
      Yoav Yahav

      Thanks Roland and excellent article !

      Author's profile photo Michael Schliebner
      Michael Schliebner

      Excellent, Thank you Roland!

      Author's profile photo Ananta chitturi
      Ananta chitturi

      Any help regarding the unstructured data. Need understanding BO reporting on unstructured data with SAP IQ.