The bad SQL is usually tablespace scans, and/or Cartesian product. They are relatively easy to identify and cancel.
MVS reports the stress in prod, the high CPU use on the dev lpar, and I find the misbehaving thread and cancel it. Mvs reports things then return to normal. The perplexing part is the bad SQL running on LPARA is affecting its own lpar and the major lpar on the CEC. It's own lpar I can understand, but the other one too? The prefetches - dynamic, list, and sequential are ziip eligible in DB2 V10, so the comment about the bad SQL taking the ziips from prod is possible. I'm adding that to my list as something to check. The I/o comment is interesting. I'll add it to my list to watch for also. I'm hitting the books tonight. Thanks for all the ideas and references. Sent from my iPad >> On Dec 17, 2014, at 9:48 PM, Clark Morris <cfmpub...@ns.sympatico.ca> wrote: >> >> On 17 Dec 2014 14:13:46 -0800, in bit.listserv.ibm-main you wrote: >> >> In pretty good with DB2, and Craig is wonderful. >> >> It's the intricacies of MVS performance I need to bring in focus. I have a >> lot of reading and research to do so I can collect appropriate doc the next >> time one of these hits. > > After reading most of this thread, two things hit this retired systems > programmer. The first is that with all DASD shared, runaway bad SQL > may be doing a number on your disk performance due to contention and I > would look at I-O on both production and test. DB2 and other experts > who are more familiar with current DASD technology and contention can > give more help. The other is the role played on both LPARs by the use > of zAAP and zIIP processors which run hecat full speed and reduced cost > for selected work loads. The bad SQL may be eligible to run on those > processors and taking away the availability from production. This is > just a guess based on a dangerous (inadequate) amount of knowledge. > > Clark Morris >> >> Linda >> Sent from my iPhone >> >>> On Dec 17, 2014, at 2:34 PM, Ed Finnell >>> <0000000248cce9f3-dmarc-requ...@listserv.ua.edu> wrote: >>> >>> Craig Mullin's DB/2 books are really educational in scope and insight(and >>> heavy). Fundamental understanding of the interoperation is key to >>> identifying and tuning problems. He was with Platinum when he first >>> began the >>> series and moved on after the acquisition by CA.(He and other vendors were >>> presenting at our ADUG conference on 9/11/01. Haven't seen him since but >>> still get the updates.) >>> >>> The CA tools are really good at pinpointing problems. Detector and Log >>> Analyzer are key. For SQL there's the SQL analyzer(priced) component. >>> Sometimes >>> if it's third party software there may be a known issues with certain >>> transactions or areas of interactions. >>> >>> For continual knowledge _www.share.org_ (http://www.share.org) is a good >>> source. Developers and installations give current and timely advice on >>> fitting metrics to machines. The proceedings for the last three Events are >>> kept >>> online >>> without signup. The new RMF stuff for EC12 and Flash drives is pretty >>> awesome. >>> >>> >>> In a message dated 12/17/2014 11:00:20 A.M. Central Standard Time, >>> linda.haged...@gmail.com writes: >>> >>> I lurk on IBM-Main, and I'm always awed by the knowledge here. >>> >>> >>> >>> ---------------------------------------------------------------------- >>> For IBM-MAIN subscribe / signoff / archive access instructions, >>> send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN >> >> ---------------------------------------------------------------------- >> For IBM-MAIN subscribe / signoff / archive access instructions, >> send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN > > ---------------------------------------------------------------------- > For IBM-MAIN subscribe / signoff / archive access instructions, > send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN ---------------------------------------------------------------------- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
