Spectrum Protect 8.1 New DB Functions Supported - UPDATE to the UPDATE!

UPDATE (11/22/2017):

I have found another interesting select option I was previously unaware of that coincides with the previous LOCATE option. We have an interesting host naming convention where I work and we use the hostname for the NODE NAME when defining the servers to TSM/SP. It is as follows:

<servername>.<datacenterID>.<OS_type>.<customer>.<domain.net>

Example:   CQSDBS01.US01.ULX.BS.US32118.NET

The issue I have is with trying to seperate out the data is a report by customer. Of course each customer has their own domains but I wanted a report that broke out the report by the customer ID. I wanted something like this:

Customer      Occupancy GB
----------    ------------
UDC2                 38144
903                  33358
CFG                  30321
ULT01                 1942
MAQ                    639
MSM                   1557
MX                       9
MX3                      0
ELO                   6741
GUS                      4
BFG                  11234
VS                     166
PMTX                    80
POS                  14148
SAM                   8031
SUX                  18562

The problem was not with breaking out the customer ID because the LOCATE select worked perfectly for extracting the Customer IDs with their various lengths. The issue was how to sum each customers data without experiencing an error requiring I use GROUP BY which caused more headaches because I could not figure out how to group by the NODE NAME from which I was getting the customer ID. So I googled GROUP BY in DB2 with SUBSTR since I was using a SUBSTR to extract the customer IDs. As turns out the GROUP BY is more flexible than I originally thought. Check this out:

Select -                           
 VARCHAR(SUBSTR(NODE_NAME, -        
 LOCATE('.',NODE_NAME, -           
 LOCATE('.', NODE_NAME, -          
 LOCATE('.', NODE_NAME)+1)+1)+1, - 
 LOCATE('.',NODE_NAME, -           
 LOCATE('.',NODE_NAME, -           
 LOCATE('.', NODE_NAME, -          
 LOCATE('.', NODE_NAME)+1)+1)+1)- -
 LOCATE('.',NODE_NAME, -           
 LOCATE('.', NODE_NAME, -          
 LOCATE('.', NODE_NAME)+1)+1)-1),8) -
 as CUSTOMER, -                    
 sum(TOTAL_MB)/1024 AS TOTAL_GB -   
from auditocc -                    
 where node_name like '%.%.%.%.%' - 
group by VARCHAR(SUBSTR(NODE_NAME, -
 LOCATE('.',NODE_NAME, -           
 LOCATE('.', NODE_NAME, -          
 LOCATE('.', NODE_NAME)+1)+1)+1, - 
 LOCATE('.',NODE_NAME, -           
 LOCATE('.',NODE_NAME, -           
 LOCATE('.', NODE_NAME, -          
 LOCATE('.', NODE_NAME)+1)+1)+1)- -
 LOCATE('.',NODE_NAME, -           
 LOCATE('.', NODE_NAME, -          
 LOCATE('.', NODE_NAME)+1)+1)-1),8) 

In DB2 I could actually group by the SUBSTR of the value I was extracting, even with it being complex with the LOCATE options in use. MIND BLOWN! 

UPDATE (5/9/2017):

I posted on StackOverflow (a great developer/scripting/DB) listserv asking how I could get similar output with the functions available in versions prior to DB2 11 and a user was able to help me out with a script that could return the same data as the one below although it's a little more complicated.  So as you know the SUBSTR function works like this:

Select SUBSTR(FILESPACE_NAME, <START POSITION>,<LENGTH>)

The issue with the earlier DB2 versions is I could only get the LOCATION function to play nice with my script. I was previously only able to figure out a select that got close:

Select -                                                                        
 SUBSTR(FILESPACE_NAME, LOCATE('\',FILESPACE_NAME, LOCATE('\',FILESPACE_NAME, - 
 LOCATE('\', FILESPACE_NAME, -                                                  
 LOCATE('\', FILESPACE_NAME)+1)+1)-1)+1) -                                      
 as FSNAME -                                                                    
from filespaces -                                                               
 where node_name='TEST-AP-DAG'

Which output this:

FSNAME                                                           
-----------------------------------------------------------------
TEST-MB-08\6bc391ef-a370-49a1-8f05-b1bed9e5ad55                  
Mailbox Database 1143943276\55ac1670-efc9-4301-ac5c-beb5cd2d77cb 
TEST-MB-05\11dedbd4-1757-45c8-8991-f0f7134ef210                  

I could not figure out a way to get the length. So a kind reader at StackOverflow helped me understand how to get the length by subtracting the 4th '\' location from the 3rd '\' -1. This gives the correct length and allows for the length size to vary rather than be fixed.

Select -
 SUBSTR(FILESPACE_NAME, -
 LOCATE('\',FILESPACE_NAME, -
 LOCATE('\', FILESPACE_NAME, -
 LOCATE('\', FILESPACE_NAME)+1)+1)+1, -
 LOCATE('\',FILESPACE_NAME, -
 LOCATE('\',FILESPACE_NAME, -
 LOCATE('\', FILESPACE_NAME, -
 LOCATE('\', FILESPACE_NAME)+1)+1)+1)- -
 LOCATE('\',FILESPACE_NAME, -
 LOCATE('\', FILESPACE_NAME, -
 LOCATE('\', FILESPACE_NAME)+1)+1)-1) -
 as FSNAME -
from filespaces -
 where node_name='TEST-AP-DAG'

Here it is color coded:
(Select SUBSTR(FILESPACE_NAME, <START POSITION>,<LENGTH>)

Select -
 SUBSTR(FILESPACE_NAME, LOCATE('\',FILESPACE_NAME, LOCATE('\', 
 FILESPACE_NAME, LOCATE('\', FILESPACE_NAME)+1)+1)+1, -
 LOCATE('\',FILESPACE_NAME, LOCATE('\',FILESPACE_NAME, LOCATE('\', 
 FILESPACE_NAME,  LOCATE('\', FILESPACE_NAME)+1)+1)+1)-  -
 LOCATE('\',FILESPACE_NAME, LOCATE('\', FILESPACE_NAME,  -
 LOCATE('\', FILESPACE_NAME)+1)+1)-1) -
 as FSNAME -
from FILESPACES -
 where - 
  NODE_NAME='TEST-AP-DAG'

So the final script looks like this

Select -
 VARCHAR(NODE_NAME,12) as NODE, -
 VARCHAR(SUBSTR(FILESPACE_NAME, -
  LOCATE('\',FILESPACE_NAME, -
  LOCATE('\', FILESPACE_NAME, -
  LOCATE('\', FILESPACE_NAME)+1)+1)+1, -
  LOCATE('\',FILESPACE_NAME, -
  LOCATE('\',FILESPACE_NAME, -
  LOCATE('\', FILESPACE_NAME, -
  LOCATE('\', FILESPACE_NAME)+1)+1)+1)- -
  LOCATE('\',FILESPACE_NAME, -
  LOCATE('\', FILESPACE_NAME, -
  LOCATE('\', FILESPACE_NAME)+1)+1)-1),30) -
  as Exch_DB,
 date(backup_end) AS LAST_GOOD_BACKUP -
from filespaces -
 where - 
  node_name='TEST-AP-DAG' -
 order by Exch_DB, backup_end asc

The results are exactly like the script that only works with TSM 8.1.

NODE              EXCH_DB                          LAST_GOOD_BACKUP

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

TEST-AP-DAG       \TEST-MB-01                            2017-05-08

TEST-AP-DAG       \TEST-MB-02                            2017-05-08

TEST-AP-DAG       \TEST-MB-03                            2017-05-08

TEST-AP-DAG       \TEST-MB-04                            2017-05-08

TEST-AP-DAG       \TEST-MB-05                            2017-05-08

TEST-AP-DAG       \TEST-MB-TEMP                          2017-05-08

TEST-AP-DAG       \MISC-MB-TEMP                          2017-05-08

TEST-AP-DAG       \Mailbox Database 1128394465           2017-05-08

TEST-AP-DAG       \Mailbox Database 1397248650           2017-05-08

(4/20/17):
I upgraded some of my 6.3.4 servers to TSM 8.1 recently and have found the newer DB2 version (v11.1) offers some added functions for SQL queries. I have been trying to build a report to track an Exchange DAG servers backups. The problem is that the easiest way to track the backups of DB's is to view the last completed backup for each of nodes file spaces. That's the easy part. The hard part is that the file space name is huge.

TEST-AP-DAG\Microsoft Exchange Writer\{76fe1ac4-15f7-4bcd-987e-8e1acb462fb7}\TEST-MB-05\0e41e645-9acf-4b80-bc85-d606e04fe4d8

TEST-AP-DAG\Microsoft Exchange Writer\{76fe1ac4-15f7-4bcd-987e-8e1acb462fb7}\Mailbox Database 1223544393\2bc06db2-1966-4fd7-9545-f667102b0b7d

So how to extract the DB name, in this case TEST-MB-05 and Mailbox Database 1397248650 from the name when the length of the DB name changes? After some investigation and trial and error I found that the LOCATE_IN_STRING function works in v8.1. Whether it works in 7.x (DB2 v10.5) I can't say since I don't have a 7.x server to run it against. If any of you out there try this and it works let me know. So here's the script. I think you can read it and see what I am doing. If not let me know in the comments and I'll explain...

tsm: TSMSERV>q script DAG-EXCH-RPT f=raw

select -
 VARCHAR(NODE_NAME,12) as NODE, -
 varchar(substring(filespace_name, LOCATE_IN_STRING(filespace_name, '\', 1, 3), -
 LOCATE_IN_STRING(filespace_name, '\', 1, 4) -  LOCATE_IN_STRING(filespace_name, '\', 1, 3)),30) as Exch_DB, -
 date(backup_end) AS LAST_GOOD_BACKUP -
from FILESPACES -
 where -
  node_name='TEST-AP-DAG' -
 order by Exch_DB, backup_end asc

Here is sample output:

NODE              EXCH_DB                          LAST_GOOD_BACKUP

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

TEST-AP-DAG       \TEST-MB-01                            2017-04-18

TEST-AP-DAG       \TEST-MB-02                            2017-04-18

TEST-AP-DAG       \TEST-MB-03                            2017-04-18

TEST-AP-DAG       \TEST-MB-04                            2017-04-18

TEST-AP-DAG       \TEST-MB-05                            2017-04-18

TEST-AP-DAG       \TEST-MB-TEMP                          2017-04-18

TEST-AP-DAG       \MISC-MB-TEMP                          2017-04-18

TEST-AP-DAG       \Mailbox Database 1128394465           2017-04-18

TEST-AP-DAG       \Mailbox Database 1397248650           2017-04-18

If another function would work better feel free to correct the script and leave it for everyone in the comments. Now to go and find more functions I can use to manipulate my data!

Value Replacement in Select Statements

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.

                  

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.

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 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.