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
rpt4 AT cornell DOT edu
> On Apr 12, 2016, at 9:58 AM, Skylar Thompson <skylar2 AT U.WASHINGTON DOT
> 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
>> 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
|
