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)

button-home

Home SQL

arrow

Previous

Next

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 info@general-storage.de

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

© 2016 by General Storage GbR. Alle Rechte vorbehalten

arrow

Previous

Next

Kontaktieren Sie uns:

Telefon: +49 6206 157686
Telefax:  +49 6206 157687
E-Mail: info@general-storage.de

arrow
arrow
GS_Logo1_Neu
gse-logo

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


IBM

SSW-2258:
An Alternative to NDMP for Network Attached Storage Backup 
with dsmISI MAGS


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


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

Hier nachlesen


General Storage Lösungen
Ready for IBM Tivoli Software

tivoli_ready