From: Mike Knight, IBM GS Central, (314)234-5096, KNIGHTM at ISSCVM
Inet:                                             KNIGHTM AT VNET.IBM DOT COM
Subject: Tapes that a node use

On Fri, 31 Jul 1998 16:04:12 -0500, Karen Krowzack asked,

>Have you any of you written an SQL statement in V3 ADSM that tells you every
>tape that a node has data written on?

Here are a couple of examples I have played with.

/*********************************************************************/
/* List count of tapes is each storage pool holding data for a node. */
/* Sort by stgpool_name within each node_name.                       */
/*********************************************************************/
select node_name, stgpool_name, count(*) as "Tapes used" -
from volumeusage -
group by node_name, stgpool_name

/*********************************************************************/
/* List tapes holding data for a specific file system.               */
/* Sort by tape within type of data within storage pool              */
/*********************************************************************/
select STGPOOL_NAME, COPY_TYPE, VOLUME_NAME -
from   VOLUMEUSAGE -
where  NODE_NAME = 'ABCDEFGH' and FILESPACE_NAME = '/usr' -
order  by STGPOOL_NAME, COPY_TYPE, VOLUME_NAME


Unfortunately I get message ANR2957E when I try them on a production
system, which says the database isn't large enough.  It is a 6GB
database that is only 40% used.  Unfortunately the maximum reduction
is 0 indicating no contiguous space.  This doesn't hurt normal
operation or some SQL commands, but doesn't allow use of group or
order keywords on the select.  As far as I can tell, the only way to
defragment is to use DUMPDB and LOADDB.  For now, I are giving up on
SQL and going back to using scripts to parse query command output.

     Mike (Just another user, not ADSM support) Knight