Blog do projektu Open Source JavaHotel

piątek, 28 grudnia 2012

DB2, ODBC/CLI, Linux, 64bit

I run into very nasty problem related to setting up ODBC connection using 64 bit Linux client. The problem is described here but is easy to ignore it (as I did).
While defining DB2 driver in /etc/odbcinst.ini file use:

# Driver for DN2
[DB2]
Description     = ODBC for DB2
Driver64        = /home/opt/ibm/db2/V10.1/lib64/libdb2o.so
Pay attention to letter o after libdb2. If you omit it the whole stuff seemingly works with only one exception: NULL values in the result set are not recognized.
libdb2.so is "CLI DB2 driver" and "libdbo.so" is "ODBC driver". The main difference is handling SQLLEN data type. On 64 bit Linux DB2/CLI uses 32 bit but unixODBC and libdb2o.so use 64 bit. It mean that the code below does not work as expected if compiled with ODBC header and run against libdb2.so. libdb2.so return 32 bit -1 but ODBC code compares it with -1 64 bit. :

        SQLRETURN ret;
        SQLLEN cbData = 0;

        ret = SQLGetData(cur->hstmt, (SQLUSMALLINT)(iCol+1), nTargetType, buffer.GetBuffer(), buffer.GetRemaining(), &cbData);

        if (cbData == SQL_NULL_DATA) {
 // expected NULL value
        }

środa, 26 grudnia 2012

SQL unit testing, new version of BoaTester

Introduction
Plugin for BoaTester allowing  SQL unit testing has been created. Full description and samples are available here. Source code (test software and samples).
Motivation
Unlike popular programming languages like Java, C/C++ or Python there is no common framework for SQL unit testing. By "SQL unit testing" I mean testing of SQL server code, SP (stored procedures) and UDF (used defined function). The common testing method is to utilize the testing framework of the client side (for instance JUnit and running server code by means of JDBC).
It works of course but is not very convenient for me because it requires some additional coding not related directly to the server code.
So I extended a BoaTester framework to run a simple test scenario without additional coding. It allows running test scenario like.

  1. Create (or replace) a table (tables).
  2. Insert some data into the tables (tables).
  3. Deploy some server code (if not loaded independently)
  4. Launch SP or UDF 
  5. Check the result
  6. ... again
The test framework is pyunit and connection method is pyodbc .
Some features and problems
  • It is not easy to test OUT parameters of SP (stored procedure). The problem is described here in more detail.
  • It is possible to test also against sql exception expected. For instance - fail the test if SP does not throw an exception expected. So it is possible to test SP also for some error condition.
  • Marker parameters are implemented.
  • The common problem while developing server code is related to the different SQL dialects. SQLTesters allows to modify some SQL statements according to database under test. More info.
  • It is possible also to create more complicated test case scenario (by means of Python). More info.

poniedziałek, 3 grudnia 2012

DB2, Oracle, NOWAIT

Introduction
NOWAIT option in Oracle allows acquiring lock immediately without waiting until the it is available or time-out period expires (60 sec in Oracle). If it is not possible the exception is thrown in no time, without any delay.
Look at this example.
Create a simple table
create table testl (x int);

From one session execute:
lock table testl in exclusive  mode
And from the second:
select * from testl for update nowait
The second will fail immediately giving the exception.
ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
DB2
DB2 does not support NOWAIT option. Default behavior is to wait indefinitely until resource is available. But it is possible to emulate NOWAIT behavior in three different ways.
DB2 Global for the whole database
One can set global database parameter LOCKTIMEOUT to 0 by issuing command:
DB2 UPDATE DB CONFIGURATION FOR USING LOCKTIMEOUT 0
The disadvantage of this approach is that this behaviour will be default for all statements executed against this database.
DB2 Connection level
Another possibility is to set connection parameter LockTimeout as 0. For ODBC/CLI connection it can be set in db2cli.ini file. For JDBC connection dbcurrentLockTimeout connection attribute should be used. But the disadvantage is as above - it is default for all statements executed via this connection which is not always desirable.
DB2 Set temporarily
 In DB2 it also possible to set locktime out parameter after connection is established by executing the command:
db2 SET CURRENT LOCK TIMEOUT 0
So we can emulate NOWAIT clause by running the following steps
  1. Remember the current value of LOCK TIMEOUT parameter.
  2. Set this parameter to 0.
  3. Execute the statement.
  4. Restore previous value.
This way we can set locktimeout value only for one statement just allowing the other statements be executed as usually.
Delphi program 
To verify how it works from the application I created a simple Delphi console program (using XE3 demo version).
The program consists of two parts. DBAccess encapsulates some database access method and UnitNoWait is the test itself.
The NOWAIT emulation is defined as:

procedure DBConnect.executeSQLNoWait(Q: TSQLQuery; withOpen : boolean);
var
  timeout: integer;
  QQ: TSQLQuery;
  inn : boolean;
begin
  case T of
    DB2:
      begin
        QQ := getQ('SELECT CURRENT LOCK TIMEOUT AS C FROM DUAL');
        with QQ do
        begin
          ExecSql;
          Open;
          timeout := FieldByName('C').AsInteger;
          Close();
        end;
        QQ := getQ('SET CURRENT LOCK TIMEOUT 0');
        QQ.ExecSQL;
        QQ.Close;

        { In case of exception TIMEOUT will not be restored.
          Find better implementation for real environment. }

        {  Run statement now. }
        Q.ExecSQL;
        if (withOpen) then Q.Open;
        inn := Conn.InTransaction;

        { restore original value }
        QQ := getQ('SET CURRENT LOCK TIMEOUT ' + IntToStr(timeout));
        QQ.ExecSql;
        QQ.Close;
      end;
    Oracle:
      with Q do
      begin
        { IMPORTANT: run executeSQLNoWait only once for the statement. Find better
          solution for real environment. }
        SQL.Add(' NOWAIT');
        ExecSql();
      end;
  end;
end;
And the test:
Constructor TestNoWait.Create(paramT: DatabaseType);
  begin
    DBTest := DBConnect.Create(paramT);
  end;

  procedure TestNoWait.runQueryNoWait;
  var
    Q: TSQLQuery;
  begin
    Q := DBTest.getQ('SELECT * FROM TESTL FOR UPDATE');
    DBTest.executeSQLNoWait(Q,true);
    Q.Close;
    Q := DBTest.getQ('SELECT * FROM TESTL FOR UPDATE');
    Q.ExecSQL();
    Q.Open;
    Q.Close;
  end;
It works for Oracle.
For DB2 we have to perform two tests to be sure that is works as expected.
First test. Execute the following statement from the console (+c parameter overcomes autocommit which is default for CLP).
db2 +c lock table testl in exclusive  mode
Then launch Dephi test, it should fail at the first statement in no time throwing SQL0911N sql error code.
 Second test should verify that default value is restored after executing the first statement. It can be accomplished by using debugger. Simpy just stop execution immediately after DBTest.executeSQLNoWait(Q,true) statement. At this moment execute again
db2 +c lock table testl in exclusive  mode
and continue with Delphi. The second statement should hang. Wait for some time and execute:
db2 commit
The execution of the Delphi program should be resumed.
Oracle WAIT n
 Oracle allows also to specify number of seconds to wait. For instance (set lock timeout to 5 seconds).
select * from testl for update wait 5
But this clause can be also easily implemented in DB2 using method described above. Just add additional parameter to executeSQLNoWait procedure and replace 0 with this value in 'SET CURRENT LOCK TIMEOUT 0' statement