Hello,
We are devoting thousands of man hours doing what many of you have
had to do - covert tons of code from BDE to dbexpress(d7). After a
month,
we have all of our apps compiling and working pretty well with
Interbase.
Now the real work begins - getting them all to work with Oracle,
MSSQL and DB2.
I dove in personally with DB2. After a dozen glaring "what the hells"
and work arounds the real problems have become clearer.
Here are a couple of the most perplexing - I hope someone out there
has some db2 experience to share.
First and formost, each connection seems to maintain a hidden,
overriding transaction.
Using a single connection within an app we may have hundreds of
selects, updates and inserts performed. Some of those are grouped into
manual transactions, most are not.
But if you simply make a db2 connection, perform normal sql updates (no
manual transaction used) then stop the app (stop in the IDE or
crash/exception occurs) several odd things happen.
For a good minute afterwards you can connect to the db, but any tables
that you previously made changes to are clearly locked - no access is
available.
After the minute has passed, all the changes are rolled back and those
tables are unlocked.
I would rather have behavier more like with the other DBs - each sql
should be performed in a single-statement implicit transaction - in
a sense be "autocommitted".
This is for two reasons, one , so it works like other dbs,
and two, so that a crash won't horribly lose and corrupt the data.
We have settled on using "cursor stability" for the db and
"dirty read" for our dbexpress connection. Anything else just hangs
at every turn.
Two other things of note that seem different when connected to
db2 using dbexpress:
Often there is no exception when executing a bad sql statement.
This negates our exception handling.
Also, as you may know, you cannot manually start a transaction when
any datasets are open using the same connection. This may not sound
important, but when you have millions of lines written that did not
have to adhere to this requirement, the implications are onerous.
Any workarounds or suggestions will be used and appreciated,
thanks
tonyM
