HOWTO: IBM DB2, ODBC, and Linux (*nix) configuration

The DB2 ODBC driver works like anything else IBM. Nonstandard to the rest of the universe. How to set up ODBC:

Unix admin needs to do:
Retrieve the config file locations:

$ odbcinst -j
unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/jason/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Add/update the following lines in the /etc/odbcinst.ini file (or wherever the file is as reported by odbcinst -j):

[DB2]
Description = DB2 Driver
Driver = /opt/ibm/db2/db2_v9.7/lib64/libdb2o.so
fileusage=1
dontdlclose=1

The driver line should point to the full path of libdb2o.so (any *nix but aix) or libdb2o.a (aix).

DBAs need to:
Add/update the following lines to the ${IBM_DB_DIR}/sqllib/cfg/db2cli.ini file for each database that uses odbc:

[MYDB2DB]
Database=MYDB2DB
Protcol=TCPIP
Hostname=mydbhost
ServiceName=67033

Add/update the following lines in the ${HOME}/.odbc.ini file:

[MYDB2DB]
Driver=DB2

Make sure we’re using the unixODBC isql:

$ /usr/bin/isql --version
unixODBC 2.2.14

Validate using unixodbc “isql”:

$ /usr/bin/isql -v MYDB2DB testuser test_password
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

The DSN for the entry is simply “dbi:ODBC:MYDB2DB” if you’re using Perl. Later versions of the db2 client allow you to specify the connect options in the DSN like so:

my $odbc_connect_str = "dbi:ODBC:"
               . "DRIVER=/opt/ibm/db2_v10.5/lib64/libdb2o.so;
               . "Protocol=tcpip;"
               . "Hostname=mydbhost;"
               . "Port=67033;"
               . "DATABASE=MYDB2DB";
Share Button

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 and SQL Anywhere: Locking and blocking only for a maximum specified time

Usually in IQ, you will lock a table for a short time (dml operations) or longer when performing a data load, table/index maintenance or issuing a holdlock within your transaction. These locks can cause other processes to block or even abort with an error “SQL Anywhere Error – 210: User ‘another user’ has the row in (some table)”. It can become problematic if you have an application that doesn’t close the transaction in a reasonable timeframe.

Consider the following scenario: A process that retrieves and loads dozens of data files to load into a single table every few minutes.
Issue: Occasionally the “SQL Anywhere Error – 210: User ‘another user’ has the row in (some table)” error will occur causing the load to fail for that file(s).

What can be done? Several things really:

  1. Trap/ignore the error
  2. Retry loading the file. How many times do we attempt this before we collide with the next set of files to load?
  3. Issue holdlock (not sure if it works with load table – I haven’t tried it) causing write access to block. If the load table is stalled, it will hold the lock indefinitely
  4. Combine all the files and load that monolithic file. Potentially faster as setting up a load table connection is very expensive, but if the file fails, the entire set fails. Do we retry?
  5. Issue temporary locks, but block only for X time and only allow itself to be blocked for Y time before rolling back, erroring.

Using the temporary locks/blocking seems to be the best option for this particular scenario to me. It allows for the load tables to run in succession without me having to set up the queue and if it does error out, I can decide in the outside code whether or not to retry the load table. Basically it gives me more control over the locking/blocking.

set temporary option date_order = 'YMD';
set temporary option timestamp_format = 'yyyy/mm/dd hh:nn:ss.sss';

-- "If the blocking option is set to “On”, any transaction attempting to obtain a lock that conflicts with an existing lock held by another transaction waits until every conflicting lock is released or until the blocking_timeout is reached. If the lock is not released within blocking_timeout milliseconds, then an error is returned for the waiting transaction. If the blocking option is set to “Off”, the transaction that attempts to obtain a conflicting lock receives an error." - https://wiki.scn.sap.com/wiki/display/SQLANY/How+to+Debug+Blocking+in+SQL+Anywhere
SET TEMPORARY OPTION "blocking" = 'ON';

-- we will wait up to 10 seconds to obtain a lock before rolling back
SET TEMPORARY OPTION "blocking_timeout" = '10000';

-- allow blocking of other transactions to maximum 5 sec before rolling back
SET TEMPORARY OPTION blocking_others_timeout = '5000';

LOAD TABLE ${REPOSITORY_SCHEMA}.activity (
    ConnHandle,
    LSServer,
    Name,
    Userid
)
USING CLIENT FILE '${OUT_CSV_FILE}'
NOTIFY 1000
QUOTES ON ESCAPES OFF
FORMAT bcp
DELIMITED BY '|'
ROW DELIMITED BY '\n';

COMMIT;
Share Button

HOWTO: Connect to SAP Sybase IQ Multiplex using JDBC and the SQL Anywhere driver: “Connection error: TCPIP requires a server name”

SAP’s documentation, like other vendors, often sucks. In today’s wonderful documentation sucky-ness the examples from SAP to connect to IQ using a JDBC connection string like so:

“jdbc:sqlanywhere:UID=***;ENG=MyIQ;PWD=***;links=tcpip(Host=MyIQ;PORT=40000)”

This is fine except when you connect to IQ multiplex and you have logical servers set up. You won’t know which IQ node you will be on and since each IQ node has an unique name (eng=), this won’t work. Alright, let’s drop the eng=MyIQ.

“jdbc:sqlanywhere:UID=***;PWD=***;links=tcpip(Host=MyIQ;PORT=40000)”

This will result in an immediate error of “Connection error: TCPIP requires a server name”. Don’t panic, there is a fix. Drop the ENG=MyIQ and links=tcpip(Host=MyIQ;PORT=40000) replacing it with the HOST=MyIQ:40000.

“jdbc:sqlanywhere:UID=***;PWD=***;HOST=MyIQ:40000)”

That works. That is so much more simple than the demented links=tcpip(Host=MyIQ;PORT=40000) nonsense.

Now the fun bit of working code. The following “IQConnect” program accepts four parameters host port username password and spits out the names of the tables on the IQ server:

import java.io.*;
import java.sql.*;

public class IQConnect {
    public static void main(String[] args) {
        if (args.length == 4) {
            String HostName;
            int Port = 0;
            String UserName;
            String Password;

            try {
                Port = Integer.parseInt(args[1]);
            } catch (NumberFormatException e) {
                System.err.println("Port # must be an integer");
                System.exit(1);
            }

            HostName = args[0];
            UserName = args[2];
            Password = args[3];

            String arg;
            Connection con;

            try {
                String ConnectionParams = String.format("jdbc:sqlanywhere:uid=%s;pwd=%s;host=%s:%d", UserName, Password, HostName, Port);
                con = DriverManager.getConnection(ConnectionParams);

                Statement stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery("select table_name from sys.systable;");

                while (rs.next()) {
                    String TableName = rs.getString(1);
                    System.out.println(TableName);
                }

                stmt.close();
                con.close();
            }
            catch (SQLException sqe)
            {
                System.out.println("Unexpected exception : " +
                  sqe.toString() + ", sqlstate = " +
                  sqe.getSQLState());
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
        } else {
            System.out.println("Requires Host Port# User Password");
        }
    }
}

Compile it with:

javac -cp $IQ16/java/sajdbc4.jar:. IQConnect.java

Execute it with

java -cp $IQ16/java/sajdbc4.jar:. IQConnect MyIQ 40000 dba sql

Of course, if you have $IQ16/java as part of your $CLASSPATH you don’t need to include “-cp $IQ16/java/sajdbc4.jar:.” to execute the program.

Share Button

IQ Error: The multiplex server ‘iq_node_3’ is not included in the multiplex – SOLVED

When you run SAP’s IQ Multiplex cluster for a while you start finding little gotchas that will just drive you to drink. If you don’t drink, you will wish you do. (Just like with any other cluster system)

In my latest foray into the murky waters of IQ Multiplex (v16), if one of the nodes if offline for a while, the coodinator node will mark the node as excluded so the cluster carries on. Not really a big deal until you try to bring up the problem node:

I. 02/09 10:31:45. Database server stopped at Tue Feb 09 2016 10:31
DBSPAWN ERROR:  -82
Unable to start specified database: autostarting database failed
Exception Thrown from stcxtlib/st_database.cxx:10050, Err# 21, tid 2 origtid 2
   O/S Err#: 0, ErrID: 5120 (st_databaseException); SQLCode: -1013113, SQLState: 'QNA49', Severity: 14
[22016]: The multiplex server 'iq_node_3' is not included in the multiplex.
-- (stcxtlib/st_database.cxx 10050)

Error: The multiplex server 'iq_node_3' is not included in the multiplex. The multiplex server 'iq_node_3' is not included in the multiplex.
Server failed to start

2016-02-09-10:31:46 Start of IQ instance iq_mpx_cluster1 failed

Log into the coordinator node, in my case iq_node_1, and run

select server_name, status, mpx_mode, inc_state from sp_iqmpxinfo();
server_name        status   mpx_mode         inc_state
---------------------------------------------------------------
iq_node_1          included coordinator      N/A
iq_node_2          included writer           active
iq_node_3          excluded unknown           timed_out

As you can see, the iq_node_3 node is excluded due to the connection to it from the coordinator timed out. What to do? Simple, first we re-include the node (on the coordinator):

alter multiplex server iq_node_3 status included;

Next we need to resync iq_node_3:

Resync IQ secondary node

The problem node should start up just fine now.

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

SAP IQ: Uptime

At some point you will be asked how long your IQ server has been up.

To determine when the IQ server was started issue:

SELECT PROPERTY('StartTime')

Output:

2015-08-14 18:04:53.918

For the number of days, hours, and minutes we can slice and dice like so:

-- Declarations
DECLARE @UPTIME_DAYS INTEGER
DECLARE @UPTIME_HOURS INTEGER
DECLARE @UPTIME_MINUTES INTEGER

-- Populate variables
SELECT DATEDIFF(day, PROPERTY('StartTime'), CURRENT TIMESTAMP) into @UPTIME_DAYS
SELECT DATEDIFF(hour, PROPERTY('StartTime'), CURRENT TIMESTAMP) % 24 into @UPTIME_HOURS
SELECT DATEDIFF(minute, PROPERTY('StartTime'), CURRENT TIMESTAMP) % 60 into @UPTIME_MINUTES

-- OUTPUT
SELECT @UPTIME_DAYS || ' days ' || @UPTIME_HOURS || ' hours ' || @UPTIME_MINUTES || ' minutes '

Output:

132 days 15 hours 40 minutes
Share Button

IBM DB2 10 for z/OS manuals fixed to work with tablet/phone PDF readers like Aldiko

Aldiko Book Reader Premium Full v2.2.3.apkMany PDF readers for smart phones (Android/iphone) and tablets manage the pdf files Calibre_Logobased solely on the Title and Author fields in the PDF file. While for this is fine for your average book, it is not all that helpful with manuals that tend to have abbreviated or no data in the title/author fields. In the case of the manuals for IBM DB2 v10 for z/OS, I’m unable to load the manuals for say v9.7 as they have the same Title/Author data.  I used the same method from SAP Sybase IQ 15.4 manuals fixed to work with tablet/phone PDF readers like Aldiko

Here are the IBM DB2 v10 for z/OS manuals that I’ve ‘fixed’ to work with Aldiko. They are identical to the PDFs on IBM’s website with the exception of the PDF fields I mentioned previously.

NO COPYRIGHT INFRINGEMENT IS INTENDED. IBM, PLEASE FEEL FREE TO TAKE THESE AND HOST THEM.

IBM DB2 v10 for z/OS – Command Reference

IBM DB2 v10 for z/OS – Data Sharing Planning and Administration

IBM DB2 v10 for z/OS – DB2 SQL Reference for Cross-Platform Development Version 4.0

IBM DB2 v10 for z/OS – Installation and Migration Guide – IBM

IBM DB2 v10 for z/OS – Internationalization Guide

IBM DB2 v10 for z/OS – Introduction to DB2 for z/OS

IBM DB2 v10 for z/OS – Installation, Administration, and Reference

IBM DB2 v10 for z/OS – IRLM Messages and Codes

IBM DB2 v10 for z/OS – Licensed Program Specifications

IBM DB2 v10 for z/OS – Managing Performance

IBM DB2 v10 for z/OS – Managing Security

IBM DB2 v10 for z/OS – Messages

IBM DB2 v10 for z/OS – ODBC Guide and Reference

IBM DB2 v10 for z/OS – pureXML Guide

IBM DB2 v10 for z/OS – RACF Access Control Module Guide

IBM DB2 v10 for z/OS – SQL Reference

IBM DB2 v10 for z/OS – User’s Guide and Reference

IBM DB2 v10 for z/OS – Utility Guide and Reference

IBM DB2 v10 for z/OS – What’s New?

IBM DB2 v10 for z/OS – Administration Guide

IBM DB2 v10 for z/OS – Application Programming and SQL Guide

IBM DB2 v10 for z/OS – Application Programming Guide and Reference for Java

IBM DB2 v10 for z/OS – Codes

Share Button

HOWTO: SAP IQ Drop Device File from DBSpace

Sometimes when we build an IQ Data Warehouse, our initial space/growth estimate is off significantly enough to warrant reducing the size of the dbspace(s). The process is quite easy put there are a few steps to perform:

Determine how much space is used by the data/indexes. The usage field is the percentage of the totalsize that is being used. For example, the below shows 38% of 4.99TB being used. (4.99 TB * 1024 GB/TB) * 0.38 = 1941.70 GB used:

select convert(varchar(20), DBSpaceName), Usage, TotalSize from sp_iqdbspace() where DBSpaceName = 'IQ_USER_MAIN';
DBSpaceName          Usage TotalSize
------------------------------------
IQ_USER_MAIN         38    4.99T

Determine which device files are part of a dbspace

select convert(varchar(20), DBSpaceName), convert(varchar(20), DBFileName), convert(varchar(70), Path), DBFileSize, Usage from sp_iqfile() where DBSpaceName = 'IQ_USER_MAIN';
DBSpaceName          DBFileName           Path                                                                   DBFileSize Usage
---------------------------------------------------------------------------------------------------------------------------------
IQ_USER_MAIN         IQ_USER_MAIN_FILE_01 /sap/iq/devices/test_usermain001.iq                                    1024G      38
IQ_USER_MAIN         IQ_USER_MAIN_FILE_02 /sap/iq/devices/test_usermain002.iq                                    1024G      38
IQ_USER_MAIN         IQ_USER_MAIN_FILE_03 /sap/iq/devices/test_usermain003.iq                                    1024G      38
IQ_USER_MAIN         IQ_USER_MAIN_FILE_04 /sap/iq/devices/test_usermain004.iq                                    1024G      38
IQ_USER_MAIN         IQ_USER_MAIN_FILE_05 /sap/iq/devices/test_usermain005.iq                                    1024G      34

Verify that the used data will fit in the new allocation

sp_iqdbspace IQ_USER_MAIN

BACKUP DATABASE

backup database FULL to '/backups/test_FULL.dmp';

Alter the file to be dropped to be read only

alter dbspace IQ_USER_MAIN ALTER FILE IQ_USER_MAIN_FILE_05 readonly

Empty the device file (raw device) to be dropped (if dropping more than one file, drop the device file *LEAST* used first)

sp_iqemptyfile 'IQ_USER_MAIN_FILE_05'

Drop the device file (you may need to restart before IQ will let you do this)

ALTER DBSPACE IQ_USER_MAIN DROP FILE IQ_USER_MAIN_FILE_05

Verify the device was dropped

select convert(varchar(20), DBSpaceName), convert(varchar(20), DBFileName), convert(varchar(70), Path), DBFileSize, Usage from sp_iqfile() where DBSpaceName = 'IQ_USER_MAIN';
DBSpaceName          DBFileName           Path                                                                   DBFileSize Usage
---------------------------------------------------------------------------------------------------------------------------------
IQ_USER_MAIN         IQ_USER_MAIN_FILE_01 /sap/iq/devices/test_usermain001.iq                                    1024G      48
IQ_USER_MAIN         IQ_USER_MAIN_FILE_02 /sap/iq/devices/test_usermain002.iq                                    1024G      48
IQ_USER_MAIN         IQ_USER_MAIN_FILE_03 /sap/iq/devices/test_usermain003.iq                                    1024G      45
IQ_USER_MAIN         IQ_USER_MAIN_FILE_04 /sap/iq/devices/test_usermain004.iq                                    1024G      47
Share Button