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
