AS400 SQL Package and Performance

rhunt

Well Known Member
I have noticed that UBE's run back to back increase in speed. For instance, if I run the R9410 for 5 business units, it takes about 10 seconds. Immediate consecutive runs take about 5 seconds. If I wait a while to run it, it goes back to 10 seconds. I have seen the same behavior with our external reporting product. For instance, if a random report (written in ANSI-SQL for DB2) takes 2 minute the first time. Immediately re-running it will take let's say, 1 minute. Waiting a while and re-running it gets you back to 2 minutes.

I'm much more familiar with the SQL optimizer as opposed to DB2, but shouldn't SQL packages on the AS/400 be storing the query path after the first run and making all subsequent runs quicker....no matter the time interval in between runs?

Thanks

Ryan Hunt
 
Ryan,

I can't tell for sure without running performance tools, but I'd bet that the better performance you are seeing has to do with the records you need already being in memory and thus you don't have to do the disk I/O each time.

This is more likely to be true if you have a dedicated memory pool for batch, and a light batch load at the time.

HTH.

Tom Davidson
 
Back
Top