TSM Topics Feed

Tuesday, January 05, 2016

Oracle RMAN Catalogue Cleanup - Revisited

Over a Decade ago I wrote an article on Object cleanup due to the issues with RMAN and our DBA's not keeping it sync'd with TSM. I then revisited it in 2007 and since then have not had to use it much. With version 6 of TSM I have not worried about my DB size, or old data not expiring since TSM seems to handle it somewhat better than the older versions did. I recently had to use the command and realized that the delete object command as it was used in TSM 5 is not 100% correct for TSM 6. So when trying to use the old command

TSM v5

delete object 0 [Object ID Number]
The 0 is telling TSM how many dependent objects to delete (at least I believe that's what it does, I can't fully remember). But with TSM 6 you don't need to provide the dependent object count in the command.

TSM v6 and higher

delete object [Object ID Number]

This command works (although it can sometimes take awhile to complete the command). So using the correct select you can produce a list and delete the objects from TSM. Reclamation with return the space but my understanding is that this is an individual expiration process and remember it is not a command support will help you with. USE AT YOUR OWN RISK!

Here is the select:

select 'delete object', object_id from backups where node_name=[TDP NODENAME] and
backup_date < '2007-06-01 00:00:00'

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, http://www.tsmblog.org/), 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.