12 Replies Latest reply on Jun 17, 2016 3:25 PM by jaminsql

    stats collection stopped

    NN G


      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.



        • Re: stats collection stopped
          NN G

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

          • Re: stats collection stopped

            Have you tried restarting the services for DPA?

              • Re: stats collection stopped
                NN G

                Yes, and it didn't help

                  • Re: stats collection stopped

                    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.

                • Re: stats collection stopped

                  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?

                  • Re: stats collection stopped
                    NN G

                    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.