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
> ********************************************************
>
|
