Db2 Tools for zOS

 View Only
  • 1.  Information Xchange - A performance question

    Posted Tue October 19, 2021 11:36 AM
    What was your most difficult query performance problem, and how did you solve it?

    ------------------------------
    CALENE JANACEK
    ------------------------------

    #Db2Toolsforz/OS


  • 2.  RE: Information Xchange - A performance question

    Posted Tue October 19, 2021 11:39 AM
    We accessed several tables thru a complex view. Thanks to index advisor we found out which index was missing

    ------------------------------
    Martin Ålund
    ------------------------------



  • 3.  RE: Information Xchange - A performance question

    Posted Tue October 19, 2021 11:45 AM

    Nice! Resolving performance problems near and dear to my heart...

    What was the performance improvement and how quickly were you able to respond?



    ------------------------------
    Patrick Bossman
    ------------------------------



  • 4.  RE: Information Xchange - A performance question

    Posted Tue October 19, 2021 11:50 AM
    We went from "not possible to run" to "execution within milliseconds". It took us about an hour to solve the problem. Most of the time spent came from figuring out how to find the problem. When we realised that we could use Data Studio we found and solved the problem in minutes.

    ------------------------------
    Martin Ålund
    ------------------------------



  • 5.  RE: Information Xchange - A performance question

    Posted Tue October 19, 2021 11:55 AM
    So it was a dynamic SQL and you saw it via the statement cache snap interface?

    ------------------------------
    Patrick Bossman
    ------------------------------



  • 6.  RE: Information Xchange - A performance question

    Posted Tue October 19, 2021 12:00 PM
    We manually found out that the view was the problem. But we needed index advisor to find out wihich index was missing. 
    If I remember it right the missing index led to scanning one 80 k row table 500 times for every execution.
    It was dynamic SQL, but we ran index advisor on the view itself.

    ------------------------------
    Martin Ålund
    ------------------------------



  • 7.  RE: Information Xchange - A performance question

    Posted Tue October 19, 2021 02:03 PM
    Yeah, I'm thinking the statement cache display, with sort on accumulated
    CPU was how you found the SQL statement?


    Patrick Bossman

    STSM, DB2 for z/OS Development
    e-mail: bossman@us.ibm.com
    Work phone: 941-870-9821




  • 8.  RE: Information Xchange - A performance question

    IBM Champion
    Posted Wed October 20, 2021 05:37 AM
    Last here we had a performance problem due to a Index issue. That took time to understand since it was on tables and indexes that are not defined/managed by us but from persons of our client. So it took time to rebuild the history of the application and find the issue.

    Other perfomance issue lately we had on IDAA, but we got the support to help us tunning, usually it is enough to get the correct ditribution and organization keys on tables on IDAA.

    Regards Chiara

    ------------------------------
    Chiara Baldan
    ------------------------------