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
        }

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.

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

DB2 optimizer

Introduction
Let's take a look at the simple table :

create table testts (ide int, name varchar2(100))
create index ideindx on testts(ide)

and simple statement:

create table testts (ide int, name varchar2(100))
select * from testts where ide=10

In order to execute this statement DB2 (like any other database engine) creates "access plan". Access plan is simply SQL statement decomposed into more atomic operations like "scan table", "apply predicate", "look up in the index", "acquire lock" etc. The simple SQL statement above can be executed in two ways:

1. Scan the table and find rows which fulfill the predicate.
2. Look up the index and then pick up row(s) from the table. 

Every way brings the same result and DB2 should choose the best one. It is obvious that the second method is better then the first. But in case of more complicated statements there are more possible ways to get result and it is not always obvious which method is the best. The engine which creates and analyzes different access plans for SQL statements is called "DB2 optimizer". The decision is based on "cost". To every basic operation in access plan a cost is attached (related to resource consumption like I/O or CPU). The best access plan is the access plan having the lowest overall cost of execution. In most cases it means that this access plan executes faster then the others but literally the best access plan has the most efficient way of resource consumption.
Problem
But take a closer look at this simple example.
To populate this table with some test data I created a simple procedure

CREATE OR REPLACE PROCEDURE INSERTS (NUMB IN INT) 
AS
  MAXI INT;
  I INT;
BEGIN
  SELECT MAX(IDE) INTO MAXI FROM TESTTS;
  IF MAXI IS NULL THEN
    MAXI := 0;
  END IF;  
  FOR I IN 1..NUMB LOOP
    INSERT INTO TESTTS VALUES(MAXI + I, 'AUTO NAME ' || (MAXI + I));
  END LOOP;  

END;

Add one record to the table and check access plan for this statement.

call inserts(1)
runstats on table testts
explain plan for select * from testts where ide=10

Using db2exfmt utility we can export access plan in human readable format. It is available here.

Access Plan:
-----------
 Total Cost:   6.87148
 Query Degree:  1

      Rows 
     RETURN
     (   1)
      Cost 
       I/O 
       |
        1 
     TBSCAN
     (   2)
     6.87148 
        1 
       |
        1 
 TABLE: DB2INST3
     TESTTS
       Q1

But there there is a surprise. Although index was created, DB2 optimizer decided that table scanning is a better option. After adding more rows:

call inserts(10) 
runstats on table testts

the access plan is as one could expect.

Total Cost:   6.87692
 Query Degree:  1

             Rows 
            RETURN
            (   1)
             Cost 
              I/O 
              |
               1 
            FETCH 
            (   2)
            6.87692 
               1 
         /----+----\
        1            11 
     IXSCAN    TABLE: DB2INST3
     (   3)        TESTTS
    0.0538374        Q1
        0 
       |
       11 
 INDEX: DB2INST3
     IDEINDX
       Q1

What's going on 
Obviously DB2 decides that scanning table is better option for one row but worse for eleven rows. But how to check it ? By virtue of optimization guidelines (practical example is described here) we can force DB2 engine to use index for one row and scan table for eleven rows.
Access plan for one row with index lookup is available here. General cost in case of index lookup is 6.87458 and is a little greater then table scanning (6.87148). It is so because index lookup requires two steps : index lookup and later table access to pick up rows. Table scanning requires only one step. Although estimated I/O cost is the same (equal to 1), CPU consumption is higher for two steps (57021 aginsts 54482).
Access plan for eleven rows ignoring index is available here. This time total cost is equal to 6.89792 and is higher then total cost with index lookup : 6.87692. Although the dilemma is the same : two steps (index and table) against one step (table only) the optimizer decides that predicated CPU consumption for scanning through 11 rows in search for one row is higher. Cost for table scanning here is : 76152. CPU cost for index lookup and picking up one row is : 58941.
But add much more rows to the table and again check access plan for this SQL statement with and without index.

call inserts(100000) 
runstats on table testts

Access plan with index lookup (default) is available here. The access plan with force table scanning is available here. The difference is overwhelming: 665.297 against 30.3052. But the main factor are not subtle differences in CPU consumption but obviously I/O cost :430 against 1. It goes without saying that I/O access with table scanning is more extensive than index lookup. This difference in also reflected in SQL real execution (DB10.1, 32 bit Ubuntu machine, 2GB memory).
 (table scanning)

$ time db2 "select * from testts where ide=10" 


IDE         NAME                                                                                                
----------- ---------------------------------------------------------------------------------------------------- 
         10 AUTO NAME 10                                                                                        

  Wybrano rekordów: 1. 


real 0m0.155s 
user 0m0.016s 
sys 0m0.032s 

(default, index lookop)

$ time db2 "select * from testts where ide=10" 

IDE         NAME                                                                                                
----------- ---------------------------------------------------------------------------------------------------- 
         10 AUTO NAME 10                                                                                        

  Wybrano rekordów: 1. 


real 0m0.062s 
user 0m0.028s 
sys 0m0.020s

Execution is more then 2 times faster with index lookup and probable more rows in the table the difference is more distinct.
Conclusion 
In this simple example one can see how DB2 optimizer works and how the decision is taken which access plan to choose. But it is important to underline that the decision is based on resource consumption and it not always goes with execution speed. This distinction is very important because sometimes the best execution time is achieved by more extensive CPU consumption. It does not matter in a test environment but is not always a good decision in production, multiuser and mulitiprocess environment. This fastest and CPU thirsty access plan can starve to death the other processes.

Delphi, Oracle, DB2, rowid

Introduction
Optimistic locking means that application does not hold rows between selecting rows and updating or deleting them. In Oracle to "lock" rows in optimistic way very often rowid pseudocolumn  is used. The typical scenario is that the application firstly collects rowids (one or more) and later updates rows using rowids just gathered. Rowid gives immediate access to the row in the table without any searching.
Test scenario
To check how it works in Delphi client for Oracle I created a simple table.

CREATE TABLE TEST13 (NUMB INT, NAME VARCHAR2(200))

Secondly I created a simple procedure to populate this table with some random numbers:

CREATE OR REPLACE
PROCEDURE POPULATETABLE(
    N_OF_ROWS IN INT)
AS
  I   INTEGER;
  NUM INTEGER;
BEGIN
  INITIALIZERANDOM;
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TEST13';
  FOR I IN 1..N_OF_ROWS
  LOOP
    NUM := dbms_random.random;
    NUM := ABS(MOD(NUM,20));
    INSERT INTO TEST13 VALUES
      (NUM, 'NUMBER=' || NUM
      );
  END LOOP;
  dbms_random.terminate;
END POPULATETABLE;

This procedure calls INITIALIZERANDOM to initialize a random generator.
Problem
But after running this procedure I started to feel a little unhappy. This table contains a lot of 13s which - as everybody knows - is unlucky and is a harbinger of a disaster. So it is a good idea to replace all 13s with a 6 number which is perfect (is equal to the sum of its positive divisors). This way will get off the unhappiness from our table and make it really perfect. Of course - it can be done with one simple UPDATE statement but I want to achieve this perfect state with more complicated way.
Solution 
The solution is a simple Delphi program. It is written as DUnit test scenario.
It contains of two source units. DBAccess.pas encapsulates some access methods to the database and DBTestCase.pas is a test case itself. It simply collects rowids with 13 :

SELECT ROWID AS R FROM TEST13 WHERE NUMB = 13

and then updates them:

UPDATE TEST13 SET NUMB = 6 WHERE ROWID = :ROWID

All works perfect for Oracle, after executing this test all 13s in our table are annihilated.
DB2
But what about DB2. Oracle ROWID pseudocolumn is supported in DB2 after setting Oracle Compatibility Mode. By means of Oracle Compatibility Mode it is possible to execute POPULATETABLE without any change. Because DBMS_RANDOM package is not supported in DB2 directly I created a simple substitution using DB2 RAND function. I also created DB2 version of INITIALIZERANDOM procedure.
After this changes and modifying database access method I was able to execute Delphi program. But the test failed and - what is more nasty - without any exception thrown.
The reason of this failure is that although ROWID pseudcolumn is supported in DB2 it is binary (VARCHAR(16) AS BINARY DATA), not string. So it could contain 0 and cannot be read or passed as a string because ODBC uses C/C++ string convention where 0 denotes the end of a string.
DB2 solution
One solution is to read ROWID not as a string but as a as bytes (using asBytes method). Then manually convert 16 bytes to 32 hexadecimals. But we cannot use this string for a substitution in a parameter marker because DB2 expected binary in a hexadecimal format (starting with x' prefix). In order to have it working we have to create manually string for direct executing (SELECT ... WHERE ROWID = x'aabb ... ).
The second and much simpler solution is to use RID() function. RID() returns number and plays the same role as ROWID - allows direct access to the row without searching.
But we have to split the code into two versions, one for Oracle and the second for DB2.

 case DBTest.getT of
    Oracle:
      Q := DBTest.getQ('UPDATE TEST13 SET NUMB = 6 WHERE ROWID = :ROWID');
    DB2:
      Q := DBTest.getQ('UPDATE TEST13 SET NUMB = 6 WHERE RID() = :ROWID');
  end;

 case DBTest.getT of
    Oracle:
      Q := DBTest.getQ('SELECT ROWID AS R FROM TEST13 WHERE NUMB = 13');
    DB2:
      { rid() for DB2. Although rid() is BIGINT can be passed as a string. }
      Q := DBTest.getQ('SELECT RID() AS R FROM TEST13 WHERE NUMB = 13');
  end;

The rest of the code is exactly the same. So after applying this change we have what we wanted.

Conclusion
 ROWID is supported in DB2 in Oracle Compatibility Mode. But - unfortunately - it not always works as expected. The nasty thing here is that no exception is throws, it looks nicely only at the first sight.
Oracle Compatibility Mode is a great thing and makes migration really easy but we cannot get rid of a deep regression testing.

DB2 and optimization guidelines

Introduction
Create a simple table and put several rows into it.

create table testts (ide int, name varchar2(100))
create index ideindx on testts(ide)
insert into testts values(10,'NAME 10')
insert into testts values(12,'NAME 11')
insert into testts values(13,'NAME 12')
insert into testts values(14,'NAME 13')

Then consider simple SELECT statement

select from testts where ide = 10

Take a look at the access plan for this statement.

As one can imagine DB2 scans index in search for IDE value then picks up the rows from the table and it seems to be reasonable, much better then scanning through the table.
But assume that for some important reason we are sure that for that particular statement ignoring the index and scanning the table is better and runs faster. How to force DB2 to overcome standard behaviour and use another ?
Oracle 
In Oracle the answer is simple, use hints. We have to modify this statement and we are done.

select /*+ full(testts)  */ * from testts where ide = 10

DB2 
In DB2 answer is a bit more complicated and means "optimization guidelines". Firstly we have to enable our DB2 instance for using optimization guidelines.

db2set DB2_OPTPROFILE=YES

Then connect to database and execute a command:

db2 "call sysinstallobjects('opt_profiles', 'c', '', '')"

This command creates SYSTOOLS.OPT_PROFILE containing all optimization profiles and guidelines.
Prepare optimization guidelines
Having DB2 instance and database enabled for guidelines we have to create a XML file containing directives for DB2 engine for that particular statement (opt.xml)

<?xml version="1.0" encoding="UTF-8"?> 

<OPTPROFILE VERSION="9.1.0.0"> 

<STMTPROFILE ID="Guidelines for simple select"> 
  <STMTKEY SCHEMA="DB2INST1"> 
select 
  * 
from 
  testts 
where 
  ide=10 
</STMTKEY> 
<OPTGUIDELINES> 
<TBSCAN TABLE='TESTTS'/> 
<!-- <IXSCAN TABLE="TESTTS" INDEX="ideindx"/> --> 
</OPTGUIDELINES> 
</STMTPROFILE> 

</OPTPROFILE>

This optimization profile contains statement and guidelines for DB2 engine how to evaluate this statement. It simply says that DB2 should scan TESTTS table for this statement. Of course, one optimization profile can contain many guidelines for different statements issued by our application. Next step is to import this guidelines into SYSTOOLS.OPT_PROFILE to be used by DB2 engine. Prepare a file (opt.del)

"DB2INST1","SELECT_SIMPLE_OPTIMIZER","opt.xml"

and run a command:

db2 "import from opt.del of del modified by lobsinfile insert_update into 
systools.opt_profile"
db2 " FLUSH OPTIMIZATION PROFILE CACHE"

SELECT_SIMPLE_OPTIMIZER is the name of the profile. We can keep a lot of different profiles but only one can be used by the connection.
CLP, how it works 
To have a connection enabled for a given optimization profile we have to issue a command:

db2 SET CURRENT OPTIMIZATION PROFILE=SELECT_SIMPLE_OPTIMIZER

Very important: This option (CURRENT OPTIMIZATION PROFILE) is enabled only for this connection and expires when connection is closed.
 But how to verify that it is working ? With or without index this statement yields the same result. To verify it execute a command:

db2 SET CURRENT EXPLAIN MODE YES

The SQL statements are evaluated and executed normally but this command causes additionally that explain plan is saved in explain tables and can be viewed after. So now run a command again:

db2 "select * from testts where ide = 10"

And (at the server, not client, side) run a db2exfmt command to pick up the latest access plan.
db2exfmt
After running db2exfmt firstly look at the header and be sure that it is the latest plan, not the plan created the previous day.

DB2_VERSION:       10.01.1
SOURCE_NAME:       SQLC2J23
SOURCE_SCHEMA:     NULLID
SOURCE_VERSION:
EXPLAIN_TIME:      2012-11-19-14.32.05.016549
EXPLAIN_REQUESTER: DB2INST1

This header information tells us that our optimization profile is enabled for this statement.

Profile Information:
--------------------
OPT_PROF: (Optimization Profile Name)
        DB2INST1.SELECT_SIMPLE_OPTIMIZER
STMTPROF: (Statement Profile Name)
        Guidelines for simple select

And last but not least the access plan itself.

Optimized Statement:
-------------------
SELECT
  10 AS "IDE",
  Q1.NAME AS "NAME"
FROM
  DB2INST1.TESTTS AS Q1
WHERE
  (Q1.IDE = 10)

Access Plan:
-----------
        Total Cost:             6.82167
        Query Degree:           1

      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
        1
     TBSCAN
     (   2)
     6.82167
        1
       |
        3
 TABLE: DB2INST1
     TESTTS
       Q1

IBM Data Studio
So far so good but regarding CLP (Command Line Processor). But what about other method of working with DB2, for instance IBM Data Studio  (free download)?
In IBM Data Studio we have to modify the connection parameter:

Then we can check the access plan for our simple SQL statement:

Java 
CLP and IBM Data Studio are great tools but we use them rather for developing and administering then running business application. In Java to take advantage of optimization profile it is necessary to modify URL used for connecting to DB2. Simple application is available here.

The URL:

        private static final String url = "jdbc:db2://aixt:60004/sample:currentExplainMode=YES;optimizationProfile=SELECT_SIMPLE_OPTIMIZER;";

After running this application we have to check the access plan by running db2exfmt tools at the server side. Warning: currentExplainMode is used only for testing purpose. If we are sure that optimization profile is in force we can remove it because it could bring down performance a little bit.
C++, ODBC, CLI 
Simple C++ application is available here.
If we use SQLDriverConnect we can modify the connection string:

#define CONNECTSTRING "DSN=SAMPLET;UID=db2inst1;PWD=db2inst1;CURRENTOPTIMIZATIONPROFILE=SELECT_SIMPLE_OPTIMIZER;DB2Explain=2"
...
    if (ConHandle != 0)
      RetCode = SQLDriverConnect(ConHandle,NULL,(SQLCHAR *)CONNECTSTRING,SQL_NTS,(SQLCHAR*)OutConnStr,255,&OutConnStrLen,SQL_DRIVER_NOPROMPT);

Another possibility (particularly if we use SQLConnect or do not have access to source code) is to modify db2cli.ini file at the client (not server) side. We can modify it manually or execute the following statements (assuming that SAMPLET is the name of the database alias).

db2 UPDATE CLI CONFIGURATION FOR SECTION SAMPLET USING CURRENTOPTIMIZATIONPROFILE SELECT_SIMPLE_OPTIMIZER
db2 UPDATE CLI CONFIGURATION FOR SECTION SAMPLET USING DB2Explain 2

The db2cli.ini file should contain:

[SAMPLET]
DB2Explain=2
CURRENTOPTIMIZATIONPROFILE=SELECT_SIMPLE_OPTIMIZER

After running our C++ application we can check the effectiveness of the change by running db2exfmt at the server side.
Summary 
As we can see optimization profile is very effective and powerful tool to modifying the way how DB2 executes SQL statements. Of course - we can do much more then forcing DB2 to use or not to use index. More information is described here. Comparing to Oracle hints it is a bit more complicated and requires different methods relating on the access method used by the application. But on the other hand we don't need access to source code to modify the access plan.

Byliśmy na operze

25 października 2012 roku byliśmy na przedstawieniu Cyrulika Sewilskiego Rossiniego w Warszawskiej Operze Kameralnej i bardzo nam się podobało.
"Cyrulik Sewilski" to dzieło bardzo popularne, chętnie wystawianie, słuchane i oglądane. Wszyscy lubią te przygody sprytnego cyrulika Figara, hrabiego Almavivy robiącego wszystko aby zdobyć względy Rosyny, podejrzliwego aczkolwiek mało bystrego doktora Bartolo oraz pięknej Rosyny, która za wszelką cenę chce się wydostać z dusznego domu don Bartolo. Chyba prawie każdy potrafi rozpoznać uwerturę czy arię Figara "„Largo al factotum" z pierwszego aktu.
Przedstawienie w Warszawskiej Operze Kameralnej (premiera w 2007) jest bardzo tradycyjne, twórcy po prostu chcą ładnie pokazać, zagrać i zaśpiewać to dzieło i uczynili to z dużym sukcesem. Nie zapomnieli, że "Cyrulik Sewilski" to przede wszystkim opera komiczna i widzowie muszą się dobrze bawić.
Na scenie na pewno dominował Andrzej Klimczak jako doktor Bartolo. Ogromnie się podobało wykonanie arii "A un dottor della mia sorte" , rolę uzupełniał wydatną aktorską ekspresją jakby ten śpiewak się niemal urodził do tej postaci scenicznej. Znakomicie także wypadł Sylwester Smulczyński jako hrabia Almaviva, który do pięknego śpiewu dodał także dużo wdzięku i lekkości. Pozostali soliści trochę pozostali w ich cieniu, ale wszyscy stanęli na wysokości zadania.
Scenografia jest bardzo udana, główny element to ściana, która pełni rolę zewnętrznej fasady w pierwszej scenie i po obróceniu staje się wnętrzem domu don Bartolo w dalszej części przedstawienia. Na tak małej scenie każdy element jest istotny, jeden przedmiot za dużo lub za mało już czyni różnicę. Ale efekt jest bardzo udany, na scenie jest pusto gdy hrabia Almaviva udający nauczyciela muzyki daje lekcję śpiewu Rosynie i mrowi się od ludzi, gdy do domu don Bartolo wkracza oddział żołnierzy w obecności wszystkich domowników.
Kto nie widział tego dzieła w Warszawskiej Operze Kameralnej na pewno dobrze zrobi kupując bilety na najbliższe przedstawienie.

Borland C++, BDE and HADR

Introduction
DB2 HADR works not only with ODBC, JDBC or any other officially supported by IBM connection methods but also with some less common although very popular technologies, even deprecated.
Borland BDE is deprecated and replaced by dbExpress.  Embarcadero discourages using BDE and recommends migration to dbExpress, although BDE framework is delivered with every version of Borland.
http://docwiki.embarcadero.com/RADStudio/XE3/en/Borland_Database_Engine

Warning: The Borland Database Engine (BDE) has been deprecated, so BDE will not be enhanced. For instance, BDE will never have Unicode support. You should not undertake new development with BDE. Consider migrating your existing database applications from BDE to dbExpress.

So it does not make any sense to develop a new software using this technology. But there is still a lot of legacy systems based on BDE and very often from business point of view it is more cost effective to maintain it then migrate to dbExpress. dbExpress is not backward compatible with BDE and switching to dbExpress requires code modification meaning costs and regression risk.
HADR
Although BDE is deprecated starting from 2000 it is still possible to benefit from HADR especially if BDE-ODBC connection is utilized.
To prove it I created simple Borland C++ console application using trial version of latest  C++ Builder XE3. Source code is available here. I used also latest DB2 10.1 version as a database server. HADR is not shipped with free DB2 Express-C version but for evaluating purpose DB2 trial (90 days) download can be used.
HADR setup
How to setup and test HADR environment is described here. On client (Windows) machine it is necessary to download and install IBM Data Server client software. Valid ODBC entry for DB2 should be configured and working.
BDE setup
Although deprecated BDE Administrator software is delivered with XE3 framework. BDE administrator allows to create BDE data source based on ODBC entry. In this example it is assumed that the name of BDE entry is named DB2HAD1 and U/P is DB2HAD1/DB2HAD1
Console program
hadrmodel.h and hadrmodel.cpp encapsulates BDE access to database. On the top of it there is a simple console application.

HADR and BDE
Because BDE-DB2 access method is based on ODBC the application is HADR enabled by virtue of DB2 client and nothing specific is necessary. DB2 client recognizes HADR failover and switches connection to stand-by server.
The main and only problem is related to SQL30108N sql error (connection has been re-established). To resolve that issue I created a simple class which encapsulates all SQL related code.

// common class for running SQL related commands
class ExecuteCommand {

        virtual void command() = 0;

        bool trans;

        TQuery *query;

protected:
        TQuery *getQ(const char *s) {
                query = new TQuery(model->T());
                query->SQL->Add(s);
                return query;
        }

public:
        ExecuteCommand(hadrmodel * p, bool ptrans) : model(p), trans(ptrans) {
                query = NULL;
        }

        void go() {
                if (model->T() == NULL) {
                        model->lastActionStatus = "Not connected";
                        return;
                }
                bool tryagain = true;
                bool first = true;
                // loop could be executed twice in case of RECREATE_CONNECTION error
                while (tryagain) {
                        tryagain = false;
                        try {
                                // BDE: autocommit takes place if ExecSQL command is issued
                                // without StartTransaction
                                if (trans && !model->autocommit) {
                                        model->T()->StartTransaction();
                                }
                                command();
                                if ((model->T() != NULL) && model->T()->InTransaction) {
                                        model->T()->Commit();
                                }
                                model->lastActionStatus = "OK";
                        }
                        catch (EDBEngineError &e) {
                                UnicodeString s = e.Message;
                                to_string(model->lastActionStatus, s);
                                std::string code;
                                to_sqlcode(code, s);
                                if (code.compare(CONN_AGAIN) == 0 && first) {
                                        // run again but only once
                                        // secondly throws exception
                                        tryagain = true;
                                        first = false;
                                }
                        }
                        catch (...) {
                                model->lastActionStatus = "SQL non EDBEngineError error";
                        }
                } // while
                if (query != NULL) {
                        query->Close();
                        delete query;
                }

        }

protected:
        hadrmodel * model;
};

This way handling SQL30108N error is resolved in one place and the application becomes HADR transparent.
Another simple problem was related to obtaining SQL error code. I was unable to  find any ready to use method so I created a simple procedure for searching and extracting SQL code from SQL error message.

     // extracts SQL code (4 or 5 digits) from SQLERR string
        void to_sqlcode(std::string &st, const UnicodeString &s) {
                int start = 0;
                UnicodeString sPos = s;
                st.clear();
                // look for SQL substring
                while (true) {
                        sPos = s.SubString(start, 1000);
                        int p = sPos.Pos("SQL");
                        if (p == 0) {
                                break;
                        }
                        p--;
                        // firstly check for 4 digits (ends up with 'N')
                        int npos = p + 7;
                        if (npos >= s.Length()) {
                                break;
                        }
                        wchar_t w = sPos.c_str()[npos];
                        if (w == 'N') {
                                // ok, 4 digits error code
                                to_string(st, sPos.SubString(p + 4, 4));
                                break;
                        }
                        // secondly look for 5 digits
                        npos = p + 8;
                        if (npos >= s.Length()) {
                                break;
                        }
                        w = sPos.c_str()[npos];
                        if (w != 'N') {
                                start = npos;
                                // look for next SQL substring (if exists)
                                continue;
                        }
                        // ok, 5 digits
                        to_string(st, sPos.SubString(p + 4, 5));
                        break;
                }
        }
}

Conclusion
By virtue of IBM Data Server Client it is possible to enable for HADR even deprecated technologies like Borland BDE. Putting aside SQL30108N sql error no code changes are necessary so even legacy systems can benefit from DB2 HADR.
If we want to save the user from pain of repeating the last transaction in case of takeover (SQL30108N code) code fixes are necessary. But with the help of small refactoring it is possible to make the application fully HADR transparent even for deprecated connection method.

CVS and AIX

Introduction
AIX does not come with CVS (client and server) installed. But it is quite easy to install them and transform our AIX server also to CVS server.
IBM AIX Toolbox and rpm
It is a set of GNU Open Source tools compiled for AIX from source files. Ready to use packages are available here: IBM AIX Toolbox download information. Before downloading read carefully licensing and installation instructions. Those packages are installed as RPM packages so make sure that your AIX has rpm already installed. Just run:

-bash-3.2# rpm --version
RPM version 3.0.5
-bash-3.2#

If rpm is not installed then install it first.
Download
Download two packages: zlib and csv. Rpm files for these packages should be like: zlib-1.2.3-4.aix5.2.ppc.rpm and cvs-1.11.17-3.aix5.1.ppc.rpm
Install
As root user:

rpm -i zlib-1.2.3-4.aix5.2.ppc.rpm
rpm -i cvs-1.11.17-3.aix5.1.ppc.rpm

Verify that cvs is installed:

-bash-3.2# cvs -version
Concurrent Versions System (CVS) 1.11.17 (client/server)
Copyright (c) 1989-2004 Brian Berliner, david d `zoo' zuhn,
Jeff Polk, and other authors
CVS may be copied only under the terms of the GNU General Public License,
a copy of which can be found with the CVS distribution kit.
Specify the --help option for further information about CVS

Add user cvs
Add user cvs who will be the owner of cvs repositories.

useradd -m cvs

Check the service 
As a root user verify that the file /etc/services contains entries:

cvspserver 2401/tcp # cvspserver
cvspserver 2401/udp # cvspserver

Create repository directory 
Login as user cvs and create the first repository.

cvs -d /home/cvs/cvstest init

Create a list of authorized users. File: vi /home/cvs/cvstest/CVSROOT/passwd

john::cvs
marry::cvs
ivan::cvs

Add the repository just created to the list of attended repositories. 
As a root user add an entry to the /etc/inetd.conf

cvspserver stream tcp nowait cvs /usr/bin/cvs cvs --allow-root=/home/cvs/cvstest pserver

Restart the service

refresh -s inetd

Test if everything is running 
From another computer run the command:

cvs -d :pserver:ivan@aixhost:/home/cvs/cvstest login

If everything is ok then this command should be completed without any message.
Create the first project:

mkdir proj
vi proj/file.txt

And import this project into repository as a repository module

cvs -d :pserver:marry@aixhost:/home/cvs/cvstest import -m "Initial import" proj proj start

Then move to another catalog or use another computer and checkout the project just created.

cvs -d :pserver:john@aixj:/home/cvs/cvstest checkout proj

If the project content is recreated it means that our repository is running and ready to act as a host for our next big project.
Add next repository. 
It is very simple now. Just create next repository directory - for instance

cvs -d /home/cvs/cvsprod init

Add list of authorized users.

vi /home/cvs/cvsprod/CVSROOT/passwd

Modify a line in the /etc/inetd.conf (important: all repositories entries should be included in one line)

cvspserver stream tcp nowait cvs /usr/bin/cvs cvs --allow-root=/home/cvs/cvstest --allow-root=/home/cvs/cvsprod pserver

Restart

refresh -s inetd

And next repository is ready to use.

New version of JSPWiki

I uploaded a new version of JSPWiki. It has attachment feature enabled, one can download and upload attachments to the pages. An example of page having attachment added - look here.
To keep attachments a Blob data type is used. Source code to entity is here, it is simple key (page name) -> Blob content schema. So it shares 1MB limitation of blob size.
It is possible to overcome this limitation by using Google App Engine Blobstore service , no problem to replace WikiPageBlob entity with Blobstore Api. It is also possible to combine this two options together, attachments less then 1MB keep in entity, for greater attachment use Blobstore service.

db2odbc_fdw, db2 and postgresql

Introduction
Postgresql FDW and ODBC allows to get access to all databases having ODBC interface. But I'm not happy with this odbc_fdw implementation and decided to create my own.
Implementation
The source code is available here. Just download, read README file and make install. At the beginning I planned to create DB2 specific fdw using CLI, (Call Level Interface) but because DB2 CLI is almost identical with ODBC I decided to implement purely ODBC interface.
Comparison with odbc_fdw

• Much smaller (600 lines of source code against 1200)
• Simpler : just define DSN name, ODBC credentials mapping and query to run
• Full signalling of connection and query errors.
• Proper handling of NULL value

Problems

• Wrapper reconnects with ODBC database every time foreign table is scanned. The performance could suffer that way. Maybe worth considering is connecting only for the first time and next time table is scanned just reuse connection opened before. But it requires passing state between wrapper invocation and could be the source of several problem.
• Tuple is created by PostgreSQL API method BuildTupleFromCStrings. So ODBC firstly decodes all columns to string and later PostgreSQL engine encodes these string to appropriate format. Maybe performance would improve if direct data type were used without string intermediary format. 
• For some reason DB2 decimal are converted to string format using coma (,) as decimal character. But posgresql expected dot (.) as decimal character so rather awkward solution (for number data types replace all , with .) is used. Some more elegant solution is needed.
• PlanForeignScan is implemented in a simple way - just put some constant values. odbc_fdw solution run (select * from .) query to retrieve number of rows in the query. But using this solution means performance degradation - instead of one query two queries are executed. So in search of more advanced solution without performance degradation.
• What about 'big' columns like: LOB, BLOB etc.

Future

The solution was tested on Fedora 17 (64 bit) against DB2 10.0 database. Next step is to test db2odbc_fdw wrapper against other databases and also test it on Windows platform. But to accomplish it I'm planning to prepare some regression test and run this test again every database. The main purpose is to check if all standard data types are converted correctly.

CellTable and line wrapping

Introduction
In the previous (before GWT 2.4) it was difficult to add "no wrap" attribute to the CellTable.

It was easy while creating user interface in HTML/CSS - just add "no-wrap" attribute to
tag or "white-space: nowrap" style. But it was difficult in GWT because there was no direct API for modifying (cell) attribute.
The only solution I found was to make copy and paste of DefaultCellTableBuilder and and modify it a little bit.

/**
 * IMPORTANT: copy and paste of DefaultCellTableBuilder. The only difference is
 * to add "nowrap" to td tag. Because all attributes in class are private I have
 * to copy also constructor with all attributes (cannot extends
 * DefaultCellTableBuilder)
 * 
 */

class MyCellTableBuilder<T> extends AbstractCellTableBuilder<T> {

    private final String evenRowStyle;
    private final String oddRowStyle;
    private final String selectedRowStyle;
    private final String cellStyle;
    private final String evenCellStyle;
    private final String oddCellStyle;
    private final String firstColumnStyle;
    private final String lastColumnStyle;
    private final String selectedCellStyle;

    // enhancement
    private boolean addNoWrap = false;
    
    /**
     * @return the addNoWrap
     */
    boolean isAddNoWrap() {
        return addNoWrap;
    }

    /**
     * @param addNoWrap the addNoWrap to set
     */
    void setAddNoWrap(boolean addNoWrap) {
        this.addNoWrap = addNoWrap;
    }
    // ===========


    MyCellTableBuilder(AbstractCellTable<T> cellTable) {
        super(cellTable);

        // Cache styles for faster access.
        Style style = cellTable.getResources().style();
        evenRowStyle = style.evenRow();
        oddRowStyle = style.oddRow();
        selectedRowStyle = " " + style.selectedRow();
        cellStyle = style.cell();
        evenCellStyle = " " + style.evenRowCell();
        oddCellStyle = " " + style.oddRowCell();
        firstColumnStyle = " " + style.firstColumn();
        lastColumnStyle = " " + style.lastColumn();
        selectedCellStyle = " " + style.selectedRowCell();
    }

    @Override
    public void buildRowImpl(T rowValue, int absRowIndex) {

        // Calculate the row styles.
        SelectionModel<? super T> selectionModel = cellTable
                .getSelectionModel();
        boolean isSelected = (selectionModel == null || rowValue == null) ? false
                : selectionModel.isSelected(rowValue);
        boolean isEven = absRowIndex % 2 == 0;
        StringBuilder trClasses = new StringBuilder(isEven ? evenRowStyle
                : oddRowStyle);
        if (isSelected) {
            trClasses.append(selectedRowStyle);
        }

        // Add custom row styles.
        RowStyles<T> rowStyles = cellTable.getRowStyles();
        if (rowStyles != null) {
            String extraRowStyles = rowStyles.getStyleNames(rowValue,
                    absRowIndex);
            if (extraRowStyles != null) {
                trClasses.append(" ").append(extraRowStyles);
            }
        }

        // Build the row.
        TableRowBuilder tr = startRow();
        tr.className(trClasses.toString());

        // Build the columns.
        int columnCount = cellTable.getColumnCount();
        for (int curColumn = 0; curColumn < columnCount; curColumn++) {
            Column<T, ?> column = cellTable.getColumn(curColumn);
            // Create the cell styles.
            StringBuilder tdClasses = new StringBuilder(cellStyle);
            tdClasses.append(isEven ? evenCellStyle : oddCellStyle);
            if (curColumn == 0) {
                tdClasses.append(firstColumnStyle);
            }
            if (isSelected) {
                tdClasses.append(selectedCellStyle);
            }
            // The first and last column could be the same column.
            if (curColumn == columnCount - 1) {
                tdClasses.append(lastColumnStyle);
            }

            // Add class names specific to the cell.
            Context context = new Context(absRowIndex, curColumn,
                    cellTable.getValueKey(rowValue));
            String cellStyles = column.getCellStyleNames(context, rowValue);
            if (cellStyles != null) {
                tdClasses.append(" " + cellStyles);
            }

            // Build the cell.
            HorizontalAlignmentConstant hAlign = column
                    .getHorizontalAlignment();
            VerticalAlignmentConstant vAlign = column.getVerticalAlignment();
            TableCellBuilder td = tr.startTD();
            td.className(tdClasses.toString());
            if (hAlign != null) {
                td.align(hAlign.getTextAlignString());
            }
            if (vAlign != null) {
                td.vAlign(vAlign.getVerticalAlignString());
            }
            // wrap enhancement
            if (addNoWrap) {
              td.attribute("nowrap", "true");
            }

            // Add the inner div.
            DivBuilder div = td.startDiv();
            div.style().outlineStyle(OutlineStyle.NONE).endStyle();

            // Render the cell into the div.
            renderCell(div, context, column, rowValue);

            // End the cell.
            div.endDiv();
            td.endTD();
        }

        // End the row.
        tr.endTR();
    }
}

The only important difference is

 // wrap enhancement
            if (addNoWrap) {
              td.attribute("nowrap", "true");
            }

in the middle. But because of code duplication I felt unhappy with that rather lame solution.
How it can be resolved now
Starting from GWT 2.4 version things have looked better because additional method setCellStyleName was added to the  Column interface which resolved the issue. So the solution now is:
Add class name to the css file.

.no_wrap_cell_style {
  white-space: nowrap;
}

And main code now is (source file PresentationTable)

    @Override
    public void setNoWrap(boolean noWrap) {
        for (int i = 0; i < table.getColumnCount(); i++) {
            Column co = table.getColumn(i);
            co.setCellStyleNames(noWrap ? "" : IConsts.nowrapStyle);
        }
        table.redraw();
    }

Demo version is available here. "FindTest"->"Ustawienia"->"Zawijaj linie"

DB2, HADR and J2EE

Introduction
HADR works perfectly with stand alone Java application using Driver Manager interface. But what about Web applications where common practice is a connection by means of DataSource ?
Sample application
 To check it I created a simple GWT application (running on Tomcat container) connecting to HADR enabled DB2 and doing several simple actions like before. Full source code is available here. I reused the whole interface and implementation of IJDBCAction without changing any single line.
The application itself looks like (I did not pay too much attention to GUI design)

It is nothing more but Web (GWT) interface to IJDBCAction.
The only difference is of course the method of obtaining JDBC Connection.

  private class GetConnection implements IGetConnection {

                @Override
                public Connection getCon() throws SQLException, ClassNotFoundException {
                        InitialContext ic;
                        DataSource ds;
                        try {
                                ic = new InitialContext();
                                ds = (DataSource) ic.lookup("java:comp/env/jdbc/SAMPLE");
                                Connection c = ds.getConnection();
                                return c;
                        } catch (NamingException e) {
                                throw new ClassNotFoundException(e.getLocalizedMessage());
                        }
                }

        }

The most important thing is defining the data source (server.xml for Tomcat)

    
<Resource auth="Container" driverClassName="com.ibm.db2.jcc.DB2Driver" name="jdbc/SAMPLE" password="db2had1" 
type="javax.sql.DataSource" 
      url="jdbc:db2://think:50001/SAMPLE:clientRerouteAlternateServerName=think;clientRerouteAlternatePortNumber=50002;" 
username="db2had1" >

The crucial point is adding additional connection parameters describing alternative DB2 server participating in HADR configuration. Beside that there is nothing specific in this application.
Additional remarks

• I reused IJDBCAction implementation from JavaHadr project by means of Eclipse "source link". The only disadvantage that I was unable to use PersonRecord bean as a transient object in GWTHadr application. The nice feature of GWT is the possibility to share code between server and client part. It is possible to achieve that but it requires to decompose JavaHadr application into two parts.
• Adding additional parameters to URL is not the only method to create DataSource connection HADR enabled. The second method is to create an instance of DB2ClientRerouteServerList and pass it to DataSource as a JNDI entry. It is described here in more detail.

DB2, PostgreSQL, FDW and Linux 64 bit

Introduction
FDW (foreign data wrapper) is a feature in PostgreSQL which allows access to external resources as if they were relational tables in PostreSQL database. There is also ODBC wrapper available so I was interested if it is possible to configure integration with DB2. Finally I was successful but it was a journey littered with troubles.
DB2 data server client
Download and install DB2 data server client. Then configure connection to DB2 database by means of CLP application. In my environment remote DB2 database SAMPLE is created on server think and access port is 50005, user/password db2inst3/db2inst3

db2 "catalog tcpip node DB2SAMPL remove think server 50005"
db2 "catalog database SAMPLE as DB2SAMPL at node DB2SAMPL"

Test the connection just defined:

db2 connect to DB2SAMPL user db2inst3 using db2inst3
db2 list tables

ODBC DB2 connection on Linux
We have to setup ODBC connection with DB2 database (local or remote) on the machine were PostgreSQL is installed.  Be sure that ODBC Linux version is installed, open source unixODBC is available for all Linux distribution. Next step is to configure ODBC access to DB2 database (DB2 Infocenter detailed information) . It can be done by GUI (ODBCConfig) or manually. You have to identify where libdb2.so ODBC driver is located and add the driver definition to /etc/odbcinst.ini file. In my environment this definition looks like:

[DB2]
Description  = UDB 10.1
Driver  = 
Driver64  = /home/opt/ibm/db2/V10.1/lib64/libdb2.so
Setup  = 
Setup64  = 
UsageCount  = 1
CPTimeout  = 0
CPTimeToLive  = 0
DisableGetFunctions  = 0
DontDLCLose  = 1
ExFetchMapping  = 1
Threading  = 3
FakeUnicode  = 0
IconvEncoding  = 
Trace  = 
TraceFile  = 
TraceLibrary  = 

Then define ODBC connection to DB2 database. Add an entry to /etc/odbc.ini file. My entry looks like:

[DB2SAMPL]
Driver = DB2
Database = SAMPLE
Servername = think
UID = db2inst3
PWD = db2inst3
Port = 50005

Next step is to verify that ODBC connection is working by means of isql command :

isql db2sampl db2inst3 db2inst3
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from emp

The last command should yield the content of EMP table created in DB2 SAMPLE database.
Additional remark. In order to have it running we have to catalog DB2 connection using DB2 CLP and ODBC connection also. Also the database alias used in CLP (DB2SAMPL here) should match the DSN name in ODBC. Otherwise the ODBC connection is not working.
Prerequisites in PostgreSQL 
Together with PostgreSQL server additional packages should be installed. In my environment (Fedora 14) I installed these packages via commands:

yum install postgresql-contrib
yum install libpq-dev
yum install postgresql-server-dev.9.1

Make sure that pg_config utility is linked to entry in /usr/bin (or any other public PATH directory). It was not done automatically in my environment.

/usr/bin/pg_config -> /usr/pgsql-9.1/bin/pg_config

Install fdw_odbc extension
 Download solution from repository. Then run command (as root user or via sudo)

make USE_PGXS=1 install

Output:

/bin/mkdir -p '/usr/pgsql-9.1/lib'
/bin/mkdir -p '/usr/pgsql-9.1/share/extension'
/bin/sh /usr/pgsql-9.1/lib/pgxs/src/makefiles/../../config/install-sh -c -m 755  odbc_fdw.so '/usr/pgsql-9.1/lib/odbc_fdw.so'
/bin/sh /usr/pgsql-9.1/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./odbc_fdw.control '/usr/pgsql-9.1/share/extension/'
/bin/sh /usr/pgsql-9.1/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./odbc_fdw--1.0.sql  '/usr/pgsql-9.1/share/extension/'

Configure extension in PostgreSQL 
 Connect to PostgreSQL database and issue the following commands:

CREATE EXTENSION odbc_fdw;
CREATE SERVER odbc_db2 FOREIGN DATA WRAPPER odbc_fdw OPTIONS (dsn 'DB2SAMPL');
CREATE USER MAPPING FOR sb SERVER odbc_db2 OPTIONS (username 'db2inst3', password 'db2inst3');

First test 
To test connection create a simple table in DB2, for instance: DB2 CLP:

db2 "create table testt(x int)"
db2 "insert into testt values(1)"
db2 "insert into testt values(2)"
db2 "insert into testt values(3)"

Then define access to this table in PostgreSQL

test=# CREATE FOREIGN TABLE TESTT (X INT) SERVER odbc_db2 OPTIONS ( sql_query'select x from TESTT', sql_count 'select count(x) from TESTT', x 'X' );
CREATE FOREIGN TABLE
test=# select * from testt;
 x 
---
 1
 2
 3
(3 wiersze)

Next test 
Now define access to EMPLOYEE table in DB2 SAMPLE database (more information)

CREATE FOREIGN TABLE EMPLOYEE (EMPNO CHAR(6), FIRSTNME VARCHAR(12), WORKDEPT CHAR(3), SALARY DECIMAL(9,2)) SERVER odbc_db2 OPTIONS (sql_query 'select cast (empno as CHAR(7)) as empno,firstnme,cast (workdept as CHAR(4)) as workdept,replace(salary,'','',''.'') as salary from employee', sql_count 'select count(*) from employee', EMPNO 'EMPNO', FIRSTNME 'FIRSTNME', WORKDEPT 'WORKDEPT', SALARY 'SALARY')

Then run several statements:

select * from employee;
select * from employee where salary > 70000;
select count(empno),workdept from employee group by workdept order by count(empno);
select max(salary),min(salary),workdept from employee group by workdept;
select count(empno),workdept from employee group by workdept having count(empno) > 5;

One has to remember that foreign tables are "read-only" in PostgreSQL. Cannot be updated and do not participate in transactional processing (more details)
More complicated example
What about joins between foreign and native tables ?
Assume that together with official personal data in SAMPLE database the sleeky employer wants to keep some political incorrect information in a database.
So create additional table in PostgreSQL:

create table secret (empno char(6), politicalviews varchar(100));
insert into secret values('000010','Democratic');
insert into secret values('000020','Democratic');
insert into secret values('000030','Conservative');
insert into secret values('000050','Conservative');
insert into secret values('000060','Liberal');

Then run several statements:

select * from employee e,secret s  where e.empno = s.empno;
select e.*, s.politicalviews from employee e left outer join secret s on e.empno = s.empno;
select e.*, case when s.politicalviews is null then 'Unstable' else s.politicalviews end  from employee e left outer join secret s on e.empno = s.empno;

Let's check the number of people with specified and unspecified political views in the company

select workdept,sum(case when p=1 then 1 else 0 end) as unstable,sum(case when p=0 then 1 else 0 end) as stable  from (select workdept, case when s.politicalviews is null then 1 else 0 end as p from employee e left outer join secret s on e.empno = s.empno) as w group by workdept;

Output:

 workdept | unstable | stable 
----------+----------+----------
 A00      |        4 |        1
 B01      |        0 |        1
 C01      |        3 |        1
 D11      |       10 |        1
 E21      |        6 |        0
 D21      |        7 |        0
 E01      |        0 |        1
 E11      |        7 |        0

Let's try with VIEW:

create view companyandpolitics select workdept,sum(case when p=1 then 1 else 0 end) as unstable,sum(case when p=0 then 1 else 0 end) as stable  from (select workdept, case when s.politicalviews is null then 1 else 0 end as p from employee e left outer join secret s on e.empno = s.empno) as w group by workdept;
select * from politics;

Some additional remarks

CREATE FOREIGN TABLE EMPLOYEE (EMPNO CHAR(6), FIRSTNME VARCHAR(12), WORKDEPT CHAR(3), SALARY DECIMAL(9,2)) SERVER odbc_db2 OPTIONS (sql_query 'select cast (empno as CHAR(7)) as empno,firstnme,cast (workdept as CHAR(4)) as workdept,replace(salary,'','',''.'') as salary from employee', sql_count 'select count(*) from employee', EMPNO 'EMPNO', FIRSTNME 'FIRSTNME', WORKDEPT 'WORKDEPT', SALARY 'SALARY')

• For some reason the last character is lost if the length of CHAR column is the same in DB2 and foreign table definition. I cannot tell where is the problem: in DB2, DB2 ODBC/CLI, fdw_odbc extension or FDW implementation in PostreSQL. The solution is simply to extend CHAR size in SELECT by DB2 CAST scalar function.
• DECIMAL. DB2 ODBC/CLI maps DECIMAL column type to CHAR as a default.. In my locale as a decimal point , (colon) is used which is not acceptable by PostgreSQL. So the solution is to use DB2 REPLACE scalar function to replace , (colon) with . (dot). 
• I did not test date, timestamp, UTF-8 string etc. But - if any problem arises - I hope that it is possible to overcome any issue using method described above. It is also possible to modify the defaults of DB2 ODBC/CLI connection - look for more details.
• What about BLOB, LOB, XML column ?
• EMPNO 'EMPNO' - use capital letters (not EMPNO 'empno'). DB2 ODBC/CLI returns all column names in upper case regardless the case used in SELECT statement.

Conclusion
Unfortunately, it was a painful job to setup this FDW for DB2. This solution is almost not documented, error handling is very poor (not existing at all). In case of any problem it can break the whole PostgreSQL or simply brings nothing without any error message. So I had to spend some time to dig through the source code and run this extension with debug mode enabled and additional logging. To my mind the extension requires more thorough test and improvements to be fully trusted.
But the result is amazing - we have a powerful tool for integrating PostgreSQL with DB2.

DB2, HADR and Java

Introduction
HADR (High Availability and Disaster Recovery) is working well with C/C++ client but what about Java client. No problem, DB2 is also supporting Java without any problem for both DataSource and DriverManager client. It is described with details here.
Example for DriverManager client
I created a simple console Java application which connects to DB2 HADR controlled database and executes several JDBC action. Full source code is available here. How to setup a simple HADR configuration is described here.
Sample application description
There is a Java interfaces encapsulating some JDBC action.

public interface IJDBCAction {

        /**
         * Connect to database
         */
        public void connect();

        /**
         * Disconnect
         */
        public void disconnect();

        /**
         * Add new person to PERSON table.
         * 
         * @param re
         *            Person data to add (only first and family name should be
         *            filled)
         */
        public void addPerson(PersonRecord re);

        /**
         * Return the list of the persons
         * 
         * @return List of persons.
         */
        List<PersonRecord> getList();

        /**
         * Create PERSON table
         */
        public void createTable();

        /**
         * Drop PERSON table
         */
        public void dropTable();

        /**
         * Get current connection status
         * 
         * @return String describing the current status
         */
        public String getStatus();

        /**
         * Get the status of the last executed statement
         * 
         * @return String describing the status of the last statement
         */
        public String getLastResult();

        /**
         * Set autocommit mode
         * @param on true: set autocommit on, off otherwise
         */
        public void setAutocommit(boolean on);
}

There is also a concrete implementation of this interface, factory providing this implementation and simple console application with UI for this JDBC actions. Pay attention that it is the responsibility of the   IJDBCAction user to provide valid Connection reference. This way IJDBCAction implementation is free of any configuration details and can be reused in different context.
The application itself:

Connection status: Not connected
1) Connect
2) Disconnect
3) Read list
4) Add person
5) Create PERSON table
6) Drop PERSON table
7) Set autocommit on
8) Set autocommit off
99) Exit

There is nothing special in it, everything is a typical JDBC application. Because Java client does not use CLP connection pools, to make it HADR enabled connection URL string should be extended. It is described here.
The code sample used in the application.

  private static class GetConn implements IGetConnection {

                private final String user = "db2had1";
                private final String password = "db2had1";
                private final String url = "jdbc:db2://think:50001/sample";

                /** Parameters for HADR stand by, server name and port. */
                private final String serverAlternate = "think";
                private final int portAlternate = 50002;

                @Override
                public Connection getCon() throws SQLException, ClassNotFoundException {
                        Class.forName("com.ibm.db2.jcc.DB2Driver");
                        // Construct URL referencing HADR environment
                        String u = url
                                        + ":"
                                        + com.ibm.db2.jcc.DB2BaseDataSource.propertyKey_clientRerouteAlternateServerName
                                        + "=" + serverAlternate + ";";
                        u += com.ibm.db2.jcc.DB2BaseDataSource.propertyKey_clientRerouteAlternatePortNumber
                                        + "=" + portAlternate + ";";
                        // Connect !
                        Connection con = DriverManager.getConnection(u, user, password);
                        return con;
                }
        }

To make connection HADR enabled one has to add two additional parameters with server and port name for HADR stand by server and that is final. Nothing more is necessary.
Making the application HADR failover transparent
Extending URL string with HADR related parameters causes only that after HADR failover application is automatically redirected to another server. But current transaction is rollbacked with SQL error message thrown:

[jcc][t4][2027][11212][4.7.85] Użycie połączenia nie powiodło się, lecz połączenie to zostało ustanowione ponownie. Nazwa hosta lub adres IP to "think", a nazwa usługi lub numer portu to 50 001.
Ponowna próba ustawienia rejestrów specjalnych może, lecz nie musi zostać podjęta (kod przyczyny = 1). ERRORCODE=-4498, SQLSTATE=08506

The solution is to replay the last transaction. But the question could be raised - how to accomplish this task automatically without any user or db admin intervention, just make this problem completely transparent. There could be a lot of different ideas - the idea implements in this sample application is to use command design pattern. To run any JDBC related task one has to extend an abstract class:

/**
 * Command design pattern, abstract command
 * 
 * @author sbartkowski
 * 
 */
abstract class SqlCommand {

        protected final SQLCommandRunTime context;

        /** Error code 'connection reestablished' */
        private final int RETRYCODE = -4498;

        /**
         * Constructor
         * 
         * @param context
         *            SQLCommandRunTime context
         */
        SqlCommand(SQLCommandRunTime context) {
                this.context = context;
        }

        /**
         * Transactional command to be executed.
         * 
         * @throws SQLException
         *             SQLException thrown
         */
        abstract protected void command() throws SQLException;

        /**
         * Executes command, encapsulation around lower level transactional code.
         * Implements logic related to HADR failover
         */
        void runCommand() {
                if (context.con == null) {
                        context.lastResult = "Cannot execute, not connected";
                        return;
                }
                try {
                        command();
                        context.lastResult = "Command executed successfully";
                } catch (SQLException e) {
                        int errCode = e.getErrorCode();
                        if (errCode == RETRYCODE) {
                                // run again command in case of reestablish error
                                try {
                                        command();
                                        context.lastResult = "Command executed successfully";
                                } catch (SQLException e1) {
                                        // do not try again, throw exception unconditionally
                                        context.lastResult = e.getMessage();
                                        e.printStackTrace();
                                }
                        } else {
                                // throws exception if error code is not reestablish error
                                context.lastResult = e.getMessage();
                                e.printStackTrace();
                        }

                }
        }
}

This way all logic related to 'connection reestablished' SQL error is implemented in one place. Pay attention that command is replayed only once, second execution of the transaction should be completed without any problem. If it does not happen then it is much better to raise an exception at once without any delay.
Conclusion

• It is quite simple to enable Java application for HADR, only simple URL string extension is required.
• This sample application keeps all connection details hardcoded in the source code. In the most business application database connection details are reconfigurable outside the source code but by all means no problem also to extend it to HADR related parameters.
• This example is related only to connection extracted from DriverManager. DataSource connection (typical for web application) should be enabled for HADR another way.
• Making application HADR failover transparent requires proper handling of "A connection failed but has been reestablished." error exception. It is quite simple if application is started from the very beginning (for example by using the solution described above) but could be complicated in case of legacy code.

JspWiki and Bach Cantatas

I started to collect Bach Cantatas recordings on YouTube service hoping to collect them all. The result of my research is available on JSPWiki implementation.
There are a lot recordings available on YouTube but I'm not happy with a lot of them. Some of them are partial  (only one movement), some of them are of very poor quality (for instance vinyl records ripped and uploaded to YouTube). There is almost complete recordings of Bach Cantatas made by Nikolaus Harnoncourt and Gustav Leonhardt but the quality is not perfect. I mean, of course, the quality of recording uploaded to You Tube - not the quality of performance which is excellent.
So I started to collect recording I'm happy with using the following rules:

• Completed (not single movement).
• Good recording quality. It goes without saying that 'quality' here reflects my personal taste.

CellTree and CellTable

Introduction
I added a support for CellTree in my JavaHotel framework. Demo is available here. Source code containing  implementation is available here.
Of course - there is nothing special in it - samples are available together with the javadoc. My purpose was to make table view "tree view" enabled.  For some data tree (hierarchical) view is often more accurate. For instance - chart of accounts - has hierarchical structure. Some accounts are used as a collective for more specialized accounts related to them. Or we can have ledger accounts and subledger (subsidiary ledger) containing more specialized information.
On the other hand flat structure is also very convenient.
More information
So this is why this extension was created. We can make some tables (for instance char of accounts) to be "tree enabled" but all the time having the property to see them as a flat table.
The main problem was to include information about tree structure inside the table to avoid keeping additional metadata.
It was achieved by adding additional numeric column to the table. This column contains two information: whether the record is tree node or a leaf and depth of the node (leaf). The tree structure reads from the beginning to the end. If the column contains number greater or equal then 100 it is regarded as a node and leaf otherwise. The number itself (minus 100 for nodes) is regarded as a node (leaf) depth. So the notation is as follows: node is the first and then all next records with the level + 1 are its children until first record (node or leaf) with the level less then the starting node is detected.
Example (* marks the node) :

*A     (number = 100, node and level = 0)
 A1    (number = 1, leaf and level = 1
 A2    (number = 1, next leaf and level = 1)
 *A3   (number = 101, node and level = 1)
  A31  (number = 2, leaf and level = 2, child of the node A3)
  A32  (number = 3, next leaf and level = 2)
 A4    (number = 1, leaf and level = 1, next child for node A)
B      (number = 0, lead and level = 0, next child for the root) 

Additional info 

• There is no need for this additional column to be displayed.
• In the table (tree) view header additional property menu is added. It allows to switch between table view and tree view.
• The disadvantage of the tree view is that a record filter cannot be applied. After removing some record (for instance nodes) it is unlikely to keep the proper tree structure. So when table view is filtered the switching to tree view is blocked.

DB2, HADR and C++, make an application failover transparent

Introduction
When failover is executed the application receives "SQL30108N" exception. The remedy is quit simple, just redo the last transaction. But how to accomplish it in a programmatic way ? If our application contains tens or hundreds transaction scattered through the code it could be complicated. One would have to identify all transaction boundaries and add additional logic to detect "SQL30108N" exception after every commit and replay transaction.
Solution example
There is no universal solution to this problem but in the example provided before  it is quite easy to make this application failover transparent. It can be done by virtue of command pattern implemented there.
To implement all methods exposed in header I designed a simple class with signature like

class SqlCommand {
    hadrmodel &ha;
protected:
    virtual SQLRETURN command(hadrmodel &ha,SQLHANDLE  StmtHandle) = 0;
    bool errorHandleSTMT;
    
    bool directrunCommand(std::string info) {
    .....
    SQLRETURN RetCode = command(ha,StmtHandle);
    ....
public :
    void runCommand(std::string info) ...
}

Client should implement 'command' method and reuse some common stuff provided (for instance statement handler created before). Main function (runCommand method) is responsible for preparing environment, launching 'command' and evaluating result. We assume that every instance of SqlCommand is stateless and contains all necessary data. Because main class evaluates result it is quite easy to recognize "SQL30108N" exception (-30108 numerically) and rerun command again. So assuming that 'directrunCommand' return 'true' if replaying is suggested the main method looks like.

  ....
  public:
    SqlCommand(hadrmodel &par) : ha(par) {}
    void runCommand(std::string info) {
      if (directrunCommand(info)) {
        directrunCommand(info);
      }
    }
  ....

The full source code is available here.
Because the next transaction after failover should be executed correctly there is no loop but simple logic : 'if (repeat suggested) then {run again once}' . It is unlikely to have series of failover and failback one after another so if again "SQL30108N" will be caught it is the other mistake and it is much better to raise an error at once.
Only modifying 'sqlCommand' class was necessary in this example. All 'client' code (meaning 'sqlCommand' implementations) and application code did not require any fixes.
Conclusion
Making application failover transparent was very easy in this simple example. I dare say that even the application were more complicated (meaning more sql related commands) it would be simple also and would involve only fixes in one place.
But it is not an universal solution, it was possible only because a proper design pattern was implemented from the very beginning.