IDS 7.31 Solaris 2.7 performance issue

Hi everyone. Does anyone have any ideas about the following? We have and IDS 7.31 database (yes old) on Solaris 2.7 that allegedly has application processes (yet to be identified) that are running significantly slower than they were about 6 months ago. These processes are primarily embedded sql in C and PERL programs and some Informix 4GL. Anyway, until development gives me some kind of hint about which specific SQL seems to be the culprit (I am still waiting on that) I was wondering about some cooked chunks I needed to add a few months ago in an absolute emergency situation. Here is my question.....If we were to identify the offending SQL and the SQL does NOT access any tables/indexes on the cooked files...would that eliminate the cooked files as the culprit? I would think that it would. Also, as we all know we are not supposed to use cooked files because of performance issues. But has anyone experienced using cooked files and seen firsthand really significant performance issues related to using cooked files? Thanks Will

WILL LANDSTROM said: > Hi everyone. Does anyone have any ideas about the following? We have and > IDS > 7.31 database (yes old) on Solaris 2.7 that allegedly has application > processes (yet to be identified) that are running significantly slower > than > they were about 6 months ago. These processes are primarily embedded sql > in C > and PERL programs and some Informix 4GL. Anyway, until development gives > me > some kind of hint about which specific SQL seems to be the culprit (I am > still > waiting on that) I was wondering about some cooked chunks I needed to add > a > few months ago in an absolute emergency situation. Here is my > question.....If > we were to identify the offending SQL and the SQL does NOT access any > tables/indexes on the cooked files...would that eliminate the cooked files > as > the culprit? I would think that it would. Also, as we all know we are not > supposed to use cooked files because of performance issues. But has anyone > experienced using cooked files and seen firsthand really significant > performance issues related to using cooked files? What does "significantly slower" mean? And have you tried UPDATE STATISTICS? Maybe the data skew has changed and you need a different set of stats? -- Bye now, Obnoxio http://obotheclown.blogspot.com/

I like Obnoxio's suggestion for starters. I wouldn't think you are having the same issue I did...... I am running db2 on solaris now and its all cooked. I used to be informix raw on solaris. Anyway, with db2 cooked we found out immediately that we needed to purchase Sun's "quick i/o" or "concurrent i/o" or something like that.... It was for making cooked perform closer to raw. But as I said we found out immediately..... It did not take 6 months to see the problem Norma Jean ----- Original Message ----- From: ids-bounces@iiug.org <ids-bounces@iiug.org> To: ids@iiug.org <ids@iiug.org> Sent: Thu Jul 17 17:04:49 2008 Subject: IDS 7.31 Solaris 2.7 performance issue [12764] Hi everyone. Does anyone have any ideas about the following? We have and IDS 7.31 database (yes old) on Solaris 2.7 that allegedly has application processes (yet to be identified) that are running significantly slower than they were about 6 months ago. These processes are primarily embedded sql in C and PERL programs and some Informix 4GL. Anyway, until development gives me some kind of hint about which specific SQL seems to be the culprit (I am still waiting on that) I was wondering about some cooked chunks I needed to add a few months ago in an absolute emergency situation. Here is my question.....If we were to identify the offending SQL and the SQL does NOT access any tables/indexes on the cooked files...would that eliminate the cooked files as the culprit? I would think that it would. Also, as we all know we are not supposed to use cooked files because of performance issues. But has anyone experienced using cooked files and seen firsthand really significant performance issues related to using cooked files? Thanks Will ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ============================================================ The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reproduction, dissemination or distribution of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Tellabs ============================================================

Sebastian, Norma J. said: > I like Obnoxio's suggestion for starters. > > I wouldn't think you are having the same issue I did...... I am running db2 on solaris now and its all cooked. I used to be informix raw on solaris. Anyway, with db2 cooked we found out immediately that we needed to purchase Sun's "quick Talk dirty to me, honey! -- Bye now, Obnoxio http://obotheclown.blogspot.com/

Oh Cute! This is like one of those speckle-painting where you have to look for the real image inside all the speckle-dots. However, I'm not very good at seeing those. Did ya get a message inside the scrambled-eggs? Jonathan Smaby Pomona College ITS Department -- =B3Laws are like sausages, it is better not to see them being made.=B2 ~Otto von Bismarck > From: "Sebastian, Norma J." <NormaJean.Sebastian@tellabs.com> > Reply-To: <ids@iiug.org> > Date: Thu, 17 Jul 2008 18:37:00 -0400 (EDT) > To: <ids@iiug.org> > Subject: Re: IDS 7.31 Solaris 2.7 performance issue [12767] >=20 > SSBsaWtlIE9ibm94aW8ncyBzdWdnZXN0aW9uIGZvciBzdGFydGVycy4NCg0KSSB3b3VsZG4nd= CB0 > aGluayB5b3UgYXJlIGhhdmluZyB0aGUgc2FtZSBpc3N1ZSBJIGRpZC4uLi4uLiAgSSBhbSByd= W5u > aW5nIGRiMiBvbiBzb2xhcmlzIG5vdyBhbmQgaXRzIGFsbCBjb29rZWQuICBJIHVzZWQgdG8gY= mUg > aW5mb3JtaXggcmF3IG9uIHNvbGFyaXMuICBBbnl3YXksIHdpdGggZGIyIGNvb2tlZCB3ZSBmb= 3Vu > ZCBvdXQgaW1tZWRpYXRlbHkgdGhhdCB3ZSBuZWVkZWQgdG8gcHVyY2hhc2UgU3VuJ3MgInF1a= WNr > IGkvbyIgb3IgImNvbmN1cnJlbnQgaS9vIiBvciBzb21ldGhpbmcgbGlrZSB0aGF0Li4uLiBJd= CB3 > YXMgZm9yIG1ha2luZyBjb29rZWQgcGVyZm9ybSBjbG9zZXIgdG8gcmF3LiAgQnV0IGFzIEkgc= 2Fp > ZCB3ZSBmb3VuZCBvdXQgaW1tZWRpYXRlbHkuLi4uLiBJdCBkaWQgbm90IHRha2UgNiBtb250a= HMg > dG8gc2VlIHRoZSBwcm9ibGVtDQoNCk5vcm1hIEplYW4NCg0KDQoNCi0tLS0tIE9yaWdpbmFsI= E1l > c3NhZ2UgLS0tLS0NCkZyb206IGlkcy1ib3VuY2VzQGlpdWcub3JnIDxpZHMtYm91bmNlc0Bpa= XVn > Lm9yZz4NClRvOiBpZHNAaWl1Zy5vcmcgPGlkc0BpaXVnLm9yZz4NClNlbnQ6IFRodSBKdWwgM= Tcg > MTc6MDQ6NDkgMjAwOA0KU3ViamVjdDogSURTIDcuMzEgU29sYXJpcyAyLjcgcGVyZm9ybWFuY= 2Ug > aXNzdWUgIFsxMjc2NF0NCg0KSGkgZXZlcnlvbmUuIERvZXMgYW55b25lIGhhdmUgYW55IGlkZ= WFz > IGFib3V0IHRoZSBmb2xsb3dpbmc/IFdlIGhhdmUgYW5kIElEUyANCjcuMzEgZGF0YWJhc2UgK= Hll > cyBvbGQpIG9uIFNvbGFyaXMgMi43IHRoYXQgYWxsZWdlZGx5IGhhcyBhcHBsaWNhdGlvbiANC= nBy > b2Nlc3NlcyAoeWV0IHRvIGJlIGlkZW50aWZpZWQpIHRoYXQgYXJlIHJ1bm5pbmcgc2lnbmlma= WNh > bnRseSBzbG93ZXIgdGhhbiANCnRoZXkgd2VyZSBhYm91dCA2IG1vbnRocyBhZ28uIFRoZXNlI= HBy > b2Nlc3NlcyBhcmUgcHJpbWFyaWx5IGVtYmVkZGVkIHNxbCBpbiBDIA0KYW5kIFBFUkwgcHJvZ= 3Jh > bXMgYW5kIHNvbWUgSW5mb3JtaXggNEdMLiBBbnl3YXksIHVudGlsIGRldmVsb3BtZW50IGdpd= mVz > IG1lIA0Kc29tZSBraW5kIG9mIGhpbnQgYWJvdXQgd2hpY2ggc3BlY2lmaWMgU1FMIHNlZW1zI= HRv > IGJlIHRoZSBjdWxwcml0IChJIGFtIHN0aWxsIA0Kd2FpdGluZyBvbiB0aGF0KSBJIHdhcyB3b= 25k > ZXJpbmcgYWJvdXQgc29tZSBjb29rZWQgY2h1bmtzIEkgbmVlZGVkIHRvIGFkZCBhIA0KZmV3I= G1v > bnRocyBhZ28gaW4gYW4gYWJzb2x1dGUgZW1lcmdlbmN5IHNpdHVhdGlvbi4gSGVyZSBpcyBte= SBx > dWVzdGlvbi4uLi4uSWYgDQp3ZSB3ZXJlIHRvIGlkZW50aWZ5IHRoZSBvZmZlbmRpbmcgU1FMI= GFu > ZCB0aGUgU1FMIGRvZXMgTk9UIGFjY2VzcyBhbnkgDQp0YWJsZXMvaW5kZXhlcyBvbiB0aGUgY= 29v > a2VkIGZpbGVzLi4ud291bGQgdGhhdCBlbGltaW5hdGUgdGhlIGNvb2tlZCBmaWxlcyBhcyANC= nRo > ZSBjdWxwcml0PyBJIHdvdWxkIHRoaW5rIHRoYXQgaXQgd291bGQuIEFsc28sIGFzIHdlIGFsb= CBr > bm93IHdlIGFyZSBub3QgDQpzdXBwb3NlZCB0byB1c2UgY29va2VkIGZpbGVzIGJlY2F1c2Ugb= 2Yg > cGVyZm9ybWFuY2UgaXNzdWVzLiBCdXQgaGFzIGFueW9uZSANCmV4cGVyaWVuY2VkIHVzaW5nI= GNv > b2tlZCBmaWxlcyBhbmQgc2VlbiBmaXJzdGhhbmQgcmVhbGx5IHNpZ25pZmljYW50IA0KcGVyZ= m9y > bWFuY2UgaXNzdWVzIHJlbGF0ZWQgdG8gdXNpbmcgY29va2VkIGZpbGVzPyANClRoYW5rcyBXa= Wxs > IA0KDQoNCioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqK= ioq > KioqKioqKioqKioqKioqKioqKioqKioqKioqKiogDQogIEZvcnVtIE5vdGU6IFVzZSAiUmVwb= Hki > IHRvIHBvc3QgYSByZXNwb25zZSBpbiB0aGUgZGlzY3Vzc2lvbiBmb3J1bS4gDQo9PT09PT09P= T09 > PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT0KVGhlI= Glu > Zm9ybWF0aW9uIGNvbnRhaW5lZCBpbiB0aGlzIG1lc3NhZ2UgbWF5IGJlIHByaXZpbGVnZWQKY= W5k > IGNvbmZpZGVudGlhbCBhbmQgcHJvdGVjdGVkIGZyb20gZGlzY2xvc3VyZS4gSWYgdGhlIHJlY= WRl > cgpvZiB0aGlzIG1lc3NhZ2UgaXMgbm90IHRoZSBpbnRlbmRlZCByZWNpcGllbnQsIG9yIGFuI= GVt > cGxveWVlCm9yIGFnZW50IHJlc3BvbnNpYmxlIGZvciBkZWxpdmVyaW5nIHRoaXMgbWVzc2FnZ= SB0 > byB0aGUKaW50ZW5kZWQgcmVjaXBpZW50LCB5b3UgYXJlIGhlcmVieSBub3RpZmllZCB0aGF0I= GFu > eSByZXByb2R1Y3Rpb24sCmRpc3NlbWluYXRpb24gb3IgZGlzdHJpYnV0aW9uIG9mIHRoaXMgY= 29t > bXVuaWNhdGlvbiBpcyBzdHJpY3RseQpwcm9oaWJpdGVkLiBJZiB5b3UgaGF2ZSByZWNlaXZlZ= CB0 > aGlzIGNvbW11bmljYXRpb24gaW4gZXJyb3IsCnBsZWFzZSBub3RpZnkgdXMgaW1tZWRpYXRlb= Hkg > YnkgcmVwbHlpbmcgdG8gdGhlIG1lc3NhZ2UgYW5kCmRlbGV0aW5nIGl0IGZyb20geW91ciBjb= 21w > dXRlci4gVGhhbmsgeW91LiBUZWxsYWJzCj09PT09PT09PT09PT09PT09PT09PT09PT09PT09P= T09 > PT09PT09PT09PT09PT09PT09PT09PT09PT09PQo=3D >=20 >=20 > *************************************************************************= ***** > *=20 > Forum Note: Use "Reply" to post a response in the discussion forum. >=20 ------------------------------------------------------------- This message has been scanned by Postini anti-virus software. =0D

If you added some COOKED chunks, did you increase the number of AIO VPs? Run onstat -g iov, if the io/wup is over 1.0 for one of more of them, you need to add more. You can do that dynamically and maybe be a hero: onmode -p +1 aio Add one and a half new AIO VPs per COOKED chunk you added. The rest would be speculation, but AIO VPs are also used to write to the message log and to sort-work files kept in filesystem space if PSORT_DBTEMP is set, so the location of the tables in the query may not matter. Art On Thu, Jul 17, 2008 at 6:04 PM, WILL LANDSTROM <willlandstrom@yahoo.com> wrote: > Hi everyone. Does anyone have any ideas about the following? We have and > IDS > 7.31 database (yes old) on Solaris 2.7 that allegedly has application > processes (yet to be identified) that are running significantly slower than > they were about 6 months ago. These processes are primarily embedded sql in > C > and PERL programs and some Informix 4GL. Anyway, until development gives me > some kind of hint about which specific SQL seems to be the culprit (I am > still > waiting on that) I was wondering about some cooked chunks I needed to add a > few months ago in an absolute emergency situation. Here is my > question.....If > we were to identify the offending SQL and the SQL does NOT access any > tables/indexes on the cooked files...would that eliminate the cooked files > as > the culprit? I would think that it would. Also, as we all know we are not > supposed to use cooked files because of performance issues. But has anyone > experienced using cooked files and seen firsthand really significant > performance issues related to using cooked files? > Thanks Will > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Dang it, did I post that to everybody? that was supposed to be a private message :-) -----Original Message----- From: .....On Behalf Of Obnoxio The Clown Subject: Re: IDS 7.31 Solaris 2.7 performance issue [12768] Sebastian, Norma J. said: > SSBsaWtlIE9ibm94aW8ncyBzdWdnZXN0aW9uIGZvciBzdGFydGVycy4NCg0KSSB3b3VsZG4n dCB0 > aGluayB5b3UgYXJlIGhhdmluZyB0aGUgc2FtZSBpc3N1ZSBJIGRpZC4uLi4uLiAgSSBhbSBy dW5u > aW5nIGRiMiBvbiBzb2xhcmlzIG5vdyBhbmQgaXRzIGFsbCBjb29rZWQuICBJIHVzZWQgdG8g YmUg Talk dirty to me, honey! -- Bye now, Obnoxio ============================================================ The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reproduction, dissemination or distribution of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Tellabs ============================================================

It's that new language I am learning.... I am learning to speak db2... I'm not doing well, I'll stick to some variant of English ;-) What I tried to post was: I like Obnoxio's suggestion for starters. (update stats) I wouldn't think it's the same issue I had...... I am running db2 on solaris and its all cooked. I used to be informix raw on solaris. Anyway, with db2 cooked we found out immediately that we needed to purchase Sun's "quick i/o" or "concurrent i/o" or something like that.... It was for making cooked perform closer to raw. But as I said we found out immediately..... It did not take 6 months to see the problem Hoping this comes through in something closer to English..... Norma Jean -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jonathan Smaby Sent: Thursday, July 17, 2008 5:41 PM To: ids@iiug.org Subject: Re: IDS 7.31 Solaris 2.7 performance issue [12769] Oh Cute! This is like one of those speckle-painting where you have to look for the real image inside all the speckle-dots. However, I'm not very good at seeing those. Did ya get a message inside the scrambled-eggs? Jonathan Smaby Pomona College ITS Department -- =B3Laws are like sausages, it is better not to see them being made.=B2 ~Otto von Bismarck > From: "Sebastian, Norma J." <NormaJean.Sebastian@tellabs.com> > Reply-To: <ids@iiug.org> > Date: Thu, 17 Jul 2008 18:37:00 -0400 (EDT) > To: <ids@iiug.org> > Subject: Re: IDS 7.31 Solaris 2.7 performance issue [12767] >=20 > SSBsaWtlIE9ibm94aW8ncyBzdWdnZXN0aW9uIGZvciBzdGFydGVycyCB0 > aGluayB5b3UgYXJlIGhhdmluZyB0aGUgc2FtZSBpc3N1ZSBJIGRpZC4u5u > aW5nIGRiMiBvbiBzb2xhcmlzIG5vdyBhbmQgaXRzIGFsbCBjb29rZWQmUg > aW5mb3JtaXggcmF3IG9uIHNvbGFyaXMuICBBbnl3YXksIHdpdGggZG ============================================================ The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reproduction, dissemination or distribution of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Tellabs ============================================================

I was private, we couldn't decipher it :-P Jonathan Smaby Pomona College ITS Department > From: "Sebastian, Norma J." <NormaJean.Sebastian@tellabs.com> > Reply-To: <ids@iiug.org> > Date: Thu, 17 Jul 2008 19:47:43 -0400 (EDT) > To: <ids@iiug.org> > Subject: RE: IDS 7.31 Solaris 2.7 performance issue [12772] > > Dang it, did I post that to everybody? that was supposed to be a > private message :-) > > -----Original Message----- > From: .....On Behalf Of Obnoxio The Clown > Subject: Re: IDS 7.31 Solaris 2.7 performance issue [12768] > > Sebastian, Norma J. said: >> > SSBsaWtlIE9ibm94aW8ncyBzdWdnZXN0aW9uIGZvciBzdGFydGVycy4NCg0KSSB3b3VsZG4n > dCB0 >> > aGluayB5b3UgYXJlIGhhdmluZyB0aGUgc2FtZSBpc3N1ZSBJIGRpZC4uLi4uLiAgSSBhbSBy > dW5u >> > aW5nIGRiMiBvbiBzb2xhcmlzIG5vdyBhbmQgaXRzIGFsbCBjb29rZWQuICBJIHVzZWQgdG8g > YmUg > > Talk dirty to me, honey! > -- > Bye now, > Obnoxio > ============================================================ > The information contained in this message may be privileged > and confidential and protected from disclosure. If the reader > of this message is not the intended recipient, or an employee > or agent responsible for delivering this message to the > intended recipient, you are hereby notified that any reproduction, > dissemination or distribution of this communication is strictly > prohibited. If you have received this communication in error, > please notify us immediately by replying to the message and > deleting it from your computer. Thank you. Tellabs > ============================================================ > > > ****************************************************************************** > * > Forum Note: Use "Reply" to post a response in the discussion forum. > ------------------------------------------------------------- This message has been scanned by Postini anti-virus software.