I'm using a PC-based data warehouse tool that submits queries to a huge, fully-optimized iSeries database through qzdasoinit server jobs. The queries created by the tool are quite inefficient, joining too many files and resulting in long processing times. I have modified the queries manually for testing purposes using Client Access, and the new processing time is typically under 5% that of the original query. Since I cannot modify the PC tool (I do not have the option to use a different one,) is there a way to intercept, parse, rewrite and submit the new queries on the iSeries? By the way, we use SMP and every optimizer parameter, with little effect on performance.
The only way that I know of to speed up this process on the AS/400 side would cause an adverse impact on other users on the system by increasing the performance of the server jobs. This could bring all other processes to a halt, so that's not a good solution. On the PC/Connection side, one solution would be to "Fool" the PC application into thinking that it is using one ODBC driver, when in fact, it would be using the one supplied with CA. The last option is to write your own extractions and put them into the PC Data Warehouse.
MORE INFORMATION ON THIS TOPIC
Ask the Experts yourself: Our experts are waiting to answer your OS/400 questions.
Search400's targeted search engine: Get relevant information on databases.
Ask your OS/400 questions--or help out your peers by answering them--in our live discussion forums.