banner_bild_01 banner_bild_02 banner_bild_03 banner_bild_04 banner_bild_05
banner_bild_06 banner_bild_07 banner_bild_08

TSM SQL Beginners Tutorial (7)


Home SQL




Besides summing up values, SQL can also build averages and perform other simple math that can be useful. Lets add some information to our little query and display the average file space utilisation in addition to the summed capacity. We use the SQL "avg" (Average) clause.

select avg(pct_util) as "Average Util.", sum(capacity) -
as "Sum Cap." from filespaces where node_name='SAPD01'

This results in the following output:

                    Average Util.                              Sum Cap.
---------------------------------     ---------------------------------
                68.30000000000000                             1352007.3

We'll now create a table containing the information from our previous example for all nodes registered at your TSM Server. We can achieve that by simply leaving away the "where" scope from our query.

select avg(pct_util) as "Averge Util.", sum(capacity) as "Sum Cap." from filespaces

As expected, this will show a single line of output displaying average utilisation and the sum of the capacities from all nodes at the same time. If we want to see the information for all nodes separately, but summed up and averaged for all file spaces of each node, we have to introduce the "group by" clause. Using "group by", we can split up the results from a query into groups with identical values for a specific field. Our example would look like this.

select avg(pct_util) as "Average Util.", sum(capacity) -
as "Sum Cap." from filespaces group by node_nameA

The resulting data will be:

                    Average Util.                              Sum Cap.
---------------------------------     ---------------------------------
                48.60000000000000                               15336.8
                68.30000000000000                             1352007.3

Since we forgot to tell SQL that we'd like to see the node name in the output, we get a table containing the requested information grouped by node name, but not the node names themselves. Lets add the node name as a field we want to be displayed:

select node_name, avg(pct_util) as "Average Util.", sum(capacity) -
as "Sum Cap." from filespaces group by node_name

We now get the result we want:

NODE_NAME                                Average Util.                              Sum Cap.
-----------------    ---------------------------------     ---------------------------------
BENJAMIN                             48.60000000000000                               15336.8
SAPD01                               68.30000000000000                             1352007.3

 Alle erwähnten Produkte und Bezeichnungen können eingetragene Warenzeichen der jeweiligen Hersteller sein und werden als solche betrachtet.
Bei Fragen oder Problemen kontaktieren Sie bitte

Navigation  | Über GS  |  Lösungen  |  ServiceReferenzen  |  Impressum  |  Download

© 2016 by General Storage GbR. Alle Rechte vorbehalten




Kontaktieren Sie uns:

Telefon: +49 6206 157686
Telefax:  +49 6206 157687


13th ISP Symposium 2017
26.-29. September 2017,
Cologne Marriott Hotel


An Alternative to NDMP for Network Attached Storage Backup 
with dsmISI MAGS

IBM Spectrum Protect und Veeam Kundenform, 28. Juni 2017, Skyloft Frankfurt Airport

dsmISI for Veeam ermöglicht unbegrenzte Speicher für Backups

Hier nachlesen

General Storage Lösungen
Ready for IBM Tivoli Software