SQL: CASE and CONCAT

SO I was trying to build a better report for TSM Client levels replacing the crappy windows OS level with the correct version using CASE but was worried that case with two fields being concatenated would work. Well it does and quite well. The only issue was that if the platform_name is longer than the varchar setting then you will receive a warning error at the end of the select (the select runs successfully but will truncate any results for platform_name which is easily fixed).

select case -
  when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 5.00' then 'WinNT 2000' -
  when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 5.02' then 'WinNT 2003' -
  when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 6.00' then 'WinNT 2008' -
  when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 6.01' then 'WinNT 2008 R2' -
  when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 6.02' then 'WinNT 2012' -
  when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 6.03' then 'WinNT 2012 R2' -
  else varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) -
end -
  AS platform_name, -
cast(client_version as char(1)) || '.' || cast(client_release as char(1)) || '.' || cast(client_level as char(1)) || '.' || cast(client_sublevel as char(1)) as TSM_Version, count(distinct tcp_name) AS COUNT from nodes where LASTACC_TIME>(CURRENT_TIMESTAMP - 70 DAYS) and node_name like '%SU%' group by platform_name, client_os_level, client_version, client_release, client_level, client_sublevel

The results were exactly what I wanted.

PLATFORM_NAME          TSM_VERSION           COUNT
------------------     -----------     -----------
SUN SOLARIS 5.9        5.2.2.0                   4
WinNT 2000             5.3.0.0                   1
WinNT 2000             5.3.6.0                   1
WinNT 2003             5.3.0.0                   4
WinNT 2003             5.3.2.0                   6
WinNT 2003             5.3.4.0                   6
WinNT 2003             5.4.0.2                   3
WinNT 2003             5.4.1.4                   2
WinNT 2003             5.4.2.0                   2
WinNT 2003             5.4.3.0                   2
WinNT 2003             5.5.0.4                   8
WinNT 2003             5.5.1.0                   1
WinNT 2003             5.5.2.0                   1
WinNT 2003             5.5.3.0                   2
WinNT 2003             6.1.3.0                   1
WinNT 2008             5.5.0.4                   1
WinNT 2008 R2          6.1.4.0                   3
WinNT 2008 R2          6.2.4.0                   2
WinNT 2008 R2          6.3.0.0                   2
WinNT 2012             6.4.1.0                   1

TKLM - Things To Know Part 3

Identifying and Releasing Empty Volumes Back To Scratch

Due to the TKLM server being unable to issue keys TSM will assign tapes to a storage pool and then fail to write to the tape. To release the tapes back to scratch, after performing the resync you should check the TSM servers to see if any volumes are assigned to a storage pool but contain no data.  Use the following select statement to list the volumes with that 0 percent utilized. You will notice it creates a command within the results allowing you to quickly release the tapes with a simple cut and paste in the TSM admin command line.

select varchar(a.server_name,10) ||':'|| 'del vol', varchar(b.volume_name,8) as volname, b.pct_utilized, varchar(b.stgpool_name,15) as stgpool_name from status a, volumes b where b.pct_utilized=0 and b.devclass_name<>'DISK' order by b.stgpool_name, b.pct_utilized

You should see the following if TSM shows tape(s) with 0% utilized:

Unnamed[1]              VOLNAME        PCT_UTILIZED     STGPOOL_NAME

-------------------     ---------     -------------     ----------------

TSM01:del vol           J02579                  0.0     COPYTAPE

TSM01:del vol           J00243                  0.0     DBTAPE

TSM01:del vol           K00700                  0.0     DBTAPE_B_NC

TSM01:del vol           J00039                  0.0     LOGTAPE

TSM01:del vol           H70341                  0.0     LOGTAPE

TSM01:del vol           J00186                  0.0     LOGTAPE

TSM01:del vol           J00115                  0.0     LOGTAPE

TSM01:del vol           J00528                  0.0     LOGTAPE

TSM01:del vol           J01224                  0.0     LOGTAPE

TSM01:del vol           J01255                  0.0     LOGTAPE

You can use a portion of the results to execute against the server to release the tapes. If you’d rather not see the PCT_UTILIZED or STGPOOL_NAME then remove them from the script:

select varchar(a.server_name,10) ||':'|| 'del vol', varchar(b.volume_name,8) as volname from status a, volumes b where b.pct_utilized=0 and b.devclass_name<>'DISK' order by b.stgpool_name, b.pct_utilized

Unnamed[1]              VOLNAME

-------------------     ---------

TSM01:del vol           J02579

TSM01:del vol           J00243

TSM01:del vol           K00700

TSM01:del vol           H70341

TSM01:del vol           J00039

TSM01:del vol           J00115

TSM01:del vol           J00186

TSM01:del vol           J00528

TSM01:del vol           J01173

TSM01:del vol           J01224

TSM01:del vol           J01255

Run this select against all the TSM servers that have libraries that use the TKLM server and run the results through the TSM admin command line to release the tapes back to scratch. You will notice we are NOT using the DISCARD=YES flag for a reason. Without the discard flag TSM will not delete a volume that has some data but the amount is so low it still reports as 0% utilized.

Note: When deleting volumes DO NOT USE THE DISCARD FLAG! This will keep you from deleting a valid storage pool volume.

Archive Report

Where I work we have a process that bi-monthly generates a mksysb then archives it to TSM. Recently an attempt to use an archived mksysb found that sometimes the mksysb process does not create a valid file, but it is still archived to TSM. So the other AIX admins asked me to generate a report that would show the amount of data that was archived and on what date it occurred. Now I would have told them it was impossible if they had asked for data from the backup table, but our archive table is not as large as the backups so I gave it a go.

First problem was determining the best table(s) to use. I could use the summary table, but it doesn't tell me what schedule ran and some of these UNIX servers do have archive schedules other than the mksysb process. The idea I came up with was to query the contents table and join it with the archive table using the object_id field.  Here's an example of the command:

select a.node_name, a.filespace_name, a.object_id, cast((b.file_size/1048576)as integer(9,2))AS SIZE_MB , cast((a.ARCHIVE_DATE)as date) as ARCHIVE from archives a, contents b where a.node_name=b.node_name and a.filespace_name='/mksysb_apitsm' and a.filespace_name=b.filespace_name and a.object_id=b.object_id and a.node_name like 'USA%'

This select takes at least 20 hours to run across 6 TSM servers. I guess that I should be happy it returns at all, but TSM is DB2! It should be a lot faster, so I am wondering if I could clean up the script or add something that would make the index the data faster??? I am considering dropping the "like" and just matching node_name between the two tables. Would putting node_name matching first then matching object_id be faster? Would I be better off running it straight out of DB2? Suggestions appreciated.

Select X Days Back

As you all know I have been writing and rewriting scripts for my 6.x servers and have been experiencing interesting results when I use the timestampdiff option when determining how far back a script should look. So when running my summary report query I was getting inaccurate results when using the following script:

example 1:
select schedule_name, entity, start_time, end_time, timestampdiff(16, char(end_time-start_time)) || ' ' || timestampdiff(8, char(end_time-start_time)) || ':' || timestampdiff(8, char(end_time-start_time)) || ':' || substr(cast((end_time-start_time) as char(22)),13,2) AS ELAPSED_TIME, affected from summary where activity='STGPOOL BACKUP' and timestampdiff(8, char(current_timestamp-start_time))<=24


This script would not return all the STGPOOL BACKUP jobs from the summary table. Not sure why, but I have a couple ideas. So, I found that the following is easier to use and returned the correct results.


example 2:
select schedule_name, entity, start_time, end_time, timestampdiff(16, char(end_time-start_time)) || ' ' || timestampdiff(8, char(end_time-start_time)) || ':' || timestampdiff(8, char(end_time-start_time)) || ':' || substr(cast((end_time-start_time) as char(22)),13,2) AS ELAPSED_TIME, affected from summary where activity='STGPOOL BACKUP' and start_time>(CURRENT TIMESTAMP - 10 DAYS)


So whenever you would like to query X days back I suggest example 2 for ease of use.

Select X Days Back

As you all know I have been writing and rewriting scripts for my 6.x servers and have been experiencing interesting results when I use the timestampdiff option when determining how far back a script should look. So when running my summary report query I was getting inaccurate results when using the following script:

example 1:
select schedule_name, entity, start_time, end_time, timestampdiff(16, char(end_time-start_time)) || ' ' || timestampdiff(8, char(end_time-start_time)) || ':' || timestampdiff(8, char(end_time-start_time)) || ':' || substr(cast((end_time-start_time) as char(22)),13,2) AS ELAPSED_TIME, affected from summary where activity='STGPOOL BACKUP' and timestampdiff(8, char(current_timestamp-start_time))<=24


This script would not return all the STGPOOL BACKUP jobs from the summary table. Not sure why, but I have a couple ideas. So, I found that the following is easier to use and returned the correct results.


example 2:
select schedule_name, entity, start_time, end_time, timestampdiff(16, char(end_time-start_time)) || ' ' || timestampdiff(8, char(end_time-start_time)) || ':' || timestampdiff(8, char(end_time-start_time)) || ':' || substr(cast((end_time-start_time) as char(22)),13,2) AS ELAPSED_TIME, affected from summary where activity='STGPOOL BACKUP' and start_time>(CURRENT TIMESTAMP - 10 DAYS)


So whenever you would like to query X days back I suggest example 2 for ease of use.

TSM 6.x Time Calculation

I use the summary table frequently and like to calculate the elapsed time by subtracting the END_TIME from the START_TIME. In TSM 5.x you could easily do this because the TSM DB was setup for returning a day/time when calculating those two fields, but TSM 6.x's DB2 database does not work the same way. When calculating the difference in 6.x the result looks like this:


      ENTITY: STG_V0653 -> STG_T10K_C2
  START_TIME: 2010-12-28 08:00:11.000000
    END_TIME: 2010-12-28 22:39:29.000000
ELAPSED_TIME: 143918.000000
    AFFECTED: 258
GB BACKED UP: 8814.00

You'll notice that the value given is actually in HHMMSS without the colon seperators. So my select formats the output to a more time based solution. You might ask why I didn't use timestampdiff, but the problem with the timstampdiff format is that it does not return in a time format like previous (5.x) versions. The timestampdiff would return the format calculation of either days, hours, minutes, or seconds only. So I actually worked with a friend who has a little more experience with DB2 and we came up with the following.


select schedule_name, entity, start_time, end_time, substr(cast((end_time-start_time) as char(22)),7,2) || ' ' || substr(cast((end_time-start_time) as char(22)),9,2) || ':' || substr(cast((end_time-start_time) as char(22)),11,2) || ':' || substr(cast((end_time-start_time) as char(22)),13,2) AS ELAPSED_TIME, affected from summary where activity='STGPOOL BACKUP' and timestampdiff(8, char(current_timestamp-start_time))<=24

Results:

SCHEDULE_NAME: AS_BACK_V655

ENTITY: STG_655 -> STG_10_C2

START_TIME: 2010-12-28 08:00:26.000000

END_TIME: 2010-12-29 07:40:46.000000

ELAPSED_TIME: 00 23:40:20

AFFECTED: 632

So now it shows the elapsed time as Days, Hours:Minutes:Seconds

You could eliminate the Day by removing the first calculation, but I have some storage pool backup processes that can run VERY long so I included it.

TSM 6.x Time Calculation

I use the summary table frequently and like to calculate the elapsed time by subtracting the END_TIME from the START_TIME. In TSM 5.x you could easily do this because the TSM DB was setup for returning a day/time when calculating those two fields, but TSM 6.x's DB2 database does not work the same way. When calculating the difference in 6.x the result looks like this:


      ENTITY: STG_V0653 -> STG_T10K_C2
  START_TIME: 2010-12-28 08:00:11.000000
    END_TIME: 2010-12-28 22:39:29.000000
ELAPSED_TIME: 143918.000000
    AFFECTED: 258
GB BACKED UP: 8814.00

You'll notice that the value given is actually in HHMMSS without the colon seperators. So my select formats the output to a more time based solution. You might ask why I didn't use timestampdiff, but the problem with the timstampdiff format is that it does not return in a time format like previous (5.x) versions. The timestampdiff would return the format calculation of either days, hours, minutes, or seconds only. So I actually worked with a friend who has a little more experience with DB2 and we came up with the following.


select schedule_name, entity, start_time, end_time, substr(cast((end_time-start_time) as char(22)),7,2) || ' ' || substr(cast((end_time-start_time) as char(22)),9,2) || ':' || substr(cast((end_time-start_time) as char(22)),11,2) || ':' || substr(cast((end_time-start_time) as char(22)),13,2) AS ELAPSED_TIME, affected from summary where activity='STGPOOL BACKUP' and timestampdiff(8, char(current_timestamp-start_time))<=24

Results:

SCHEDULE_NAME: AS_BACK_V655

ENTITY: STG_655 -> STG_10_C2

START_TIME: 2010-12-28 08:00:26.000000

END_TIME: 2010-12-29 07:40:46.000000

ELAPSED_TIME: 00 23:40:20

AFFECTED: 632

So now it shows the elapsed time as Days, Hours:Minutes:Seconds

You could eliminate the Day by removing the first calculation, but I have some storage pool backup processes that can run VERY long so I included it.

UPDATE: Weird Query Results From 6.1.4

So the problem has been identified and listed in an APAR. In the document it states that SELECT commands on multiple tables not working as expected on a 6.x server.  The table list is quite large and it looks like the problem is resolved in 6.1.5 and 6.2.2.  Which means I have no fix unless I upgrade the servers.

UPDATE: Weird Query Results From 6.1.4

So the problem has been identified and listed in an APAR. In the document it states that SELECT commands on multiple tables not working as expected on a 6.x server.  The table list is quite large and it looks like the problem is resolved in 6.1.5 and 6.2.2.  Which means I have no fix unless I upgrade the servers.

Update of SQL For TSM 6.1

So the previous select is not compatible with TSM 6.1 so I was searching through the IBM Redbooks for TSM and found changes to make TSM conform to SQL standards.  There are changes to the TSM supported time functions.  Here is the link, it's under Appendix B.


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 timestampdiff(8, char(current_timestamp-scheduled_start))<=24 -'
upd script event-check '    and result<>0 and node_name is not NULL'

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!

Useful SQL statements

Here is a grand list of TSM SQL statements that you can use in your daily report. Excellent work by Thobias, thank you very much.

Nice List of TSM SQL Querries

I came across this web page when doing a search on TSM SQL queries and thought I would post it for all to reference. Thanks goes to Thobias Salazar Trevisan for the list and as he requests please send him any SQL queries you think would be beneficial to the masses they can be added. Thobias also has created a TSM monitoring tool that can be found here. For those of you not as versed in TSM, this tool might help you with your daily tasks.