Db2

 View Only
Expand all | Collapse all

DYNAMICSQL - NTH Data Collection Options Keyword

  • 1.  DYNAMICSQL - NTH Data Collection Options Keyword

    Posted Thu May 25, 2023 12:01 AM

    We use OMEGAMON for DB2 Performance Expert on z/OS to monitor performance but unfortunately DYNAMICSQL is set to NO for NTH which means we're flying blind analysing application SQL problems,

    We're considering having DYNAMICSQL turned on but would like to know if anyone has any overhead metrics for IFCIDs 63 & 350?

    Any information or reference would be appreciated

    Cheers
    Bruce



    ------------------------------
    Bruce Williamson
    ------------------------------


  • 2.  RE: DYNAMICSQL - NTH Data Collection Options Keyword

    IBM Champion
    Posted Thu May 25, 2023 04:30 AM

    Hi Bruce,

    I can imagine it's like Dynamic Statement Cache (DSC). So, activate the trace records IFCIDs 316, 317 and 318. 
    IFCID 316 contains the first 60 bytes of SQL statement plus identifying information and a whole bunch of statistics.
    IFCID 317 is used in addition to IFCID 316 to obtain the full dynamic SQL statement text and the PREPARE attributes string.
    IFCID 318 acts as a switch for IFCID 316 to enable the collection of statistics for the dynamic SQL statement in the statement cache. It can be used to stop and start the statistics collection process.
    Note: the CPU overhead is low when the three IFCIDs are activated and should not affect the volume of SMF you are collecting.



    ------------------------------
    Guillaume VENTRE
    IT Specialist IBM Z
    Sopra Banking Software
    Paris, France
    ------------------------------

    Original Message