Correction, I referred to "last use" when it should have been "last_use" (I missed the underscore)
- Andy ____________________________________________________________________________ Andrew Raibeck | Tivoli Storage Manager Level 3 Technical Lead | stor...@us.ibm.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 ----- Forwarded by Andrew Raibeck/Hartford/IBM on 2014-11-21 08:13 ----- Andrew Raibeck/Hartford/IBM wrote on 2014-11-21 08:12:33: > From: Andrew Raibeck/Hartford/IBM > To: "ADSM: Dist Stor Manager" <ADSM-L@VM.MARIST.EDU> > Date: 2014-11-21 08:12 > Subject: Re: Weird SQL output > > 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 | > stor...@us.ibm.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@VM.MARIST.EDU> wrote on 2014-11-2108:02:09: > > > From: "Loon, EJ van (ITOPT3) - KLM" <eric-van.l...@klm.com> > > To: ADSM-L@VM.MARIST.EDU > > Date: 2014-11-21 08:03 > > Subject: Re: Weird SQL output > > Sent by: "ADSM: Dist Stor Manager" <ADSM-L@VM.MARIST.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@VM.MARIST.EDU] On > Behalf Of Efim > > Sent: vrijdag 21 november 2014 13:41 > > To: ADSM-L@VM.MARIST.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.l...@klm.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 > > ******************************************************** > >