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.

DB2 Doesn't Make A Difference

I've been working with some IBM reps/consultants lately, and I find it kind of funny how they talk about TSM. We were discussing the issue with some queries to the TSM DB being so hard to process that many times they don't return any data, when the IBM rep said "With DB2 that wont happen." I laughed and said, "DB2 didn't help that much." For example try something like this and see how long it takes to get a response.


select  cast(sum(b.file_size/1073741824) as decimal(18,2)) AS GB_SIZE from backups a, contents b where a.node_name in ('DEV01_ORA','DEV02_ORA','DEV03_ORA','PRD01_ORA','PROD02_ORA') and a.backup_date < '2011-11-01 00:00:00' and a.object_id=b.object_id

I'm running this query to determine the amount of space I would free up if I deleted old oracle backup objects that they DBA's never reconciled through RMAN. I ran it over 30 minutes ago.....still waiting! The problem is the schema has not changed enough in the TSM table structure to make some select statements run any better than in pre-DB2 days. Anyone else seen this? 


(Yes! I know if I used a specific NODE_NAME then TSM would probably return some data, but handles queries 1000x times more complex than these in the non-TSM world)

Tivoli Storage Manager Reporting and Monitoring v6.3

This is a query from the TSM v6.3 agent:

select node_name, count(distinct volume_name) from volumeusage a, stgpools b where (a.stgpool_name=b.stgpool_name) and devclass in (select DEVCLASS_NAME from devclasses where devtype in ('3570','3590','3592','4MM','8MM','DLT','DTF','ECARTRIDGE','GENERICTAPE','LTO','QIC')) group by node_name

Could you run it on a TSM v5.x.x.x productive system for me!

Do you get any result in 10 minutes?

What Problem? It's Working As Designed!

When upgrading from TSM 5.x to 6.x you have probably found that your select queries don't return in table format but in list format. You ask yourself, "WTH is going on here?" First off don't complain to IBM, it's "working as designed." You see with the change of the TSM DB to DB2 many of table column widths have changed. To have your select statements return in table format IBM states that you should use the CAST function on certain (if not all) columns. Otherwise, the best alternative is to save the results in commadelimited format and open the data in Excel. Here's the APAR??? that states it's not a problem. To quote Lady Gaga, TSM 6.x was "Born This Way!"

NOTE: Is it me or does IBM's last example leave one scratching their head wondering how using cast helped???

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.

Weird Query Results From 6.1.4

So we have a couple servers we upgraded from 5.3 to 6.1.4.2 and since the upgrade the following select statement does not work. I posted it on ADSM.org and others report their 6.x servers run it without issue.  Here is the statement and my results:

select server_name, node_name, PLATFORM_NAME || ' ' || cast(CLIENT_OS_LEVEL as char(20)) as OS_Version, cast(client_version as char) || '.' || cast(client_release as char) || '.' || cast(client_level as char) || '.' || cast(client_sublevel as char(2)) as TSM_Version from nodes, status


here is the return code I get:


ANR2034E SELECT: No match found using this criteria.
ANS8001I Return code 11.

I run this through a while loop and append the data to an comma-delimited output file so I can pull it into a spreadsheet and organize clients by version. I can then more easily prioritize my client upgrades.  I really need this to work so I can sort and identify the clients location by server_name. Any ideas?

Weird Query Results From 6.1.4

So we have a couple servers we upgraded from 5.3 to 6.1.4.2 and since the upgrade the following select statement does not work. I posted it on ADSM.org and others report their 6.x servers run it without issue.  Here is the statement and my results:

select server_name, node_name, PLATFORM_NAME || ' ' || cast(CLIENT_OS_LEVEL as char(20)) as OS_Version, cast(client_version as char) || '.' || cast(client_release as char) || '.' || cast(client_level as char) || '.' || cast(client_sublevel as char(2)) as TSM_Version from nodes, status


here is the return code I get:


ANR2034E SELECT: No match found using this criteria.
ANS8001I Return code 11.

I run this through a while loop and append the data to an comma-delimited output file so I can pull it into a spreadsheet and organize clients by version. I can then more easily prioritize my client upgrades.  I really need this to work so I can sort and identify the clients location by server_name. Any ideas?

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!

Calculating Active Data

I was recently asked to calculate the amount of active data in TSM storage for file system backups, not TDP's and had some interesting results. If you search TSM active data in Google the first result will be this IBM support doc that explains how to calculate active data to help size an Active Data Storage Pool. IBM recommends that you use the EXPORT NODE command with PREVIEW=YES to determine the amount of active data. This, in theory, should work well but for TSM to process this request it has to analyze the backups table and who knows what else to gather the data. I have 10 instances I needed to gather the information from, they all vary in TSM DB size, and the amount of managed data stored. My smallest DB is a new instance that is 25GB and my largest is 155GB and size did not matter when it came to how fast the information was calculated. The TSM instance with the largest DB completed the taks in over two days (YES TWO DAYS!). Two TSM instances were still running the EXPORT NODE query after THREE DAYS and they had moderate to large sized DB's.

So what caused this problem? It all comes down to the number of files TSM has to inspect. The two instances that never completed the query have large numbers of Windows nodes and have the most registered nodes overall. These two instances seemed to be crawling through the process and where they should have calculated into the ten to twenty TB, as the next largest instance did after just over two days, the problem two were still in the 7 to 6 TB range and increasing slowly. My only explanation (and this is a guess) is that due to the fact that Windows servers tend to have hundred of thousands if not millions of files which TSM gets bogged down trying to inspect them all. I didn't notice a performance impact but IBM claims that it is a resource intensive task and should be run during non-peak hours.  How can you do that when it runs 24hrs or more?

Finally after three days, and no end in sight, I canceled the processes and now have to figure out some other way to calculate the amount of active data they have stored. I could calculate (i.e. guesstimate) by summing the amount of space used per file space.

Example:

select cast(sum(capacity*(pct_util/100)) AS decimal (18,2)) As Total_Used_Space from filespaces where node_name in (select node_name from nodes where domain_name like '%STD%') 

The problem is that this will not be accurate and will probably cause me to oversize any Active Data Pool I create. Now that's not a horrible thing (more space is always better than to little) but the whole process seems to time consuming for something that on the surface should be fairly easy to calculate.  This where I hope the new DB2 database can help, but until someone has it up and running and can try this process we can't say if there is a reasonable solution to find the total active data in TSM within larger instances.

Addendum

Are wondering why I used the '%STD%' filter in the select statement above? The team here at Infocrossing separates the file system backups and application (TDP) backups with different domain names using a standardized naming process. This is great because it also allowed us to run the EXPORT NODE command using wild cards for the specific domains to include in the export query.

EXPORT NODE * DOMAINS=*STD* FILEDATA=ALLACTIVE PREVIEW=YES

I highly recommend you follow a similar process when creating domains and even schedules to make it easier to process groups of nodes. So you could create a WIN-STD-DOM domain or like we do for our TDP's WIN-APP-DOM. These are examples but they can make life easier. 

Mislabeled Tapes Headache

I am periodically running into a situation where TSM will check in our tapes as private or relabel them to private, causing me headaches until I get the tapes status updated. So how do you identify the tapes that are mislabeled? Well if you have setup a library controller instance (which I recommend even if you will only have one instance for clients) then this script should work for you. What you will find when tapes are mislabeled is that they will show the library controller as their owner, will have a NULL value for LAST USE, and will of course be in private status. So a simple select statement can identify them for use.

select VOLUME_NAME from LIBVOLUMES where volume_name not in (select volume_name from volumes) and status='Private' and last_use is NULL and owner=''

If your library uses different types of media (LTO-2 and LTO-3) and you have different volume label series for them you can add another filter to further refine the results. For example my LTO-3 tape labels all start with L3 so I use:

select VOLUME_NAME from LIBVOLUMES where volume_name not in (select volume_name from volumes) and status='Private' and last_use is NULL and owner='' and volume_name like 'L3'

Once TSM has generated a list you can then run it through a while loop to reset their status or do further refining of the list if you like. If you are proficient with scripts you can automate the whole process and make life a lot easier.

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.

TSM Summary Table Question

So here is a question I hope someone can answer, does the SUMMARY table in the TSM DB track all backups whether they are scheduled or client initiated (cron or manually) or does it just track scheduled backups? Also does it track LAN-Free backups with data amounts? I figure it only tracks scheduled backups, but I haven't taken the time to look. If anyone cares to wager a guess or has the definitive answer please let me know.