Using select subqueries TSM 5.x

[yquirion]

Dear all,

I'm trying to run the following SQL queries on TSM 5.x and I'm getting an error:

select substr(a.node_name,1,25) as NODE_NAME, -
a.lastsess_recvd, -
b.backup_end -
from nodes a, -
(select node_name, max(backup_end) backup_end -
from filespaces -
group by node_name) b -
where (a.node_name = b.node_name)

This SQL query uses a subquery in the FROM clause which doesn't seems to be supported by TSm 5.x SQL commands. I test the same kind of query on DB2 or TSM 6.x and it works.

Basically, I would like to get the most recent filespace that has been successfully backed up for a specific node name (the subquery will return that value). There after, I will calculate the number of days elapsed since the date of day. If the answer greater than 7, I will issue an alarm into my script.

If I cannot use subquery with TSM 5.x, is somebody has another idea how I can get the needed information?

Any suggestion or comments will be greatly appreciated.

That you and best regards,
Yanick Quirion

 

[TonyB]

I suspect that you'll need to list all of the filespaces with backup end dates and post-process the data in your script - TSM5's SQL interface is very much a subset of SQL92; the interpreter isn't all that smart.

EDIT: On second thoughts I wonder if this might do it for you:

select node_name from filespaces group by node_name having max(backup_end)<current_timestamp -7 day