Database Query

[pytzen]

I need to figure out how much space each node is taking up in each of my defined storage pools. Someone did this for me once and i don't remember what the query he used was and i have gotten close, but not exactly.

This is what i would like as the output

NODE_NAME              STGPOOL_NAME                                  STORAGE_MB
------------------     ------------------     ---------------------------------
ACCTPAY                DRPOOL                                         312611.49
ACCTPAY                ONLINEFILE                                      21162.52
ACCTPAY                ONLINEPOOL                                     286228.26
ADC                    ARCHIVE_POOL                                  1091179.17
ADC                    DRPOOL                                         800744.92
ADC                    ONLINEFILE                                     122189.14
ADC                    ONLINEPOOL                                     528157.82
ADC-STORSERVER         DRPOOL                                          87216.91
ADC-STORSERVER         ONLINEFILE                                      17820.18
ADC-STORSERVER         ONLINEPOOL                                      58703.21

Thanks. Any help is appreciated

 

[Raakin]

Is this what you are looking for?

select node_name, stgpool_name, physical_mb as storage_mb from occupancy

 

[pytzen]

Close. But what happens is i get the following output

NODE_NAME              STGPOOL_NAME                 STORAGE_MB
------------------     ------------------     ----------------
ACCTPAY                DRPOOL                         41369.81
ACCTPAY                ONLINEFILE                       976.49
ACCTPAY                ONLINEPOOL                     42044.87
ACCTPAY                DRPOOL                           530.71
ACCTPAY                ONLINEFILE                         0.00
ACCTPAY                ONLINEPOOL                       530.71
ACCTPAY                DRPOOL                        184221.11
ACCTPAY                ONLINEFILE                      3586.02
ACCTPAY                ONLINEPOOL                    184185.58
ACCTPAY                DRPOOL                         11196.67
ACCTPAY                ONLINEFILE                      2965.83
ACCTPAY                ONLINEPOOL                     10572.40
ADC                    DRPOOL                            14.52
ADC                    ONLINEPOOL                        14.52
ADC                    ARCHIVE_POOL                       0.24
ADC                    DRPOOL                             0.26
ADC                    ONLINEPOOL                         0.26
ADC                    ARCHIVE_POOL                  556170.60
ADC                    DRPOOL                        378854.93
ADC                    ONLINEPOOL                    371940.70

As you can see there are multiple lines of the same storage pool for the same node. I believe this is because they are different filespaces, i would like those lines to be condensed to one for each storage pool.

 

[Raakin]

Sorry about that... try this one

SELECT node_name, stgpool_name, CAST(FLOAT(SUM(logical_mb)) / 1024 AS DEC(8,2)) as storage_mb FROM occupancy GROUP BY node_name, stgpool_name

 

[pytzen]

Yeah that looks good.

what are you doing to the Logical_mb. Are you converting Megabytes into Gigabytes?

 

[GregE]

what are you doing to the Logical_mb. Are you converting Megabytes into Gigabytes?

That's correct.