Monday, September 28, 2015

TSM (Spectrum Protect) Symposium 2015, Dresden (in pictures)

We made it:

About 340 people!!!

 Norbert Pott (IBM Germany), Tommy Hueber (Rocket Software, The Netherlands,, me

Matt Anglin (IBM USA)

Who always knows everything and tells the truth!

Zsolt Fekete (SCSS Kft., Hungary), me and Paul Oh (Sentia Solutions Inc., Canada)

Wednesday, September 23, 2015

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.


Friday, September 04, 2015

TSM (Spectrum Protect) Symposium 2015, Dresden again

Ready, steady, ...  TSM2015: Promising Future

Will anyone go from here? Chad? Harry? or anybody else?

Friday, June 26, 2015

Remote Site Backup

Ok here is the scenario, we have a 2.8TB Windows file server at a remote location and we have to back it up over the WAN.  We can't put a TSM server locally due to many issues (one is that it's not worth wasting resources on 2-3 servers). Due to throughput the backup was calculated to possibly take 25+ days to complete. So the idea I came up with was to copy the data onto a 4TB USB  drive and then ship the drive to our Data Center and attach it to another Windows server as the same drive name/label and the drive has at the remote site, setup a dsm.opt with the node name of the remote server and run the backup. The hope is that then when we run the backup remotely it will see all it's data as already backed up and then pickup with incremental backups. Will it work? Anyone tried it? I'm worried there are too many file variables involved and it will still try and backup the files all over again.

Tuesday, June 23, 2015

RHEL 6.6, non IBM devices and UDEV rules

Few days back we had to move one of our TSM servers and its library - which is a shared one. Did it before with TSM version 5.x and everything was fine that day. Now we are on the 7.1 and I have found that even the SANDISCOVERY found the new path correctly (and updated it on the Library Manager), drives were not working.
TSM 6.x+ server does not run under "root" account anymore, but when the new device is discovered, its device file is created as RW for root only.
Normaly (when you manually add the new device) you update the device files privileges and create the links using "/opt/tivoli/tsm/devices/bin/autoconf -a", but this does not work automatically.

On the TSM Symposium 2013 in Berlin there was a great presentation "Tape configuration for TSM" by Bruno Friess which (among other things) solves this situation by using persistent names via "udev".
It only has a 2 minor limitations
a) Linux variant mentioned is SLES
b) lacks details for Linux, udev and non-IBM devices

As our Linux servers run on RHEL amd our libraries are Overland brand with HP tape drives, I had to modify the steps a bit:
a) udevadm info .... does not display serial numbers for HP tape drives and Overland libraries - you need sg3_utils package for having "sginfo" command to get the device serial numbers
b) links are created as /dev/library1 and /dev/drive1 (2, 3) pointing to corresponding /dev/sgX device with crw-rw-rw permissions
c) file below was created as /etc/udev/rules.d/71-persistent-tape.rules
# This file shoud create persistent devices for HP tapes and libraries used by TSM

# Known serial numbers
# <library1>    LIBRSN1234
# <drive1>      DRV1SN3456
# <drive2>      DRV2SN4567
# <drive3>      DRV3SN5678

KERNEL=="sg*", SUBSYSTEM=="scsi_generic", SYSFS{type}=="8", PROGRAM="/usr/bin/sginfo -s /dev/%k", RESULT=="*LIBRSN1234*", SYMLINK+="library1",MODE="0666"
KERNEL=="sg*", SUBSYSTEM=="scsi_generic", PROGRAM="/usr/bin/sginfo -s /dev/%k", RESULT=="*DRV1SN3456*", SYMLINK+="drive1",MODE="0666"
KERNEL=="sg*", SUBSYSTEM=="scsi_generic", PROGRAM="/usr/bin/sginfo -s /dev/%k", RESULT=="*DRV2SN4567*", SYMLINK+="drive2",MODE="0666"
KERNEL=="sg*", SUBSYSTEM=="scsi_generic", PROGRAM="/usr/bin/sginfo -s /dev/%k", RESULT=="*DRV3SN5678*", SYMLINK+="drive3",MODE="0666"
Many thanks to Bruno and hope this helps someone.

Monday, June 08, 2015

Best Desktop Linux Distro

Ok not a TSM subject, but I am running Linux (Kubuntu 15.04) on an old Sony laptop and while I like it, something is missing. I want a launcher bar but didn't like Unity. Anyone out there suggest a distro that they like? I've used Mint with Cinnamon and Mate but thought I'd try KDE again after the new release.  I don't particularly like GNOME but....

Wednesday, April 08, 2015

TSM 7.1 Discussion

I was asked by a former colleague to start a discussion on the merits of TSM 7.1. I use TSM 7.1 currently on my newer TSM servers and don't see a huge difference from our 6.4 servers. I know there are some features and updates with 7.1 but none that impact me other than any possible performance enhancements.

So tell me why you upgraded to TSM 7.1 or what features made it a "No Brainer" for you to implement 7.1. I'd like to hear from you.

Q MOUNT Research

So in yesterday's post I spoke of a "better Q MOUNT" using a script I wrote. The problem is the Q MOUNT command does not reference a accessible TSM table. It appears to gathers the information on the mount with the associated process or session from various tables (as seen with Q MOUNT F=D). So I became more intrigued with how TSM gathers the info and decided to bypass TSM all together and research on the DB2 side. Well out of 779 tables in the TSM 7.1 DB2 database I did not see any with MOUNT in the name (you can find a list of all the TSM DB 7.1 tables here). There definietle some tables that appear in the list that are hidden from TSM or unused, but I queried a number of tables looking for any that had fields (columns) that correlated to the Q MOUNT command. The closest I came was the MMS_DRIVE table which has the following fields.

$ db2 "select char(COLNAME,24) as COLNAME,char(TYPENAME,24)as DATATYPE,LENGTH,SCALE from syscat.columns where tabname='MMS_DRIVES' and tabschema='TSMDB1'"

COLNAME                  DATATYPE                 LENGTH      SCALE
------------------------ ------------------------ ----------- ------
ACSDRVID                 VARCHAR                          126      0
CLEANFREQ                INTEGER                            4      0
DEVICE                   VARCHAR                           65      0
DEVTYPE                  INTEGER                            4      0
DEV_INQ                  SMALLINT                           2      0
DRIVENAME                VARCHAR                           31      0
ELEMENT                  INTEGER                            4      0
INQUIRY                  VARCHAR                         1025      0
KBYTES_PROC              BIGINT                             8      0
KBYTES_PROC_HI           INTEGER                            4      0
LIBNAME                  VARCHAR                           31      0
ONLINE                   INTEGER                            4      0
OWNER                    VARCHAR                           65      0
OWNERVOL                 VARCHAR                         1025      0
RD_FORMAT                BIGINT                             8      0
SERIAL                   VARCHAR                           65      0
UPDATE_DATE              TIMESTAMP                         10      6
UPDATOR                  VARCHAR                           65      0
WR_FORMAT                BIGINT                             8      0
WWN                      VARCHAR                           17      0

There should be a table that would show the drive, what tape is mounted, what process or session it's assigned and who owns it, just like a Q MOUNT F=D. I haven't found it yet.

NOTE: Of the 779 tables I listed a more refined search shows 154 tables that correspond to what we seem to be able to access from the TSM admin command line.

Tuesday, March 24, 2015

A Better Q MOUNT?

I was playing around with my QDRV script which I feel gives a better showing than Q DRIVE and realized that I could technically produce a better Q MOUNT display than the built in TSM command provides. FYI - Unlike the Q MOUNT command my QMNT script shows the device definition for the current DRIVE_OWNER, so a storage agent or library client.  I have provided both my scripts for you, let me know what you think. Suggestions are welcome.

QDRV Script

select cast((library_name)as char(15)) as LIBRARY_NAME, -
cast((DRIVE_NAME)as char(16)) as DRIVE_NAME, -
cast((drive_state)as char(10)) as DRIVE_STATE, -
cast((volume_name)as char(8)) as VOL_NAME, cast((online)as char(10)) as ONLINE, -
cast((ALLOCATED_TO)as char(20)) as DRV_OWNER from drives order by library_name, drive_name

QDRV Macro

DEFINE SCRIPT  QDRV DESC="Show tape drive status"
UPDATE SCRIPT  QDRV "select cast((library_name)as char(15)) as LIBRARY_NAME, -"
UPDATE SCRIPT  QDRV "cast((DRIVE_NAME)as char(16)) as DRIVE_NAME, -"
UPDATE SCRIPT  QDRV "cast((drive_state)as char(10)) as DRIVE_STATE, -"
UPDATE SCRIPT  QDRV "cast((volume_name)as char(8)) as VOL_NAME, cast((online)as char(10)) as ONLINE, -"
UPDATE SCRIPT  QDRV "cast((ALLOCATED_TO)as char(20)) as DRV_OWNER from drives order by library_name, drive_name"

QMNT Script

select varchar(a.library_name,15) as LIB_NAME, -
cast((a.DRIVE_NAME)as char(16)) as DRIVE_NAME, -
cast((a.drive_state)as char(10)) as DRIVE_STATE, -
cast((a.volume_name)as char(8)) as VOL_NAME, -
varchar(b.device,12) as device, cast(( char(10)) as ONLINE, -
cast((a.ALLOCATED_TO)as char(20)) as DRV_OWNER from drives a, paths b -
where a.library_name=b.library_name and a.drive_name=b.destination_name -
and b.source_name=a.ALLOCATED_TO order by a.drive_name

QMNT Macro

DEFINE SCRIPT  QMNT DESC="Show tape mount status"
UPDATE SCRIPT  QMNT "select varchar(a.library_name,15) as LIB_NAME, -"
UPDATE SCRIPT  QMNT "cast((a.DRIVE_NAME)as char(16)) as DRIVE_NAME, -"
UPDATE SCRIPT  QMNT "cast((a.drive_state)as char(10)) as DRIVE_STATE, -"
UPDATE SCRIPT  QMNT "cast((a.volume_name)as char(8)) as VOL_NAME, -"
UPDATE SCRIPT  QMNT "varchar(b.device,12) as device, cast(( char(10)) as ONLINE, -"
UPDATE SCRIPT  QMNT "cast((a.ALLOCATED_TO)as char(20)) as DRV_OWNER from drives a, paths b -"
UPDATE SCRIPT  QMNT "where a.library_name=b.library_name and a.drive_name=b.destination_name -"
UPDATE SCRIPT  QMNT "and b.source_name=a.ALLOCATED_TO order by a.drive_name"

Wednesday, February 18, 2015

Data Domain Compression

I currently manage 7 Data Domain's (890's and 670's) and none of them are seeing compression above 5x. We obviously need to do some cleanup to get rid of data that is a bad candidate for dedupe, but the question is "where to start?" Have any of you successfully increased you dedupe compression through cleanup? If so what steps did you take?

Tuesday, February 17, 2015

Rumor Mill Update!

So my source at IBM was right except for the name, but she was close, in that IBM is going to be restructuring its storage software group under the Spectrum Storage moniker (Specter was close but Spectrum is a better title). ZDNet states that IBM will be investing $1 Billion in software defined storage which also means TSM is no longer under the Tivoli division....(Please, Please, Please let this be true!) In my opinion Tivoli was a horrible brand name and IBM should have never put ADSM under its umbrella.

Hopefully we will see some huge changes to TSM to make it more competitive. Some cloud connectivity would be a good start as more companies are implementing hybrid data-centers. I'd also like to see the new administration and reporting tool become a little more flexible with user defined reports, but we'll have to wait and see where this restructuring takes TSM or should we now call it Spectrum Storage Manager?

Monday, February 16, 2015

TSM Rumor Mill

So I was talking with an IBM source about IBM's recent layoffs and she stated that many groups were being restructured and TSM was affected. According to my source she said that TSM is possibly going to have a name change. This is no shock for those who remember when TSM was called ADSM under the ADSTAR (ADvanced STorage And Retrieval) division. A partial name drop was something along the lines of Specter blah blah blah!! So take this rumor with some reservations. IBM is going through a major upheaval as they decide what their service path with be in the future, so it's anybodies guess where TSM will end up.

Monday, January 12, 2015

Poll Results

The TSM Server usage poll closed Jan 1st and the results were interesting. See for yourself, but I thought the usage of 6.3 and 7.1 over 6.4 was interesting.  I guess it's not worth going to 6.4 seeing as 7.1 is out???


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.

------------------     -----------     -----------
SUN SOLARIS 5.9                   4
WinNT 2000                      1
WinNT 2000                      1
WinNT 2003                      4
WinNT 2003                      6
WinNT 2003                      6
WinNT 2003                      3
WinNT 2003                      2
WinNT 2003                      2
WinNT 2003                      2
WinNT 2003                      8
WinNT 2003                      1
WinNT 2003                      1
WinNT 2003                      2
WinNT 2003                      1
WinNT 2008                      1
WinNT 2008 R2                   3
WinNT 2008 R2                   2
WinNT 2008 R2                   2
WinNT 2012                      1