AS400 SQL Package and Performance

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 between 5 and 7 second. 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,

The time differential you are seeing is more likely due to caching of data/indices by the database engine than anything else.

Cheers,