SAP IQ Basic knowledge

What is SAP IQ ?

SAP IQ is the RDBMS that specializes in search speed by using the SAP SQL Anywhere (SQLA).
To the data file is stored in the compressed index information, by expanding all the data necessary for the query on the memory, and provides high-speed search speed.

SAP IQ is used in the following situations

Decision support system(DSS)
Data warehouse (DWH)

IQ Advantage

Major advantage of SAP IQ

High speed
- Designed for ad hoc queries
- Reduction of I/O in the search
- Speed of execution
Economy
- Superior data compression technology
- Reduction of disk storage size
Flexibility
- The use of different data sources

Features

Special data structure

Column width data structure
- Read and store the data in columns
- Returns only the columns required for the query, reduce the I/O
Bit width index
- High-speed calculation/comparison/Sort by new index of by the patented technology
Data compression
- Storing compressed data to disk
- Database size can be compressed to 1/10
Multiplex
- Provide a distributed processing by up to 1024 nodes
Provide a standard open interfaces

Structure of SAP IQ

(Simplex)

Structure of Catalog Engine

Catalog Engine(SQLAnywhere)
- params.cfg
  - Set of start-up parameters
- dbname.log
  - Transaction log file
- dbname.db
  - Catalog store(Pure SQLAnywhere database)
- dbname.000 – 999.srvlog
  - Server log file(Error log of Catalog engine)
Catalog engine will be used to manage the IQ database and user connections.

Structure of IQ engine

IQ engine will process the user queries.
IQ_SYSTEM_MAIN does not create a user table because the system DB store.
IQ engine (IQ)
- dbname.iq
  IQ_SYSTEM_MAIN DB store
- dbname.iqtmp
  Temporary DB store
- usermain.iq
  User DB store
- dbname.iqmsg
  IQ Message Log(Error of IQ engine)
- dbname.lmp
  License info

Location of the base file (SAP IQ 16.0)

IQ modules and LOG is exists in IQ-16_0 directory

Windows

IQ HOME : %IQDIR16%

SYBASE HOME: %SYBASE%

DB Files: User-specified directory

SRV Log:

oBy default, it is not created. If you start as an application, the task tray window in the log display. (KBA 1949642)

oIf you specify a start-up option "-o", it exists in the specified directory.

IQMSG File:

oDB file in the same directory (the default). Or directory specified in the MESSAGE PATH clause of the CREATE DATABASE statement.

Linux/UNIX

IQ HOME : $IQDIR16

SYBASE HOME: $SYBASE

DB file: User-specified directory

SRV log:

o$IQDIR16/logfiles directory. If you specify a start-up option "-o", it exists in the specified directory.

IQMSG file:

oDB file in the same directory (the default). Or directory specified in the MESSAGE PATH clause of the CREATE DATABASE statement.

Basic information required for the Incident registration

When you register the Incident, please attach the following files in order to find the exact product versions and error details to Incident.

iqmsg file

srvlog file

stderr file

These information does not need to be collected separately. SAP IQ provides a utility to collect this information. → getiqinfo

If the problem occurs, to get the results of one of the utility and three procedures.

getiqinfo - It is a utility to obtain all the environmental information of SAP IQ, including the OS information. (including Iqmsg, srvlog)

See Also: KBA 2120431 - How to gather information about an SAP IQ installation

sp_iqstatus - For the current database, display the status information of SAP IQ.

sp_iqtransaction*** - Gather information of a running transaction

sp_iqconnection*** - Gather information of current connection.

*** - Run several times at intervals of a few seconds

Recovery from abnormal termination

When IQ server is down, gather error logs before restart.

SAP IQ in order to reduce disk I/O, do most of the operations on the buffer cache (memory).
Therefore, even if the process is terminated abnormally if during the writing of the data, the memory data is rolled back to the time of restart, a possibility that the data on the device is broken is very low.
However, in order to verify the integrity of the page (disk), after the re-start from the abnormal termination, it is recommended that you always run the sp_iqcheckdb.
If the recovery time can not be taken, check the allocation of the entire database in the allocation mode.
–sp_iqcheckdb 'allocation database‘
After the restart from serious trouble, you can check the error of the entire database allocation and index in check mode.
–sp_iqcheckdb ‘check database'

Check the page breakage or leakage page using the results of sp_iqcheckdb.

Action and Output of sp_iqcheckd Modes

mode	Errors Detected	Output	Speed
Allocation	Allocation errors	Allocation statistics only	4TB / hour
Check	Allocation errors Most index errors	All available statistics	60GB/hour
Verify	Allocation errors All index errors	All available statistics	15GB/hour
Dropleaks	Allocation errors	Allocation statistics only	4TB/hour

Get a memory dump(Crash Dump)

If the following conditions are occurring, global support may require a memory dump of SAP IQ.

Error message and stack trace is not output to IQMSG and SRVLOG
Process terminates abnormally (silent crash)

Global Support analyzes the memory dump, and then get the stack trace where the problem occurred.

Search for past cases from the stack trace for problem-solving.
It is not possible to Fixed a code is also to identify the problem from the memory dump.
Fix the problem will require a reproduction procedure.

Reproducible -Linus/UNIX

Only in Linux / UNIX, if IQ server when you run a certain process is down, IQ process is capable of generating the Core file.

[Setting]
Set the IQ_UNLIMIT_CORE to YES.

[Ex]
% setenv IQ_UNLIMIT_CORE YES

YES, it must be specified in uppercase.

When you restart the IQ after setting, it will be displayed Notice to the start-up message.If you are able to down the IQ process again, it will complete Core file is generated.

Reproducible -Windows

Special procedures exist in the Windows

–When a IQ process was downed, VC++ Runtime Error screen is sometimes indicated.

–If you do not click on the OK button, IQ process does not disappear.

–Before that, look for IQ process from process tab of the task manager. (Iqsrv16.exe)

Select the IQ process, from the context menu, select the "Create Dump File", to create a process dump.

Then, restart the SAP IQ

.

If reproduction is rare, you can be set to automatically create a process dump at the time of the crash of the program.

1.Create the following key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\Windows Error Reporting\LocalDumps

2.Add the following values.
See Alosp: MSDN (https://msdn.microsoft.com/en-us/library/bb787181.aspx)

値	種類	設定例	説明
DumpFolder	REG_EXPAND_SZ	%LOCALAPPDATA%¥CrashDumps	The path where the dump files are to be stored
DumpCount	REG_DWORD	10	The maximum number of dump files in the folder. When the maximum value is exceeded, the oldest dump file in the folder will be replaced with the new dump file.
DumpType	REG_DWORD	1	Specify one of the following dump types: 0: Custom dump 1: Mini dump 2: Full dump
CustomDumpFlags	REG_DWORD	0	This value is used only when DumpType is set to 0.