space alerting

Hi, Has anyone had any experience(or idea)on enabling alert against space in Informix? With OAT or a automated query, I can find the space used and ..., But I need an alert like alarm program. Regards

Consider using AGS Sentinel with Server Studio. That's what it's built for. Another option is to use Nagios but you'd have to build your own sensors. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Dec 21, 2009 at 9:12 PM, ALI SHAHNAZI <ali@datasync.com.au> wrote: > Hi, > > Has anyone had any experience(or idea)on enabling alert against space in > Informix? > > With OAT or a automated query, I can find the space used and ..., But I > need > an alert like alarm program. > > Regards > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0ce044f06fe3b1047b47caf9

Hi, you could use the following sql-script against sysmaster: select sysdbspaces.dbsnum, sysdbspaces.name, (sum(chksize) - sum(nfree)) / sum(chksize) used, sum(chksize) total_pages, sum(nfree) free_pages from syschunks, sysdbspaces where syschunks.dbsnum= sysdbspaces.dbsnum and sysdbspaces.is_blobspace = 0 and sysdbspaces.name not matches "logdbs*" and sysdbspaces.name != "physdbs" group by sysdbspaces.dbsnum, sysdbspaces.name having sum(nfree) / sum(chksize) < 0.02 order by sysdbspaces.dbsnum The exclusions should be modified by your needs as the percentage of free space. Evaluate the result of the script. If there are lines with dbspacenumber and dbspacename then send an e-mail to your cellphone, pager or whereever you want. HTH, Reinhard. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On > Behalf Of ALI > SHAHNAZI > Sent: Tuesday, December 22, 2009 3:12 AM > To: ids@iiug.org > Subject: space alerting [18459] > > > Hi, > > Has anyone had any experience(or idea)on enabling alert > against space in > Informix? > > With OAT or a automated query, I can find the space used and > ..., But I need > an alert like alarm program. > > Regards > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. >

I forgot: Put the sql-script in a shell-script, where you set the environment and put the shell-script in the crontab for example of informix. Hi, you could use the following sql-script against sysmaster: select sysdbspaces.dbsnum, sysdbspaces.name, (sum(chksize) - sum(nfree)) / sum(chksize) used, sum(chksize) total_pages, sum(nfree) free_pages from syschunks, sysdbspaces where syschunks.dbsnum= sysdbspaces.dbsnum and sysdbspaces.is_blobspace = 0 and sysdbspaces.name not matches "logdbs*" and sysdbspaces.name != "physdbs" group by sysdbspaces.dbsnum, sysdbspaces.name having sum(nfree) / sum(chksize) < 0.02 order by sysdbspaces.dbsnum The exclusions should be modified by your needs as the percentage of free space. Evaluate the result of the script. If there are lines with dbspacenumber and dbspacename then send an e-mail to your cellphone, pager or whereever you want. HTH, Reinhard. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On > Behalf Of ALI > SHAHNAZI > Sent: Tuesday, December 22, 2009 3:12 AM > To: ids@iiug.org > Subject: space alerting [18459] > > > Hi, > > Has anyone had any experience(or idea)on enabling alert > against space in > Informix? > > With OAT or a automated query, I can find the space used and > ..., But I need > an alert like alarm program. > > Regards > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. >

Hi Ali, have a look here: http://www.informix-zone.com/node/901 HTH Davorin

Hi, Take a look in the redbook on Embedding IDS ( http://www.redbooks.ibm.com/abstracts/sg247666.html?Open ). Code for automating the exercise of adding chunks to spaces as they fill is in chapter 9. I have not tried the code as it is, but it appears to be workable (or close anyway.) If you don't want to add space automatically, you can modify things to send email or whatever. This does require IDS 11.10 or later. Earlier versions don't have the task/sensor feature. Cheers, Dick Snoke Executive IT Specialist IBM Software Group - ChannelWorks Tel: (404) 487-1595 Email: dsnoke@us.ibm.com From: "ALI SHAHNAZI" <ali@datasync.com.au> To: ids@iiug.org Date: 12/21/09 09:13 PM Subject: space alerting [18459] Sent by: ids-bounces@iiug.org Hi, Has anyone had any experience(or idea)on enabling alert against space in Informix? With OAT or a automated query, I can find the space used and ..., But I need an alert like alarm program. Regards ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Thanks All, 1 - Reinhard's script seems good, but I prefer to use 'ph_alert' and other ph ... tables. 2 - In ph_... tables I can define a sensor, but all examples in books and articles talk about running database functions whereas I want to send an email like alarmprogram.sh. Richard, Davotin and other guys, Have you had any idea ? Regards

From a stored procedure you can make a system call to mail. There are examples in the manual of how to accomplish this. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 12/22/2009 06:20:23 PM: > Thanks All, > > 1 - Reinhard's script seems good, but I prefer to use 'ph_alert' andother ph > .... tables. > > 2 - In ph_... tables I can define a sensor, but all examples in books and > articles talk about running database functions whereas I want to > send an email > like alarmprogram.sh. > > Richard, Davotin and other guys, Have you had any idea ? > > Regards > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Ali, as John already said, create a stored proc and make a system call. To make this a bit more generic, put a trigger on insert on ph_alert table, make it call the stored proc above and based on conditions (severity, colour, etc...) it can send an email, SMS, ... Then post your code here :) Davorin

Happy New Year. Thanks to All, I used this link : http://www.informix-zone.com/node/901 and made this script : -- Putting information in ph_threshold table ( We can exclude some dbspaces ) -- Delete from ph_threshold where task_name = "Dbspace full alarm"; insert into ph_threshold values (0, "DBSPACE_PCT_FULL_RED", "Dbspace fullalarm", 90, "NUMERIC", "Dbspace percentage full alarm - red"); insert into ph_threshold values (0, "DBSPACE_PCT_FULL_YELLOW", "Dbspace fullalarm", 70, "NUMERIC", "Dbspace percentage full alarm - yellow"); insert into ph_threshold values (0, "DBSPACE_PCT_FULL_EXCLUDE", "Dbspace fullalarm", "''", "STRING", "Comma separated list of dbspaces (each in single quites) to exclude from the check"); -- Checking space and put information in ph_alert -- DROP FUNCTION _sysadmin_dbspace_full; CREATE FUNCTION _sysadmin_dbspace_full(task_id INT, task_seq INT) RETURNINGINTEGER DEFINE dbspace_name CHAR(129); DEFINE exclude_list LVARCHAR(1024); DEFINE dbspace_query LVARCHAR(2048); DEFINE dbspace_pct_free DECIMAL(4,2); DEFINE dbspace_pct_free_red DECIMAL(4,2); DEFINE dbspace_pct_free_yellow DECIMAL(4,2); ON EXCEPTION IN (-201, -217) INSERT INTO ph_alert (ID, alert_task_id, alert_task_seq, alert_type, alert_color, alert_object_type, alert_object_name, alert_message, alert_action) VALUES (0,task_id, task_seq, "ERROR", "RED", "SQL", exclude_list, "Dbspace full alarm: Exclude list [" || exclude_list || "] wrong format!", NULL); RETURN 0; END EXCEPTION; LET exclude_list = (select value from ph_threshold where name = "DBSPACE_PCT_FULL_EXCLUDE" and task_name = "Dbspace full alarm"); IF exclude_list IS NULL OR exclude_list = '' THEN LET exclude_list = "''"; END IF LET dbspace_query = "select trim(name) dbspace, round ((sum(nfree))/(sum(chksize))*100,2) percent_free from sysmaster:sysdbspaces d, sysmaster:syschunks c where d.dbsnum = c.dbsnum and trim(name) not in (" || exclude_list || ") and is_sbspace != 1 group by 1 union select trim(name)||' (UD)', round(100/sum(udsize)*sum(udfree),2) from sysmaster:sysdbspaces d, sysmaster:syschunks c where d.dbsnum=c.dbsnum and trim(name) not in (" || exclude_list || ") and is_sbspace=1 group by 1 union select trim(name)||' (MD)', round(100/sum(mdsize)*sum(nfree),2) from sysmaster:sysdbspaces d, sysmaster:syschunks c where d.dbsnum=c.dbsnum and trim(name) not in (" || exclude_list || ") and is_sbspace=1 group by 1 INTO TEMP __t_dbspace_full"; EXECUTE IMMEDIATE dbspace_query; let dbspace_pct_free_red = (select value from ph_threshold where name = "DBSPACE_PCT_FULL_RED" and task_name = "Dbspace full alarm"); IF dbspace_pct_free_red IS NULL THEN INSERT INTO ph_alert (ID, alert_task_id, alert_task_seq, alert_type, alert_color, alert_object_type, alert_object_name, alert_message, alert_action) VALUES (0,task_id, task_seq, "ERROR", "YELLOW", "SQL", dbspace_name, "Task parameter [dbspace_pct_free_red] for task [Dbspace full alarm] is not defined. Task not run.", NULL ); RETURN 0; END IF let dbspace_pct_free_yellow = (select value from ph_threshold where name = "DBSPACE_PCT_FULL_YELLOW" and task_name = "Dbspace full alarm"); IF dbspace_pct_free_yellow IS NULL THEN INSERT INTO ph_alert (ID, alert_task_id, alert_task_seq, alert_type, alert_color, alert_object_type, alert_object_name, alert_message, alert_action) VALUES (0,task_id, task_seq, "ERROR", "YELLOW", "SQL", dbspace_name, "Task parameter [dbspace_pct_free_yellow] for task [Dbspace full alarm] is not defined. Task not run.", NULL ); RETURN 0; END IF FOREACH SELECT dbspace, percent_free INTO dbspace_name, dbspace_pct_free FROM __t_dbspace_full WHERE percent_free <= (100.00 - dbspace_pct_free_yellow) AND percent_free > (100.00 - dbspace_pct_free_red) ORDER BY percent_free DESC INSERT INTO ph_alert (ID, alert_task_id, alert_task_seq, alert_type, alert_color, alert_object_type, alert_object_name, alert_message, alert_action) VALUES (0,task_id, task_seq, "WARNING", "YELLOW", "DBSPACE", dbspace_name, "Dbspace [" || trim(dbspace_name) || "] has less than " || (100.00-dbspace_pct_free_yellow) || " percent free ", NULL ); END FOREACH FOREACH SELECT dbspace, percent_free INTO dbspace_name, dbspace_pct_free FROM __t_dbspace_full WHERE percent_free <= (100.00 - dbspace_pct_free_red) ORDER BY percent_free DESC INSERT INTO ph_alert (ID, alert_task_id, alert_task_seq, alert_type, alert_color, alert_object_type, alert_object_name, alert_message, alert_action) VALUES (0,task_id, task_seq, "WARNING", "RED", "DBSPACE", dbspace_name, "Dbspace [" || trim(dbspace_name) || "] has less than " || (100.00-dbspace_pct_free_red) || " percent free ", NULL ); END FOREACH DROP TABLE __t_dbspace_full; RETURN 0; END FUNCTION; -- Put above function in ph_task for running automatically -- DELETE FROM ph_task WHERE tk_name = "Dbspace full alarm"; INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time,tk_frequency ) VALUES ( "Dbspace full alarm", "TASK", "DISK", "Checks if dbspace free space has dropped below configured thresholds", "_sysadmin_dbspace_full", DATETIME(00:00:00) HOUR TO SECOND, NULL, INTERVAL ( 1 ) HOUR TO HOUR ); -- Send Email Procedure -- DROP PROCEDURE SendEmail CREATE PROCEDURE SendEmail( subject char(8), body char(200) ) SYSTEM 'echo "' || body || '" > /tmp/spacealert' ; SYSTEM 'mail -s "' || subject || 'Alert For SPACE" informix@localhost </tmp/spacealert' ; SYSTEM 'rm -f /tmp/spacealert' ; END PROCEDURE ; -- Trigger on insertion in ph_alert -- DROP TRIGGER SpaceAlert CREATE TRIGGER SpaceAlert INSERT ON ph_alert REFERENCING NEW AS post FOR EACH ROW ( EXECUTE PROCEDURE SendEmail( post.alert_color,post.alert_message ) ) ;