Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

stats collection stopped

Hello,

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.

Thanks.

Find more posts tagged with

Accepted answers

All comments

dpausr20

And, would like to know how DB2 Explain tables are used by DPA. Thanks again

dmartzall

Have you tried restarting the services for DPA?

dpausr20

Yes, and it didn't help

janis.griffin

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

, stmt_start

, stmt_type

, CAST(stmt_text AS BLOB) dynamic_sql_text

, CAST(st.text AS BLOB) static_sql_text

, stmt_partition_number

, (CASE appl_status WHEN 3 THEN stmt_operation * 1000 ELSE appl_status END) waitevent

, ai.agent_id

, appl_name

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.

dpausr20

Thanks. The SQL you gave #2 show current application info, how do you use it to verify DPA's access?

gcp

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?

dpausr20

Yes, all post fixpack steps were done & monitor switches are ON

janis.griffin

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.

Required DB2 permissions needed by DPA for monitoring - SolarWinds Worldwide, LLC. Help and Support

dpausr20

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.

jaminsql

Please open a support case. We can get a look on a meeting and resolve this issue much faster there as we can get logs and help you find the problem.