EJ: Wish I could be as helpful this time. Sadly, I’ve not found a way to generate this output with a single SQL statement - the TSM SQL engine doesn’t seem to support the concept of OUTER JOIN. I’ve had to resort to doing 2 queries - one of the occupancy table and one of the filespaces table - and then using an external application (MS Excel, join, etc) to merge the results. And my employer frowns on running SQL directly against DB2.
Perhaps someone more clever than I has found a way that they can share. BTW, I’ve got the subquery to limit occupancy data to PRIMARY storage memorized… Robert Talda EZ-Backup Systems Engineer Cornell University +1 607-255-8280 r...@cornell.edu > On Apr 12, 2016, at 7:10 AM, Loon, EJ van (ITOPT3) - KLM > <eric-van.l...@klm.com> wrote: > > Hi Robert! > Thanks for the tip! You're right, all missing filespaces were not in the > occupancy table. And to make things even more difficult, some filespaces were > listed twice, because data for the same filespace resides in the diskpool and > the primary pool... The latter issue I can solve by adding a > o.stgpoolpool_name parameter and only include the primary (VTL) pool entries. > Did you find a way to include the filespaces that were not in the occupancy > table? I want them listed in the exception report too, even though they are > not taking up space on the TSM server... > Thanks again for your help! > Kind regards, > Eric van Loon > Air France/KLM Storage Engineering > > -----Original Message----- > From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of > Robert Talda > Sent: maandag 11 april 2016 19:34 > To: ADSM-L@VM.MARIST.EDU > Subject: Re: SQL statement > > EJ: > > Are you sure the missing filespaces have data? if not, they won’t have > associated occupancy records and thus won’t appear in the output. > > I trip over that from time to time myself > > > > Robert Talda > EZ-Backup Systems Engineer > Cornell University > +1 607-255-8280 > r...@cornell.edu > > >> On Apr 11, 2016, at 10:07 AM, Loon, EJ van (ITOPT3) - KLM >> <eric-van.l...@klm.com> wrote: >> >> Hi guys! >> I'm trying to create a SQL statement which should list all filespaces, along >> with their occupancy, with a backup date longer than 2 days ago, but only >> for nodes with an last access date of today or yesterday. If the node hasn't >> contacted the server for two days or more it's reported in a different >> report. >> This is what I came up with thus far: >> >> SELECT f.node_name AS "Node name", f.filespace_name AS "Filespace", >> to_char(char(f.backup_end),'YYYY-MM-DD') AS "Last Backup Date", >> CAST(ROUND(o.physical_mb/1024) as int) as "GB Stored" FROM nodes n, >> filespaces f, occupancy o WHERE o.node_name=n.node_name AND >> n.node_name=f.node_name AND o.filespace_name=f.filespace_name AND >> days(f.backup_end)<(days(current_date)-2) AND cast(timestampdiff(16, >> current_timestamp - n.lastacc_time) as decimal(5,1))>= 2 ORDER BY >> f.node_name DESC >> >> I am however missing several filespaces in the output returned. I must be >> doing something wrong but I can't find what. >> Thanks in advance for any help! >> Kind regards, >> Eric van Loon >> Air France/KLM Storage Engineering >> >> ******************************************************** >> For information, services and offers, please visit our web site: >> http://www.klm.com. This e-mail and any attachment may contain confidential >> and privileged material intended for the addressee only. If you are not the >> addressee, you are notified that no part of the e-mail or any attachment may >> be disclosed, copied or distributed, and that any other action related to >> this e-mail or attachment is strictly prohibited, and may be unlawful. If >> you have received this e-mail by error, please notify the sender immediately >> by return e-mail, and delete this message. >> >> Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its >> employees shall not be liable for the incorrect or incomplete transmission >> of this e-mail or any attachments, nor responsible for any delay in receipt. >> Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch >> Airlines) is registered in Amstelveen, The Netherlands, with registered >> number 33014286 >> ******************************************************** > > ******************************************************** > For information, services and offers, please visit our web site: > http://www.klm.com. This e-mail and any attachment may contain confidential > and privileged material intended for the addressee only. If you are not the > addressee, you are notified that no part of the e-mail or any attachment may > be disclosed, copied or distributed, and that any other action related to > this e-mail or attachment is strictly prohibited, and may be unlawful. If you > have received this e-mail by error, please notify the sender immediately by > return e-mail, and delete this message. > > Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its > employees shall not be liable for the incorrect or incomplete transmission of > this e-mail or any attachments, nor responsible for any delay in receipt. > Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch > Airlines) is registered in Amstelveen, The Netherlands, with registered > number 33014286 > ******************************************************** >