One of our batch job which has a very long query is taking long time to run. As per our DBAs this can not be hinted bcoz this is a dynamic query unless we create a new access module for this query to make it static. ( In our shop we put all the queries under the access module which gets created thru the in house tool called "code wizard" and the cobol code access them thru the unique statement name.) Now the problem is, to create a new view in code wizard needs a lot of approvals and will take long time, we are cancelling this job every day as it get hanged after some run. We need some quick solution for this.

Just wondering, is there any other way we can hint the query to make it efficient. Also, DBA suggessted us some changes in query to make it efficient but none of them worked.

Any pointers would be appreciated. Thanks!

I would apply the usual tuning techniques: analyzing what the query does on the system level (disk I/O), in the buffer pool, whether it uses indexes or table scans, how many sorts are there - the usual stuff. Then you can rewrite the query, creating indexes, or take any other corrective measures that are all well-known.