Or how about this one, it's what we use. It is a large query and takes 
a while to complete depending on the size of your TSM server.

select node_name,stgpool_name,count(distinct(volume_name)) as \"Number of 
Tapes\" from volumeusage where stgpool_name like '%TAPEPOOL%' group by 
node_name,stgpool_name order by node_name, \"Number of Tapes\" desc"

        It looks at ~all~ the nodes on the TSM server and ~all~ the primary 
tapepools (which all have "TAPEPOOL" in their name). You should be able to 
adjust it to your environment.

Ben

-----Original Message-----
From: Andrew Raibeck [mailto:storman AT US.IBM DOT COM]
Sent: Friday, May 02, 2003 8:18 AM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: AW: sql select to count volumes per stgpool by node


What do you get if you do this:

select stgpool_name, volume_name
   from volumeusage
   where node_name='AOHEXCHANGE00'
   order by stgpool_name, volume_name

Are duplicate volumes showing up?

How about:

select volume_name
   from volumes
   where stgpool_name='TPBACSAFE'

Just to confirm what is in that pool?

Regards,

Andy

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
Internet e-mail: storman AT us.eyebm DOT com (change eye to i to reply)

The only dumb question is the one that goes unasked.
The command line is your friend.
"Good enough" is the enemy of excellence.




Salak Juraj <j.salak AT ASAMER DOT AT>
Sent by: "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
05/02/2003 07:03
Please respond to "ADSM: Dist Stor Manager"

 
        To:     ADSM-L AT VM.MARIST DOT EDU
        cc: 
        Subject:        AW: sql select to count volumes per stgpool by node



Andy,

I do not see an error in your SQL statement, 
but the results are much too good to be correct,
new volumes must be created on the fly during the SQL run:
e.G.
  AOHEXCHANGE00          TPBACSAFE                       81
While I have only 29 tapes in TPBACSAFE ;-)

regards
Juraj





-----Ursprüngliche Nachricht-----
Von: Andrew Raibeck [mailto:storman AT US.IBM DOT COM]
Gesendet: Freitag, 02. Mai 2003 15:41
An: ADSM-L AT VM.MARIST DOT EDU
Betreff: Re: sql select to count volumes per stgpool by node


Maybe this?

select node_name as "Node_Name",
       stgpool_name as "StgPool_name",
       count(*) as "Count"
   from volumeusage
   group by stgpool_name, node_name
   order by node_name, stgpool_name

Regards,

Andy

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
Internet e-mail: storman AT us.eyebm DOT com (change eye to i to reply)

The only dumb question is the one that goes unasked.
The command line is your friend.
"Good enough" is the enemy of excellence.




Joel Fuhrman <joelf AT CAC.WASHINGTON DOT EDU>
Sent by: "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
05/01/2003 17:49
Please respond to "ADSM: Dist Stor Manager"


        To:     ADSM-L AT VM.MARIST DOT EDU
        cc:
        Subject:        sql select to count volumes per stgpool by node



What is the syntax of the select statement to show the number of distinct
volumes used by each stgpool for each node.  The desired output would be
something like:

Node_Name   StgPool_Name  Count
---------   ------------  ------
yakima      backupcopy         5
yakima      backuptape         3
yakima      arccopy            7
yakima      arctape            6
olympia     backupcopy        23
olympia     backuptape        15
...