Blog do projektu Open Source JavaHotel

wtorek, 27 listopada 2012

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.

Brak komentarzy:

Prześlij komentarz