When working with the TSM DB there are times you might want to change/replace a returned value for easier reporting. I am finding that with TSM now using DB2 more SQL functions are available to use when trying to gather information from the DB using select statements. There are a couple functions available to facilitate this which I will discuss below. The first one to cover is the CASE function (
available in TSM pre-DB2) which also has the benefit of allowing flow logic to your SELECT statement. I first came across this when I found a Q EVENT macro someone had created on a TSM server I inherited.
def script event-check desc="Events - Exceptions"
upd script event-check "/* ---------------------------------------------*/"
upd script event-check "/* Script Name: event-check */"
upd script event-check "/* ---------------------------------------------*/"
upd script event-check ' select -'
upd script event-check ' schedule_name, -'
upd script event-check ' cast(SUBSTR(CHAR(actual_start),12,8) as char(8)) AS START, - '
upd script event-check ' node_name, -'
upd script event-check ' cast(status as char(10)) as "STATUS", -'
upd script event-check ' case -'
upd script event-check " when result=0 then ' 0-Succ' -"
upd script event-check " when result=4 then ' 4-SkFi' -"
upd script event-check " when result=8 then ' 8-Warn' -"
upd script event-check " when result=12 then '12-Errs' -"
upd script event-check " else cast(result as char(7)) -"
upd script event-check ' end -'
upd script event-check ' as "RESULT" -'
upd script event-check ' from events -'
upd script event-check ' where scheduled_start<=(current_timestamp - 24 hours) -'
upd script event-check ' and result<>0 and node_name is not NULL'
As you can see in the macro we replace the basic result with some extra information to help identify what happened with the backup. The CASE function also comes in handy when changing the values for BYTES to the appropriate result of K, MB, GB, TB etc.
....
CASE -
WHEN bytes>1099511627776 THEN CAST(DEC(bytes)/1024/1024/1024/1024 AS DEC(5,1))||' TB' -
WHEN bytes>1073741824 THEN CAST(DEC(bytes)/1024/1024/1024 AS DEC(5,1))||' GB' -
WHEN bytes>1048576 THEN CAST(DEC(bytes)/1024/1024 AS DEC(5,1))||' MB' -
WHEN bytes>1024 THEN CAST(DEC(bytes)/1024 AS DEC(5,1))||' KB' -
ELSE -
CAST(bytes AS DEC(5,0))||' B' -
END AS bytes, -
There is also another option for changing results when the field has a NULL value. When created a script that might encounter NULL values rather than return nothing you can use the NVL and NVL2 operators. For example say you want to create a report that shows all nodes and specifies whether they have ever connected to TSM. With TSM this can be determined not by the LASTACC_TIME but by the PLATFORM_NAME value. If PLATFORM_NAME is NULL then the node has never connected to TSM to set the value. So we can create a script that looks at the PLATFORM_NAME value and returns a value of
Has Not Connected.
select varchar(node_name,40) as "Node Name", nvl(platform_name, 'Has Not Connected') AS "Platform Name" from nodes order by platform_name desc
Node Name Platform Name
----------------------------------------- ------------------
HUCDSON.ADCC.W.ZZ.OU812.NET.SQL Has Not Connected
DERELICT.ADCC.W.ZZ.OU812.NET.SQL Has Not Connected
NOSTROMO.ADCC.W.ZZ.OU812.NET.MAIL Has Not Connected
SULACO.ADCC.AIX.ZZ.OU812.NET.SQL Has Not Connected
WIN-SEC-JMP-VH.ADCC.W.ZZ.OU812.NET Has Not Connected
HICKS.ADCC.AIX.ZZ.OU812.NET.SQL Has Not Connected
VASQUEZ.DLX.W.CS.AS19229.NET WinNT
BISHOP.ULX.W.CS.AS19229.NET WinNT
GORMAN.LV426.W.CS.AS19229.NET WinNT
SPUNKMEYER.LV426.W.CS.AS19229.NET WinNT
...
The results work but what if you want to substitute results for PLATFORM_NAME when the value is not NULL? You can use the NVL2 operator for this and return a list of the nodes showing whether they are ACTIVE or INACTIVE.
select varchar(node_name,40) as NODE_NAME, nvl2(platform_name, 'Active Node', 'Inactive') as STATUS from nodes order by Platform_name desc
Node Name Platform Name
----------------------------------------- ----------------
HUCDSON.ADCC.W.ZZ.OU812.NET.SQL Inactive
DERELICT.ADCC.W.ZZ.OU812.NET.SQL Inactive
NOSTROMO.ADCC.W.ZZ.OU812.NET.MAIL Inactive
SULACO.ADCC.AIX.ZZ.OU812.NET.SQL Inactive
WIN-SEC-JMP-VH.ADCC.W.ZZ.OU812.NET Inactive
HICKS.ADCC.AIX.ZZ.OU812.NET.SQL Inactive
VASQUEZ.DLX.W.CS.AS19229.NET Active
BISHOP.ULX.W.CS.AS19229.NET Active
GORMAN.LV426.W.CS.AS19229.NET Active
SPUNKMEYER.LV426.W.CS.AS19229.NET Active
...
So ALL values that were not NULL were reported as
Active. These functions can be very helpful when you need a specific report type. If you have other examples or operators you find useful feel free to leave a note in the comment section and I can add them to this post with your reference.
