HOWTO: SAP Sybase IQ, SQLAnywhere, ADO.net 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=17.0.0.10624, 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 ado.net assemblies, continue to use the recommended method.

A simpler mechanism is using “LoadWithPartialName” where powershell will load the v4.5 ado.net driver for SQLAnywhere if it is in the Global Assembly Cache (GAC):

[void][System.Reflection.Assembly]::LoadWithPartialName("Sap.Data.SQLAnywhere.v4.5")

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

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

[void][System.Reflection.Assembly]::LoadWithPartialName("Sap.Data.SQLAnywhere.v4.5")

# 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
$conn.Open()

# 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
$DataTable.Load($reader)

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

Output:

col1
----
   1
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

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

SAP IQ: dbisql is unable to load the SybaseIQ SQLAnywhere plugins. SOLVED!

I recently patched an SAP IQ server to 16.0 SP8 PL30 and ran into an interesting error message when trying to start dbisql:

$ dbisql
Interactive SQL could not load the "SQLAnywhere" plug-in.
Its "ngdbc.jar" file has moved or has been deleted. You will not be able to connect to the databases handled by that plug-in.
Interactive SQL could not load the "SybaseIQ" plug-in.
Its "ngdbc.jar" file has moved or has been deleted. You will not be able to connect to the databases handled by that plug-in.
Interactive SQL could not load the "HANA" plug-in.
Its "ngdbc.jar" file has moved or has been deleted. You will not be able to connect to the databases handled by that plug-in.
Interactive SQL could not load the "GenericODBC" plug-in.
Its "ngdbc.jar" file has moved or has been deleted. You will not be able to connect to the databases handled by that plug-in.
Interactive SQL cannot start because it is not installed correctly. No database plug-ins has been registered.
To fix this problem, you should reinstall the program.

If you scan your IQ directory, you will notice there isn’t a “ngdbc.jar” file. You take a look at another IQ box that is working and it doesn’t have the ngdbc.jar file either. The error message is incorrect. The message should report that it isn’t able to access the saip16.jar (or saip11.jar if you’re on IQ 15.x) and/or the jodbc4.jar file in the $SYBASE/IQ-(IQ RELEASE)/java directory (e.g. $SYBASE/IQ-16_0/java).
Verify that the two files exist and the permissions are correct:

316 -rwxr-xr-x.  1 sybase sybase  320071 Mar 20 14:51 jodbc4.jar
112 -rwxr-xr-x.  1 sybase sybase  112325 Mar 20 14:56 saip16.jar

If everything looks okay and it still gives the error, you will need to re-register the plugins. The first thing is to move the dbisql ‘registry’:

$ mv $SYBASE/IQ-(IQ RELEASE)/bin64/dbisql_64.rep $SYBASE/IQ-(IQ RELEASE)/bin64/dbisql_64.rep.old

Next, re-register (for IQ 16):

$ cd $SYBASE/IQ-(IQ RELEASE)/java
$ dbisql -Xregister sa16 SybaseIQ com.sybase.saisqlplugin.IQISQLPlugin "$(pwd)/saip16.jar:$(pwd)/jodbc4.jar"

for IQ 15:

$ cd $SYBASE/IQ-(IQ RELEASE)/java
$ dbisql -Xregister sa11 SybaseIQ com.sybase.saisqlplugin.IQISQLPlugin "$(pwd)/saip11.jar:$(pwd)/jodbc4.jar"

Newer IQ 15 patches use SQL Anywhere 12, so if you have saip12.jar instead of saip11.jar in your java dir, use that:

$ cd $SYBASE/IQ-(IQ RELEASE)/java
$ dbisql -Xregister sa12 SybaseIQ com.sybase.saisqlplugin.IQISQLPlugin "$(pwd)/saip12.jar:$(pwd)/jodbc4.jar"

dbisql should now work 🙂

The dbisql_64.rep is simply a glorified ini file that contains the following which should be transferrable but I’ve found that dbisql is very finicky with regards to this file.

[SybaseIQ]
classLoaderName=sa16
mainclass=com.sybase.saisqlplugin.IQISQLPlugin
classpath=/opt/client/SAP-IQ/IQ-16_0/java/saip16.jar:/opt/client/SAP-IQ/IQ-16_0/java/jodbc4.jar

You may need to update the permissions if other users rely on this particular dbisql installation.

Share Button

Introducing QweryBuilder, a Developer GUI for Sybase ASE, SQL Anywhere, Microsoft SQL Server and Oracle 10g/11g

qwerybuilder_reflectedimageQweryBuilder uses innovative ideas to provide developers with the ability to easily insert, extract and modify data from a variety of databases.

The goal of QweryBuilder is to increase a database developer’s productivity. It contains many time saving features.

SQL Editor

QweryBuilder - SQL Editor
QweryBuilder - SQL Editor
  • Script auto complete
  • Custom auto complete lists
  • Code Templates
  • Display column list for tables and views in editor
  • Display procedure parameters in editor
  • Keyboard shortcut to open procedures
  • Generate and insert new GUIDs
  • Syntax folding
  • Auto indenting

Criteria Query

QweryBuilder - Criteria Query
QweryBuilder - Criteria Query
  • Form view criteria screen for easy data retrieval
  • Updateable result set
    • Insert nulls, GUID’s, and computed values into results
    • Insert, delete and update data rows
    • Generate insert statements from results
    • Create graphs from result data

Graphic Query

QweryBuilder - Graphic Query
QweryBuilder - Graphic Query
  • Create queries graphically
  • Cross table querying without writing SQL

Other Features

  • Support for Microsoft SQL Server, Sybase ASE, SQL Anywhere and Oracle
  • DDL Browser
  • Database Search
  • Visual Difference
  • Code Formatter
  • Getting Started Window
    • Displays last used connections, files and database objects
    • Easily navigate to the last thing you were working on
  • Clipboard Saver
  • File Browser
  • Object Browser
Share Button

ISUG Technical Journal Sep/Oct 2008 is out! Includes my “Perl in the Shell” article

The ISUG Technical Journal Sep/Oct 2008 is out.

This issue contains:

  • Sybase IQ and Sybase WorkSpace Part II By Mike Crocker
  • Perl in the shell By Jason Froebe
  • Taming the process nightmare: Consolidating a Horde of Process Diagrams with PowerDesigner By Mike Nicewarner
  • Information overload: Weapons-grade data analysis comes of age By Teresa Foster
  • Maximizing PowerBuilder and SQLAnywhere Part III SQLAnywhere, a PowerBuilder Developer’s perspective
    By Chris Pollach
  • select random (stuff) from SYBASE Command shell by proxy By Bill Grant
  • Mobile Enterprise Everywhere: Taking Sybase on the road By Adrian Bridgwater
  • Sybase TechWave 2008: Time for the technology tsunami

You need to be an ISUG member to receive the ISUG Technical Journal.  Join ISUG today!

International Sybase User Group
International Sybase User Group
Share Button