Step-by-Step Deployment of an NLS in a a BW Environment - Part I: Installing SAP IQ
1. Introduction
In this step-by-step, I will show all the necessary infrastructure configurations for the setup of NLS in an SAP BW environment running on top of a Hana database. NLS will be using an SAP IQ database. After these settings are done, the activities of the functional BW (or the figure responsible for data archiving) can start to move data to the NLS/IQ.
For the creation of this document I used information from the SAP documents "SAP First Guidance - SAP BW: Implementation SAP-NLS/SDA with SAP IQ 16.x" (https://assets.cdn.sap.com/sapcom/docs/ 2013/03/d2ff3a4c-577c-0010-82c7-eda71af511fa.pdf) and “SAP Sybase IQ 16 Hardware Sizing Guide” (https://www.sap.com/documents/2017/02/363ddfab-a77c-0010-82c7 -eda71af511fa.html). The first document is very detailed and has most of the activities that I performed here, but I think it lacks objectivity and a sequence of activities. I tried to use a more logical sequence here.
NLS in the scope of this tutorial is a solution used to move "warm" data from an SAP BW environment to a database at a lower cost than the main database in that environment. "Warm" data refers to that data that is not used as often as "hot" data. For example, in 2021, 2019 data is used less than 2020 or 2021 data. Thus, 2019 data can be considered "warm" data, and 2020 or 2021 data is "hot" data. What about "cold"? "Cold" data would be data that is only accessed under specific circumstances. For example, 2012 data is only needed for an audit so this data could be stored in a solution with even lower cost than NLS. Please keep in mind that this is just an example of data organization, each environment has its peculiarities and only the business area that uses this environment will be able to provide information about what would be "cold" and "warm" data. In some SAP documentation, NLS has been referred to as "cold" data storage, but I consider "cold" data to be data that is not necessarily available and may need some action to be restored to the environment. As such, I will consider "hot" data like that in Hana and "warm" data like that which can be moved to NLS. "Cold" data is beyond the scope of this document.
A little bit about how it works: for each BW object that is archived, some tables are created in IQ and Hana databases. At least one of the tables that are created in Hana is a virtual table that points to an IQ table. When a query is executed in BW, Hana itself is responsible for fetching this data from the IQ. The image below (source: https://blogs.sap.com/2016/06/15/reporting-on-nls-near-line-storage/) shows in a very clear way how queries are executed. It also shows that during the archiving process, DAP (Data Archive Process) directly accesses NLS.
The landscape I used was a Netweaver 7.5 (SID: NW1) running on Hana 2.0 (SID: HA1). The IQ used version was 16.1 (SID NLS) installed on a dedicated server with 16 CPUs and 128 Gb of memory. All on x64 hardware.
I've tried to detail each step as much as possible to allow even those who don't have a vast knowledge of IQ to setup this NLS, but anyway it's expected that the performer of these activities has intermediate knowledge of IT in general so that they can have a great learning experience.
This walkthrough got quite big. Not even LinkedIn would support all content in a single article and because of that, I'm splitting the content into 2 parts:
In this article, I put the syntax of the commands to be executed in a box of code and the complete result I put in a second one. For example, this is the command executed:
ls -l .profile
This is the result expected from the previous command:
sybase@servernls:~> ls -l .profile
-rw-r--r-- 1 sybase sapsys 1180 Nov? 9 12:15 .profile
sybase@servernls:~>
2. Used medias
To create this document, I used the following media:
3. OS preparation of the IQ server
I described some information that I used in preparing the server in the following steps. I also added OS-related activities that must be performed before installing the IQ.
3.1 Filesystem layout
servernls:/usr/sap # df -h | grep -e Mount -e NLS
Filesystem? ? ? ? ? ? ? ? ? ? ? ? Size? Used Avail Use% Mounted on
/dev/mapper/vg_data-lv_NLS? ? ? ? 3.0G? ?33M? 3.0G? ?2% /usr/sap/NLS
/dev/mapper/vg_data-lv_NLSbackup? 600G? ?33M? 600G? ?1% /usr/sap/NLS/data/backup
/dev/mapper/vg_data-lv_NLSmain? ? 190G? ?33M? 190G? ?1% /usr/sap/NLS/data/main
/dev/mapper/vg_data-lv_NLStemp? ? 230G? ?33M? 230G? ?1% /usr/sap/NLS/data/temp
/dev/mapper/vg_data-lv_NLSserver? ?10G? ?33M? ?10G? ?1% /usr/sap/NLS/server
/dev/mapper/vg_data-lv_NLSuser? ? 320G? ?33M? 320G? ?1% /usr/sap/NLS/data/user
3.2 Creation of sapsys group and sybase user
groupadd sapsys
useradd -d /home/sybase -g sapsys -m sybase
servernls:~ # groupadd sapsys
servernls:~ # useradd -d /home/sybase -g sapsys -m sybase
servernls:~ #
Edit the file /home/sybase/.profile and add the line below (SAP note 2803563):
export MALLOC_ARENA_MAX=1
Log off/log on and confirm that the variables are being set correctly:
grep MALLOC_ARENA_MAX .profile
echo $MALLOC_ARENA_MAX
sybase@servernls:~> grep MALLOC_ARENA_MAX .profile
export MALLOC_ARENA_MAX=1
sybase@servernls:~> echo $MALLOC_ARENA_MAX
1
3.3 Setting filesystems permissions
chown -R sybase:sapsys /usr/sap/NLS
find /usr/sap/NLS -ls
servernls:~ # chown -R sybase:sapsys /usr/sap/NLS
servernls:~ # find /usr/sap/NLS -ls
??? 64??? 0 drwxr-xr-x?? 4 sybase?? sapsys???????? 32 Nov? 6 09:36 /usr/sap/NLS
??? 67??? 0 drwxr-xr-x?? 6 sybase?? sapsys???????? 56 Nov? 6 09:36 /usr/sap/NLS/data
??? 64??? 0 drwxr-xr-x?? 2 sybase?? sapsys????????? 6 Nov? 6 09:36 /usr/sap/NLS/data/backup
??? 64??? 0 drwxr-xr-x?? 2 sybase?? sapsys????????? 6 Nov? 6 09:36 /usr/sap/NLS/data/main
??? 64??? 0 drwxr-xr-x?? 2 sybase?? sapsys????????? 6 Nov? 6 09:36 /usr/sap/NLS/data/temp
??? 64??? 0 drwxr-xr-x?? 2 sybase?? sapsys????????? 6 Nov? 6 09:36 /usr/sap/NLS/data/user
??? 64??? 0 drwxr-xr-x?? 2 sybase?? sapsys????????? 6 Nov? 6 09:36 /usr/sap/NLS/server
servernls:~ #
4. Installation of IQ Server
There are some tweaks to allow installation in graphical mode. It is also necessary for a terminal that has an X11 server such as MobaXterm.
As root execute the commands below:
chmod 755 /root
chmod o+r /root/.Xauthority
echo $DISPLAY
servernls:/tmp/syb_inst/ebf29663 # chmod 755 /root
servernls:/tmp/syb_inst/ebf29663 # chmod o+r /root/.Xauthority
servernls:/tmp/syb_inst/ebf29663 # echo $DISPLAY
localhost:10.0
servernls:/tmp/syb_inst/ebf29663 #
Using sybase user merge X authenticatio and export DISPLAY variable ("localhost:10.0" should match the result of "echo $DISPLAY" command above):
touch ~/.Xauthority
xauth merge /root/.Xauthority
export DISPLAY="localhost:10.0"
sybase@servernls:~> touch ~/.Xauthority
sybase@servernls:~> xauth merge /root/.Xauthority
sybase@servernls:~> export DISPLAY="localhost:10.0"
sybase@servernls:~>
To make sure that your configuration is working properly, the xclock and xeyes commands can be used. A new window for theirs should open if everything is correct:
After the terminal is configured to allow installation using the graphical interface, unzip the IQSERV161004P_7-80002767.TGZ media. The ebf29663 directory will be extracted with the setup.bin installer:
sybase@servernls:/tmp/syb_inst> ls -l ebf29663
total 91096
drwxr-xr-x 1 sybase sapsys????? 418 Sep 29 17:48 archives
-rwxr-xr-x 1 sybase sapsys??? 98024 Sep 29 17:48 Cover.SP04.07.txt
-rwxr-xr-x 1 sybase sapsys???? 2560 Sep 29 17:48 installer.properties
-rwxr-xr-x 1 sybase sapsys????? 377 Sep 29 17:48 readme_ja.txt
-rwxr-xr-x 1 sybase sapsys????? 298 Sep 29 17:48 readme.txt
-rwxr-xr-x 1 sybase sapsys????? 251 Sep 29 17:48 readme_zh.txt
-rwxr-xr-x 1 sybase sapsys 93166009 Sep 29 17:48 setup.bin
drwxr-xr-x 1 sybase sapsys?????? 20 Sep 29 17:48 sysam_utilities
sybase@servernls:/tmp/syb_inst>
Start the installation process:
sybase@servernls:/tmp/syb_inst/ebf29663> ./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...
IQ installation follows the traditional “Next, next, finish” process so a very handful settings will differ from the default. On the screens below are the options I used in each step of the installation.
Edit /home/sybase/.profile file and add the lines below:
. /usr/sap/NLS/server/IQ.sh
export PATH=$PATH:/usr/sap/NLS/server/shared/SAPJRE-8_1_062_64BIT/bin
export ODBC_IQ=libdbodbc17_r.so
Log off/log on and validate the variables:
grep -e IQ.sh -e PATH -e ODBC_IQ .profile
echo $PATH
echo $ODBC_IQ
sybase@servernls:~> grep -e IQ.sh -e PATH -e ODBC_IQ .profile
. /usr/sap/NLS/server/IQ.sh
export PATH=$PATH:/usr/sap/NLS/server/shared/SAPJRE-8_1_062_64BIT/bin
export ODBC_IQ=libdbodbc17_r.so
sybase@servernls:~> echo $PATH
/usr/sap/NLS/server/IQ-16_1/bin64:/usr/sap/NLS/server/COCKPIT-4/bin:/usr/sap/NLS/server/OCS-16_0/bin:/home/sybase/bin:/usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games:/usr/sap/NLS/server/shared/SAPJRE-8_1_062_64BIT/bin
sybase@servernls:~> echo $ODBC_IQ
libdbodbc17_r.so
sybase@servernls:~>
5. Creation of the IQ database
Create /usr/sap/NLS/data/log, /usr/sap/NLS/data/messages, /usr/sap/NLS/data/db directories:
mkdir -p /usr/sap/NLS/data/log /usr/sap/NLS/data/messages /usr/sap/NLS/data/db
sybase@servernls:~> mkdir -p /usr/sap/NLS/data/log /usr/sap/NLS/data/messages /usr/sap/NLS/data/db
sybase@servernls:~>
“cd” to the /usr/sap/NLS/data/db directory:
cd /usr/sap/NLS/data/db
sybase@servernls:~> cd /usr/sap/NLS/data/db
sybase@servernls:/usr/sap/NLS/data/db>
Start the utility_db database:
start_iq -n utility -x "tcpip{PORT=34238}" -su Sap123 -c 2048m -gp 32768 -iqmc 2048
sybase@servernls:/usr/sap/NLS/data/db> start_iq -n utility -x "tcpip{PORT=34238}" -su Sap123 -c 2048m -gp 32768 -iqmc 2048
?
?
Starting server utility on servernls at port? (11/06 10:11:22)
?
Run Directory?????? : /usr/sap/NLS/data/db
Server Executable?? : /usr/sap/NLS/server/IQ-16_1/bin64/iqsrv16
Server Output Log?? : /usr/sap/NLS/server/IQ-16_1/logfiles/utility.0001.srvlog
Server Version????? : 16.1.040.1490/SP04.07
Open Client Version : 16.0 SP03 PL06
User Parameters???? : '-n' 'utility' '-x' 'tcpip{PORT=34238}' '-su' 'Sap123' '-c' '2048m' '-gp' '32768' '-iqmc' '2048'
Default Parameters? : -gc 20 -gd all -gl all -gm 10 -ti 4400 -gn 25
?
I. 11/06 10:11:25.027807 SAP IQ
I. 11/06 10:11:25.028026 Version 16.1
I. 11/06 10:11:25.028067 (64bit mode)
I. 11/06 10:11:25.028080 Copyright 1992-2020 by SAP AG or an SAP affiliate company. All rights reserved
I. 11/06 10:11:25.028095 Copyright (c) 2020 SAP SE or an SAP affiliate company.
I. SAP IQ
Version 16.1
(64bit mode)
Copyright 1992-2020 by SAP AG or an SAP affiliate company. All rights reserved
Copyright (c) 2020 SAP SE or an SAP affiliate company.
11/06 10:11:25.028110 All rights reserved.
I. 11/06 10:11:25.028257 Use of this software is governed by the SAP Software Use Rights Agreement.
All rights reserved.
Use of this software is governed by the SAP Software Use Rights Agreement.
I. 11/06 10:11:25.028380 Refer to https://www.sap.com/about/agreements.html.
I. 11/06 10:11:25.028426
I. 11/06 10:11:25.028451 Processors detected: 16 logical processor(s) on 16 core(s) on 1 physical processor(s)
I. 11/06 10:11:25.028556 This server is licensed to use: all logical processors in the system
Refer to https://www.sap.com/about/agreements.html.
?
Processors detected: 16 logical processor(s) on 16 core(s) on 1 physical processor(s)
I. 11/06 10:11:25.028592 Processors in use by server: 16 logical processor(s) on 16 core(s) on 1 physical processor(s)
This server is licensed to use: all logical processors in the system
Processors in use by server: 16 logical processor(s) on 16 core(s) on 1 physical processor(s)
I. 11/06 10:11:25.028638 Running Linux 4.12.14-122.17-default #1 SMP Wed Feb 26 10:31:05 UTC 2020 (6090a75) on X86_64
Running Linux 4.12.14-122.17-default #1 SMP Wed Feb 26 10:31:05 UTC 2020 (6090a75) on X86_64
I. 11/06 10:11:25.028826 Server built for X86_64 processor architecture
Server built for X86_64 processor architecture
I. 11/06 10:11:25.073157 2084600K of memory used for caching
2084600K of memory used for caching
I. 11/06 10:11:25.073542 Minimum cache size: 2097152K, maximum cache size: 2097152K
I. 11/06 10:11:25.073710 Using a maximum page size of 32768 bytes
Minimum cache size: 2097152K, maximum cache size: 2097152K
Using a maximum page size of 32768 bytes
I. 11/06 10:11:25.089979 Multiprogramming level: 25
Multiprogramming level: 25
I. 11/06 10:11:25.090313 Automatic tuning of multiprogramming level is disabled
Automatic tuning of multiprogramming level is disabled
?
?
=============================================================
IQ server starting with:
???? 10 connections???????? (?????? -gm )
???? 42 cmd resources?????? ( -iqgovern )
??? 893 threads???????????? (???? -iqmt )
??? 512 Kb thread stack size?? (?? -iqtss? )
? 457216 Kb thread memory size ( -iqmt * -iqtss )
?? ??16 IQ number of cpus? ( -iqnumbercpus )
????? 0 MB maximum size of IQMSG file ( -iqmsgsz )
????? 0 copies of IQMSG file archives ( -iqmsgnum )
?? 2048 MB maximum size of main buffer cache ( -iqmc )
???? 64 MB maximum size of temp buffer cache ( -iqtc )
?? 2048 MB maximum size of large memory pool ( -iqlm )
????? 0 MB maximum size of heap memory ( -iqmem )
?? 2048 MB maximum size of RLV memory ( -iqrlvmem )
=============================================================
?
I. 11/06 10:11:25.276666 Database server started at Fri Nov 06 2020 10:11
HOS_MEMMGR: Heap memory tracking is enabled.
[hos_InMemMsgLog] Using perThreadBufferLength=1048576 bytes...
Database server started at Fri Nov 06 2020 10:11
I. 11/06 10:11:25.277538 Trying to start SharedMemory link ...
Trying to start SharedMemory link ...
I. 11/06 10:11:25.278181???? SharedMemory link started successfully
??? SharedMemory link started successfully
I. 11/06 10:11:25.278259 Trying to start TCPIP link ...
Trying to start TCPIP link ...
I. 11/06 10:11:25.279052 Starting on port 34238
Starting on port 34238
I. 11/06 10:11:30.280260???? TCPIP link started successfully
??? TCPIP link started successfully
I. 11/06 10:11:30.281993 Now accepting requests
Now accepting requests
New process id is 20238
?
Server started successfully
?
sybase@servernls:/usr/sap/NLS/data/db>
To create the database, create a script with the content below. In this example, the DBA user's password will be MyPassword. This password is set at database creation time and can be changed at any time.
CREATE DATABASE '/usr/sap/NLS/data/db/SAPIQDB.db'
LOG ON '/usr/sap/NLS/data/log/SAPIQDB.log'
CASE RESPECT
PAGE SIZE 32768
COLLATION 'CESU8BIN' ENCODING 'CESU8'
DBA USER 'DBA'
DBA PASSWORD 'MyPassword'
BLANK PADDING ON
JCONNECT ON
IQ PATH '/usr/sap/NLS/data/main/SAPIQDB_main_001.iq'
IQ SIZE 20480
IQ PAGE SIZE 262144
IQ RESERVE 8192
BLOCK SIZE 32768
MESSAGE PATH '/usr/sap/NLS/data/messages/SAPIQDB_msg.iqmsg'
TEMPORARY PATH '/usr/sap/NLS/data/temp/SAPIQDB_tmp_001.iqtmp'
TEMPORARY SIZE 24576
Run the created script:
dbisql -c "uid=DBA;pwd=Sap123;eng=utility;dbn=utility_db" -nogui /tmp/create_database.sql
sybase@servernls:/usr/sap/NLS/data/db> dbisql -c "uid=DBA;pwd=Sap123;eng=utility;dbn=utility_db" -nogui /tmp/create_database.sql
CHAR collation sequence:? CESU8BIN(CaseSensitivity=Respect)
CHAR character set encoding:? CESU-8
NCHAR collation sequence:? UCA(CaseSensitivity=UpperFirst;AccentSensitivity=Respect;PunctuationSensitivity=Primary)
NCHAR character set encoding:? UTF-8
Database is not encrypted
Creating system tables
Creating system views
Setting option values
sybase@servernls:/usr/sap/NLS/data/db>
Stop the utility_db database with the stop_iq command:
sybase@servernls:/usr/sap/NLS/data/db> stop_iq
?
Checking system ...
?
The following 1 server(s) are owned by 'sybase'
?
## Owner????????? PID?? Started? CPU Time? Additional Information
-- ---------? -------? --------? --------? ------------------------------------
1: sybase?????? 20238???? 10:11? 00:00:14? SVR:utility DB:none PORT:
????????????? /usr/sap/NLS/server/IQ-16_1/bin64/iqsrv16 -n utility -x tcpip{PORT=34238} -su Sap123 -c 2048m -gp 32768 -iqmc 2048 -gc 20 -gd all -gl all -gm 10 -ti
--
?
??????? Please note that 'stop_iq' will shut down a server completely
??????? without regard for users, connections, or load process status.
??????? For more control, use the 'dbstop' utility, which has options
??????? that control stopping servers based on active connections.
?
Do you want to stop the server displayed above <Y/N>? y
?
Shutting down server (20238) ...
Checkpointing server (20238) ...
Server shutdown.
?
sybase@servernls:/usr/sap/NLS/data/db>
Create the server configuration file with the contents below. The file name must be SAPIQDB.cfg.
#/* -----------------------------------------------------------*/
#/* @(#)SAPIQDB.cfg */
#/* SAP IQ database instance "SAPIQDB" - CONFIGURATION */
#/* -----------------------------------------------------------*/
## Name of the IQ engine, e.g. <server>_<SID>_<instance>
-n SAPIQSERVER
## cache memory for catalog store. Set to 2 - 8 times size of the catalog file
-cl 256m
-ch 4096m
-gc 20
-gd all
## Use 80% of existing RAM and reserve 1/3 for every parameter
-iqlm 32768
-iqtc 32768
-iqmc 32768
# Specifies the number of SAP Sybase IQ threads to create.
-iqmt 968
## we have 4x4 core CPU hardware this forces on Intel to do more parallel
-iqnumbercpus 16
-iqpartition 16
-iqgovern 42
## network
-x tcpip{port=34238}
#### improving SQL anywhere interface for NLS queries
-gss 8192
-iqtss 8192
## Number of connections in the IQ/SQL anywhere server/stack size/page size
-gn 192
-gm 128
-gp 32768
#Disconnects inactive connections.
-ti 4400
## we force clients to use large size as default
#Sets the maximum size of communication packets.
-p 64000
## Displays diagnostic communication messages, and other messages, for troubleshooting purposes.
-z
## enable SAP IQ SSL network and database support
# -ec TLS(IDENTITY=server.id)
# -ep | -ek
## location of the temp dir for SQL anywhere (SAP Note 2084237)
-dt /usr/sap/NLS/data/temp
## LOG DIAGNOSTIC and Name of log file
-o /usr/sap/NLS/data/log/SAPIQDB_DIAG.log
-zo /usr/sap/NLS/data/log/SAPIQDB_PROTOCOL.log
## Number of log files and size of iqmessagefile in mb
-iqmsgsz 64
-iqmsgnum 16
-zn 5
-zs 10m
Also, create a link called params.cfg pointing to the generated file (Cockpit requirement):
ln -s SAPIQDB.cfg params.cfg
ls -l
sybase@servernls:/usr/sap/NLS/data/db> ln -s SAPIQDB.cfg params.cfg
sybase@servernls:/usr/sap/NLS/data/db> ls -l
total 17028
lrwxrwxrwx 1 sybase sapsys?????? 11 Nov? 6 10:46 params.cfg -> SAPIQDB.cfg
-rw-r--r-- 1 sybase sapsys???? 1554 Nov? 6 10:45 SAPIQDB.cfg
-r--r--r-- 1 sybase sapsys 17432576 Nov? 6 10:44 SAPIQDB.db
sybase@servernls:/usr/sap/NLS/data/db>
Start the created server/database. As the database initialization generates lots of information, I've copied here the snippets that I found most relevant.
start_iq -STARTDIR /usr/sap/NLS/data/db @params.cfg -n SAPIQDB -x 'tcpip(port=2638)' /usr/sap/NLS/data/db/SAPIQDB.db
sybase@servernls:/usr/sap/NLS/data/db> start_iq -STARTDIR /usr/sap/NLS/data/db @params.cfg -n SAPIQDB -x 'tcpip(port=2638)' /usr/sap/NLS/data/db/SAPIQDB.db
?
?
Starting server SAPIQSERVER on servernls at port 2638 (11/06 10:49:51)
?
Run Directory?????? : /usr/sap/NLS/data/db
Server Executable?? : /usr/sap/NLS/server/IQ-16_1/bin64/iqsrv16
Server Output Log?? : /usr/sap/NLS/data/log/SAPIQDB_DIAG.log
Server Version????? : 16.1.040.1490/SP04.07
Open Client Version : 16.0 SP03 PL06
User Parameters???? : '@params.cfg' '-n' 'SAPIQDB' '-x' 'tcpip(port=2638)' '/usr/sap/NLS/data/db/SAPIQDB.db'
Default Parameters? : -gl all
?
I. 11/06 10:49:54.034075 1: -n
I. 11/06 10:49:54.034309 2: SAPIQSERVER
I. 11/06 10:49:54.034438 3: -cl
I. 11/06 10:49:54.034680 4: 256m
1: -n
2: SAPIQSERVER
3: -cl
I. 11/06 10:49:54.034858 4: 256m
5: -ch
I. 11/06 10:49:54.035225 6: 4096m
5: -ch
I. 11/06 10:49:54.035323 7: -gc
6: 4096m
I. 11/06 10:49:54.035409 8: 207: -gc
?
I. 11/06 10:49:54.035507 8: 20
9: -gd
I. 11/06 10:49:54.035683 10: all9: -gd
?
I. 11/06 10:49:54.035801 11: -iqlm10: all
?
I. 11/06 10:49:54.036022 12: 3276811: -iqlm
?
I. 11/06 10:49:54.036153 13: -iqtc12: 32768
?
I. 11/06 10:49:54.036278 14: 3276813: -iqtc
?
I. 11/06 10:49:54.036374 15: -iqmc14: 32768
?
I. 11/06 10:49:54.036470 15: -iqmc
16: 32768
I. 11/06 10:49:54.036611 17: -iqmt16: 32768
?
I. 11/06 10:49:54.036743 17: -iqmt
18: 968
I. 11/06 10:49:54.036875 18: 968
19: -iqnumbercpus
I. 11/06 10:49:54.037011 20: 1619: -iqnumbercpus
?
I. 11/06 10:49:54.037138 21: -iqpartition20: 16
?
I. 11/06 10:49:54.037255 22: 1621: -iqpartition
?
I. 11/06 10:49:54.037381 23: -iqgovern22: 16
?
I. 11/06 10:49:54.037477 23: -iqgovern
24: 42
I. 11/06 10:49:54.037607 25: -x24: 42
?
I. 11/06 10:49:54.037719 26: tcpip{port=34238}25: -x
?
I. 11/06 10:49:54.037821 26: tcpip{port=34238}
27: -gss
I. 11/06 10:49:54.037952 28: 819227: -gss
?
I. 11/06 10:49:54.038055 29: -iqtss28: 8192
?
I. 11/06 10:49:54.038181 30: 819229: -iqtss
?
I. 11/06 10:49:54.038291 31: -gn30: 8192
?
I. 11/06 10:49:54.038408 32: 19231: -gn
?
I. 11/06 10:49:54.038502 33: -gm32: 192
?
I. 11/06 10:49:54.038630 34: 12833: -gm
?
I. 11/06 10:49:54.038734 35: -gp34: 128
?
I. 11/06 10:49:54.038865 36: 3276835: -gp
?
I. 11/06 10:49:54.038974 37: -ti36: 32768
?
I. 11/06 10:49:54.039073 38: 440037: -ti
?
I. 38: 4400
11/06 10:49:54.039185 39: -p
I. 11/06 10:49:54.039315 39: -p
40: 64000
I. 11/06 10:49:54.039442 41: -z40: 64000
?
I. 11/06 10:49:54.039548 42: -dt41: -z
?
I. 11/06 10:49:54.039650 42: -dt
43: /usr/sap/NLS/data/temp
I. 11/06 10:49:54.039778 44: -o43: /usr/sap/NLS/data/temp
?
I. 11/06 10:49:54.039888 44: -o
45: /usr/sap/NLS/data/log/SAPIQDB_DIAG.log
I. 11/06 10:49:54.039997 46: -zo45: /usr/sap/NLS/data/log/SAPIQDB_DIAG.log
?
I. 11/06 10:49:54.040108 47: /usr/sap/NLS/data/log/SAPIQDB_PROTOCOL.log
46: -zo
I. 11/06 10:49:54.040212 48: -iqmsgsz47: /usr/sap/NLS/data/log/SAPIQDB_PROTOCOL.log
?
I. 11/06 10:49:54.040312 49: 6448: -iqmsgsz
?
I. 11/06 10:49:54.040408 50: -iqmsgnum49: 64
?
I. 11/06 10:49:54.040503 51: 1650: -iqmsgnum
?
I. 11/06 10:49:54.040601 52: -zn51: 16
?
I. 11/06 10:49:54.040696 53: 552: -zn
?
I. 11/06 10:49:54.040794 54: -zs53: 5
?
I. 54: -zs
11/06 10:49:54.040909 55: 10m
I. 11/06 10:49:54.041062 55: 10m
56: -n
I. 11/06 10:49:54.041194 56: -n
57: SAPIQDB
I. 11/06 10:49:54.041305 58: -x57: SAPIQDB
?
I. 11/06 10:49:54.041403 59: tcpip(port=2638)58: -x
?
I. 11/06 10:49:54.041529 60: /usr/sap/NLS/data/db/SAPIQDB.db59: tcpip(port=2638)
?
I. 11/06 10:49:54.041624 61: -gl60: /usr/sap/NLS/data/db/SAPIQDB.db
?
I. 11/06 10:49:54.041719 62: all61: -gl
?
I. 11/06 10:49:54.041834 62: all
63: -hn
I. 11/06 10:49:54.041969 64: 563: -hn
?
I. 11/06 10:49:54.042064
64: 5
I. 11/06 10:49:54.042165 SAP IQ
?
I. 11/06 10:49:54.042263 Version 16.1SAP IQ
?
I. 11/06 10:49:54.042361 (64bit mode)Version 16.1
?
I. 11/06 10:49:54.042458 Copyright 1992-2020 by SAP AG or an SAP affiliate company. All rights reserved(64bit mode)
?
I. 11/06 10:49:54.042558 Copyright 1992-2020 by SAP AG or an SAP affiliate company. All rights reserved
Copyright (c) 2020 SAP SE or an SAP affiliate company.
I. 11/06 10:49:54.042688 All rights reserved.Copyright (c) 2020 SAP SE or an SAP affiliate company.
?
I. All rights reserved.
11/06 10:49:54.042806 Use of this software is governed by the SAP Software Use Rights Agreement.
I. 11/06 10:49:54.042979 Use of this software is governed by the SAP Software Use Rights Agreement.
Refer to https://www.sap.com/about/agreements.html.
I. Refer to https://www.sap.com/about/agreements.html.
11/06 10:49:54.043102
I.
11/06 10:49:54.043213 Processors detected: 16 logical processor(s) on 16 core(s) on 1 physical processor(s)
I. Processors detected: 16 logical processor(s) on 16 core(s) on 1 physical processor(s)
11/06 10:49:54.043373 This server is licensed to use: all logical processors in the system
I. This server is licensed to use: all logical processors in the system
11/06 10:49:54.043503 Processors in use by server: 16 logical processor(s) on 16 core(s) on 1 physical processor(s)
I. Processors in use by server: 16 logical processor(s) on 16 core(s) on 1 physical processor(s)
11/06 10:49:54.043635 Running Linux 4.12.14-122.17-default #1 SMP Wed Feb 26 10:31:05 UTC 2020 (6090a75) on X86_64
I. Running Linux 4.12.14-122.17-default #1 SMP Wed Feb 26 10:31:05 UTC 2020 (6090a75) on X86_64
11/06 10:49:54.043756 Server built for X86_64 processor architecture
Server built for X86_64 processor architecture
I. 11/06 10:49:54.067020 263804K of memory used for caching
I. 11/06 10:49:54.067308 Minimum cache size: 263584K, maximum cache size: 4194304K
263804K of memory used for caching
I. 11/06 10:49:54.067498 Using a maximum page size of 32768 bytes
Minimum cache size: 263584K, maximum cache size: 4194304K
Using a maximum page size of 32768 bytes
I. 11/06 10:49:54.101963 Multiprogramming level: 192
I. 11/06 10:49:54.102132 Automatic tuning of multiprogramming level is disabled
Multiprogramming level: 192
Automatic tuning of multiprogramming level is disabled
I. 11/06 10:49:54.102462 IPv6 support enabled
IPv6 support enabled
?
?
=============================================================
IQ server starting with:
??? 128 connections???????? (?????? -gm )
???? 42 cmd resources?????? ( -iqgovern )
??? 968 threads???????????? (???? -iqmt )
?? 8192 Kb thread stack size?? (?? -iqtss? )
? 7929856 Kb thread memory size ( -iqmt * -iqtss )
???? 16 IQ number of cpus? ( -iqnumbercpus )
???? 64 MB maximum size of IQMSG file ( -iqmsgsz )
???? 16 copies of IQMSG file archives ( -iqmsgnum )
? 32768 MB maximum size of main buffer cache ( -iqmc )
? 32768 MB maximum size of temp buffer cache ( -iqtc )
? 32768 MB maximum size of large memory pool ( -iqlm )
????? 0 MB maximum size of heap memory ( -iqmem )
?? 2048 MB maximum size of RLV memory ( -iqrlvmem )
=============================================================
?
E. 11/06 10:49:54.315950 WARNING: Failed to load PAM library - PAMUA will be disabled
WARNING: Failed to load PAM library - PAMUA will be disabled
I. 11/06 10:49:54.332091 Starting database "SAPIQDB" (/usr/sap/NLS/data/db/SAPIQDB.db) at Fri Nov 06 2020 10:49
HOS_MEMMGR: Heap memory tracking is enabled.
[hos_InMemMsgLog] Using perThreadBufferLength=1048576 bytes...
Starting database "SAPIQDB" (/usr/sap/NLS/data/db/SAPIQDB.db) at Fri Nov 06 2020 10:49
I. 11/06 10:49:55.663857 UTC time zone offset for "SAPIQDB" set to -180 minutes
I. 11/06 10:49:55.664343 Transaction log: /usr/sap/NLS/data/log/SAPIQDB.log
I. 11/06 10:49:54. IQ OpenDatabase, checkpointblock: 131090, recover: 1
I. 11/06 10:49:54. Using AIO based prefetch manager
I. 11/06 10:49:55. Using AIO based prefetch manager
I. 11/06 10:49:55. 140110919739136 [MpxTrace-GFLSHT]-stcxtlib/st_globalFlMgr.cxx:00075 The Diagnostic toggle value for st_globalFlMgr is set to 0
UTC time zone offset for "SAPIQDB" set to -180 minutes
Transaction log: /usr/sap/NLS/data/log/SAPIQDB.log
I. 11/06 10:49:55.679957 Starting checkpoint of "SAPIQDB" (SAPIQDB.db) at Fri Nov 06 2020 10:49
Starting checkpoint of "SAPIQDB" (SAPIQDB.db) at Fri Nov 06 2020 10:49
I. 11/06 10:49:55.805528 Finished checkpoint of "SAPIQDB" (SAPIQDB.db) at Fri Nov 06 2020 10:49
Finished checkpoint of "SAPIQDB" (SAPIQDB.db) at Fri Nov 06 2020 10:49
I. 11/06 10:49:58.612872 Update previous log GUID to: cf3e6306-2003-11eb-8000-be1e14d5f0f6
Update current log GUID to :ab0519fc-2004-11eb-8000-895503642702
I. 11/06 10:49:55. 140110919739136 [MpxTrace-GFLSHT]-stcxtlib/st_globalFlMgr.cxx:00075 The Diagnostic toggle value for st_globalFlMgr is set to 0
I. 11/06 10:49:57. IQ OpenDatabase, checkpointblock: 131098, recover: 0
I. 11/06 10:49:58. Using AIO based prefetch manager
I. 11/06 10:49:58. Using AIO based prefetch manager
Using licenses from: /usr/local/flexlm/licenses/license.dat
Checked out graced license for 1 IQ_CORE (2020.0910) will expire Thu 04 Feb 2021 12:00:00 AM -03.
Failed to obtain license(s) for IQ_CORE feature from license file(s) or server(s).
Cannot find license file.
?The license files (or license server system network addresses) attempted are listed below.
License feature name:? IQ_CORE
License filename:????? /usr/sap/NLS/server/SYSAM-2_0/licenses:/usr/sap/NLS/data/db
License search path:?? /usr/sap/NLS/server/SYSAM-2_0/licenses:/usr/sap/NLS/data/db
FlexNet Licensing error:-1,359.? System Error: 2 "No such file or directory"
WARNING: IQ functionality that requires the IQ_CORE license will be disabled on Thu 04 Feb 2021 12:00:00 AM -03, unless a suitable IQ_CORE license is obtained before that date.
Update previous log GUID to: cf3e6306-2003-11eb-8000-be1e14d5f0f6
Update current log GUID to :ab0519fc-2004-11eb-8000-895503642702
I. 11/06 10:49:58.689941 Database "SAPIQDB" (SAPIQDB.db) started at Fri Nov 06 2020 10:49
Database "SAPIQDB" (SAPIQDB.db) started at Fri Nov 06 2020 10:49
I. 11/06 10:49:58.729899 IQ Server SAPIQSERVER.
IQ Server SAPIQSERVER.
I. 11/06 10:49:58.731010 Starting checkpoint of "SAPIQDB" (SAPIQDB.db) at Fri Nov 06 2020 10:49
Starting checkpoint of "SAPIQDB" (SAPIQDB.db) at Fri Nov 06 2020 10:49
I. 11/06 10:49:58.887984 Finished checkpoint of "SAPIQDB" (SAPIQDB.db) at Fri Nov 06 2020 10:49
Finished checkpoint of "SAPIQDB" (SAPIQDB.db) at Fri Nov 06 2020 10:49
Database server started at Fri Nov 06 2020 10:49
Trying to start SharedMemory link ...
I. 11/06 10:49:58.895430 Database server started at Fri Nov 06 2020 10:49
I. 11/06 10:49:58.895740 Trying to start SharedMemory link ...
I. 11/06 10:49:58.896852???? SharedMemory link started successfully
I. 11/06 10:49:58.897069 Trying to start TCPIP link ...
??? SharedMemory link started successfully
Trying to start TCPIP link ...
I. 11/06 10:49:58.898164 Starting on port 34238
Starting on port 34238
I. 11/06 10:49:58.898570 Starting TCPIP listener on IP address (::):34238
Starting TCPIP listener on IP address (::):34238
I. 11/06 10:49:58.899181 Starting TCPIP listener on IP address 0.0.0.0:34238
Starting TCPIP listener on IP address 0.0.0.0:34238
I. 11/06 10:49:58.899432 Starting UDP listener on IP address 0.0.0.0:34238
Starting UDP listener on IP address 0.0.0.0:34238
I. 11/06 10:49:58.899704 Starting UDP listener on IP address (::):34238
Starting UDP listener on IP address (::):34238
I. 11/06 10:49:58.899964 Starting UDP listener on IP address 0.0.0.0:2638
Starting UDP listener on IP address 0.0.0.0:2638
I. 11/06 10:49:58.900199 Starting UDP listener on IP address (::):2638
Starting UDP listener on IP address (::):2638
I. 11/06 10:49:58.900574 Looking for server with name sapiqserver
I. 11/06 10:49:58.900630 Sending broadcast to find server
Looking for server with name sapiqserver
I. 11/06 10:49:58.900673 Using broadcast address of: 127.255.255.255
Sending broadcast to find server
Using broadcast address of: 127.255.255.255
I. 11/06 10:49:58.900793 Looking for server with name sapiqserver
Looking for server with name sapiqserver
I. 11/06 10:49:58.900842 Sending broadcast to find server
I. 11/06 10:49:58.900896 Using broadcast address of: 192.168.82.255
Sending broadcast to find server
Using broadcast address of: 192.168.82.255
I. 11/06 10:49:58.901031 Looking for server with name sapiqserver
Looking for server with name sapiqserver
I. 11/06 10:49:58.901108 Sending broadcast to find server
I. 11/06 10:49:58.901149 Using broadcast address of: ff02::1%2
Sending broadcast to find server
Using broadcast address of: ff02::1%2
I. 11/06 10:50:03.901199???? Server not found (no reply received)
I. 11/06 10:50:03.901509???? TCPIP link started successfully
??? Server not found (no reply received)
??? TCPIP link started successfully
Now accepting requests
I. 11/06 10:50:03.903034 Now accepting requests
New process id is 21557
?
Server started successfully
?
sybase@servernls:/usr/sap/NLS/data/db>
Validate connectivity. The password is “MyPassword” or the password that was set during the creation of the database.
dbisql -c "uid=DBA;eng=SAPIQSERVER;dbn=SAPIQDB" -nogui
sybase@servernls:/usr/sap/NLS/data/db> dbisql -c "uid=DBA;eng=SAPIQSERVER;dbn=SAPIQDB" -nogui
Password for DBA:
?
(DBA)> quit
sybase@servernls:/usr/sap/NLS/data/db>
6. Configuring the IQ Cockpit Service
As root, create the link below:
ln -s /usr/bin/env /bin/env
servernls:~ # ln -s /usr/bin/env /bin/env
servernls:~ #
If the Cockpit service has been started, stop it. With the sybase user:
$SYBASE/COCKPIT-4/bin/cockpit.sh -stop
sybase@servernls:~> $SYBASE/COCKPIT-4/bin/cockpit.sh -stop
Cockpit server is stopped
sybase@servernls:~>
Start it in the background:
nohup $SYBASE/COCKPIT-4/bin/cockpit.sh > ~/cockpit-console.out &
sybase@servernls:~> nohup $SYBASE/COCKPIT-4/bin/cockpit.sh > cockpit-console.out &
[1] 23844
sybase@servernls:~> nohup: ignoring input and redirecting stderr to stdout
?
sybase@servernls:~>
The login URL can be found in the ~/cockpit-console.out file:
sybase@servernls:~> grep https cockpit-console.out
https://servernls:4283/cockpit
sybase@servernls:~>
Create a DSN pointing to the created database:
iqdsn -y -w "SAPIQDB" -c "UID=DBA;PWD=MyPassword;ServerName=SAPIQSERVER;LINKS=tcpip(host= servernls;port=34238)"
sybase@servernls:~> iqdsn -y -w "SAPIQDB" -c "UID=DBA;PWD=MyPassword;ServerName=SAPIQSERVER;LINKS=tcpip(host= servernls;port=34238)"
SAP IQ Data Source Utility Version 16.1.40.1490
Configuration "SAPIQDB" written to file /home/sybase/.odbc.ini
sybase@servernls:~>
Validate connection using DSN:
dbping -d -c dsn=SAPIQDB
sybase@servernls:~> dbping -d -c dsn=SAPIQDB
SQL Anywhere Server Ping Utility Version 17.0.10.1490
Connected to SQL Anywhere 16.1.40.1490 server "SAPIQSERVER" and database "SAPIQDB"
at address 192.168.82.102.
Ping database successful.
sybase@servernls:~>
Create the necessary roles for administration using the IQ Cockpit:
dbisql -c dsn=SAPIQDB -nogui $SYBASE/COCKPIT-4/plugins/IQ-CMAP/cockpit_monitor_role_based_privileges_setup.sql
dbisql -c dsn=SAPIQDB -nogui $SYBASE/COCKPIT-4/plugins/IQ-CMAP/cockpit_monitor_authority_based_privileges_setup.sql
sybase@servernls:~> dbisql -c dsn=SAPIQDB -nogui $SYBASE/COCKPIT-4/plugins/IQ-CMAP/cockpit_monitor_role_based_privileges_setup.sql
sybase@servernls:~> dbisql -c dsn=SAPIQDB -nogui $SYBASE/COCKPIT-4/plugins/IQ-CMAP/cockpit_monitor_authority_based_privileges_setup.sql
sybase@servernls:~>
To register agents in the IQ Cockpit, access the URL "https://servernls:4283/cockpit", authenticate with the DBA user and password "MyPassword":
Navigate to EXPLORE -> IQ Servers -> select the SAPIQSERVER server and click on the down arrow:
First select Register Agent...:
Then click the down arrow again and this time select Authenticate Agent... (user uafadmin password is Sybase4me):
To create the “technical user” of the IQ Cockpit, grant execute permission in the TechnicalUserCreate.sh script:
ls -l $SYBASE/COCKPIT-4/plugins/IQ-CMAP/TechnicalUserCreate.sh
chmod u+x $SYBASE/COCKPIT-4/plugins/IQ-CMAP/TechnicalUserCreate.sh
ls -l $SYBASE/COCKPIT-4/plugins/IQ-CMAP/TechnicalUserCreate.sh
sybase@servernls:~> ls -l $SYBASE/COCKPIT-4/plugins/IQ-CMAP/TechnicalUserCreate.sh
-rw-r--r-- 1 sybase sapsys 1816 May? 4? 2020 /usr/sap/NLS/server/COCKPIT-4/plugins/IQ-CMAP/TechnicalUserCreate.sh
sybase@servernls:~> chmod u+x $SYBASE/COCKPIT-4/plugins/IQ-CMAP/TechnicalUserCreate.sh
sybase@servernls:~> ls -l $SYBASE/COCKPIT-4/plugins/IQ-CMAP/TechnicalUserCreate.sh
-rwxr--r-- 1 sybase sapsys 1816 May? 4? 2020 /usr/sap/NLS/server/COCKPIT-4/plugins/IQ-CMAP/TechnicalUserCreate.sh
sybase@servernls:~>
“cd” and run the TechnicalUserCreate.sh script
cd $SYBASE/COCKPIT-4/plugins/IQ-CMAP
./TechnicalUserCreate.sh
sybase@servernls:~> cd $SYBASE/COCKPIT-4/plugins/IQ-CMAP
sybase@servernls:/usr/sap/NLS/server/COCKPIT-4/plugins/IQ-CMAP> ./TechnicalUserCreate.sh
Do you wish to connect to local database? (y/n)n
Enter Technical User Login:iqtech
Enter Technical User Password:
Enter resource name for which to use this technical user:SAPIQSERVER
Technical user store is updated successfully.
sybase@servernls:/usr/sap/NLS/server/COCKPIT-4/plugins/IQ-CMAP>
Where: “iqtech” is the username to be created and “SAPIQSERVER” is the name of your IQ server (not the hostname of the machine, but the name of the “instance” IQ).
7. IQ Configuration
7.1 Creating the Application User
Creation of the application connection user with the database, in this case, the SAPBWNLS user is being created. Connect to the database using the dbisql syntax below:
dbisql -c dsn=SAPIQDB -nogui
And execute the commands below. I also used the password "MyPassword" but it must be changed to a secure one:
create user SAPBWNLS identified by 'MyPassword' login policy root;
grant READCLIENTFILE to SAPBWNLS;
grant MANAGE any EVENT to SAPBWNLS;
grant MONITOR to SAPBWNLS;
grant ROLE SYS_AUTH_RESOURCE_ROLE to SAPBWNLS with no ADMIN OPTION;
grant CREATE ANY OBJECT to SAPBWNLS with no ADMIN OPTION;
grant CREATE TABLE to SAPBWNLS with no ADMIN OPTION;
grant RESOURCE to SAPBWNLS;
sybase@servernls:~> dbisql -c dsn=SAPIQDB -nogui
?
(DBA)> create user SAPBWNLS identified by 'MyPassword' login policy root;
?
(DBA)> grant READCLIENTFILE to SAPBWNLS;
?
(DBA)> grant MANAGE any EVENT to SAPBWNLS;
?
(DBA)> grant MONITOR to SAPBWNLS;
?
(DBA)> grant ROLE SYS_AUTH_RESOURCE_ROLE to SAPBWNLS with no ADMIN OPTION;
?
(DBA)> grant CREATE ANY OBJECT to SAPBWNLS with no ADMIN OPTION;
?
(DBA)> grant CREATE TABLE to SAPBWNLS with no ADMIN OPTION;
?
(DBA)> grant RESOURCE to SAPBWNLS;
?
(DBA)> quit
sybase@servernls:~>
7.2 Configuration of IQ Server Options:
Validate the current options with the "sp_iqcheckoptions" procedure (omitted the "connection_authentication" option line for easier viewing):
sybase@servernls:~> dbisql -c dsn=SAPIQDB -nogui
?
(DBA)> sp_iqcheckoptions
User_name Option_name? ? ? ? ? ? ? ?Current_value? ?Default_value Option_type
-----------------------------------------------------------------------------
DBA? ? ? ?checkpoint_time? ? ? ? ? ?20? ? ? ? ? ? ? 60? ? ? ? ? ? Temporary
DBA? ? ? ?compression? ? ? ? ? ? ? ?Off? ? ? ? ? ? ?6? ? ? ? ? ? ?Temporary
DBA? ? ? ?progress_messages? ? ? ? ?Formatted? ? ? ?Off? ? ? ? ? ?Temporary
DBA? ? ? ?sql_flagger_error_level? ?Off? ? ? ? ? ? ?W? ? ? ? ? ? ?Temporary
DBA? ? ? ?sql_flagger_warning_level Off? ? ? ? ? ? ?W? ? ? ? ? ? ?Temporary
DBA? ? ? ?suppress_tds_debugging? ? On? ? ? ? ? ? ? Off? ? ? ? ? ?Temporary
DBA? ? ? ?time_zone_adjustment? ? ? -180? ? ? ? ? ? 0? ? ? ? ? ? ?Temporary
(8 rows)
(DBA)> quit
sybase@servernls:~>
Create a script with the parameters below:
set option public.allow_read_client_file='ON';
set option public.blocking='ON';
set option public.ansi_substring='OFF';
set option public.chained='OFF';
set option public.default_kb_per_stripe='128';
set option public.force_no_scroll_cursors='ON';
set option public.main_reserved_dbspace_mb='30720';
set option public.temp_reserved_dbspace_mb='15360';
set option public.iq_system_main_recovery_threshold='5';
set option public.max_cartesian_result='0';
set option public.string_rtruncation='OFF';
set option public.enable_lob_variables='ON';
Run it:
dbisql -c dsn=SAPIQDB -nogui /tmp/options.sql
sybase@servernls:~> dbisql -c dsn=SAPIQDB -nogui /tmp/options.sql
sybase@servernls:~>
Connect to the database and validate the new options with the procedure "sp_iqcheckoptions":
sybase@servernls:~> dbisql -c dsn=SAPIQDB -nogui
?
(DBA)> sp_iqcheckoptions
User_name Option_name? ? ? ? ? ? ? ? ? ? ? ?Current_value? ? ?Default_value Option_type
---------------------------------------------------------------------------------------
DBA? ? ? ?Default_KB_Per_Stripe? ? ? ? ? ? ?128? ? ? ? ? ? ? ?1024? ? ? ? ? Permanent
PUBLIC? ? Default_KB_Per_Stripe? ? ? ? ? ? ?128? ? ? ? ? ? ? ?1024? ? ? ? ? Permanent
DBA? ? ? ?Enable_LOB_Variables? ? ? ? ? ? ? On? ? ? ? ? ? ? ? OFF? ? ? ? ? ?Permanent
PUBLIC? ? Enable_LOB_Variables? ? ? ? ? ? ? ON? ? ? ? ? ? ? ? OFF? ? ? ? ? ?Permanent
DBA? ? ? ?Force_No_Scroll_Cursors? ? ? ? ? ?On? ? ? ? ? ? ? ? OFF? ? ? ? ? ?Permanent
PUBLIC? ? Force_No_Scroll_Cursors? ? ? ? ? ?ON? ? ? ? ? ? ? ? OFF? ? ? ? ? ?Permanent
DBA? ? ? ?IQ_SYSTEM_MAIN_RECOVERY_THRESHOLD 5? ? ? ? ? ? ? ? ?1? ? ? ? ? ? ?Permanent
PUBLIC? ? IQ_SYSTEM_MAIN_RECOVERY_THRESHOLD 5? ? ? ? ? ? ? ? ?1? ? ? ? ? ? ?Permanent
DBA? ? ? ?Main_Reserved_DBSpace_MB? ? ? ? ? 30720? ? ? ? ? ? ?200? ? ? ? ? ?Permanent
PUBLIC? ? Main_Reserved_DBSpace_MB? ? ? ? ? 30720? ? ? ? ? ? ?200? ? ? ? ? ?Permanent
DBA? ? ? ?Max_Cartesian_Result? ? ? ? ? ? ? 0? ? ? ? ? ? ? ? ?100000000? ? ?Permanent
PUBLIC? ? Max_Cartesian_Result? ? ? ? ? ? ? 0? ? ? ? ? ? ? ? ?100000000? ? ?Permanent
DBA? ? ? ?Temp_Reserved_DBSpace_MB? ? ? ? ? 15360? ? ? ? ? ? ?200? ? ? ? ? ?Permanent
PUBLIC? ? Temp_Reserved_DBSpace_MB? ? ? ? ? 15360? ? ? ? ? ? ?200? ? ? ? ? ?Permanent
DBA? ? ? ?allow_read_client_file? ? ? ? ? ? On? ? ? ? ? ? ? ? Off? ? ? ? ? ?Permanent
PUBLIC? ? allow_read_client_file? ? ? ? ? ? ON? ? ? ? ? ? ? ? Off? ? ? ? ? ?Permanent
DBA? ? ? ?ansi_substring? ? ? ? ? ? ? ? ? ? Off? ? ? ? ? ? ? ?On? ? ? ? ? ? Permanent
PUBLIC? ? ansi_substring? ? ? ? ? ? ? ? ? ? OFF? ? ? ? ? ? ? ?On? ? ? ? ? ? Permanent
DBA? ? ? ?blocking? ? ? ? ? ? ? ? ? ? ? ? ? On? ? ? ? ? ? ? ? Off? ? ? ? ? ?Permanent
PUBLIC? ? blocking? ? ? ? ? ? ? ? ? ? ? ? ? ON? ? ? ? ? ? ? ? Off? ? ? ? ? ?Permanent
DBA? ? ? ?chained? ? ? ? ? ? ? ? ? ? ? ? ? ?Off? ? ? ? ? ? ? ?On? ? ? ? ? ? Permanent
PUBLIC? ? chained? ? ? ? ? ? ? ? ? ? ? ? ? ?OFF? ? ? ? ? ? ? ?On? ? ? ? ? ? Permanent
DBA? ? ? ?checkpoint_time? ? ? ? ? ? ? ? ? ?20? ? ? ? ? ? ? ? 60? ? ? ? ? ? Temporary
DBA? ? ? ?compression? ? ? ? ? ? ? ? ? ? ? ?Off? ? ? ? ? ? ? ?6? ? ? ? ? ? ?Temporary
DBA? ? ? ?progress_messages? ? ? ? ? ? ? ? ?Formatted? ? ? ? ?Off? ? ? ? ? ?Temporary
DBA? ? ? ?sql_flagger_error_level? ? ? ? ? ?Off? ? ? ? ? ? ? ?W? ? ? ? ? ? ?Temporary
DBA? ? ? ?sql_flagger_warning_level? ? ? ? ?Off? ? ? ? ? ? ? ?W? ? ? ? ? ? ?Temporary
DBA? ? ? ?string_rtruncation? ? ? ? ? ? ? ? Off? ? ? ? ? ? ? ?On? ? ? ? ? ? Permanent
PUBLIC? ? string_rtruncation? ? ? ? ? ? ? ? OFF? ? ? ? ? ? ? ?On? ? ? ? ? ? Permanent
DBA? ? ? ?suppress_tds_debugging? ? ? ? ? ? On? ? ? ? ? ? ? ? Off? ? ? ? ? ?Temporary
DBA? ? ? ?time_zone_adjustment? ? ? ? ? ? ? -180? ? ? ? ? ? ? 0? ? ? ? ? ? ?Temporary
(32 rows)
(DBA)> quit
sybase@servernls:~>
7.3 Creation and extension of DBSPACES
Create a script with the content below:
-- USER0001 - 256 Gb
CREATE DBSPACE USER0001 USING
FILE USER0001_001 '/usr/sap/NLS/data/user/USER0001_001.iq' size 32768,
FILE USER0001_002 '/usr/sap/NLS/data/user/USER0001_002.iq' size 32768,
FILE USER0001_003 '/usr/sap/NLS/data/user/USER0001_003.iq' size 32768,
FILE USER0001_004 '/usr/sap/NLS/data/user/USER0001_004.iq' size 32768,
FILE USER0001_005 '/usr/sap/NLS/data/user/USER0001_005.iq' size 32768,
FILE USER0001_006 '/usr/sap/NLS/data/user/USER0001_006.iq' size 32768,
FILE USER0001_007 '/usr/sap/NLS/data/user/USER0001_007.iq' size 32768,
FILE USER0001_008 '/usr/sap/NLS/data/user/USER0001_008.iq' size 32768
STRIPING ON
STRIPESIZEKB 512;
?
-- IQ_SYSTEM_TEMP = 192 Gb
ALTER DBSPACE IQ_SYSTEM_TEMP ADD
FILE IQ_SYSTEM_TEMP_02 '/usr/sap/NLS/data/temp/SAPIQDB_tmp_002.iqtmp' size 24576,
FILE IQ_SYSTEM_TEMP_03 '/usr/sap/NLS/data/temp/SAPIQDB_tmp_003.iqtmp' size 24576,
FILE IQ_SYSTEM_TEMP_04 '/usr/sap/NLS/data/temp/SAPIQDB_tmp_004.iqtmp' size 24576,
FILE IQ_SYSTEM_TEMP_05 '/usr/sap/NLS/data/temp/SAPIQDB_tmp_005.iqtmp' size 24576,
FILE IQ_SYSTEM_TEMP_06 '/usr/sap/NLS/data/temp/SAPIQDB_tmp_006.iqtmp' size 24576,
FILE IQ_SYSTEM_TEMP_07 '/usr/sap/NLS/data/temp/SAPIQDB_tmp_007.iqtmp' size 24576,
FILE IQ_SYSTEM_TEMP_08 '/usr/sap/NLS/data/temp/SAPIQDB_tmp_008.iqtmp' size 24576;
?
-- SYSTEM_MAIN = 160 Gb
ALTER DBSPACE IQ_SYSTEM_MAIN ADD
FILE IQ_SYSTEM_MAIN_02 '/usr/sap/NLS/data/main/SAPIQDB_main_002.iq' size 20480,
FILE IQ_SYSTEM_MAIN_03 '/usr/sap/NLS/data/main/SAPIQDB_main_003.iq' size 20480,
FILE IQ_SYSTEM_MAIN_04 '/usr/sap/NLS/data/main/SAPIQDB_main_004.iq' size 20480,
FILE IQ_SYSTEM_MAIN_05 '/usr/sap/NLS/data/main/SAPIQDB_main_005.iq' size 20480,
FILE IQ_SYSTEM_MAIN_06 '/usr/sap/NLS/data/main/SAPIQDB_main_006.iq' size 20480,
FILE IQ_SYSTEM_MAIN_07 '/usr/sap/NLS/data/main/SAPIQDB_main_007.iq' size 20480,
FILE IQ_SYSTEM_MAIN_08 '/usr/sap/NLS/data/main/SAPIQDB_main_008.iq' size 20480;
Run it:
dbisql -c dsn=SAPIQDB -nogui /tmp/extend.sql
sybase@servernls:~> dbisql -c dsn=SAPIQDB -nogui /tmp/extend.sql
sybase@servernls:~>
7.4 Option settings related to the new DBSPACE
Connect to IQ using dbisql:
dbisql -c dsn=SAPIQDB -nogui
Make DBSPACE USER0001 the default one for public and SAPBWNLS users:
set option public.default_dbspace='USER0001';
set option SAPBWNLS.default_dbspace='USER0001';
sybase@servernls:~> dbisql -c dsn=SAPIQDB -nogui
?
(DBA)> set option public.default_dbspace='USER0001';
?
(DBA)> set option SAPBWNLS.default_dbspace='USER0001';
?
(DBA)> quit
sybase@servernls:~>
Optional: validate the options above. Connect to the database with dbisql and execute the sp_iqcheckoptions procedure. I'm omitting the values already validated from the result below.
sybase@servernls:~> dbisql -c dsn=SAPIQDB -nogui
?
(DBA)> sp_iqcheckoptions
User_name Option_name?????????????????????? Current_value??????????????????????????????????????????????????????????????????????????????????????????????????????????????????? Default_value Option_type
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
...
DBA?????? default_dbspace?????????????????? USER0001?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? Permanent
PUBLIC??? default_dbspace?????????????????? USER0001??????????????????????????????? ???????????????????????????????????????????????????????????????????????????????????????????????????????Permanent
SAPBWNLS? default_dbspace?????????????????? USER0001?????????????????????????????????????????????????????????????????????????????????????????? ????????????????????????????????????????????Permanent
...
?
(35 rows)
(DBA)> quit
sybase@servernls:~>
7.5 Licenses
The SAP note “2628620 - How to create Sybase IQ license keys - SAP ONE Support Launchpad” shows a step-by-step how to generate the license in SAP One Launchpad. SAP Note “2372218 - Request IQ licenses for SAP-NLS solution - SAP BW with SAP IQ Nearline-Storage” provides specific information about the license required to use IQ as NLS.
I generated three licenses for my environment with the features: IQ_CORE, IQ_VLDBMGMT,and IQ_UDA (which includes the IQ_LOB license). With the generated licenses, I copied the contents of each one of them and pasted it into a file called licenses.lic inside the /usr/sap/NLS/server/SYSAM-2_0/licenses/ directory.
To validate I restarted the IQ and in the boot log what it used to show:
I. 11/06 10:49:58. Using AIO based prefetch manager
Using licenses from: /usr/local/flexlm/licenses/license.dat
Checked out graced license for 1 IQ_CORE (2020.0910) will expire Thu 04 Feb 2021 12:00:00 AM -03.
Failed to obtain license(s) for IQ_CORE feature from license file(s) or server(s).
Cannot find license file.
?The license files (or license server system network addresses) attempted are listed below.
License feature name:? IQ_CORE
License filename:????? /usr/sap/NLS/server/SYSAM-2_0/licenses:/usr/sap/NLS/data/db
License search path:?? /usr/sap/NLS/server/SYSAM-2_0/licenses:/usr/sap/NLS/data/db
FlexNet Licensing error:-1,359.? System Error: 2 "No such file or directory"
WARNING: IQ functionality that requires the IQ_CORE license will be disabled on Thu 04 Feb 2021 12:00:00 AM -03, unless a suitable IQ_CORE license is obtained before that date.
Update previous log GUID to: cf3e6306-2003-11eb-8000-be1e14d5f0f6
Now it shows:
I. 11/06 13:04:18. Using AIO based prefetch manager
Using licenses from: /usr/sap/NLS/server/SYSAM-2_0/licenses/licenses.lic:/usr/sap/NLS/data/db/*.lic
Checked out license for 16 IQ_CORE (2021.12310/permanent/02CC B79A 8944 31FB).
Update previous log GUID to: ab0519fc-2004-11eb-8000-895503642702
Activate IQ licenses:
sp_iqlmconfig 'allow', 'ALL';
sp_iqlmconfig 'allow', 'IQ_VLDBMGMT', '4';
(DBA)> sp_iqlmconfig 'allow', 'ALL';
Property???????????????????????????? Value
--------------------------------------------------------
Licence Notice?????????????????????? xxxxxxxxxxxxxxx
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 : 0)
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???????????????????????? sybase
Email Recipients???????????????????? sybase
?
(19 rows)
?
?
(DBA)> sp_iqlmconfig 'allow', 'IQ_VLDBMGMT', '4';
Property???????????????????????????? Value
--------------------------------------------------------
Licence Notice?????????????????????? xxxxxxxxxxxxxxx
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???????????????????????? sybase
Email Recipients???????????????????? sybase
?
(19 rows)
?
?
(DBA)>
Test object creation:
CREATE TABLE dbo.udatest (U1 integer NOT NULL, U2 long varchar NULL, PRIMARY KEY(U1));
CREATE TEXT INDEX udaidx ON dbo.udatest(U2) CONFIGURATION default_char;
(DBA)> CREATE TABLE dbo.udatest (U1 integer NOT NULL, U2 long varchar NULL, PRIMARY KEY(U1));
?
(DBA)> CREATE TEXT INDEX udaidx ON dbo.udatest(U2) CONFIGURATION default_char;
?
(DBA)>
Validate the creation of these objects:
sp_iqdbspaceobjectinfo USER0001;
(DBA)> sp_iqdbspaceobjectinfo USER0001;
dbspace_name dbspace_id object_type owner object_name object_id?? id columns indexes metadata primary_key unique_constraint foreign_key partitions
--------------------------------------------------------------------------------------------------------------------------------------------------
USER0001????????? 16387 table?????? dbo?? udatest????????? 5013 1709 2/2???? 1/1???? Y????????????????? 1 0/0?????????????? 0/0???????? 0/0
(1 rows)
(DBA)>
Grant select permission on the table to the SAPBWNLS user (will be used in connectivity validation):
grant select on udatest to SAPBWNLS
(DBA)> grant select on udatest to SAPBWNLS
(DBA)>
Insert a test record into the newly created table:
insert into dbo.udatest (U1,U2) values (123456789,'The quick brown fox jumps over the lazy dog.')
(DBA)> insert into dbo.udatest (U1,U2) values (123456789,'The quick brown fox jumps over the lazy dog.')
1 row(s) inserted
(DBA)>
After the creation of the above objects, when checking the licenses again, IQ_UDA and IQ_LOB must have Yes:
sp_iqlmconfig;
(DBA)> sp_iqlmconfig 'allow', 'ALL';
Property???????????????????????????? Value
--------------------------------------------------------
Licence Notice?????????????????????? xxxxxxxxxxxxxxx
Edition????????????????????????????? EE
License Type???????????????????????? CP
Application Type???????????????????? IQ
IQ_CORE License Count in use???????? 16 (CPU core based)
Optional license in use : IQ_UDA???? Yes(Allowed)
Optional license in use : IQ_LOB???? Yes(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???????????????????????? sybase
Email Recipients???????????????????? sybase
(19 rows)
(DBA)>
8. End of the part I
If you've made it this far, your SAP IQ database is configured to work as an NLS repository for your BW environment. In the next part, I will describe the steps for the final settings in Hana and Netweaver.
GM - SAP COE | SAP Basis & HANA | Application | Database | Azure Cloud infrastructure | IT Budgets | Security Operation | Solution Architect | Downstream Integrations | GRC | SAP Implementations & Rollouts| HA & DR
6 个月Excellent post . Very helpful . The IQ cockpit is missing in 16.1 , sps05
Technical Support Manager at MDSap Turkey
1 年Thanks for this detailed documentation
Technical Service Manager at SAP | Enterprise Cloud Services
1 年Great work Alex, this document made it easy for me to install/set up SAP IQ Server, DB &the configuration steps required to set up Archiving. Thank you...
SAP Senior Basis Consultant/Technical Architect
1 年Fantastic work ! Thank you