TSM Topics Feed

Thursday, April 20, 2017

Spectrum Protect 8.1 New DB Functions Supported

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(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 -
 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!

1 comment:

  1. nope,

    for Version I get the following error:

    ANR0162W Supplemental database diagnostic information: -1:42884:-440 ([IBM][CLI Driver][DB2/AIX64] SQL0440N No authorized routine named "SUBSTRING" of type "FUNCTION" having compatible arguments was