IBM i Global

 View Only

  • 1.  It's been asked and answer before, "How to reuse sql plan cache snapshot on IBM i?"

    Posted Fri September 27, 2024 11:29 AM

    It's been asked and answer before, "How to reuse sql plan cache snapshot on IBM i?".  Our "Plan cache hit ratio is 83%" and we are putting a CUM on this weekend.  Somebody tell me I can recover Plan cache without manually creating every index that has been recommended, please.



    ------------------------------
    michael woodard
    ------------------------------


  • 2.  RE: It's been asked and answer before, "How to reuse sql plan cache snapshot on IBM i?"
    Best Answer

    Posted Fri September 27, 2024 10:39 PM
    Edited by michael woodard Mon September 30, 2024 06:19 PM

    Dear Michael

    It would be better if you try to explain goal(s) that you want to achieve when you mentioned "reuse".   Db2 for i makes a decision to "reuse" an access plan in the Plan Cache automatically by itself.   There is no manual way for programmer to do this.   Do you mean a "reuse" by human? If so, for what purpose?

    >>>> Somebody tell me I can recover Plan cache without manually creating every index that has been recommended, please.  <<<<

    You have or remember an inaccurate information. 

    Each index that you create is a "permanent" one until you explicitly delete it.   I suspect that you may be thinking about "Autonomic Indexes" (MTI - Maintained Temp Index) created by DB2 for i but they do not exist as permanent logical file objects.  

    After you apply the cumulative PTF package, a system IPL will clean away all the entries in Plan Cache and all MTIs.  After the IPL, Plan Cache content will start from scratch to build up one by one as well as MTIs.    For Plan Cache content, you can dump its snapshot image out (with QSYS2.DUMP_PLAN_CACHE procedure) before the IPL ( https://www.ibm.com/docs/en/i/7.4?topic=cache-creating-snapshots ) but this is for the purpose of the ability to look into past plan cache entries for analysis by human (Is this what you meant by "reuse"?), NOT for DB2 for i to "reuse" it after IPL. 

    As for MTIs, you should identify them and create them as permanent indexes before the IPL.  Scott Forstie provides a utility for this here:  https://www.ibm.com/support/pages/node/6347648   (search for "mti" in the page).    Some of my IBM i customers who have a lot of SQL/Queries workload in their systems, but "unknowingly" have very many MTIs because they create very few, if at all, advised indexes, encounter abnormally slow performance after an IPL because all MTIs  are gone and need time to be re-created as the workload run.  Is this a situation that you want to avoid?



    ------------------------------
    Satid S
    ------------------------------

    Original Message


  • 3.  RE: It's been asked and answer before, "How to reuse sql plan cache snapshot on IBM i?"

    Posted Mon September 30, 2024 04:05 AM

    Hello Michael,

    When starting to create permanent indexes based on the MTIs found. Please determine upfront where to create them. If you have an in-house build application, you might as well create them in the same library/schema as the tables are resident in. If however you have a third party application running, my advice would be to create a separate library/schema for them. The reason for this is that your ISV is unaware of these indexes and might run into trouble when them are doing a database table adjustment. If you have physical files defined, those indexes are more likely a stand in the way. Once you have them in a separate library/schema the ACS Schema option allows you tp reverse engineer the entire schema by creating a script to recreate them. So if ISV database maintenance is planned, simply delete the schema, after you have created that script.

    There are also applications which allow you to add your own indexes based on a MTI to their database as a permanent one. I know SAP allows you to do that. 

    Hope this helps you in dealing with MTIs in general. 

    Greetings, 



    ------------------------------
    Rudi Van Helvoirt
    ------------------------------

    Original Message


  • 4.  RE: It's been asked and answer before, "How to reuse sql plan cache snapshot on IBM i?"

    Posted Mon September 30, 2024 06:26 PM

    Thanks for your replies Satid and Rudi.  Forgive my novice's question. 



    ------------------------------
    michael woodard
    ------------------------------

    Original Message


  • 5.  RE: It's been asked and answer before, "How to reuse sql plan cache snapshot on IBM i?"

    Posted Tue October 01, 2024 02:14 AM

    Michael, some info about PC ... 

    Snapshot Scheduling New Year's Resolution?

    https://db2ibmi.blogspot.com/2023/01/snapshot-scheduling-new-years-resolution.html

    The Amazing & Disappearing Plan Cache

    https://db2ibmi.blogspot.com/2023/06/the-amazing-disappearing-plan-cache.html

    We all ask for the first time at some point



    ------------------------------
    Fernando Plaza
    IBM i System Administrator
    CD INVEST
    MADRID
    +34637503273
    ------------------------------

    Original Message


  • 6.  RE: It's been asked and answer before, "How to reuse sql plan cache snapshot on IBM i?"

    Posted Wed October 02, 2024 09:01 AM
    Edited by Rich Malloy Wed October 02, 2024 09:05 AM

    Hello Michael. If I may throw my 2 cents in.....Generally speaking, it is best to do a dump of the plan cache before doing a CUME or DB Related PTFs. Depending on the size of the system, the dump can run awhile and its best to do it while there is little to no activity on the system. As an example, we dump the plan cache before we do our CUME installs while in a restricted state and it takes aprox 10-15 minutes to complete. The PC Dump is very useful and will be requested from the IBM Perf Support team if, after the CUME/PTFs, you complain that queries are now running slower. It is also useful to you if you want to dive into the plan cache and do any analysis for which indexes to create etc. I also suggest, using the MTI_INFO SQL Service -> MTI_INFO table function - IBM Documentation.  This has a lot of useful info and you can wrap this around a create or replace table statement if you want to consume the results into a perm table. This will also show you how many times the MTI has been referenced, which job caused the MTI to be created etc..In short, it has a lot of great info that you could use to create indexes from this information. Hope this helps and good luck - Rich

    P.S - While not directly related to your question, you may also find this view helpful to monitor the overall size of MTIs on the system, ->  SYSTMPSTG view - IBM Documentation
    Bucket number 14 will show you the overall size of the MTIs that are on your system, other buckets are very useful as well and can help identify storage leaks. 



    ------------------------------
    Rich Malloy
    ------------------------------

    Original Message