Hi Eric,
In your last SELECT statement from the initial post:
select count(*) from libvolumes where status='Private' and last_use=NULL
Unnamed[1]
------------
0
I think you meant to use "last use is NULL" instead of "last use=NULL".
Try this instead:
select count(*) from libvolumes where status='Private' and last_use is
null
and see if that explains the difference you see.
- Andy
____________________________________________________________________________
Andrew Raibeck | Tivoli Storage Manager Level 3 Technical Lead |
storman AT us.ibm DOT com
IBM Tivoli Storage Manager links:
Product support:
http://www.ibm.com/support/entry/portal/Overview/Software/Tivoli/Tivoli_Storage_Manager
Online documentation:
http://www.ibm.com/support/knowledgecenter/SSGSG7/welcome
Product Wiki:
https://www.ibm.com/developerworks/community/wikis/home/wiki/Tivoli%20Storage%20Manager
"ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU> wrote on 2014-11-21
08:02:09:
> From: "Loon, EJ van (ITOPT3) - KLM" <Eric-van.Loon AT KLM DOT COM>
> To: ADSM-L AT VM.MARIST DOT EDU
> Date: 2014-11-21 08:03
> Subject: Re: Weird SQL output
> Sent by: "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
>
> Hi Efim!
> Since we don't checkout tapes it won't be an issue for us. Apart
> from that, it still doesn't explain why the total of last_use='Data'
> and last_use!='Data' is not equal to the output from the query with
> no last_used specified.
> As to your query: I found this one online too, but it's not always
> reliable, especially when you create backupsets. They are not in a q
> volume list but they are not scratch. I think this query is usefull
> when you somehow include the volume history which does contain the
> backupset volumes.
> Kind regards,
> Eric van Loon
> AF/KLM Storage Engineering
>
>
> -----Original Message-----
> From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf
> Of
Efim
> Sent: vrijdag 21 november 2014 13:41
> To: ADSM-L AT VM.MARIST DOT EDU
> Subject: Re: Weird SQL output
>
> Hi,
> Select with last_use = ‘Data’ is not correct. If you checkout and
> when checkin tapes the parameter last_use can be lost.
> If you want to check private tapes without the data, try to use
> select like this:
>
> select volume_name from libvolumes where status='Private' and
> last_use is NULL and volume_name not in (select volume_name from volumes)
>
> Efim
>
> 2014-11-21 12:16 GMT+03:00 Loon, EJ van (ITOPT3) - KLM <Eric-
> van.Loon AT klm DOT com>:
> > Hi guys!
> > I have some weird output from one of my SQL statements. Here it's is:
> >
> > select count(*) from libvolumes where status='Private'
> > Unnamed[1]
> > ------------
> > 3698
> >
> > Ok, so 3698 private volumes on this server. Now, how many contain data:
> >
> > select count(*) from libvolumes where status='Private' and last_use =
'Data'
> > Unnamed[1]
> > ------------
> > 1011
> >
> > Right, I know a lot of tapes do not have a value in the Last Use
> column (my guess it is that these are tapes created by SAN clients),
> so how many are there:
> >
> > select count(*) from libvolumes where status='Private' and
> last_use != 'Data'
> > Unnamed[1]
> > ------------
> > 0
> >
> > Huh? There should be 3698-1011=2687! Last_use is empty then?
> >
> > select count(*) from libvolumes where status='Private' and
last_use=NULL
> > Unnamed[1]
> > ------------
> > 0
> >
> > I'm lost here... I'm I doing something wrong? The total of
> last_use='Data' and last_use!='Data' should be the same as the
> output from the query with no last_used specified, right?
> > Thanks for any help in advance!
> > Kind regards,
> > Eric van Loon
> >
> > ********************************************************
> > 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
> > ********************************************************
>
>
>
> --
> Efim
> ********************************************************
> 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
> ********************************************************
>
|
