Skylar: Never thought of this - thanks for sharing! Gonna be experimenting with some of my existing queries
Robert Talda EZ-Backup Systems Engineer Cornell University +1 607-255-8280 r...@cornell.edu > On Apr 12, 2016, at 9:58 AM, Skylar Thompson <skyl...@u.washington.edu> wrote: > > The SQL engine actually does support OUTER JOIN, it's just that the view > schema exposed to us depends on joining on multiple keys; occupancy and > filespace are joined with (node_name,filespace_name). You can kind of fake > it by generating those keys in subqueries: > > SELECT - > f.fs_key - > FROM - > (SELECT node_name || ',' || filespace_name AS fs_key FROM filespaces) f - > WHERE - > f.fs_key NOT IN (SELECT node_name || ',' || filespace_name AS occ_key FROM > occupancy) > > This obviously is not going to be particularly performant but should get > the job done. > > On Tue, Apr 12, 2016 at 01:39:03PM +0000, Robert Talda wrote: >> 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 >>> ******************************************************** >>> >> > > -- > -- Skylar Thompson (skyl...@u.washington.edu) > -- Genome Sciences Department, System Administrator > -- Foege Building S046, (206)-685-7354 > -- University of Washington School of Medicine