DBexpress with DB2 questions

[tonymeadors]

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

[Serge Rielau]

to*********@adelphia.net wrote:

Any workarounds or suggestions will be used and appreciated,

Maybe explain what DBExpress is to begin with. It appears like a lot of
your problems are situated in the client.
From all I read the DB2 server is operating exactly as it should when
abused...

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Bob Swart]

Hi Tony,

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.
I've written a few dozen articles about Delphi and DB2, including a
dozen using dbExpress. See articles #1..#10, 15, #16, #28 and #29 on
http://www.drbob42.com/IBM for example.
tonyM

Groetjes,
Bob Swart (aka Dr.Bob - www.DrBob42.com)

--
Bob Swart Training & Consultancy (eBob42) - Borland Technology Partner
Delphi 2005 PDF manuals available from http://www.drbob42.com/training

[Bagieta]

If you want to use your application work with many databases don't use
BDE or dbExpress connection. There is a component pack I use for native
database connection. It supports many different databases (Oracle,
MSSQL, PostgreSQL, MySQL, Firebird, Interbase, DB2, Sybase and others)

It is called SQL Direct.
www.sqldirect-soft.com

If you are working with many databases I can also advise you nice
program for cross database management.

It is called dbDeveloper
www.dbdeveloper.prominentus.com
This is really professional database edition/admin tool that will help
you to save lots of work hours.