Skip to Content
Business Trends

SAP (Sybase) IQ – the hidden treasure …

last changed: 23th of November, 2020

SAP (Sybase) IQ – the hidden treasure …

 

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

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. Since this time, the Document – SAP First Guidance – SAP NLS Solution with SAP IQ is available to ease the IQ implementation. So I would recommend the read this Document first, and then proceed with the SAP IQ Overview below.

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

Getting Started With SAP IQ

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 themself!

The most probably reason, why IQ is treated as a black box or an unknown stanger (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 Hyperscalers or on-premise Linux VM’s.

Q – the easy SAP IQ database installer

The IQ DB Installation routine can be also taken as “the defacto standard” for 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.
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.


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

 


simplex server architecture fits all purpose

IQ simplex server architectureSAP IQ database

 


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
  • Semistructured (for example, text) – the database supports the data structure and provides supporting functions (for example, string functions).

 


data load management – you decide and not the RDBMS decides

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.

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 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)
-xs https(port=1443;IDENTITY=/sapmnt/NLS/exe/sec/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 beginning, 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.

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.”

5 Comments
You must be Logged on to comment or reply to a post.