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.
