Hello,

I would like to monitor our DB2 database for tablespaces which are not in
a normal state; this could mean detecting tablespaces in "backup pending"
mode.

I would like to do it through a Nagios plugin[1] which queries the system
catalogs for tablespaces where status is <> from something or NOT IN (...)
- Kind of how I do to detect tables where status is non-normal:
http://troels.arvin.dk/db/db2/nagios...ablestatus.sql


But I can't seem to find a way to do it: It doesn't look like
syscat.tablespaces has a STATUS column, for example. Am I overlooking
something, or is the only way to parse output from "db2 list tablespaces"?



1: http://troels.arvin.dk/db/db2/nagios/check_db2

--
Troels

On 2010-09-16 17:05, Troels Arvin wrote:
Hi Troels, have you tried sysibmadm.SNAPTBSP_PART? Example:

[lelle@c-83-219-209-129 ~]$ db2 "select TBSP_NAME, TBSP_STATE from
sysibmadm.SNAPTBSP_PART"

TBSP_NAME
TBSP_STATE




--------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSCATSPACE
NORMAL




TEMPSPACE1
NORMAL




USERSPACE1
NORMAL




IBMDB2SAMPLEREL
NORMAL




IBMDB2SAMPLEXML
NORMAL




SYSTOOLSPACE
NORMAL




SYSTOOLSTMPSPACE
NORMAL





7 record(s) selected.

On 2010-09-16 17:23, Lennart Jonsson wrote:
I ran this against v9.7.0.2 but according to the docs, it should be
availible in 9.5 as well.


/Lennart

Lennart Jonsson wrote:

Thanks, but that view seems to be very restricted.

Even setting up a view like this gives no errors, but when a non-
privileged user tries to read from the view, access is denied:

CREATE OR REPLACE VIEW nagios.tablespace_status AS
WITH cnt(num_non_normal) AS (
SELECT count(*)
FROM sysibmadm.snaptbsp_part
WHERE tbsp_state<>'NORMAL'
)
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 tablespaces in normal mode'
ELSE num_non_normal||' tablespaces in non-normal mode'
END AS explanation
FROM cnt
;
GRANT SELECT ON nagios.tablespace_status TO PUBLIC;

command or operation.


I then tried wrapping a count of troublesome tablespaces in a procedure
like this, but that was no success, either - the procedure could not be
called by normal users (SQL1092N, again):

CREATE OR REPLACE PROCEDURE nagios.num_non_normal_tablespaces_(OUT n
INTEGER)
LANGUAGE SQL
BEGIN ATOMIC
DECLARE stmt_txt VARCHAR(1000);
DECLARE stmt STATEMENT;
SET stmt_txt = 'SELECT count(*) FROM sysibmadm.snaptbsp_part WHERE
tbsp_state<>''NORMAL''';
PREPARE stmt FROM stmt_txt;
BEGIN
DECLARE cur CURSOR FOR stmt;
OPEN cur;
FETCH cur INTO n;
CLOSE cur;
END;
RETURN n;
END@
GRANT EXECUTE ON PROCEDURE nagios.num_non_normal_tablespaces_ TO PUBLIC@

I don't want my monitoring system to have to be running with high
privileges. Is my only choice a clumsy sudo-executed shell-script which
parses output from "list tablespaces"?

--
Troels

On Sep 16, 4:37*pm, Troels Arvin <tro... (AT) arvin (DOT) dk> wrote:

No, of course not. There is a SYSMON privilege (in the database
manager configuration) that
can be granted to a group. Just make sure your nagios user belongs to
whatever group you
designate for SYSMON.

Ian wrote:
But won't that give the monitoring system access to view SQL statements?
(Which may contain sensitive information.)

--
Troels

On Sep 18, 7:26*am, Troels Arvin <tro... (AT) arvin (DOT) dk> wrote:
Yes, that's true. You could revoke select on the sysibmadm views from
public,
granting privileges only to the appropriate user(s); but this wouldn't
prevent the
nagios user from executing "get snapshot for dynamic sql" or similar.

An alternative would be to set up a cron job for the instance owner
that runs
every X minutes and populates a table or file with the monitoring
information
that nagios needs. Your nagios script would just look at this table
or file; if
you write to a file, the nagios user could have no access to the
database
whatsoever.