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 (1)


Home SQL



This Tutorial is intended to help the advanced TSM Administrator in developing complex SQL queries. We shall focus on real world examples, some of which may address problems you've already solved while others might be new.
Check out the Beginner's Tutorial for simple queries and SQL basics.

Before we start, we should take a brief look at how TSM handles user defined scripts. Since we don't want to enter our more complex queries over and over, we can store them inside of the TSM Database and run them whenever we want.
To define a Script in TSM, we use the command "define script" (check "h define script" in TSM for further details) or - and this is MUCH more convenient - we click through to "Object View -> Automation -> Server Command Scripts" on the Web interface. While on the Web, you can simply type away like you would with an editor, on the command line "define script" thing, you would have to enter line by line running "update script" each time.
Either way, you can define scripts of any complexity within TSM and reuse them as needed.

Once a script has been defined, you can execute it using the "run" command. Run allows you to enter parameters than can be used as variables in your script code. A variable is constructed using the dollar character "$", followed by the number of the parameter on the command line. We'll now construct a trivial example.
This script will print out all specified summary information from a certain activity and a certain entity. We start writing the select statement directly to see, how its working:

select * from summary where activity='BACKUP' and entity='BENJAMIN'

We can now store the query in a script and make it reusable in a way, that it will show us any desired activity (like archive, restore etc.) from any node we want. We do that by replacing BACKUP with our first parameter ($1) and BENJAMIN with our second parameter ($2). We also have to name the script (since it doesn't do much I'll call mine "sample") and give a description. On the TSM admin command line, we'd enter:

define script sample "select * from summary where -" desc="sample script, please delete"
update script sample "activity='$1' and entity='$2'"

We can look at our script by entering:

q script sample f=l

and always change any line by repeating the "update script" command with "line=linenumber" appended to it.
Now, in order to run the script we enter:


and TSM will behave just like before when we entered the entire script on the command line. The nice thing about scripting is that you can now simply enter the parameters you want with the run command rather than typing the whole query over and over. If you'd like to see all restores done by node "SAPD01" you can just do so by typing "runs script sample RESTORE SAPD01".

Note: The summary table in the TSM database is theoretically very useful, because it tells you almost everything about server and client activities in a single, convenient table. But unfortunately the summary table is not used by any internal TSM function, so bugs concerning this table are usually not being discovered and fixed by IBM as quickly as we would like to see. To cut a long story short: Don't trust all information you find in summary.

 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