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