Thanks for your contribution during your rest! George H.
-----Original Message----- From: ADSM: Dist Stor Manager [mailto:[email protected]] On Behalf Of Steven Harris Sent: Wednesday, July 29, 2009 8:36 PM To: [email protected] Subject: Re: [ADSM-L] Summarizing Tape Utilization Hi George I have a copy of the interesting bits of a TSM database in sqlite3 for a side project I'm doing while "resting". On that database I can use select count(*), cast(abs(pct_utilized-.01)/10 as integer) as decile, stgpool_name from volumes where devclass_name != 'DISK' group by stgpool_name, cast(abs(pct_utilized-.01)/10 as integer) to give something like what you are looking for. The main issue is that deciles with no members are not listed. You may need to adjust the SQL for the TSM database engine, but I can't see anything that I know isn't valid TSM sql. Regards Steve Steven Harris TSM Admin, "resting" in Sydney Australia Huebschman, George J. wrote: > No, just the command line > > -----Original Message----- > From: ADSM: Dist Stor Manager [mailto:[email protected]] On Behalf > Of Lindsay Morris > Sent: Wednesday, July 29, 2009 4:40 PM > To: [email protected] > Subject: Re: [ADSM-L] Summarizing Tape Utilization > > George, aren't you a Servergraph user? Doesn't it give you what you > need? I don't know what you're trying to accomplish here, but > Servergraph handles the usual culprits... > > -------------------- > Lindsay Morris > Principal > TSMworks, Inc. > 1-919-403-8260 > www.tsmworks.com > > On Jul 29, 2009, at 2:52 PM, Bob Levad <[email protected]> wrote: > > >> I think you'll need a separate query for each utilization range. >> At least, I haven't thought of a good way to iterate. >> >> >> >> select stgpool_name, count(*) as "60% < utilized < 70%" - from >> volumes >> > > >> - where devclass_name='LTOCLASS4' - >> and pct_utilized>=60 - >> and pct_utilized<70 - >> group by stgpool_name - >> order by stgpool_name >> >> Etc... >> >> >> You could maybe nest several of these selects inside another select, >> but that can be pretty cumbersome. >> >> Bob >> >> >> >> >> >> -----Original Message----- >> From: ADSM: Dist Stor Manager [mailto:[email protected]] On Behalf >> Of Huebschman, George J. >> Sent: Wednesday, July 29, 2009 10:59 AM >> To: [email protected] >> Subject: [ADSM-L] Summarizing Tape Utilization >> >> Greetings everyone, >> I have a/an SQL Select question. >> >> Most of my TSM Servers are at 5.5.1.0, one is at 5.5.2.0 >> >> I am trying to count the number of tapes with a percentage >> utilization >> > > >> in brackets of 10 percent. In other words, how many tapes with >> utilization between 100 and 90, 90 and 80, and so forth. >> >> I first tried: >> select count(volume_name), pct_utilized, stgpool_name from volumes - >> where (pct_utilized between 100 and 90) or (pct_utilized between 90 >> and >> 80) or (pct_utilized between 70 and 60) or (pct_utilized between 60 >> and >> 50) or (pct_utilized>50)- >> group by stgpool_name, pct_utilized >> >> Although the statement functions and returns valid data, it is not >> what I expected. I mistakenly expected it to count all the tapes >> within each given range. What it really does is count tapes with >> distinct pct_util. >> I might as well not have specified the ranges. The only tapes it >> counted cumulatively were of the exact same pct_util. >> >> Unnamed[1] PCT_UTILIZED STGPOOL_NAME >> ----------- ------------ ------------------ >> 1 59.4 C_TSMSERVER_TAPE >> 1 68.7 C_TSMSERVER_TAPE >> 1 78.2 C_TSMSERVER_TAPE >> 1 79.5 C_TSMSERVER_TAPE >> 1 99.9 C_TSMSERVER_TAPE >> 1 100.0 C_TSMSERVER_TAPE >> 1 66.0 NASPOOL >> >> If I select for a count for greater than or less than a particular >> value, I get the kind of count I expect. I had expected "between" to >> do similar work but be less klunky. >> >> select count(volume_name) from volumes where pct_utilized<100 and >> pct_utilized>89 >> >> >> The other option I tried was CASE, WHEN, THEN: >> >> select count(case when pct_utilized between 100 and 91 then 1 else >> 0) from >> volumes and, select count(case when (pct_utilized between 100 and >> 91) then 1 >> else 0) from volumes >> >> Those failed for syntax errors. >> >> Is there a clean way to do this? >> >> George Huebschman >> >> IMPORTANT: E-mail sent through the Internet is not secure. Legg Mason therefore recommends that you do not send any confidential or sensitive information to us via electronic mail, including social security numbers, account numbers, or personal identification numbers. Delivery, and or timely delivery of Internet mail is not guaranteed. Legg Mason therefore recommends that you do not send time sensitive or action-oriented messages to us via electronic mail. This message is intended for the addressee only and may contain privileged or confidential information. Unless you are the intended recipient, you may not use, copy or disclose to anyone any information contained in this message. If you have received this message in error, please notify the author by replying to this message and then kindly delete the message. Thank you.
