× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Rick

I realize I didn't responded with what you were looking for, but I couldn't resist expressing my surprise that IBM are touting SQL as a silver bullet for a batch performance problem, especially an overnight long running batch process.

I'm probably going out on a limb here, but SQL is just about the last thing I would expect to help the situation you described. Of course 20 years of operations and tuning experience has probably blinded me to the benefits of SQL as a performance aid ;)

All of the benefits of SQL I've seen touted so far in this thread - with the possible exception of RI being removed from the program logic (and I'm unconvinced on that as well) - may all be nice to have but in all likelihood will not solve your problem, at least in my opinion .

One advantage I saw mentioned - automatic journalling - would be more likely to hurt performance as far as I can see.

The automatic blocking is a non-issue in my mind - this is one of the first things I would have considered in this situation especially given the file sizes you are talking about. Does SQL also take advantage of SETOBJACC to store critical small files in memory ?

The advantages of RI and all the updates being in one place - as with the other advantages mentioned - seems to me to be a two edged sword: there are less levers to pull when tuning. If it isn't efficient enough all your eggs are in one basket and your are forced to upgrade hardware to solve the problem.

My experience has been more along the lines that breaking large processes down into components, using server jobs, data queues, file blocking, parallel job streams etc. etc. is far more effective.

Large overnight batch jobs tend to make me suspicious regarding either the application or the database design anyway. I like to see multiple small real-time server driven processes as opposed to single threaded huge updates wherever possible.

Of course I know nothing about your situation so it may well be that I am talking through my southern region :)

While it is true that converting to SQL might make the problem go away, in reality this would be a side-effect of re-engineering the application rather than a direct benefit of SQL itself. It is also possible that re-engineering the application without converting an SQL approach might achieve the same result.

Your simple performance measurement of the difference approaches would appear to indicate that SQL itself will not magically outperform an RPG program. Given that you did not use blocking to enhance the RPG program (did I get that right ?) it would appear that it may have been able to outperform SQL if tweaked. I guess you were just comparing the basic vanilla output of the two approaches.

Sadly, without testing and measuring both approaches (and who gets that choice !) we are never going to know for sure what the real situation is, we'll just be left with our gut feel and experience to guide us, at least for the time being.

I can't help but think that whoever provided the performance recommendation is not an "AS/400 guy". I would expect that when the expected performance gains don't materialise you will be encouraged to move to DB2 on either pSeries or xSeries as the next stage of "the solution".

I would even go as far as to say that the recommendations you received are the kind of advice that is killing off the AS/400 by trying to solve a performance problem the same way it is attacked on completely different platforms - a stupid approach in my opinion as they do not leverage the AS/400s job management strengths.

I hope it all works as IBM have indicated it will. Incidentally was it Global Services or the iSeries technical guys that came up with this recommendation ?

regards
Evan Harris


The recommendations from IBM are as follows:

* SQL would be a better choice for long running batch processes because it
/automatically uses blocking operations for read only cursors and index and
table scans are parallel enabled through the use of the Symmetrical
Multiprocessing (SMP) feature of OS/400.

* Using  SQL for mass file updates minimizes I/O since only rows matching
the local selection criteria are read for update. In addition, DB2 UDB for
iSeries is capable of generating an updateable join.

* The SQL blocked FETCH and INSERT support provides an additional layer of
blocking over and above the physical and logical layers. This is application
layer blocking. SQL automatically blocks read and write operations at the
maximum blocking factor available for that release (Currently using a 128k
buffer vs. 4 - 8 for DDS).  The programmer need not be concerned about
OVRDBF calculations.  In essence, one FETCH or INSERT statement can read or
write a maximum of 32767 rows in one operation from the program to the Open
Data Path (ODP).  Native programs must issue multiple reads or writes to
move data to and from the ODP.

The long range proposal is to move to a three tiered approach where the
database is de-coupled from the application and the database is accessed
through a set of I/O modules.  Various aspects of the existing database
would be duplicated using views and indexes as I understand it.

Out of curiosity I created two programs to read through a physical file
performing a math calculation and date conversion on each record read.  The
first program was written using the READ op-code inside a Do loop.  The
second program was written using a blocked SQL fetch inside a DO loop.
Running on an 890 under V5R2 I could see no significant difference in speed
after processing over 200 million records from a physical file.

Right now I am just trying to gather information and evaluate some of the
suggestions.  I would really appreciate hearing of anyone else's
experiences.

Rick






As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.