Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 8

stats collection stopped


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.


0 Kudos
12 Replies
Level 8

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.

0 Kudos

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.

0 Kudos
Level 12

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?

0 Kudos

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

0 Kudos

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

0 Kudos
Level 11

Have you tried restarting the services for DPA?

0 Kudos

Yes, and it didn't help

0 Kudos

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.

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

0 Kudos

You should be able to run this as the DPA user on the monitored instance if this query completes that will confirm DPA has access to it as required.

0 Kudos

I don't have DPA id's password to login & run the sql. Is there any other way I can verify the access

0 Kudos
Level 8

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

0 Kudos