troubleshooting Question

Part-1 Complex SQL Tuning Migraine...runs for 7 hrs have only 4.5hrs at to do that...what's a man to do?

Avatar of Enyinnaya
EnyinnayaFlag for United States of America asked on
Unix OSDB2System Utilities
4 Comments3 Solutions649 ViewsLast Modified:
Hi all, I hope all is well in your homes. I do have s set of SQLs that is giving me migraine because the performance is driving me up the wall. The set of SQL is coming from SIEBEL generated SDLs, indexed to dearth, I think, runs from 12 am in the morning with a mandate to be done with at 5 am in a 24/7 business, and I have nary a sophisticated or clever tool to help in this work.  Lately, this code has been running till 7am and it causing our business partners to miss their commitments to other partners...not a good place to be as a DB2 DBA or programmer!!
I need your collective tuning expertise to solve this one. I have attached one set of this SQL - this one set works like a WLM-managed - each set must complete to complete a unit of wor (UOW) to produce a useable output for the next.

One of my thoughts is to look at ways to combine the three SQLs to one or only two because they almost identical.

Another thought is that between these SQLs are over 60 indexes with mostly index scans on all of them - a terrible code made possible by a code generator. I will like to see if I can utilize multi-dimentional clustering to facilitate less scans, and the heavy-load that excessive indexing that this imposses on the CPU and run-time, to say the lest.

The third thought that I have is if MQT can help this case. Can anyone gives his/her considered thought and opinion?

Finally, any other idea that anyone has for consideration will be highly highly appreciated.

Also, to make t worth the and to show my heightened concerns, I am willing to make each of my questions a seperate but related question so that people can focuse on areas they believe they can offer more effective advise. Is that okay with everyone?
Thanks everyone.
WLM-SET1-SQL.sql
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 3 Answers and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros