Cmd to find how many pages are occupied by a node

[Cyclone]

I have a TSM system that I inherited back in May. I have upgraded it to 5.5 and put many new nodes into the backup rotation to this server. Since then I have watched the db grow from only 47% full to now over 83%. I have not added more than one or two nodes since August, so I puzzeled why the DB is contantly growing, even though we only have a 30 day retention policy set.

Is there a way that I can query the system to see what nodes taking up the most db pages? If there are just a few servers consuming the majority of the growth, then I can address them individually.

Thanks for any input.

Don

 

[chad_small]

Depends on the data on the nodes you added? Do they have a higher rate of daily change? How much more is backed up nightly since the added nodes?

 

[Cyclone]

I've added about 30 nodes, but most have been PC workstations and the others have been servers. One that I particularly suspect is TSM-like cloud backup server. It creates about 3 million plus file everyday. Many are relatively small, but I'm sure they add up. I'd like to see how many pages that node consumes in the db.

 

[GregE]

If that system is creating 3 million files a day, there's your answer to your growth. TSM database size is based on number of backup objects, not their size.

A db page is 4096 bytes. Each initial object is about 600 bytes. Each additional copy of an object is about 200 bytes. Get the number of objects for that node, in all storage pools, and you may be able to get a ballpark figure on how much of the TSM db it's occupying.

This might be helpful in getting some numbers of files to work with.

SELECT -
o.node_name||'   ' as NODE_NAME, -
sum(cast(o.physical_mb/1024 as decimal (10,2))) as TOTAL_GB, -
sum(cast(o.num_files as decimal (10,0))) as TOTAL_FILES_STORED,-
dec(dec(sum(o.num_files))/(select dec(sum(num_files)) from occupancy)*100,6,4) as PCT_FILES_STORED,-
n.platform_name -
FROM occupancy o,nodes n -
WHERE o.node_name=n.node_name -
GROUP BY n.node_name, o.node_name, n.platform_name -
ORDER BY n.platform_name,TOTAL_GB desc

 

[Cyclone]

Well, that is quite a select statement. I'm running it now (its been running for about 30 mins, but it did warn me ) I will have to reply once I get some output.

 

[Harry_Redl]

Hi,

you can use this command as the first approximation ....

select node_name,sum(num_files) as Total_file_sum from occupancy group by node_name order by 2 desc

Not as exact as previous one suggested by Greg, but surely runs faster

Harry

 

[Cyclone]

For some reason my commands have failed due to a problem with the "occupancy" table.

tsm: TSM1>select node_name,sum(num_files) as Total_file_sum from occupancy group by node_name order by 2 desc
Session established with server TSM1: AIX-RS/6000
  Server Version 5, Release 5, Level 4.0
  Server date/time: 12/02/10   08:01:46  Last access: 12/02/10   07:55:46

ANR2963W This SQL query may produce a very large result table, or may require a significant amount of time to compute.

Do you wish to proceed? (Yes (Y)/No (N)) y
ANR2956E Unable to access SQL base table 'OCCUPANCY'.

                                    |
         ...........................V...................................
         es) as Total_file_sum from occupancy group by node_name order b

ANS8001I Return code 26.

 

[Cyclone]

Follow up. I had a DB bufferpool problem that I have correct. They system is now much speeder and this sql command is now working.

Thanks again.

Don

 

[Cyclone]

The commands did reveal which systems are the "heavy" burdens. I still don't know how much db pages are occupied by each node, but at least I have an idea on where to take aim.

Thanks again for your help everyone.