Re: JDBC vs DB2 Connect

On 8/23/2011 10:58 AM, Gary Thompson wrote:

I am working on a project where we need to read and write data between our
System i
and a 3rd party MS SQL Server database.

Data volume will not be large, but will be frequent and must be very
reliable and
respond within a few seconds.

An additional concern is ease of use for legacy RPG skill sets.

I am currently setting up to test Scott Klement's JDBCR4 service progarm
and
Jon Juracich's example that accesses MS SQL Server.

I welcome any comments that help us select the better solution, or
that suggest another.

The proposed approach sounds good. There are two obstacles to consider.

1) The RPG program needs to know about the details of the MS SQL
database. Details that the MS SQL admin might change without telling you.
2) The MS SQL server will be offline every so often.

The first problem can be addressed by use of stored procedures. Have
the MS SQL admin make SPs for each transaction and then let her worry
about keeping all the SPs up to date. If she puts a comment in there to
remind her that the SP is used by an external application, she'll
appreciate that in a year's time :-)

The second obstacle is tougher. you'll need a way to queue up requests
that haven't successfully reached the other side. One way (of many) to
tackle this is to use a data queue which feeds the actual update
process. If the update falls over, the updater puts the entry back on
the queue. Works great if there's no need to be properly sequenced. If
temporal order is important, use a database file and flag the updates
that made it. With a logical file of only 'did not make it' records,
it's easy to pick up where the updates left off.

If the update process encounters a problem, consider having it send an
SMS or twitter message out to let someone know. In any case, a log file
is invaluable.
--buck