Skip to Content
Technical Articles
Author's profile photo Roland Kramer

Q – the easy Installer for SAP IQ

last changed: 30th of June 2022

Q – the easy Installer for SAP IQ – Motivation …

 

Blog Content

 


Beginning with 2019, 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)

Beside that, other bullet points came into the consideration:

Despite that, 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

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:

  • <SID> (What, also IQ get’s 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 HelpConfiguring SSL for SAP Host Agent on UNIX

or run the following command (same setup as for the SL Container Bridge):

server:/usr/sap/hostctrl/exe #./saphostexec -install -setup slplugin -passwd
server:/usr/sap/hostctrl/exe #

add the following parameter to the file host_profile and restart the SAP Host Agent

server:/usr/sap/hostctrl/exe # vi host_profile
service/admin_users = sapadm <sid>adm
service/disable_capability = ExecuteOperation/PermissionPolicy

server:/usr/sap/hostctrl/exe # ./saphostexec -restart

now it is possible to call the SAP Host Agent on HTTPS Port 1129

#create the url on host via:
echo https://$(hostname -f):1129/NLSSetup/prepare

 


the Answer: Q – the easy Installer for SAP IQ

Q introduces to James …

the following picture shows the Setup of Q, which is actually the same Installation Procedure as the SAP Datahub SL Container Bridge Architecture … (for the Installer contact roland.kramer@sap.com)

Q%20-%20overall%20process%20of%20functionality

Q – overall process of functionality


Version v0.74 uses “standard” values for IQ Block and Page Size

Q – enter the three values

 

Please Note: the latest used Version is now IQ 16.4 SP04 PL13 or SP05 PL03
for SAP NLS and ILM scenarios, also update the IQ Network Client to the latest Version

Note 3210982 – SAP IQ 16.1 SP04 PL13 (Build 17090) Release Notes Information
Note 3210938 – SAP IQNC 16.1 SP04 PL13 (Build 17090) Release Notes Information

Note 3213605 – SAP IQ 16.1 SP05 PL03 (Build 11595) Release Notes Information
Note 3214513 – SAP IQNC 16.1 SP05 PL03 (Build 11595) 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

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

JRE libraries are missing or not compatible....
Exiting....
server:/ebf29137 # mount | grep "/tmp"
/dev/mapper/vg_sys_r1-tmp on /tmp type ext4 (rw,nodev,noexec,noatime,data=ordered)
server:/ebf29137 #
server:/ebf29137 # export IATEMPDIR=/sapmnt/tmp
server:/ebf29137 # export DISPLAY= 10.xxx.xxx.xxx:0.0
server:/ebf29137 # ./setup.bin

 

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.

 

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

 


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

sp_iqstatus;
sp_iqlmconfig;
sp_iqcheckoptions;
sp_iqconnection;
sp_iqwho;

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.

sq1adm@hostname:/usr/sap/SQ1/data/db>
iqdsn -y -w "hostname_SQ1_11" -c "UID=DBA;PWD=Sybase4me;DatabaseName=SAPIQDB;
ServerName=hostname_SQ1_11;LINKS=tcpip(host=hostname.fqdn;port=31138)"
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 -y -w "utility" -c "UID=DBA;PWD=Sybase4me;
ServerName=utility;DatabaseName=utility_db;LINKS=tcpip(host=hostname.fqdn;port=34238)"
sq1adm@hostname:/usr/sap/SQ1/data/db> iqdsn -l
SAP IQ Data Source Utility Version 16.1.50.1949
SAP IQ User Data Sources:
utility
hostname_SQ1_11
sq1adm@hostname:/usr/sap/SQ1/data/db>

 

sq1adm@hostname:/usr/sap/SQ1/data/db>
dbisql -c "uid=SAPSQ1USR;pwd=Sybase4me;DSN=hostname_SQ1_11" -nogui
(SAPSQ1USR>) 
sp_iqlmconfig;
sp_iqstatus;
sp_iqcheckoptions;
sp_displayroles;
sp_iqdbspace;
sp_iqdbspaceobjectinfo USER0001;

 

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 IQ installation environment

export SECUDIR=/usr/sap/hostctrl/exe/sec
export PATH=/usr/sap/sapmachine-jdk-11.0.14.1/bin:/opt/openssl/bin:/usr/sap/hostctrl/exe:$PATH
export JAVA_HOME=/usr/sap/sapmachine-jdk-11.0.14.1/
export JRE_HOME=/usr/sap/sapmachine-jdk-11.0.14.1/

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 with SAPCryptoLib8 for SAP IQ with sapgenpse and openssl as follows:

go to the directory SECUDIR (/usr/sap/hostctrl/exe/sec)
1)	sapgenpse gen_pse -p hostname.pse -x changeit -r hostname.p10 "CN=hostname.fqdn"
2)	hostname.p10 => sending to CA =>  hostname_cr.p7b
3)	sapgenpse seclogin -p hostname.pse -x changeit -O root
4)	sapgenpse import_own_cert -p hostname.pse -x changeit -c hostname_cr.p7b
5)	sapgenpse get_my_name -p hostname.pse -x changeit -v
6)	sapgenpse export_p12 -p hostname.pse -x changeit -v hostname.p12
7)	openssl pkcs12 -in hostname.p12 -out hostname.id -nodes
8)	openssl pkcs7 -in hostname_cr.p7b -print_certs -out hostname.pem
9)	cp hostname.pem hostname.crt
10) openssl verify -CAfile hostname.crt hostname.id
11) viewcert hostname.pem/hostname.id/hostname.crt
12) copy the files hostname.pem/hostname.id/hostname.crt to /usr/sap/<SID>/data/db

or use the TutorialSet Up Transport Layer Security (TLS) in SAP IQ

SSL%20decrypted%20remote%20source%20IQ%20-%20HANA

SSL decrypted remote source IQ – HANA

 

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;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

 


the new DBACOCKPIT for IQ


As Flash is not 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 3123892 – New features for DBACOCKPIT on SAP IQ (TCI for SAP_BASIS release 750 SP11-22 and 753 SP00-SP07)

tx. DBACOCKPIT – create/trigger IQ events

tx.%20DBACOCKPIT%20-%20check%20the%20Iq%20license

tx. DBACOCKPIT – check the IQ license

tx.%20DBACOCKPIT%20-%20check%20the%20IQ%20config%20file

tx. DBACOCKPIT – check the IQ config file

 


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 have to 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
  • 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@sap.com


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

 

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

 

 

Assigned Tags

      8 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Muhammad Shafiq
      Muhammad Shafiq

      Hi Roland Kramer,

      Thanks for sharing valuable knowledge and expertise with community. We have just  start  to Implement SAP NLS BW 7.4 with Sybase IQ 16.1 install on Windows Server 2016.After installation and configuration  with your Guide line for NLS.We have create DBCO connection with sybase IQ but connection not successfully showing error (ERROR Database connection SAPIQDB: ADBC error ‘Internal error 16 has occurred).We have implement relevant SAP Note but unfortunately can’t find any solution.

      Kindly give guideline regarding this.

      Regards,

      Muhammad Shafiq

       

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

      Hello,

      If not already figured out the root case: there are no Windows based IQ Client drivers for Unix Based SAP BW Application Servers available.

      Please try to find a suitable OS-DB combination ...

      Best regards Roland

      Author's profile photo Raymond Lackey
      Raymond Lackey

      Thank you for sharing, one question however, with the demise of Flash, Cockpit and SCC management in a browser is now done is it not?

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

      Hello Raymond

      No, it is not. you can still use the Transaction DBACOCKPIT to include the Monitoring Web based instead of Flash.

      As an Alternative, you can use Ruffle -  to emulate the Flash Content from the Cockpit, but it is not jet working properly ...

      Best Regards Roland

      Author's profile photo Ravikanth Lingamaneni
      Ravikanth Lingamaneni

      Try Waterfox browser and IQ cockpit works in it

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

      hello Ravikanth Lingamaneni

      thanks for the Tipp. I'm using Firefox 78.9.0esr with Adobe Flash Player at this time.
      unfortunately, when the Company blocks Flash Systemwide, also this Browser will not help.

      but much easier as the workaround with Firefox and the Non Supported Adobe Flash Player.

      Thanks and Best Regards Roland

      Author's profile photo Alexander Hach
      Alexander Hach

      Is this setup still up to date? Where can I find the nlssetup file?

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

      just write me an email ...