We recently went through DB2 LUW database fixpack upgrade from 10.1 fp2 to 10.1 fp5 without stopping DPA monitoring on the database (it's quick restart of db2 instance - took about 20 seconds, so, didn't bother to stop DPA). After the upgrade, DPA tool is not collecting details of SQLs or stats data on the database. While I can drop and recreate monitoring (which, I think, will work), I am trying to find root cause so that I can include necessary steps in future upgrade.
I think I wasn't specific on the issue. This is what is missing after the fixpack upgrade:
on "Advice for SQL <sql-hash-value>", after the "Supporting Data" table, there used to be a table which displays 3 columns:
Statistics, Value, per Execution
I am trying to get back this table.
Just checking you've done all the post-fixpack steps? Assuming linux - db2iupdt instance, db2updv10 -d dbname, and rebinds?
Might be worth verifying the monitor switches haven't changed after the FP install and restart (e.g. DFT_MON_STMT, DFT_MON_UOW etc).
Yeah, I know it shouldn't happen, but maybe there was a deferred dbm cfg update that was applied on the restart?
To run the query listed, you will need to log in as the DPA monitoring user. If you have admin rights in the DPA application, you should be able to connect to that account via Options > Support > Database Query Tool. If you don't know the password, you'll need to ask the DBA (who knows it) to run the query under this user. Or make sure the DPA monitoring user has the permissions listed in this KB article.
A couple of things to check after the upgrade...
1. Make sure the monitoring tables are still there and accessible. If not, you may need to drop / recreate them.
2. Make sure DPA has access to them by manually running the following query:
SELECT auth_id igniteex
, CAST(stmt_text AS BLOB) dynamic_sql_text
, CAST(st.text AS BLOB) static_sql_text
, (CASE appl_status WHEN 3 THEN stmt_operation * 1000 ELSE appl_status END) waitevent
FROM table(snapshot_appl_info('', -2)) AS ai
, table(snapshot_statement('', -2)) AS s
LEFT OUTER JOIN syscat.statements st
ON (st.pkgname = s.package_name
AND st.sectno = s.section_number)
WHERE s.agent_id = ai.agent_id
ORDER BY agent_id, stmt_start DESC, stmt_type;
If neither of these options work for you, you may need to contact support.
DPA uses the explain tables in DB2 to perform live plans on SQL Statements collected. If you drill down into a SQL Statement in DPA, it is possible to issue an explain plan for that specific query real time in the monitored database. When DPA starts monitoring, explain tables are checked against the DB2 version to make sure they are up-to-date and compliant with that version. If DPA finds they are not up-to-date, DPA attempts to drop and recreate the tables for that version. If you see DPA trying to drop/recreate the explain tables (which fail) in DPA logs, you may want to recreate the explain tables manually using DB2's stored procedure for your current version instead. There is a current defect being worked on dealing with the creation of the explain tables after an upgrade.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.