Exercising Informix IDS and other RDBMS

While trying to run our application with Informix IDS server I had to solve two problems:

  1. Cannot delete many rows from single table because of long running transaction error.
  2. Receive locking error during parallel batch processing on distinct data (ie. one table, distinct rows).

So I’ve read some manuals about Informix: Administrator’s Guide, Database Design and Implementation Guide, Guide to SQL – Syntax, Installation Guide for Microsoft Windows, JDBC Driver Programmer’s Guide and Informix Unleashed book. It took me 4 days to understand Informix specific issues :-) The database is quite complex and has many interesting features (eg. table inheritance, vritual processors, …).
The result is, that there is no good solution :-(

The “solution” is following:

  1. Drop all indexes, primary and foreing keys on such table, change table type to raw (without transaction log). Delete data. Recreate indexes, primary and foreing keys. (or delete data by chunks, use XPS server and operational table type). That’s quite clumsy isn’t it?
  2. Change default locking granurality for all table to row mode (default is page mode) and set sime timeout for waiting for exclusive lock (default is no wait). That doesn’t help to get rid of all locking errors, becaus Informix locks also previous and following record in all indexes over table…

I wrote a simple test to test locking problem on other databases we use (DB2 AS400, DB2 9.1, PostgreSQL, Oracle 10g, Firebird and MS SQL Server 2005).

The test simmulates parallel batch processing:

  • create connection 1 (session – we use Hibernate)
  • start transaction1
  • update some rows in tableA
  • create connection2
  • start transaction2
  • update some other rows in tableA
  • commit transaction2
  • commit transaction1

The result’s are:

  • Informix IDS – fails with default settings, second update tries to lock index records that are already locked by first update … this is more or less probable. Wait with my settings until transaction1 commits.
  • MS SQL Server 2005 – Wait until transaction1 commits, second update tries to lock page that is already locked by first update, which causes waiting. The documentation says that server chooses appropriate lock (db, table, page, row) but as I could see it does not. You can force MS SQL Server 2005 to use row lock. But only at pre statement basis and that’s not comfortable.
  • DB2 AS400, DB2 9.1, PostgreSQL, Oracle 10g and Firebird passed my test without waiting.

Now I’m little frustrated about what database to use for combined OLTP and batch processing thas are required by our application.

SRVE0080E error with IBM Rational Software Architect 6.0.1.1

I spent three days solving problem with IBM Rational Software Architect 6.0.1.1 and IBM WebSphere 6.x:

I generated Java Skeleton for Web Service from WSDL files. When I deployed “generated” ear onto server and tested my service with Web Services Test Client. All went OK.

When I ran generated Java Client for that service it caused SRVE0080E error inside application server (see IBM WebSphere online help).

I thought that problem was within server. I tested all possible version of WebSphere 6.x (from 6.0.0.1 to 6.1.0.0)… but it did not helped and took long time :-(

The problem was that I had been using IBM Rational Software Architect. When I changed to IBM Rational Application Developer everything went right. I’ll have to have two computers one with RSA for modelling and another one with RAD for development (RSA installs over RAD). What a lucky days :-)

I wrote this because nothing about SRVE0080E error could have been found in google.

List of books that I’ve read in 2006

Why we need ODS(DWH) for integration

I am a developer of software that queries banks’ data (eg. ask executors, tax burear etc. questions about clients property). It is now uses in 16 banks.

The main integration problem is that there are usually many systems storing required information (e.g. 4x accounts IS, 2x safebox IS, credit card IS, stock and bond IS,…). These system duplicate data (mostly clients).
But our application need consolidated data nearly online. Doing consolidation inside our application is costly (some consolidation must be done manually) and unuseable by other system that also need consolidated data (e.g. you need data consolidated for communicating with central bank). Some consolidation cannot be done using ETL (SQL etc.), it must be programmed – some old applications do not use neither 1NF.
At least the information how to consolide data must be stored somewhere. I mean that the right place where to store it is ODS (DWH). Pure SOA is not possible.

Then I could directly read data from ODS (DWH) or use WS over it.

As I see other “developers” have same problems and same solution. Read discussion under SOA & ODS (DWH) links.

ODS (DWH) has some specific problems:

  • security granularity – also have to consolidate authentication and authorization informations which is almost impossible
  • might be a performance bottleneck
  • poor data quality makes this problem worse
  • (I’ll try to add more statements later)

2tier application using Hibernate

I got involved in new project in Janury. The goal was to develop new version of existing application. It must be 2tier RCP and it must be albe work over many databases (used by 16 banks, political reasons).

We decided to use Eclipse RCP and Hibernate.
Eclipse RCP work’s fine. SWT and JFace makes gui development quite easy.
But using Hibernate in 2tier application brings many difficulties:

  • Session slows down when contains many (hundreds) objects
  • You cannot use Session after you got some error on it
  • There are some bugs in HQL that make advanced usage quite difficult

See Hibernate in 2 tier application discussiong Is it Possible to Combine Hibernate + Swing?????.

After 5 months (I became the project leader meanwhile) we eneded up with 2,5 tier design:

  • use DAO classes to load light objects displayed in views (eg. load, initialize and close session in getChildren methods)
  • use SQL and HQL for batch updates where possible
  • use session per editor, load with LockMode.UPGRADE
  • use Maps instead of querying database
  • divide batch processing into small part, each in new session and transaction or use StateLess session

Advanced Hibernate bugs

PS: next version must be 3-tier application.