Global Data Management Forum

Data Management Global

A hub for collaboration, learning, networking, and cultural exchange, and contributing to positive global engagement

 View Only

  • 1.  assistance with SQL statements and ASC

    Posted 09/19/24 04:02 PM
    Hello,
    I was wondering if someone can help me out with a SQL scripts issue.
    My qustion is:
    Exampel -
    I have a phisical file - F_Name
    there are 2 LF with diffrent keys each.
    If i have a SQL script (of course the select statment is directed the PF.)
    The statement is missing the correct approach to the file because there is no LF with the keys needed.
    there is few options:
    1 - let the machine create the "LF" on the fly in the machine (in the memory) and the query will run with the keys it needs
    2 - With the ASC, check what is the LF that should be created to make the query (SQL statement) runs in the optimal way
    3 - Change the SQL statement and add in the reading part of the statement, more fields that it will use an existing LF (one of the 2 in this example)
     
    My qustion is:
    Like in the feature of the ASC that recomend what LF should be create to make the query run's in the best way, 
    Is there a feature in the ASC that recomend what field/s to add to the SQL statment based on the existing LF?
     
     
    I realy hope I explained myself and you can understand me.
     
    My best regards


    ------------------------------
    Moshe Stein
    ------------------------------


  • 2.  RE: assistance with SQL statements and ASC

    Posted 09/22/24 05:10 AM
    1. SQL does not always use an Access Path (Index or logical file). A indes will only be used if less than ~ 15$ of the data is selected
    2. SQL can use (and quite often will use) an index with a key that does not match the final select. First goal is to return the data as fast as possible. So it might be faster to gather the result in a temporary object and finally sort the data in the temporary object before returning them
    3. A MTI (Maintained Temporary Index) is only generated if there is no other way to run the query. MTIs are quasi permanent, i.e. the keep available until the last Access Plan that uses this MTI disappears from the Plan Cache (As an asside the Plan Cache is completely cleared if an IPL is performed. I you want to a void the creating and recreating of the MTIs (which is as time consuming as creating a permanent index), you should create permanent indexes for the MTIs
    4. The query optimizer will save index advices in the SYSIXADV. If you think you need one of the indexes you can create it, rerun your query and check if the index is used of not.

    Otherwise it depends. Without seeing the query (and know you date) it is hard to propose an index.

    Also SQL Performance Analysis is a little more complex, because there are a lot of criteria which can affect the optimization.

    You may read the following White Paper: Indexing and Statistics Stragegies by Kent Milligan and Mike Caine

     https://www.ibm.com/support/pages/system/files/inline-files/Indexing%20and%20Statistics_3.pdf

    ... and you should no longer create DDS described logical files, but SQL Indexes.  BTW an SQL Index cannot be specified in an SQL statement, but used in composition with native I/O like any keyed logical file.



    ------------------------------
    Birgitta Hauser
    Database and Software Engineer
    Birgitta Hauser - Modernization-Education-Consulting on IBM i (selfemployed)
    Kaufering
    +49 170 5269964
    ------------------------------



Related Content