DB2 - L

 View Only

  • 1.  DYNAMICSQL - NTH Data Collection Options

    Posted May 25, 2023 12:08 AM

    Hi Folks

    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
    Commonwealth Bank of Australia
    ------------------------------


  • 2.  RE: DYNAMICSQL - NTH Data Collection Options

    Posted May 26, 2023 07:18 AM
    Edited by Christoph Theisen May 26, 2023 07:40 AM

    Hello Bruce, 
    sorry for a typical "it depends" answer. From my perspective it's not the IFCID 63 record itself that may cause that much overhead. You need to consider that every dynamic SQL statement would cause a trace record be written to the OP buffer and read by OMEGAMON Db2 PE's near term history collector. Then OMPE would store the information from these records in the NTH's VSAM data sets. When you have many dynamic SQL calls per transaction or per accounting record the NTH VSAMs could fill up quicker than expected. The period of time available for NTH analysis would be shortened in that case. OMPE has a helpful menu (option H.C.B in Classic UI) which displays the distribution on NTH records across the different record types. This can give you an indication about the impact of dynamic SQL records on your NTH range.

    Kind regards
    Christoph



    ------------------------------
    Christoph Theisen
    Rocket Software Inc.
    ------------------------------

    Original Message