Friday, November 26, 2010

Previously Unknown TSM SQL Feature

So while doing some script maintenance on a TSM server I came across this select statement and was shocked to see the CASE option in a select. I've used it in shell scripts but didn't think TSM allowed for it. Well it does and it works. Go figure!  (Runs on a TSM 5.3.5.2 server).


/* ---------------------------------------------*/
/* Script Name: eventx */
/* ---------------------------------------------*/
select -
schedule_name as "Schedule Name ", -
cast(substring(cast(scheduled_start as character(26)) from 12 for 8) as char(8)) as "Sched", -
node_name, -
cast(status as char(10)) as "Status", -
case -
when result=0 then ' 0-Succ' -
when result=4 then ' 4-SkFi' -
when result=8 then ' 8-Warn' -
when result=12 then '12-Errs' -
else cast(result as char(7)) -
end -
as "Result" -
from events -
where current_timestamp-scheduled_start<interval '24' hours -
and result<>0


I saw this and a whole new world opened up before my eyes! The possibilities!

1 comment:

  1. I was pretty surprised by this too, good spot Chad!

    However, a colleague just tried this on a 6.2.1 server - didn't get such a favourable response:

    ANR0162W Supplemental database diagnostic information: -1:42601:-104
    ([IBM][CLI Driver][DB2/SUN64] SQL0104N An unexpected token "for 8" was found
    following "ter ( 26 ) ) from 12". Expected tokens may include:
    "". SQLSTATE=42601
    ).
    ANR0516E SQL processing for statement select schedule_name as "Schedule Name "
    , cast ( substring ( cast ( scheduled_start as character ( 26 ) ) from 12 for 8
    ) as char ( 8 ) ) as "Sched" , node_name , cast ( status as char ( 10 ) ) as
    "Status" , case when result = 0 then ' 0-Succ' when result = 4 then ' 4-SkFi'
    when result = 8 then ' 8-Warn' when result = 12 then '12-Errs' else cast (
    result as char ( 7 ) ) end as "Result" from events where
    current_timestamp-scheduled_start < 24 hours and result <> 0 failed.
    ANS8001I Return code 3.

    ReplyDelete