- SAP Managed Tags
last changed: 28th of June 2024
Q - the easy Installer for SAP IQ - ng 2024
as the rudimental HTML anker functionality is not available anymore in the current SAP Blog capabilities, I had to deactivate the TOC.
- enable HTTPS Port 1129 for the SAP Host Agent
- the Answer: Q – the easy Installer for SAP IQ
- create DSN (.odbc.ini) with iqdsn
- prepare the SSL/TLS IQ Setup
- the new DBACOCKPIT for IQ
- the SAP BW(/4) nearline storage configurationthe SAP S/4 ILM Store configuration
Beginning with 2017, the request for the Installation of SAP (Sybase) IQ grow for several reasons, everyone can understand also followed by the message - SAP Commitment to On-Premise SAP IQ Customers (May 2020)
- from nearline storage to cold store - SAP & PBS - SAP HANA Data Lake -
- private vs. public DataLake - SAP (Sybase) IQ – the hidden treasure …
- reducing the memory food print on SAP HANA - btellegent - DataVard Glue - clariba -
- better TCO for in-memory applications - GrayMatter
- simplified data management - IT Central Station
- System Properties Comparison SAP HANA vs. SAP IQ - DB Engines
- who owns your own data - why SAP BW/4HANA?
- SAP ILM with IQ - reducing memory footprint in S/4
Besides that, other bullet points came into the consideration:
- structured access to unstructured content in Data Lake - SAP (Sybase) IQ – the hidden treasure …
- performance alternative to existing Data Lake implementation - clariba - webobjects - askdata
Since 2013, the SAP First Guidance - SAP NLS Solution with SAP IQ 16.x became the "Almanack" and Blue Print for SAP IQ Database Implementations, despite for SAP or native usage.
Overview - SAP NLS Solution for BW(4) - Overview 2021
These SAP KBA Notes where created with the user roland.kramer@sap.com
Note 2780668 - SAP First Guidance - BW NLS Implementation with SAP IQ
Note 3017355 - SAP IQ 16.1 SP04 PLx - correct SAPIQDB.cfg settings
Note 3094594 - Enable SAP IQ for DBACOCKPIT
Note 3085053 - Landscape Considerations for NLS and ILM
Note 3119008 - Configure SAP IQ and HANA for SDA/ODBC
Note 3196447 - troubleshooting an existing SAP IQ 16.x Implementation for NLS/DTO and ILM
Imagine now, there is a simple routine based on the SAPHostAgent to install SAP (Sybase) IQ 16.x with three input values to create any kind of SAP IQ database instantly:
- <SID> (What, also IQ gets a SAP <SID>?)
- Instance Number (what's about port 2638?)
- Size of DB in GB (including all sizing considerations)
even more, to solve all mysteries around the usage there is the following source available:
Blog - demystifying NLS/ILM error messages …
enable HTTPS Port 1129 for the SAP Host Agent
either you call the SAPHostAgent via HTTP Port 1128 or HTTPS Port 1129
SAP Help - Configuring SSL for SAP Host Agent on UNIX
or run the following command (same setup as for the SL Container Bridge)
check that NO secondary sapadm user is logged on to the server via ADS!
server:/usr/sap/hostctrl/exe # ./saphostexec -install
# optional TLS 1.2 since PL57 for 7.22
server:/usr/sap/hostctrl/exe # ./saphostexec -setup tls12
# check the correct settings for /tmp
server:/usr/sap/hostctrl/exe # mount | grep "/tmp"
/dev/mapper/vg_sys_r1-tmp on /tmp type ext4 (rw,nodev,noexec,noatime,data=ordered)
server:/usr/sap/hostctrl/exe #
# check the correct execution of the SAPHostAgent webmethods (*.conf)
server:/usr/sap/hostctrl/exe # saphostctrl -host <hostname> -function ExecuteOperation -name versioninfo
Webmethod returned successfully
Operation ID: 5CF3FCDCB65C1EDD9281CCF3DEC085A5
Info: Retrieve version info: Linux
Info: saphostcontrol: exitcode=0
Info: saphostcontrol: 'Retrieve version info' successfully executed
server:/usr/sap/hostctrl/exe #
add/check the following parameter to the file host_profile and restart the SAPHostAgent
server:/usr/sap/hostctrl/exe # vi host_profile
service/admin_users = sapadm <sid>adm
service/disable_capability = ExecuteOperation/PermissionPolicy
saphostagent/ssl_setup = true
ssl/server_pse = /usr/sap/hostctrl/exe/sec/SAPSSLS.pse
server:/usr/sap/hostctrl/exe # ./saphostexec -restart
now it is possible to call the SAPHostAgent on HTTPS Port 1129
#create the url on host via:
echo https://$(hostname -f):1129/NLSSetup/prepare
If you still have trouble with the execution see the - SAP Host Agent Troubleshooting Guide
the Answer: Q - the easy Installer for SAP IQ
the following picture shows the Setup of - Q - which is actually the same Installation Procedure as the SAP Datahub SL Container Bridge Architecture ...
Version v0.75(s) uses "standard" values for IQ Block and Page Size
(you can further optimize the Block and Page size for the ILM and NLS use case. (s) stands for SSL/TLS setup of the SAP IQ database)
SAP IQ Hardware Sizing Guide – The Complete Memory Picture
Note 2551457 – SAP IQ 16.1 SP 03 Supported Operating Systems and Versions
Note 2669590 - SAP IQ 16.1 SP 04 Supported Operating Systems and Versions
Note 3060790 - SAP IQ 16.1 SP 05 Supported Operating Systems and Versions
Please Note: IQ 16.1 SP05 PL12 (the latest Version in 2024)
for SAP NLS and ILM scenarios, also update the IQ Network Client to the latest Version.
Note 3233538 - SAP IQ 16.1 SP04 PL14 (Build 17091) Release Notes Information
Note 3233571 - SAP IQNC 16.1 SP04 PL14 (Build 17091) Release Notes Information
Note 3444872 - SAP IQ 16.1 SP05 PL12 (Build 14315) Release Notes Information
Note 3444902 - SAP IQNC 16.1 SP05 PL12 (Build 14315) Release Notes Information
In case the /tmp Directory is mounted as read-only then follow the steps in the Note
Note 2104632 - ./setup.bin raises warning /tmp does not have enough disk space! - SAP IQ
Note 3218186 – Error: Create new (IQ) database from utility database fails
# in case for a manual IQ binary Installation or upgrade
server:/ebf29137 # export IATEMPDIR=/sapmnt/tmp
server:/ebf29137 # export DISPLAY= 10.123.456.789:0.0
server:/ebf29137 # ./setup.bin
Preparing to install
Extracting the JRE from the installer archive...
Unpacking the JRE...
Extracting the installation resources from the installer archive...
Configuring the installer for this system's environment...
Launching installer...
The SAPHostAgent uses a routine called "sapinstancebuilder" which creates a complete file system structure equally to an existing SAP Systems like SAP HANA or SAP BW/ERP/etc.
the SAP Instance Builder function in the SAP Host Agent
You can always watch the progress of the IQ Installation in the URL of the Browser
http://server.domain.ext:1128/NLSSetup/prepare
https://server.domain.ext:1129/NLSSetup/execute
/usr/sap/watch -d du -sh <SID>/*
/sapmnt/watch -d du -sh <SID>/*
or alternatively in the SAPHostAgent working directory
/usr/sap/hostctrl/work # tail -f nls.execution.status
Details of the Q Logfile
create DSN (.odbc.ini) with iqdsn
Once the IQ Database Installation is finished, you can use the I-SQL Windows Client to connect to the Database and Check the status with the sp procedures.
SAP IQ allows you with the sql tool iqdsn to create a DSN entry directly in the file .odbc.ini located in the home directory of the SAP IQ Administrator.
Note 2285881 - How to Enable ODBC Connection Tracing in SAP IQ
sq1adm@hostname:/usr/sap/SQ1/data/db> iqdsn -y -w iq_utility -c "UID=DBA;PWD=Sybase4me;
ServerName=iq_utility;DatabaseName=utility_db;HOST=${HOSTNAME}:34238"
sq1adm@hostname:/usr/sap/SQ1/data/db>
iqdsn -y -w hostname_SQ1_11 -c "UID=DBA;PWD=Sybase4me;DatabaseName=SAPIQDB;
ServerName=hostname_SQ1_11;HOST=${HOSTNAME}:$SIQ_SERVERPORT"
Configuration "hostname_SQ1_11" written to file /usr/sap/SQ1/home/.odbc.ini
sq1adm@hostname:/usr/sap/SQ1/data/db> dbping -d -c dsn=hostname_SQ1_11
Connected to SQL Anywhere 16.1.50.1949 server
Ping database successful.
sq1adm@hostname:/usr/sap/SQ1/data/db> iqdsn -l -b
SAP IQ Data Source Utility Version 16.1.50.1949
SAP IQ User Data Sources:
iq_utility
hostname_SQ1_11
sq1adm@hostname:/usr/sap/SQ1/data/db>
essential sp procedures to check the SAP IQ database
# essential sp procedures for SAP IQ
sq1adm@hostname:/usr/sap/SQ1/data/db>
dbisql -c "uid=SAPSQ1USR;pwd=Sybase4me;DSN=hostname_SQ1_11;logfile=connect_SQ1.txt" -nogui
(SAPSQ1USR>)
sp_iqlmconfig;
sp_iqstatus;
sp_iqcheckoptions;
sp_displayroles;
sp_iqdbspace;
sp_iqdbspaceobjectinfo USER0001;
optional SAP IQ database validation procedure
sq1adm@hostname:/usr/sap/SQ1/data/db> dbvalid -c "uid=DBA;pwd=Sybase4me;DSN=hostname_SQ1_11"
SQL Anywhere Validation Utility Version 17.0.9.4899
WARNING: database being validated was not started read-only
VALIDATE DATABASE
VALIDATE TABLE "SYS"."DUMMY"
...
VALIDATE TABLE "rs_systabgroup"."rs_lastcommit"
VALIDATE TABLE "rs_systabgroup"."rs_threads"
No errors reported
sq1adm@hostname:/usr/sap/SQ1/data/db>
If for any reason it is not possible to connect to the IQ Database with the Windows Client, you can also directly call the Interface with the tool dbisql (also with the usage of the DSN above)
Blog - Increasing the SAP-NLS Performance
prepare the SSL/TLS IQ Setup
if you want to enable an encrypted SAP IQ Database with SSL/TLS support, it is suitable to include the following software components to the SAP IQ installation environment:
- SapMachine 11 or higher
if you want to install a newer version of openssl to the existing OS Version do as follows:
### Unix / Linux / macOS - download from https://www.openssl.org/source/
/opt/openssl/> tar -xvf openssl-3.0.4.tar.gz
/opt/openssl/openssl-3.0.4> ./Configure
/opt/openssl/openssl-3.0.4> make
/opt/openssl/openssl-3.0.4> make install
/opt/openssl/openssl-3.0.4> ldconfig -v
/opt/openssl/openssl-3.0.4> openssl version -v
OpenSSL 3.0.4 21 Jun 2022 (Library: OpenSSL 3.0.4 21 Jun 2022)
prepare the SSL scenario
Note 3093731 - How to set up TLS (Transport Layer Security) in SAP IQ16.X -- SAP IQ
Note 2117279 - SAP IQ: How to generate CSR for third party trusted CA (VeriSign)
Q - the easy SAP IQ Database Installer is creating this now automatically.
sq1adm@hostname:/usr/sap/SQ1/data/db>
createcert -x -ca 1 -co rsaroot.crt -ko rsaroot.key -kp changeit -io dummy -scn "IQ Certification Authority" -so "IQ, Inc." -sou "IQ Signing Department" -sl "Walldorf" -sst "ON" -sc "CA" -3des -v 10 -u 6,7 -b 2048 -m "101"
createcert -ca 0 -co dummy1 -ko dummy2 -kp changeit -io rsaroot.id -c rsaroot.crt -ck rsaroot.key -cp changeit -scn "hostname.fqdn" -so "IQ Inc." -sou "IQ Accounts" -sl "Walldorf" -sst "ON" -sc "CA" -3des -v 10 -u 1,3 -b 2048 -m "102"
sq1adm@hostname:/usr/sap/SQ1/data/db> dir
-rwxrwxr-x 1 sq1adm sapsys 1436 Jul 26 13:47 rsaroot.crt
-rwxrwxr-x 1 sq1adm sapsys 4729 Jul 26 13:47 rsaroot.id
-rwxrwxr-x 1 sq1adm sapsys 1874 Jul 26 13:47 rsaroot.key
sq1adm@hostname:/usr/sap/SQ1/data/db>
or use the Tutorial - Set Up Transport Layer Security (TLS) in SAP IQ
connect to a SSL/TLS encrypted SAP IQ Database via dbisql has to be enhanced:
dbisql -c "uid=DBA;pwd=Sybase4me;eng=hostname_<SID_<nr>;LINKS=TCPIP;ENC=TLS(FIPS=Y;TLS_TYPE=RSA;trusted_certificate=hostname_<SID>_<nr>.crt;skip_certificate_name_check=on)" -nogui
the SAP IQ Remote Source had to be enhanced as well (Extra Adapter Properties):
EngineName=hostname_<SID>_<nr>;DatabaseName=SAPIQDB;CommLinks=tcpip(host=hostname.fqdn;port=3<nr>38);LINKS=TCPIP;ENC=TLS(FIPS=Y;TLS_TYPE=RSA;trusted_certificates=/usr/sap/B4H/SYS/global/hostnam_<SID>_<nr>.crt;skip_certificate_name_check=on)
the DBCO String is also enhanced for the SSL/TLS usage:
SYBASE_SERVER=hostname.fqdn SYBASE_PORT=3<nr>38 SYBASE_IQ_ENGINE=hostname_<SID>_<nr>
SYBASE_DBNAME=SAPIQDB SYBASE_SSL=1 skip_certificate_name_check=yes
SYBASE_IQ_LOAD_DIR=/sapmnt/IQ SYBASE_CONTYPE=IQ SYBASE_IQ_LOAD=1 SIQ_BULK_OP=1
SYBASE_IQ_CESU-8=1 SYBASE_IQ_LOCKWAIT=600 SYBASE_IQ_BUFFER_SIZE=500000
as this exeeds the limit of 256 CHAR in SAP NetWeaver Versions until 7.50, it is almost not possible the activate the SSL/TLS option in lower Systems due to the limit in the Table DBCON.
the new DBACOCKPIT for IQ
As Flash is no longer available from Adobe since end of 2020, the SAP IQ COCKPIT cannot be used anymore as of now. If your Company is not Blocking Flash System Wide, simply use the Waterfox Browser to continue.
You can use the improved and extended IQ Monitoring with Transaction DBACOCKPIT.
Blog - enable SAP IQ for DBACOCKPIT
Note 3223681 – New features for DBACOCKPIT on SAP IQ (cumulative Release)
contains the TCI for SAP_BASIS releases 750 SP11-SP25 and 753 SP00-SP09
for SAP Basis 7.56 (BW/4 2021) the new DBACOCKPIT for IQ is part of the SAP Basis 7.56 SP03
the SAP BW(/4) nearline storage configuration
Blog - SAP-NLS Solution for SAP BW based on SAP IQ
For the SAP-NLS Implementation finally, you must call some transactions in the SAP BW(/4) backend:
- DBCO => depending on the scenario additional work for SDA is necessary
- RSDANLCON => create the NLS storage target
- DBACOCKPIT => reuse the DBCO connection to monitor the Database within BW(/4)
- RSOADSODTO => cold store usage with BW/4
the SAP S/4 ILM Store configuration
Blog - SAP ILM based on SAP IQ Database
For the SAP ILM Implementation finally, you have to call some transactions in the SAP S/4 backend:
- SICF => configure Web Services
- ILMSTOREADM => ILM Store Administration
- WEBGUI => for Web Dynpro only Functions, like SFW5 or AS_AFB
- SARA => Archive Administration
- SARI => Archive Information System
The needed files can be found in the Document -
SAP First Guidance - Q - the easy SAP IQ Database Installer
- please contact roland.kramer@first-guidance.com for a copy of the Document
- a recording introducing Q (Q-CustomerCoffeeCorner.mp4)
- an example logfile - nls.execution.status
Roland Kramer
@SAPFirstGuidance
“I have no special talent; I am only passionately curious.”
| User | Count |
|---|---|
| 17 | |
| 6 | |
| 6 | |
| 5 | |
| 4 | |
| 4 | |
| 3 | |
| 3 | |
| 3 | |
| 3 |
