SpitfireLoad Testing & APMWhat's new

Infrastructure: IBM Db2 (Beta)

IBM Db2 LUW 11.5 and 12 (Beta) is read through the Tower: the Tower signs in over the Db2 port (DRDA, 50000 by default) and reads the MON_GET_* table functions once a minute. No Db2 client, driver or Collector is needed. The user gets only CONNECT and EXECUTE on nine table functions; it cannot read your tables. Each database is one monitored system.

Page: All integrations In Spitfire: /admin/integrations → Add integration → IBM Db2. The <spitfire> and <token> in the command come filled in the UI (install note).

Quick install

  1. Run the command on the Db2 server as root (or a user with sudo), answer the questions.
bash
curl -fsSL "https://<spitfire>/api/v1/setup/ds/db2.sh?t=<token>" -o /tmp/sf-db2.sh && bash /tmp/sf-db2.sh
  1. When you see OK: ..., you are done.

Manual install

  1. On the Db2 server as root, create the spitmon OS user (Db2 checks the password with the operating system).
bash
id spitmon >/dev/null 2>&1 || useradd -M -s /sbin/nologin spitmon; passwd spitmon && echo 'OK: spitmon user'
  1. On the Db2 server, give the user CONNECT and EXECUTE on the nine table functions. Replace SAMPLE with your database name; every GRANT prints DB20000I.
bash
su - db2inst1 -c 'DB=SAMPLE
db2 connect to $DB
db2 "GRANT CONNECT ON DATABASE TO USER SPITMON"
db2 "GRANT EXECUTE ON FUNCTION SYSPROC.MON_GET_DATABASE TO USER SPITMON"
db2 "GRANT EXECUTE ON FUNCTION SYSPROC.MON_GET_BUFFERPOOL TO USER SPITMON"
db2 "GRANT EXECUTE ON FUNCTION SYSPROC.MON_GET_TABLESPACE TO USER SPITMON"
db2 "GRANT EXECUTE ON FUNCTION SYSPROC.ADMIN_GET_STORAGE_PATHS TO USER SPITMON"
db2 "GRANT EXECUTE ON FUNCTION SYSPROC.MON_GET_TRANSACTION_LOG TO USER SPITMON"
db2 "GRANT EXECUTE ON FUNCTION SYSPROC.MON_GET_HADR TO USER SPITMON"
db2 "GRANT EXECUTE ON FUNCTION SYSPROC.MON_GET_PKG_CACHE_STMT TO USER SPITMON"
db2 "GRANT EXECUTE ON FUNCTION SYSPROC.MON_GET_CONNECTION TO USER SPITMON"
db2 "GRANT EXECUTE ON FUNCTION SYSPROC.MON_GET_APPL_LOCKWAIT TO USER SPITMON"
db2 connect reset' && echo 'OK: grants'
  1. Check that the nine grants are there.
bash
su - db2inst1 -c 'db2 connect to SAMPLE >/dev/null; db2 -x "SELECT COUNT(DISTINCT R.ROUTINENAME) FROM SYSCAT.ROUTINEAUTH A JOIN SYSCAT.ROUTINES R ON R.ROUTINESCHEMA = A.SCHEMA AND R.SPECIFICNAME = A.SPECIFICNAME WHERE A.GRANTEE = '"'"'SPITMON'"'"'"; db2 connect reset >/dev/null' | grep -qw 9 && echo 'OK: 9 grants'
  1. From the Tower's machine, check the Db2 port.
bash
timeout 5 bash -c 'exec 3<>/dev/tcp/<db2-host>/50000' && echo 'OK: Db2 port reachable'
  1. On the Db2 server, check that the password is taken encrypted. If this prints nothing, use TLS (note below) or, in a maintenance window (the instance restarts), run su - db2inst1 -c 'db2 update dbm cfg using AUTHENTICATION SERVER_ENCRYPT && db2stop && db2start'.
bash
su - db2inst1 -c 'db2 get dbm cfg' | grep -q '(AUTHENTICATION) = SERVER_ENCRYPT' && echo 'OK: encrypted sign-in'
  1. Spitfire → Infrastructure sources → IBM Db2.
    • Address: db2.corp:50000
    • Database: SAMPLE
    • User / password: spitmon and the password from step 1.
  2. Test connection → Save. The first metrics arrive within ~1 min.
Note

For TLS, tick TLS and give the SSL port (SSL_SVCENAME, often 50001); with an internal CA also tick Skip TLS certificate verification. Without TLS the password goes with DH + DES (security mechanism 9), which needs AUTHENTICATION = SERVER_ENCRYPT. Under the default SERVER Db2 asks for a clear-text password, which Spitfire sends only inside TLS. Kerberos and AES-only sign-in (KERBEROS, ALTERNATE_AUTH_ENC = AES_ONLY) are not supported; the test shows a sign-in error.

The most common case: step 5 prints nothing because the instance is on the default SERVER. Our advice: if TLS is already on in Db2, use it; if not, plan the SERVER_ENCRYPT change for a maintenance window.

Panels and findings

The panels show locks, buffer pool and sorts, log and tablespace usage (auto-resize and MAXSIZE are taken into account), storage paths, backup age and HADR. The live view lists the top SQL by elapsed time and by CPU (literal values masked), the current lock waits and the connections per application.

Finding Threshold Severity
Db2 not answering up = 0 critical
Deadlock rose warning
Lock timeout rose warning
Lock escalation rose warning
Many lock waits > 5 applications waiting warning
Low buffer pool hit ratio < 90% (at least 1000 reads) warning
High sort overflows > 10% of sorts (at least 100) warning
Log usage > 80% warning, > 90% critical warning / critical
Tablespace near its limit > 85% warning, > 95% critical warning / critical
Tablespace not normal state is not NORMAL warning
Storage path full > 85% warning, > 95% critical warning / critical
Old backup > 7 days warning, > 14 days critical warning / critical
Never backed up no backup warning
HADR disconnected connected = 0 critical
HADR not in PEER state is not PEER warning
HADR replay delay > 300 s warning
Long lock wait (live view) ≥ 30 s warning

Uninstalling

Delete the source in Spitfire first: All integrations In Spitfire: /admin/integrations#observability → the bin on the source's row → Delete. Metrics already collected stay.

  1. On the Db2 server, as root, revoke the grants. Put your database's name in place of SAMPLE and the instance owner in place of db2inst1; each REVOKE prints DB20000I.
bash
su - db2inst1 -c 'DB=SAMPLE
db2 connect to $DB
db2 "REVOKE CONNECT ON DATABASE FROM USER SPITMON"
db2 "REVOKE EXECUTE ON FUNCTION SYSPROC.MON_GET_DATABASE FROM USER SPITMON RESTRICT"
db2 "REVOKE EXECUTE ON FUNCTION SYSPROC.MON_GET_BUFFERPOOL FROM USER SPITMON RESTRICT"
db2 "REVOKE EXECUTE ON FUNCTION SYSPROC.MON_GET_TABLESPACE FROM USER SPITMON RESTRICT"
db2 "REVOKE EXECUTE ON FUNCTION SYSPROC.ADMIN_GET_STORAGE_PATHS FROM USER SPITMON RESTRICT"
db2 "REVOKE EXECUTE ON FUNCTION SYSPROC.MON_GET_TRANSACTION_LOG FROM USER SPITMON RESTRICT"
db2 "REVOKE EXECUTE ON FUNCTION SYSPROC.MON_GET_HADR FROM USER SPITMON RESTRICT"
db2 "REVOKE EXECUTE ON FUNCTION SYSPROC.MON_GET_PKG_CACHE_STMT FROM USER SPITMON RESTRICT"
db2 "REVOKE EXECUTE ON FUNCTION SYSPROC.MON_GET_CONNECTION FROM USER SPITMON RESTRICT"
db2 "REVOKE EXECUTE ON FUNCTION SYSPROC.MON_GET_APPL_LOCKWAIT FROM USER SPITMON RESTRICT"
db2 connect reset'
  1. Check that no grant is left. It should print OK: no grants.
bash
su - db2inst1 -c 'db2 connect to SAMPLE >/dev/null; db2 -x "SELECT (SELECT COUNT(*) FROM SYSCAT.ROUTINEAUTH WHERE GRANTEE = '"'"'SPITMON'"'"') + (SELECT COUNT(*) FROM SYSCAT.DBAUTH WHERE GRANTEE = '"'"'SPITMON'"'"') FROM SYSIBM.SYSDUMMY1"; db2 connect reset >/dev/null' | grep -qw 0 && echo 'OK: no grants'
  1. On the Db2 server, as root, delete the spitmon OS user. It should print OK: spitmon removed.
bash
userdel spitmon; id spitmon >/dev/null 2>&1 || echo 'OK: spitmon removed'

The script does not change AUTHENTICATION; if you set it to SERVER_ENCRYPT yourself, it can stay. The state file of a run that stopped halfway goes with rm -f ~/.spitfire-setup/db2.state.