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 Advanced Tutorial (4)


Home SQL




Sometimes you need data in a different format and/or data type than what you find in the database. You can convert formats in SQL using the "Cast" clause and data types using various descriptors like "Float". We shall start with a simple example. The following Query will sum up all bytes backed up to our TSM Server during the last 7 days.

select sum(bytes) as "Backed up data" from summary where -
activity='BACKUP' and start_time>timestamp(current_date)-(7)days

TSM will possibly print out a very long number, representing the sum of all bytes backed up during the last seven days. In most cases, we will want to see gigabytes or terabytes rather than bytes. We already know how to calculate within SQL, so lets convert the output to gigabytes:

select sum(bytes)/1024/1024/1024 as "Backed up data in TB" from summary - 
where activity='BACKUP' and start_time>timestamp(current_date)-(7)days

If you really backed up more than one gigabyte during the last seven days, you'll see the number of whole gigabyte backed up. Otherwise you'll get a "blank" result, indicating that less than one gigabyte has been backed up. This happens, because SQL handles the "bytes" entry and any associated math (like our conversion to gigabyte using /1024/1024/1024 - terabyte would be /1024/1024/1024/1024) as an integer without any fractal component. To get a little more precision into our gigabyte conversion, we will have to convert "bytes" into a floating point data type first:

select float(sum(bytes))/1024/1024/1024 as "Backed up data in GB" from -
summary where activity='BACKUP' and start_time>timestamp(current_date)-(7)days

  Backed up data in GB

Now we've got a rather precise result, however unless you're a math genius you'll probably find it a little hard to read. We'll therefore convert the result to a straight decimal format with a definable number of digits before and after the decimal point. In our example we'll choose 4 and 4 digits, which convert to the clause dec(8,4) which means 8 digits overall and 4 digits after the decimal point.

select cast(float(sum(bytes))/1024/1024/1024 as dec(8,4)) -
as "Backed up data in GB" from summary where -
activity='BACKUP' and start_time>timestamp(current_date)-(7)days

Don't forget to put parentheses around the expression following "cast".

Here's another example for changing types of Data. We want to find out which Activities happened with the greatest thruput in terms of Bytes/Second. We'll get that Information from the Summary Table. The sort of Activity can be found in the "ACTIVITY" field. The Client, Storagepool etc. is being stored in the "ENTITY" Field. The number of transferred Bytes comes from the "BYTES" Field. What we can't find directly is the Information about how long the Activity actually took to execute. However, the Fields "START_TIME" and "END_TIME" can be subtracted to deliver the desired Information. If you enter:

select (end_time - start_time) from summary

you'll get the runtimes of all activities in timestamp format (hours:minutes:seconds), which is cool if you're looking for that information. However if we want to calculate Bytes/Second, we need the number of seconds. Change the statement to

select (end_time - start_time)seconds from summary

and we're getting closer to what we need. However we still can not calculate, since the "seconds" format, while obviously being a number, would still generate a SQL error when used in simple arithmetic like "select bytes/(end_time - start_time)seconds from summary". An additional conversion is required. Our Query would have to look like this:

select activity, entity, bytes / (integer((end_time - start_time)seconds)) -
as "Bytes per Second" from summary order by 3 desc

But there might still be a problem. If TSM comes across an Activity which started and ended within the same second, our little calculation would generate a "division by zero" error. To prevent this sort of failure, we'll simply add a single Second to the Interval:

select activity, entity, bytes / (integer((end_time - start_time)seconds)+1) -
as "Bytes per Second" from summary order by 3 desc

This way, our division will always be legal.

 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