Need help- Select statement to see type of tapes in library

[ramohi]

Hi,

I need some help.

Can you please show me the select statment to see different types of tapes in the library, like L4..L5..and so on

Thanks a lot & regards
Ram

 

[rgg]

I'm not sure that the format (besides 'LTO') is recorded, so you'll probably need to rely on the volume names...

select volume_name from volumes where volume_name like '%L3'

EDIT: Looks like there is a MEDIATYPE column in the libvolumes table:

select volume_name from libvolumes where MEDIATYPE='LTO-3'

 

[ramohi]

Thanks rgg...
It gives me the volumename... I am looking for count.

I did give this command:
select count(*) from libvolumes where volume_name like '%L3'
this help's but gives me all the tapes offsite and in the library....

I am looking for tapes only in the library.

your help is appreciated.

Regards

 

[marclant]

Is it possible those copy pool tapes are still checked in? Compare with the output of QUERY LIBVOLUMES. Both query libvolumes and select from libvolumes should only give you volumes that are checked in the library.

 

[ramohi]

Thanks Marclant and rgg:

But I am looking for "count"...the actual tapes in library.

 

[marclant]

Thanks Marclant and rgg:

But I am looking for "count"...the actual tapes in library.

You had it right:

select count(*) from libvolumes where volume_name like '%L3'

Just need to replace L3 with all the different types you have, one query per type.

 

[jmaas]

Try

select library_name,mediatype,count(*) as number from libvolumes group by mediatype, library_name

 

[ramohi]

Hi Marclant,
Thanks for a quick reply.

tsm: TSMNY01>select count(*) from libvolumes where volume_name like '%L3'
Unnamed[1]
------------
6450

Now the library has only 3020 slots.... and the returned value is more....

what I am looking for is the how many L3 tapes are physically present in the library....

Sorry, if I was not clear

and thanks again for the response.

Regards

 

[ramohi]

Hi,

In the meanwhile "jmaas" posted a select statement and it worked.....

Thanks jmaas
and rgg and marclant too

regards

 

[ramohi]

Also one more update for future reference, if anybody see's this post.

our library has 3000+ slost and the select statement
select count(*) from libvolumes where volume_name like '%L3'
was giving more then 6000+ tapes
But we have VTL configured with meditype "L3" and that was counted in the above select statement...
I missed to take vtl in consideration.

Thanks everyone.

Regards