would like a script that tells me if a tape volume is only used by one node

[andyfitz]

I'm trying to find out tapes that are only used by one node and what that node is .

I tried using the volumeusage table but can't get the exact syntax to give me the above

 

[kulbicka]

q nodedata node_name

 

[JohanW]

I don't get further than this:

SELECT VOLUME_NAME
FROM (

SELECT VOLUME_NAME, NODE_NAME
FROM VOLUMEUSAGE
GROUP BY VOLUME_NAME, NODE_NAME
ORDER BY VOLUME_NAME

) GROUP BY VOLUME_NAME
having count (node_name)=1

This gives you the tapes but not the nodes. And it won't work in dsmadmc - you need to do it in Access with the ODBC driver. Which means you might as well build a query, run a query over the query and run a query over the query. Which doesn't work as you can't join on a memo field.

How far did you get? Please post your script.

 

[JohanW]

q nodedata node_name

doesn't restrict output to volumes that are only used by one node. please read the question. you're not helping.

 

[andyfitz]

JohanW

Many thanks
I forgot about the odbc interface which allows me to do subqueries and union, minus etc

This is the query that i come up with which hopefully will give me what i want

SELECT VOLUME_NAME,node_name from volumeusage where volume_name in ( SELECT VOLUME_NAME
FROM VOLUMEUSAGE
GROUP BY VOLUME_NAME
having count (node_name)=1)

 

[JohanW]

Sorry, won't work. Invalid Memo Object; you can't link on volume_name. I tried that ..

 

[andyfitz]

Johanw


It works for me when using microsoft query or running the query through excel

 

[JohanW]

Oh wait, there *is* more than Access!

Unfortunately, it seems the output isn't correct.

 

[JohanW]

You're excluding from the results tapes with one node but multiple filespaces on 'em.

 

[JohanW]

OK, Access and Query both have their peculiarities. I think this would work:

Define an MS Access query

SELECT VOLUME_NAME
FROM (

SELECT VOLUME_NAME, NODE_NAME
FROM VOLUMEUSAGE
GROUP BY VOLUME_NAME, NODE_NAME
ORDER BY VOLUME_NAME

) GROUP BY VOLUME_NAME
having count (node_name)=1

as qVolumes.

Define an MS Query query

SELECT VOLUME_NAME,node_name
from volumeusage, qVolumes
where qVolumes.volume_name=volumeusage.volume_name

or something. I hope MS Query will do the join MS Access won't. Or do

SELECT VOLUME_NAME,node_name
from volumeusage
where volume_name in
    (select volume_name from qVolumes)

(You have Volumeusage as a linked table and qVolumes in the same Access database, and point MS Query to that one.)

 

[khawts]

Try this


Select a.volume_name, b.node_name from -
volumes a, volumeusage b where a.volume_name=b.volume_name -
node_name='<NODE_NAME>'

e.g.
Select a.volume_name, b.node_name from -
volumes a, volumeusage b where a.volume_name=b.volume_name -
node_name='NODE_A1'

 

[JohanW]

I think we're beyond that.

 

[mateinone]

Mate this should be very simple to achieve via an OS script, probably a lot easier than running through ODBC. What OS are you running on? Is it viable to run scripts on this server to get the information you are after?