HOWTO: SAP Sybase IQ, SQLAnywhere, and Powershell

Connecting to IQ can be a rather pain in the tuckus.

In the v16 IQ client for Microsoft Windows, the .net assembly changed to “Sap.Data.SQLAnywhere” from “iAnywhere.Data.SQLAnywhere”.

Another annoyance is the loading of the assembly where SAP recommends loading it via

Add-Type -AssemblyName ("Sap.Data.SQLAnywhere.v4.5, Version=, Culture=neutral, PublicKeyToken=f222fc4333e0d400")

This forces you to remember the exact version of the assembly as well as the public key token. If you’re worried about a nefarious individual gaining access to your machine and infecting your assemblies, continue to use the recommended method.

A simpler mechanism is using “LoadWithPartialName” where powershell will load whatever driver is available for SQLAnywhere:


Working example of connecting to IQ from PowerShell with a v16.1 IQ client (v17 SQLAnywhere client under the covers):

$HostName = ""
$Port = 20001
$User = "DBA"
$Password = "password"


# Connection string
$connectionString = "Host=$HostName`:$Port;UserID=$User;Password=$Password"

# Create SAConnection object
$conn = New-Object Sap.Data.SQLAnywhere.SAConnection($connectionString)

# Connect to remote IQ server

# simple query
$Query = 'select 1 as col1 from dummy'

# Create a SACommand object and feed it the simple $Query
$command = New-Object Sap.Data.SQLAnywhere.SACommand($Query, $conn)

# Execute the query on the remote IQ server
$reader = $command.ExecuteReader()

# create a DataTable object 
$Datatable = New-Object System.Data.DataTable

# Load the results into the $DataTable object

# Send the results to the screen in a formatted table
$DataTable | Format-Table -Auto


Share Button

VIDEO: PostgreSQL at 10 TB and beyond

If you’re interested in large databases and PostgreSQL, Chris Travers at Edument has been kind enough to share his experiences with such a setup (YouTube)

Share Button

SAP IQ and SQL Anywhere UnixODBC UserDSN and SystemDSN issues: WORKAROUND

SAP’s IQ / SQLAnywhere ODBC driver on Linux/*nix doesn’t work with a system that has both UserDSNs (~/.odbc.ini) and SystemDSNs (/etc/odbc.ini).

Currently when the driver looks for a DSN (db instance), the resolution is:

  1. $ODBCINI – must contain the exact full path name of the .odbc.ini file.
  2. $HOME
  3. Current directory
  4. $PATH

Notice that /etc/odbc.ini is not processed. A workaround to use SystemDSNs is to set the ODBCINI variable to /etc/odbc.ini but it doesn’t help if you have a mix of SystemDSNs and UserDSNs.

I’ve requested SAP to fix this. They’ve opened KBA2571931 CR812127. Unfortunately, SAP requires a support contract to view the KBA 🙁

CR812127 order of resolution:

  1. $ODBCINI – must contain the exact full path name of the .odbc.ini file.
  2. $HOME
  3. Current directory
  4. $PATH
  5. /etc/odbc.ini
Share Button

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
USER DATA SOURCES..: /home/jason/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8

/etc/odbcinst.ini : list of the available drivers and default configuration for each driver
/etc/odbc.ini : list of the instances/databases for *all* users aka “System DSNs”
${HOME}/.odbc.ini : list of the instances/databases specific to *you* aka “User DSNs”

Add/update the following lines in the /etc/odbcinst.ini file (or wherever the file is as reported by odbcinst -j). Make sure there is no whitespace at the beginnings of the lines.

Description = DB2 Driver
Driver = /opt/ibm/db2/db2_v9.7/lib64/

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

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


You need to catalog the database (assuming you already cataloged the node [db2host in my case] – see

 # catalog database database_name as database_alias at node node_name
catalog database MYDB2DB as MYDB2DB at node db2host

Test connectivity to the remote database:

db2 connect to MYDB2DB user testuser using test_password

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

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/;
               . "Protocol=tcpip;"
               . "Hostname=mydbhost;"
               . "Port=67033;"
               . "DATABASE=MYDB2DB";
Share Button

HowTo: Set up iSCSI with Multipath (MPIO) in Windows 10 SOLVED

I searched high and low and wasn’t able to find a definitive answer on whether Microsoft Windows 10 supports multipath (MPIO) over iSCSI. I found many many blog posts, articles, and press releases for Windows servers but nothing for Windows 10.

The good news is that Windows 10 supports it out of the box. You don’t have to install anything extra. Of course, the see the benefits of multipath, you will need two or more network cards. You can either bond the network cards together or set up individual routes to the iSCSI network addresses. I’ll assume that you did that already. 🙂

This is only for new connections to your iSCSI targets.  You can not retrofit multipath on to existing connections.  You will need to offline the volumes, remove the targets from the favorites and then reboot.

As an administrator on your Windows 10 box:

  • Launch the iSCSI initiator (Windows key and type iscsi initiator)

  • Add both ip/ports to the iSCSI host in the Discovery -> Portal Groups tab

  • Refresh the iSCSI targets in the Targets tab.  If they aren’t shown, add them manually
  • Select the iSCSI target, click connect, and select “Add this connection to the list of favorites…” (so it will automatically connect the next time you start windows)  and “Enable MPIO”.  Click Okay

  • Select the iSCSI target, click properties.  In the Portal Groups tab you should see both ip/port paths

  • If you needed to offline the volumes, you will need to now online them.  They should retain any drive letter assignments
Share Button

VMware Horizon Client Disconnects Immediately After Connecting: HccChannelPlugin::ChannelAddRef: No shared memory channel

Recently I’ve needed to connect to a virtual desktop using VMware Horizon Client. Immediately after connecting, I received the error message:

ERROR (1FD0-19D0) <6608> [vmware-remotemks] HccChannelPlugin::ChannelAddRef: No shared memory channel. Horizon client service may be down.
FATAL (1FD0-1E14) <vmware-usbd> [vmware-remotemks] GOT DEP ACCESS VIOLATION

After much research and try & error, I was able to determine that VMWare Horizon Client requires up to 4GB of RAM for physical machines and 5GB of RAM for virtualized Windows 7, 8.1, 10 systems (VirtualBox, VMWare Workstation).

Share Button

Hello world!

Welcome to WordPress. This is your first post. Edit or delete it, then start writing!

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.

    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.


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
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 Mumy
Director, Enterprise Architecture, Global HANA CoE | SAP
M +1 347-820-2136 | E mark.mumy at
My Blogs:

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

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


Share Button