Db2

  • 1.  Very expensive query at DMC startup

    IBM Champion
    Posted Tue August 25, 2020 09:51 AM
    When DMC connects to a database for the first time, we can see it issuing the attached query.  This performs EXTREMELY poorly on systems with large numbers of tables (1000+ tables), and consumes a LOT of CPU.
    In a customer environment where there were 1,475 tables in the monitored database, I watched this query execute for over 950 seconds, consuming 923 seconds of CPU time while this was running.  After 1000 seconds (nearly 22 minutes) we shut down DMC so that the query would stop.  This database was on a server with 4 CPUs, and nearly 1 entire CPU was being consumed to process this query.  DSM was configured using the default monitoring profile for the database.
    I was able to reproduce this behavior on a test database by creating 1200 empty tables.  On this system, the query executed for roughly 275 seconds before returning.
    Is there an open defect for this?  I can't imagine how any customer could be running DMC in production when it introduces this much load onto the monitored system.
    How often does the attached query get executed against a monitored database?


    ------------------------------
    Ian Bjorhovde
    XTIVIA
    Phoenix AZ
    ------------------------------

    #Db2


  • 2.  RE: Very expensive query at DMC startup

    Posted Tue August 25, 2020 06:41 PM
    Hello Ian,

    Thank you for your observation.  The query attached is calling the SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO() for compression info during DMC startup.  I see that this function is taking a lot of time to execute.  We are following up and see if it is normal for high resource consumption for using this function and take remedy on how this query should be called.  

    Sincere regards,


    ------------------------------
    Jason Sizto
    ------------------------------