-- For checking that all tables in the database have status N
-- (normal).

-- May be called from the check_db2 Nagios plugin
-- (using the '-q' parameter):
-- SELECT * FROM nagios.table_status

-- Author: Troels Arvin <tra@sst.dk>
--
-- $Date: 2010-09-20 10:23:00 +0200 (man, 20 sep 2010) $
--
-- This code 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.

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

CREATE OR REPLACE VIEW nagios.table_status AS
    WITH cnt(num_non_normal) AS (
        SELECT count(*)
        FROM   syscat.tables
        WHERE  status<>'N'
    )
    SELECT  CASE
                WHEN num_non_normal = 0
                THEN 0                      -- nagios code for OK
                ELSE 2                      -- nagios code for CRITICAL
            END AS nagios_status_code,
            CASE
                WHEN num_non_normal = 0
                THEN 'All tables in normal mode'
                ELSE num_non_normal||' tables in non-normal mode'
            END AS explanation
    FROM    cnt
;
GRANT SELECT ON nagios.table_status TO PUBLIC;
