comparing computed colums

[cilesiz]

Hi guys,

I'll write a tsm script for monitoring the DB filesystem usage somthing like the following:

SELECT ROUND(SUM(100-(free_space_mb*100)/tot_file_system_mb)) AS PCT_UTILIZED FROM db WHERE ROUND(SUM(100-(free_space_mb*100)/tot_file_system_mb))>70
if(rc_ok) goto GT70
GT70:
issue message w "DB PCT_UTILIZED IS GREATER 70%"

But I get the follwing error for the select :

ANR2940E The command 'SELECT ROUND ( SUM ( 100- ( free_space_mb*100 ) /tot_file_system_mb ) ) AS PCT_UTILIZED FROM db WHERE ROUND ( SUM ( 100- ( free_space_mb*100 ) /tot_file_system_mb ) ) > 70 ' contains an unknown SQL column name.

thx in advance

 

[marclant]

I tested, looks like it doesn't like the math in the where clause.

ANR0162W Supplemental database diagnostic information:  -1:42903:-120 ([IBM][CLI Driver]
[DB2/AIX64] SQL0120N  Invalid use of an aggregate function or OLAP function.  SQLSTATE=42903).

ANR2940E The command 'SELECT ROUND ( SUM ( 100- ( free_space_mb*100 ) /tot_file_system_mb ) ) AS 
PCT_UTILIZED FROM db WHERE ROUND ( SUM ( 100- ( free_space_mb*100 ) /tot_file_system_mb ) )
> 70 ' contains an unknown SQL column name.
ANS8001I Return code 3.

There's a single record, so you don't need the sum.

Your command is also complex, it could be simpler, you just want to RC_OK if greater than 70% utilized or less than 30% free. So you could do this:

SELECT database_name FROM db WHERE (free_space_mb/tot_file_system_mb)<.30

 

[RecoveryOne]

Have you looked at what thobias has done in the past? Its a nifty monitor script, but might need some cleanup. https://github.com/thobiast/tsmmonitor

 

[cilesiz]

Guys you're so great!
Thank you very much