-- Example of code which may be called from the check_db2
-- Nagios plugin (using the '-q' parameter).

-- Author: Troels Arvin <tra@sst.dk>
--
-- $Date: 2010-09-16 15:17:45 +0200 (Thu, 16 Sep 2010) $
--
-- This script is unsupported and distributed without any guarantees;
-- use it at your own risk. The code is public domain and you may
-- use it in any way you want on your own responsibility.

-- Assumption: A user "nagios" is able to connect to the database,
-- and a table "NAGIOS.WRITE_TEST" may be created.

-- Import with "db2 -td@ -v -f filename.sql"
-- after having connected to the relevant database.

-- Uncomment this to clean up before adding the table and routines:
-- DROP FUNCTION nagios.write_probe(INT)@
-- DROP PROCEDURE nagios.write_probe_do(INT,INT,VARCHAR())@
-- DROP TABLE nagios.write_test@

CREATE TABLE nagios.write_test(ts TIMESTAMP)@
COMMENT ON TABLE nagios.write_test IS 'Table used by the nagios.probe monitoring routine.'@
GRANT SELECT ON nagios.write_test TO PUBLIC@
INSERT INTO nagios.write_test VALUES (CURRENT_TIMESTAMP)@

-- Not to be called directly.
-- Called by the nagios.write_probe function
CREATE PROCEDURE nagios.write_probe_do(IN timeout_secs INT,OUT return_code INT,OUT ret_msg VARCHAR(254))
    LANGUAGE SQL
    MODIFIES SQL DATA
    NO EXTERNAL ACTION
BEGIN
    DECLARE err_state CHAR(5) DEFAULT '?????';
    DECLARE err_code INTEGER DEFAULT 0;
    DECLARE SQLSTATE  CHAR(5) DEFAULT '?????';
    DECLARE SQLCODE INTEGER DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        BEGIN
            VALUES (SQLSTATE,SQLCODE) INTO err_state,err_code;
            SET ret_msg = 'While attempting to update nagios.write_test, got error: SQLSTATE='||err_state||', SQLCODE='||RTRIM(CHAR(err_code));
            SET return_code = 2;
        END;

    SET return_code = 0;
    SET ret_msg = 'Updating nagios.write_test OK';
    SET CURRENT LOCK TIMEOUT timeout_secs;

    UPDATE nagios.write_test SET ts = CURRENT_TIMESTAMP;
END@
COMMENT ON PROCEDURE nagios.write_probe_do(INT,INT,VARCHAR()) IS 'Called by the nagios.write_probe function; not to be called directly'@

-- Call this function from the monitoring software:
--   SELECT * FROM TABLE(nagios.write_probe(30)) AS foo
-- (30 is the number of seconds to wait for locks.)
CREATE FUNCTION nagios.write_probe(lock_timeout INT)
    RETURNS TABLE (return_code INT, message VARCHAR(254))
    LANGUAGE SQL
    MODIFIES SQL DATA
    NO EXTERNAL ACTION
BEGIN ATOMIC
    DECLARE return_code_ INTEGER;
    DECLARE message_ VARCHAR(254);
    CALL nagios.write_probe_do(lock_timeout,return_code_,message_);
    RETURN VALUES(return_code_,message_);
END@
GRANT EXECUTE ON FUNCTION nagios.write_probe(INT) TO USER nagios@
COMMENT ON FUNCTION nagios.write_probe(INT) IS 'To be called by monitoring software to ensure that the database is in a proper state'@
