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
> rpt4 AT cornell DOT edu
>
>
> > On Apr 12, 2016, at 7:10 AM, Loon, EJ van (ITOPT3) - KLM <Eric-van.Loon AT
> > KLM DOT 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 AT VM.MARIST DOT EDU] On
> > Behalf Of Robert Talda
> > Sent: maandag 11 april 2016 19:34
> > To: ADSM-L AT VM.MARIST DOT 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
> > rpt4 AT cornell DOT edu
> >
> >
> >> On Apr 11, 2016, at 10:07 AM, Loon, EJ van (ITOPT3) - KLM <Eric-van.Loon
> >> AT KLM DOT 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 (skylar2 AT u.washington DOT edu)
-- Genome Sciences Department, System Administrator
-- Foege Building S046, (206)-685-7354
-- University of Washington School of Medicine
|
