TSM Topics Feed

Wednesday, November 22, 2017

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! 

mind blown wat animated GIF

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!

1 comment:

  1. nope,

    for Version 7.1.6.0 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

    ReplyDelete