Dieter, Scott, *et al*.
Thanks for your responses…
A little background info is in order: About eight years ago, management
decided to ditch the (iSeries based) applications at the clinic I
currently work for, going the SAP/Oracle/HP route. The system went into
production at last in mid-2009, with numerous problems in the performance,
data-integrity and reliability departments.
For those of you who haven’t enjoyed (…) the opportunity of working with
SAP (Severe A.. Pain), let me tell you it consumes HUGE resources:
hardware, user licenses, consultants, etc.
So I was tasked with retrieving the data of some Oracle tables, loading
them in our i5 520 (V5R3) and using the i5 as a (sort of) of BI and
data-debugging system. A lot cheaper in resources, licences and
programmer/analyst (me!!) costs...
Initially the requirements where for just a couple of tables, with a few
columns each, and for this I used Scott’s JDBCR4 routines (Thanks Scott!).
I wrote some service programs and subroutines in top of Scott’s and
nowadays the process of writing a data-retrieving program (along with
checks, type-conversion, logging, etc) is quite straightforward.
In view of the success of the i5 in the data analysis area, the demand for
retrieving data of different tables has skyrocketed. The data is retrieved
thru a series of batch programs that run just after midnight.
Unfortunately, some of the latest tables being processed are somewhat big,
both in terms of number of records and, just as important, number of
columns, so the run time is quite high.
I have also been using Dieter’s routines, but only for running quick
queries under STRSQL. I have been converting one of my JDBCR4 programs for
accessing one of the biggest tables, but this has been a project done on
“free” time, and all of you know how hard is to find that!!
Both sets of utilities (Scott’s and Dieter’s) have been a big help for me.
I have found JDBCR4 a little easier to use, as I have more control over the
data types (Everything is char, and I get to decide what to convert it to).
SAP/Oracle data can be a little pain to process. Many of the numeric
data are stored as Varchar, and there are also some DB2 incompatible data
As soon as I finish my programs and test them I will post the results and
analysis to the list.
Thanks again to all,
IBM Certified Systems Expert — eServer i5 iSeries