[HOWTO] Calcaulate the difference in occupancy between two pools

[chad_small]

I am trying to calculate the difference in occupancy and num_files between two storage pools using the occupancy table. What I get when I run my command is an incorrect calculation because I cannot seem to separate the sum by pool correctly within the select. There has to be a way to do this, here is my select for just the LOGICAL_MB and I know what it's doing I just need a way to separate the sum. Once I figure out how to separate the sums I'll expand it to show the difference in NUM_FILES also.

select cast((sum(BIGINT(a.LOGICAL_MB/1024))) - sum(BIGINT(b.LOGICAL_MB/1024)) as decimal(8,2)) as GB_DIFF from occupancy a, occupancy b where a.stgpool_name='XY-STD-COPY' and b.stgpool_name='XY-STD-TAPE'

Obviously my select is not seperating a.stgpool_name data from b.stgpool_name data because the result is incorrect.

 

[moon-buddy]

Chad,

I can't also seem to make things 'work' the way it should. I tried:

select (cast((sum(FLOAT(a.LOGICAL_MB)))  / 1024 / 1024 as decimal(8,2)) - cast((sum(FLOAT(b.LOGICAL_MB)))  / 1024 / 1024 as decimal(8,2))) as "TiB" from occupancy a,occupancy b where a.stgpool_name='STORAGEX' and b.stgpool_name='STORAGEY'

and it gives me a bogus result which is very bloated than the difference of STORAGEX and STORAGEY ran separately. The result is about ~15 times bloated!

Still looking for the right syntax.

 

[chad_small]

Yeah it's not calculating it correctly. You can't use a nested SQL in a column call so not sure how to do it.

 

[moon-buddy]

Yes, nested SQL calls are not supported except within DB2 itself. Not sure how this is done.

There must be a way to run plain SELECT arithmetic commands that process correctly from within TSM.

 

[reesema]

This seems to work:

http://thobias.org/tsm/sql/index.html#toc122

 

[moon-buddy]

This seems to work:

http://thobias.org/tsm/sql/index.html#toc122

That seems to work as far as difference is concerned. I don't know if this is what Chad wants.

 

[chad_small]

Ah so I can use nested selects to build a column. Awesome! I've modified it a little to add the number of files and turned it into a macro so you can load it as a script into TSM. Cheers!

def script occ_diff desc="Shows the difference in number of files and GB stored between two storage pools using the occupancy table data"
upd script occ_diff " SELECT varchar(stgpool_name,20) as STGPOOL_NAME, sum(num_files) as TOTAL_FILES, - "
upd script occ_diff " ( - "
upd script occ_diff " (select sum(num_files) FROM occupancy WHERE stgpool_name='$1' GROUP BY stgpool_name) - - "
upd script occ_diff " (select sum(num_files) FROM occupancy WHERE stgpool_name='$2' GROUP BY stgpool_name) - "
upd script occ_diff " ) as FILE_DIFF, - "
upd script occ_diff " CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) as total_data_gb, - "
upd script occ_diff " ( - "
upd script occ_diff " (SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name='$1' GROUP BY stgpool_name) - - "
upd script occ_diff " (SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name='$2' GROUP BY stgpool_name) - "
upd script occ_diff " ) as pending_copy_gb - "
upd script occ_diff " FROM occupancy -"
upd script occ_diff " WHERE stgpool_name='$1' or stgpool_name='$2' - "
upd script occ_diff " GROUP BY stgpool_name "

DOH! Just saw Thobias already had the script for this, I just didn't scroll down far enough! Oh well! LOL!

 

[chad_small]

It works. I think it helps because you can see the total of the two pools then see the difference. Otherwise you wouldn't know where the issue is when you get a negative value like I did when my copypool size is larger than my primary storage pool I'm comparing it against. The thing to take into account is that this works when the primary to copypool is a one to one, but it wont return valid data when multiple storage pools are backing up to a single copypool.

 

[rmazzon]

Chad,

Unless I modified it incorrectly, when I tried out the code you provided I get two lines where the difference columns are always 0. This is my sample output using your code

STGPOOL_NAME TOTAL_FILES FILE_DIFF TOTAL_DATA_GB PENDING_COPY_GB
--------------------- ------------ ------------ -------------- ----------------
EXCHCOPY 2852033 0 130506.18 0.00
EXCHTAPE 2852033 0 130506.18 0.00

I believe you are looking for output more along these lines:

PRIMARY GB PRIM FILES COPY GB COPY FILES DIFF GB DIFF FILES
----------- ------------- ----------- ------------- ------------ --------------
130862.18 2860470 130506.18 2852033 -356.00 -2857691

While not anywhere near as elegant as the code provided by you and reesema, this is what I did to get the second output sample. This is hard-coded to my example; no macro or script.

select cast(sum((case when stgpool_name in ('EXCHDISK','EXCHTAPE') \
then LOGICAL_MB end )/1024) as decimal(8,2)) as "PRIMARY GB",\
cast(sum(case when stgpool_name in ('EXCHDISK','EXCHTAPE') \
then NUM_FILES end ) as decimal(10,0)) as "PRIM FILES",\
cast(sum((case when stgpool_name='EXCHCOPY' \
then LOGICAL_MB end )/1024) as decimal(8,2)) as "COPY GB", \
cast(sum(case when stgpool_name='EXCHCOPY' \
then NUM_FILES end ) as decimal(10,0)) as "COPY FILES",\
cast(sum((case when stgpool_name='EXCHCOPY' \
then LOGICAL_MB end )/1024) as decimal(8,2)) - \
cast(sum((case when stgpool_name in ('EXCHDISK','EXCHTAPE') \
then LOGICAL_MB end )/1024) as decimal(8,2)) as "DIFF GB", \
cast(sum((case when stgpool_name='EXCHCOPY' \
then NUM_FILES end )/1024) as decimal(10,0)) - \
cast(sum(case when stgpool_name in ('EXCHDISK','EXCHTAPE') \
then NUM_FILES end ) as decimal(10,0)) as "DIFF FILES" \
from occupancy

 

[chad_small]

Wierd, no issue on my 7.1 server.