• Please help support our sponsors by considering their products and services.
    Our sponsors enable us to serve you with this high-speed Internet connection and fast webservers you are currently using at ADSM.ORG.
    They support this free flow of information and knowledge exchange service at no cost to you.

    Please welcome our latest sponsor Tectrade . We can show our appreciation by learning more about Tectrade Solutions
  • Community Tip: Please Give Thanks to Those Sharing Their Knowledge.

    If you receive helpful answer on this forum, please show thanks to the poster by clicking "LIKE" link for the answer that you found helpful.

  • Community Tip: Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING)

    Click the link above to access ADSM.ORG Acceptable Use Policy and forum rules which should be observed when using this website. Violators may be banned from this website. This notice will disappear after you have made at least 3 posts.

How to find occupancy of active files only in specified pool?

mirrorsaw

ADSM.ORG Member
#1
Hi all,
Q OCC will give me occupancy by pool for each node, but if I'm trying to find out the amount (either number of files, or total GB) of active files per node, for a given storage pool, is there a SELECT statement I can use for this?

Thanks
 

marclant

ADSM.ORG Moderator
#2
It would be a complex query because you'd need data from multiple tables.
You would need to get the list of nodes in that stgpool, that's easy:
SQL:
select distinct node_name from occupancy where stgpool_name='POOL_NAME'
You would need the object_id from all the files that belongs to the nodes listed above:
SQL:
select object_id from backups where state='ACTIVE_VERSION' and node_name in (select distinct node_name from occupancy where stgpool_name='POOL_NAME')
Then you need to sum the filesize from the contents table (assuming you are not using container pools).
SQL:
select node_name,sum(file_size) as BYTES, count(file_size) as NUMBER_OF_FILES from contents where object_id in (select object_id from backups where state='ACTIVE_VERSION' and node_name in (select distinct node_name from occupancy where stgpool_name='POOL_NAME')) group by node_name
DISCLAIMER:
I have not tested this on a large server, I tested it on a small test box, the logic works, but I don't know how it will behave on a large server. Use at your own risk.
 

mirrorsaw

ADSM.ORG Member
#4
wait no I think I spoke to soon. I'm pretty sure that query gives me the total number of active files (and total) size from any nodes that have data in that stgpool.

But I need the number of active files per node *IN* that stgpool. for example if a node has half its active files in one pool, and half in another, your query will give me the total across both pools.
 

marclant

ADSM.ORG Moderator
#5
You are absolutely right.

You'd need a combination of the VOLUMES table to know which volumes belong to the storage pool
The BACKUPS table to have only active objects.
The CONTENTS table to get info only about the volumes that belong to that pool, and only the object_ids from BACKUPS that are active. And of course, also match the node.

It's getting to be above my pay grade.
 

Advertise at ADSM.ORG

If you are reading this, so are your potential customer. Advertise at ADSM.ORG right now.

UpCloud high performance VPS at $5/month

Get started with $25 in credits on Cloud Servers. You must use link below to receive the credit. Use the promo to get upto 5 month of FREE Linux VPS.

The Spectrum Protect TLA (Three-Letter Acronym): ISP or something else?

  • Every product needs a TLA, Let's call it ISP (IBM Spectrum Protect).

    Votes: 12 15.8%
  • Keep using TSM for Spectrum Protect.

    Votes: 48 63.2%
  • Let's be formal and just say Spectrum Protect

    Votes: 9 11.8%
  • Other (please comement)

    Votes: 7 9.2%

Forum statistics

Threads
31,350
Messages
133,531
Members
21,475
Latest member
sag
Top