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

SAP Sybase SQL Anywhere 12.0.1 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 Sybase IQ, I’m unable to load the manuals for say v11.0 and v12.0.1 as they have the same Title/Author data.

How to fix? Easy. Go get Calibre. Drop the PDF files on to the running Calibre. Edit them by hitting the E key.

In my case, I edited the “Title”, “Author”, “Tags”, “Publisher” and “Languages”:

Calibre

Calibre doesn’t modify the PDF files themselves so I will need to export the files to a custom directory. In Calibre nomenclature, this is “Saving”. Highlight all the titles you want to export and hit “S” twice. Why twice? No idea. Choose the directory.

SQL Anywhere 12.0.1 (r) Server – Database Administration – SAP, Inc_

You can now copy the exported PDF files to your phone, tablet, whatever without fear of the v12.0.1 version of the P&T Guide being rejected by Aldiko because the v12.0 version is already added.

Here are the SQL Anywhere v12.0.1 manuals that I’ve ‘fixed’ to work with Aldiko. They are identical to the PDFs on sybooks with the exception of the PDF fields I mentioned previously.

No copyright infringement is intended. SAP/Sybase, please feel free to take these and host them.

SQL Anywhere 12.0.1 (r) Server – Programming – SAP, Inc_
SQL Anywhere 12.0.1 (r) Server – Spatial Data Support – SAP, Inc_
SQL Anywhere 12.0.1 (r) Server – SQL Reference – SAP, Inc_
SQL Anywhere 12.0.1 (r) Server – SQL Usage – SAP, Inc_
SQL Anywhere 12.0.1 – Changes and Upgrading – SAP, Inc_
SQL Anywhere 12.0.1 – Error Messages – SAP, Inc_
SQL Anywhere 12.0.1 – Introduction – SAP, Inc_
SQL Anywhere 12.0.1 – MobiLinkTM – Client Administration – SAP, Inc_
SQL Anywhere 12.0.1 – MobiLinkTM – Getting Started – SAP, Inc_
SQL Anywhere 12.0.1 – MobiLinkTM – Server Administration – SAP, Inc_
SQL Anywhere 12.0.1 – MobiLinkTM – Server-Initiated Synchronization – SAP, Inc_
SQL Anywhere 12.0.1 – QAnywhereTM – SAP, Inc_
SQL Anywhere 12.0.1 – Relay Server – SAP, Inc_
SQL Anywhere 12.0.1 – SQL RemoteTM – SAP, Inc_
SQL Anywhere 12.0.1 – UltraLite(r) – .NET Programming – SAP, Inc_
SQL Anywhere 12.0.1 – UltraLite(r) – C and C__ Programming – SAP, Inc_
SQL Anywhere 12.0.1 – UltraLite(r) – Database Management and Reference – SAP, Inc_
SQL Anywhere 12.0.1 – UltraLite(r) – Java Programming – SAP, Inc_
SQL Anywhere 12.0.1 – UltraLite(r) – M-Business Anywhere Programming (deprecated) – SAP, Inc_

Share Button

Calling Sybase developers! Please help fix the Sybase and Microsoft driver for the CakePHP framework!

While the CakePHP framework is very good, the dbo “driver” (wrapper) between CakePHP and the PHP Sybase/Microsoft drivers ar not implemented to be actually usable.  Most of the issues are differences of SQL dialect between MySQL and the commercial DBMSs.

The issues tend to be simple ones such as, “select * from my_table limit 10” to retrieve the first 10 rows not working.  Using “select top 10 col1,col2,….coln from my_table” will.

So how can you help?  Very easily! 🙂  The CakePHP website shows us how:

Contributing to Cake

We are always looking for contributions, so don’t be shy. There are two ways to get involved:

1. Documentation:

The Cookbook is ready to accept your input. Everyone can help with the documentation effort by simply logging in with your Bakery account, writing (or editing) a section and submitting it for review. The application is still under development, so if you find a problem, submit a ticket following the recommended procedure. Visit #cakephp-docs on irc.freenode.net.

2. Bugs, Enhancements and Optimizations:

Contributing code to CakePHP is easy: Submit test cases and attach them to tickets. That’s it. Fame, glory, and praise to all those who make the effort.

Thanks to all those who have already contributed to CakePHP. We look forward to everyone’s future contributions.

Take a look at the source code for the drivers.

Share Button

Virtualization and Databases

In Chris Brown‘s Virtualization and ASE blog post, he brings up the question of whether Sybase’s ASE can be used in a virtual environment (VMWare, Xen, etc) but doesn’t answer it. I’ve been using various databases in virtual environments for several years, here is what I found out:

Running ASE, ASIQ, or SQL Anywhere under virtualization software such as Xen, VMWare, Parallels, etc is very useful under a number of situations:

  1. development of new applications – each developer group can have its own “db server” on the same machine
  2. testing new ebfs/releases with your applications
  3. reproducing problems either in the sybase software or in the application code – a ‘virgin’ instance that can be duplicated at will
  4. trying out new operating systems (moving from Windows to Linux or Windows to Solaris x86?) without investing in new hardware

The main caveat is that the performance stinks – databases typically require high disk i/o, memory i/o and cpu responsiveness. The virtualization software currently available, even with hardware help (newer Intel,AMD chips), are not up to the task of running a *production* database.  In a couple years… possibly.

Share Button