Erik, I can't believe that you're never checkin private tapes. After checked in last_use is NULL. It is not an issue. It just tell that nobody use this tape after it checked in. also don't forget about last_use='DbBackup' so I think:
Total private tapes = tapes with last_use='Data' + tapes with last_use='DbBackup' + tapes with last_use is NULL but present in volumes table + tapes with last_use is NULL but with type=Backupset (select volume_name from volhistory where type='BACKUPSET') Efim 2014-11-21 16:02 GMT+03:00 Loon, EJ van (ITOPT3) - KLM <eric-van.l...@klm.com>: > 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 > ******************************************************** > -- Efim
