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