Connect SAP IQ to SAP IQ using ODBC the easy way

The documentation on setting up a linked server is overly complex in SAP’s documentation and doesn’t actually tell you what you need to do. This is rather typical of SAP’s IQ documentation unfortunately. Setting up the linked server is easy and only takes about 20 seconds.

First add an entry into the $SYBASE/interfaces file. Seriously. There shouldn’t be any reason for this because we specify the ip/port in the server definition. Chock it up to SAP not cleaning up ancient code.

iq_02
    query tcp ether siq02 50000
    master tcp ether siq02 50000

Next we add the server entry into the local IQ server. Yes, you really do want to use the ASA (SQL Anywhere) driver here. You don’t have to do anything special for multiplex unlike the IQ driver which really doesn’t give you anything more than a raging headache and the urge to excessively partake in your least favorite poison.

CREATE SERRVER iq_02 CLASS 'ASAODBC' USING 'siq02:50000'

Finally we add the remote login mapping. In this case, I’m mapping the remote user “k2k23” to the local dba account:

CREATE EXTERNLOGIN "dba" to "iq_02" REMOTE LOGIN "k2k23" IDENTIFIED BY klj3haq345as56t

You are now free to set up your proxy tables and load from (remote) location without issue.

Share Button

SAP Sybase IQ: Do not use LF indexes in IQ v16

Really, there isn’t a good reason to use LF indexes in version 16 and higher of IQ. Use HG indexes instead. From Mark Mumy of SAP fame:

Mumy, Mark mark.mumy at sap.com
Mon Aug 17 11:31:37 MST 2015

There is no need with IQ 16 SP08.20 and beyond to use an LF. The HG index has been augmented over the past few years to take on the same characteristics for low cardinality data while adding parallelism and other join/sort features that just don’t exist in the LF.

Remove all LF indexes and replace them with HGs, if they don’t have one. There is no need for an HNG as the LF (don’t use) and HG have many of those features and can cost less.

Mark

Mark Mumy
Director, Enterprise Architecture, Global HANA CoE | SAP
M +1 347-820-2136 | E mark.mumy at sap.com
My Blogs: http://scn.sap.com/people/markmumy/blog

Share Button

SAP IQ: iqinit error SQLCode: -1000338, SQLState: ‘QDD38’, Severity: 14: Insufficient cache to allocate free list SOLVED

When you create a new IQ v16 instance, you need to use the iqinit program for initial creation of the database.

iqinit -iqpath '/siq/devices/main001.iq' -iqpgsize 131072 -iqblksize 16384 -p 4096 -iqtmppath '/siq/devices/temp001.iqtmp' -dba DBA,SUPER_PASSWORD  -m '/siq/devices/myiq.mir' -o '/siq/log/myiq.iqmsg' -t '/siq/devices/myiq.log' -b -c '/siq/devices/myiq.db'

Occasionally you may run into the SQLCode: -1000338, SQLState: ‘QDD38’, Severity: 14: Insufficient cache to allocate free list error message which basically states the IQ temp cache is too small to start the maintenance IQ instance. The iqinit program is a compiled program that calls the $SYBASE/IQ-16_0/bin64/iqsrv16 program.

SQL Anywhere Initialization Utility Version 16.0.0.809
=============================================================
IQ server starting with:
     10 connections         (       -gm )
    106 cmd resources       ( -iqgovern )
   2459 threads             (     -iqmt )
    512 Kb thread stack size   (   -iqtss  )
  1259008 Kb thread memory size ( -iqmt * -iqtss )
     48 IQ number of cpus  ( -iqnumbercpus )
      0 MB maximum size of IQMSG file ( -iqmsgsz )
      0 copies of IQMSG file archives ( -iqmsgnum )
=============================================================

CHAR collation sequence:  ISO_BINENG(CaseSensitivity=Respect)
CHAR character set encoding:  ISO_8859-1:1987
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
Exception Thrown from stcxtlib/st_database.cxx:1938, Err# 49, tid 2 origtid 2
   O/S Err#: 0, ErrID: 5120 (st_databaseException); SQLCode: -1000338, SQLState: 'QDD38', Severity: 14
[21221]: Insufficient cache to allocate free list. Temp buffers required: 132; temp buffers avaliable: 63.
-- (stcxtlib/st_database.cxx 1938) .
SQL error (-1000338) -- Insufficient cache to allocate free list. Temp buffers required: 132; temp buffers avaliable: 63.
-- (stcxtlib/st_database.cxx 1938) .
Database "/siq/devices/myiq.db" not created

Unfortunately there is no way to specify the IQ temp cache size via the iqinit program:

Usage: iqinit [options] database
        @data expands data from environment variable data or file data

Options (use specified case, as shown):
        -a             accent sensitivity on all UCA string comparisons
        -af            accent sensitivity (French rules) on all
                       UCA string comparisons
        -b             blank padding of strings for comparisons
        -c             case sensitivity on all string comparisons
        -dba uid,pwd set dba username and password
        -dbs size    set initial database size
        -ea alg      encryption algorithm (default none)
        -ek key      specify encryption key
        -ep            prompt for encryption key
        -et            enable encrypted tables
        -i             do not install jConnect support
        -k             SYS.SYSCOLUMNS and SYS.SYSINDEXES views not created
        -l             list available collation sequences
                       (use -l+ to list older collations)
        -le            list available character set encoding labels
                       (use -le+ to list more encoding label aliases)
        -m name      set transaction log mirror name
        -n             no transaction log
        -o file      log output messages to file
        -p size      set page size (must be power of 2 = 2048 and = 32768)
        -pd            legacy system stored procedure behavior
        -q             quiet: do not display messages
        -s             add checksum to database pages
                       (by default checksums are added to database pages,
                       use '-s-' to turn off adding checksums to database pages)
        -t name      transaction log file name (default is database.log)
        -z coll      specify collation sequence for CHAR data type (defaults
                       to server platform's codepage and ordering)
        -ze encoding specify character set encoding for CHAR data type
        -zn coll     specify collation sequence for NCHAR data type
                       (use '-zn UTF8BIN' if the ICU libraries are not
                       available on the server that will load the database. ICU
                       libraries might not be installed on some platforms, such
                       as Windows CE.)
        -iqpath        The path name of the main segment file containing the
                       IQ data.
        -iqsize        The size in MB of either raw partition or OS file with
                       the -iqpath.
        -iqpgsize      The page size in bytes for the Sybase IQ segment of the
                       database.
        -iqblksize     The I/O transfer block size in bytes..
        -iqreservesize Specifies the size in MB of the space to reserve for
                       the Main IQ store.
        -iqmsgpath     The path name of the segment containing the Sybase IQ
                       message trace file.
        -iqtmppath     The path name of of the temporary segment file.
        -iqtmpsize     The size in MB of either the raw partition or OS file
                       for the -iqtmppath.
        -iqtmpreservesize     The size in MB of space to reserve for the
                        temporary IQ store.

Since iqinit calls the iqsrv16 program, we can rename the iqsrv16 to iqsrv16.orig and wrap it with the following iqsrv16 shell script:

#!/bin/ksh

$SYBASE/IQ-16_0/bin64/iqsrv16.orig -iqtc 200 -iqmc 200 $*

Note that I’m allocating 200mb of both main and temporary cache. The IQ instance is now able to start and once the database is created, we can simply replace the iqsrv16 wrapper with the iqsrv16.orig program.

Share Button

UPDATED: HOWTO: Extract Permissions from SAP Sybase IQ using the LIST() function

Back in August I provided an updated method to extract the permissions for tables in a SAP IQ server. This provided SAP_IQquite a few GRANT statements to execute. If you have thousands of tables and/or thousands of users, the created a large file that can take a while to execute. We can reduce the number of GRANT statements by combining the SELECT, DELETE, UPDATE, ALTER, REFERENCE and INSERT grants into a single GRANT statement with the LIST() function.

The LIST() function joins a value from a set of rows with a specified delimiter. Think of this as the equivalent of the Perl JOIN() command.

Take for example the following strings:

VALUE1
VALUE2 
VALUE3

In Perl if you wanted a single string with a comma as the delimiter you would write the following:

my $STRING = join(', ', 'VALUE1', 'VALUE2', 'VALUE3');
print "$STRING\n";
VALUE1, VALUE2, VALUE3

In IQ we can do the equivalent with the LIST() function:

CREATE TABLE #test (column_a CHAR(6));
INSERT INTO #test VALUES ('VALUE1');
INSERT INTO #test VALUES ('VALUE2');
INSERT INTO #test VALUES ('VALUE3');

SELECT LIST(column_a, ', ') FROM #test;
VALUE1, VALUE2, VALUE3

The extract permissions for IQ tables code as updated to use the LIST() function:

SELECT 'GRANT ' 
    + LIST ( stp.auth, ', ' )
    + ' ON '
    + USER_NAME(st.creator)
    + '.'
    + st.table_name
    + ' TO '
    + su.user_name + ';'
FROM
    ( SELECT grantee, stable_id, 'SELECT' as auth FROM sys.systableperm WHERE selectauth = 'Y'
      UNION ALL
      SELECT grantee, stable_id, 'DELETE' as auth FROM sys.systableperm WHERE deleteauth = 'Y'
      UNION ALL
      SELECT grantee, stable_id, 'UPDATE' as auth FROM sys.systableperm WHERE updateauth = 'Y'
      UNION ALL
      SELECT grantee, stable_id, 'ALTER' as auth FROM sys.systableperm WHERE alterauth = 'Y'
      UNION ALL
      SELECT grantee, stable_id, 'REFERENCE' as auth FROM sys.systableperm WHERE referenceauth = 'Y'
      UNION ALL
      SELECT grantee, stable_id, 'INSERT' as auth FROM sys.systableperm WHERE insertauth = 'Y'
    ) stp,
    sys.systable st,
    sys.sysuser su
WHERE stp.stable_id = st.table_id
    and su.user_id = stp.grantee
    and lower(su.user_name) not in ('dba', 'public', 'sys', 'dbo', 'diagnostics', 'rs_systabgroup', 'sa_debug', 'extenv_main', 'extenv_worker', 'sys_spatial_admin_role')
GROUP BY su.user_name, st.table_name, st.creator

Produces the following SQL output:

GRANT SELECT ON MYUSER.my_table TO otheruser_i;
GRANT DELETE, UPDATE, INSERT, SELECT ON MYUSER.my_table TO otheruser_u;
GRANT DELETE, UPDATE, INSERT, SELECT ON MYUSER.my_table TO otheruser_e;
Share Button