IBM i Global

 View Only

  • 1.  Access path for VIEWS and INDEXES

    Posted Wed December 06, 2023 10:58 AM

    I should know this, but I cannot find the document to tell me. Searching the internet is like trying to find the right haystack among the torrent of needles. :) 

    I was discussing VIEWS with a colleague recently and they asked me about the impact on the system for a VIEW or an INDEX. With a standard DDS logical file, we have the MAINT keyword to specify the timing.  When I tested changing that value for an INDEX or a VIEW, I get CPD3201 telling me hands off. I need to be able to answer the question with chapter and verse from the IBM documentation.

    This comes in to play especially when we load data from production to test for large files. 

      



    ------------------------------
    David Taylor
    ------------------------------


  • 2.  RE: Access path for VIEWS and INDEXES

    Posted Wed December 06, 2023 11:52 AM

    Hi David,

    I am not aware of any IBM documentation that answers that question. There is some reference to access path maintenance and rebuilding here:

    DatabasePerformance and Query Optimization (ibm.com) --> p. 211-212,

    but nothing that clearly states which is which.

    Is the loading performed through a restore? If yes, you can restore the physical file first and then any dependent logicals. 

    On a side note, for large files, it may be useful to control when table statistics are created. On that topic, you can find information here:

    Best regards,

    Kurt Thomas



    ------------------------------
    Kurt Thomas
    ------------------------------

    Original Message


  • 3.  RE: Access path for VIEWS and INDEXES

    Posted Wed December 06, 2023 12:04 PM

    I will look into the docs, @Kurt Thomas.  The in-house tool we use knows to set the logicals to *DLY and reset them after the load is done. 



    ------------------------------
    David Taylor
    ------------------------------

    Original Message


  • 4.  RE: Access path for VIEWS and INDEXES

    Posted Thu December 07, 2023 04:07 PM

    Hi David,

    If you are trying to restore production files into TEST environment, with SQL logical files (Views) then after you restore the physical file, you have to re-build the SQL views. I had the similar issue long time back when I found out that the SQL views are still pointing to the Production Physical files.

    This is different from DDS Logical files, after you restore the physical files, if you restore the logical files in the same library (Assuming TEST) as the physical files then it should be fine, but if any of the DDS logical file goes to other library than the Physical file than you need to rebuild the DDS logical files as well.

    I hope this helps.

    Regards

    Zahir Ahmed



    ------------------------------
    Zahir Ahmed
    ERP Analyst
    City of Lacey
    WA
    ------------------------------

    Original Message


  • 5.  RE: Access path for VIEWS and INDEXES

    IBM Champion
    Posted Wed December 06, 2023 10:59 PM
    Edited by Satid Singkorapoom Wed December 06, 2023 11:01 PM

    Dear David

    When you load data into large physical files (test or not) on which a lot of indexes exist and you care about performance of the data loading process, you can choose to do one of these :

    1. Remove all indexes of those large physical files (views are of little performance concern), load the data, and then recreate the indexes.  This approach is proper for the case where you know the overall disk response time of your server is particularly bad during the data loading process - you look at PDI chart on disk response time to know this.
    2. If your disk response time is still good during the data loading process but you want faster index build time, you can leave all indexes intact and use DB2 SMP to speed up index rebuild process during the data loading process.  ( SMP as Related to Index Building at https://www.ibm.com/support/pages/smp-related-index-building )  I hope you are aware DB2 SMP is now free of charge for all IBM i 7.x customers with active SWMA. 
    3. You can use approach 1 but also utilize DB2 SMP during index recreation. 

    For approach 2 more than approach 3, you need to have good disk HW performance because parallel index build produces more disk IO workload than non-parallel one. 

    Nowadays, if you use Power System with all SSD/NVMe/Flash Disk (NO spinning disk at all), the concern you raised tends not to be a big one any more. 



    ------------------------------
    Chance favors only the prepared mind.
    -- Louis Pasteur
    ------------------------------
    Satid S.
    ------------------------------

    Original Message


  • 6.  RE: Access path for VIEWS and INDEXES

    Posted Thu December 07, 2023 02:43 AM

    Hi David

    if you are talking about SQL Views they don't have an access path. A view is only executed at runtime and will choose the appropriate access path on the LF/Indexes available.



    ------------------------------
    Juan Manuel Alcudia
    CD-Invest
    IBM Champion
    President of Common Europe
    ------------------------------

    Original Message


  • 7.  RE: Access path for VIEWS and INDEXES

    Posted Thu December 07, 2023 05:34 AM

    SQL views are logical data set definitions so they don't contain any data.  Thus, there's nothing to maintain.  An SQL View is equivalent to a non-keyed logical file.



    ------------------------------
    Kent Milligan
    ------------------------------

    Original Message


  • 8.  RE: Access path for VIEWS and INDEXES

    Posted Thu December 07, 2023 04:09 PM

    A VIEW is not identical to a LF and shouldn't have any impact.
    See Simon Hutchinson's blog: https://www.rpgpgm.com/2015/09/build-views-and-views-of-views.html



    ------------------------------
    Anton Gombkötö
    ------------------------------

    Original Message