Blog do projektu Open Source JavaHotel

środa, 28 listopada 2012

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.

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.

środa, 21 listopada 2012

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.

środa, 14 listopada 2012

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.

czwartek, 1 listopada 2012

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.