ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

query builds index?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • query builds index?

    Hi All:

    I have a cl program which has the following:

    Code:
    RUNQRY     QRY(NOSTART) QRYFILE((*LIBL/PA107 &DSP) +
                 (*LIBL/MYFILE)) RCDSLT(*NO)
    Today we noticed that every time this program is run (which is at least 75 times/day) there is an index built on myfile. (wrkactjob function = IDX-MYFILE)

    What's going on here? The join of the 2 files is on the key of MYFILE and the sort is on PA107 fields. Why is an index being built?

    Thanks in advance
    GLS
    The problem with quotes on the internet is that it is hard to verify their authenticity.....Abraham Lincoln

  • #2
    Re: query builds index?

    Well, I believe that is the norm.

    It builds it's index in QTEMP and then deletes it when it is done.

    Queries are easy but not overly efficient

    Comment


    • #3
      Re: query builds index?

      Hi All:

      I thought the qry optomizer would select an existing index if one existed for the join. The join in this case is on the keys of MYFILE.

      We had IBM on the phone, They had us run a STRDBMON job with the cl being logged and an alternate qaqqini file. From the job log

      The query optimizer considered all access paths built over
      member MYFILE of file MYFILE in library MYLIB. The list below shows the
      access paths considered.

      Following each access path name in the list is a reason code which explains why the access path was not used.
      MYLIB/MYFILE 6,

      6 - The keys of the access path did not match the fields specified for the join criteria.
      Any thoughts?

      GLS
      The problem with quotes on the internet is that it is hard to verify their authenticity.....Abraham Lincoln

      Comment


      • #4
        Re: query builds index?

        Any chance you could change this to an SQL query ? It will prob run MUCH faster. Also, if you have V5R4, if it builds a temp index, it will keep the temp index maintained and re-usable.

        If not, you could at least run it thru Visual Explain and see whats happening. Evidently it thinks the keys don't match.

        Comment


        • #5
          Re: query builds index?

          Hi Arrow:

          If not, you could at least run it thru Visual Explain and see whats happening. Evidently it thinks the keys don't match.
          As suggested I ran it through visual explain and the optimizer selected the keys from MYLIB.MYFILE.

          I think I'll give IBM a chance and if that takes too long I will use the SQL approach.
          I'm just concerned about the other runqrys in the system

          Thanks for your help

          GLS
          The problem with quotes on the internet is that it is hard to verify their authenticity.....Abraham Lincoln

          Comment


          • #6
            Re: query builds index?

            Makes sense. BTW, what release are you at ?

            Also, be aware that RUNQRY will never use the SQL Query Engine (SQE) with all its optimizations. It will always use the "Classic Query Engine". I've seen as much as 400% improvement moving to SQE.

            Comment


            • #7
              Re: query builds index?

              Hi Arrow:

              We just upgraded from v5r2 to V5r4 this past week end. This particular query is the only one modified since the upgrade and as far as I can tell is the only one failing like this.

              By the way I'm not very familiar with SQL. We only got it with the upgrade. Getting up to speed with is on my short list of to do's. Is there a report option?

              Thanks
              GLS
              The problem with quotes on the internet is that it is hard to verify their authenticity.....Abraham Lincoln

              Comment


              • #8
                Re: query builds index?

                Hi,

                just try the following:
                Before running your query just enter STRDBG (without anything else). This command writes the Joblog you posted before. But there are additional informations, exspecially about the access path to be adviced.

                Example:
                Arrival sequence access was used for file ORDHDR.
                Access path suggestion for file ORDHDR.
                If you look at the detailed message for the access path suggestion (position the cursor on the message and press F1), you see the recommended key fields on page 3 or 4.
                Example:
                Message . . . . : Access path suggestion for file ORDHDR.
                Cause . . . . . : To improve performance the query optimizer is suggesting a
                permanent access path be built with the key fields it is recommending. The
                access path will access records from member ORDHDR of file ORDHDR in library
                MYSCHEMA.
                In the list of key fields that follow, the query optimizer is recommending
                the first 1 key fields as primary key fields. The remaining key fields are
                considered secondary key fields and are listed in order of expected
                selectivity based on this query. Primary key fields are fields that
                significantly reduce the number of keys selected based on the corresponding
                selection predicate. Secondary key fields are fields that may or may not
                significantly reduce the number of keys selected. It is up to the user to
                determine the true selectivity of secondary key fields and to determine
                whether those key fields should be used when creating the access path.
                The query optimizer is able to perform key positioning over any
                combination of the primary key fields, plus one additional secondary key
                field. Therefore it is important that the first secondary key field be the
                most selective secondary key field. The query optimizer will use key
                selection with any remaining secondary key fields. While key selection is
                not as fast as key positioning it can still reduce the number of keys
                selected. Hence, secondary key fields that are fairly selective should be
                included. When building the access path all primary key fields should be
                specified first followed by the secondary key fields which are prioritized
                by selectivity. The following list contains the suggested primary and
                secondary key fields:
                ORDER_DATE.

                If file ORDHDR in library MYSCHEMA is a logical file then the access
                path should be built over member ORDHDR of physical file ORDHDR in library
                MYSCHEMA.
                If you run your query about 75 times a day you should consider to create a permanent access path (either DDS described logical file or SQL index).

                Just create the access path adviced and rerun you query to see if it is used.

                BTW running the SQL statement from iSeries Navigator and analyzing it with Visual Explain, may not return the same results. Query/400 is not an SQL Interface and can only be executed by the Classic Query Engine, while SQL-statements can be executed by the SQL Query Engine.

                In this way analyzing a to SQL translated query that normally runs with Query/400 will not help. The only way to analyse Query/400 queries is to either use STRDBG or a Database Monitor.

                An other way would be to use QMQRY (STRQM) instead of Query/400. QMQRY is an SQL interface, contrary to Query/400.

                BTW autonomic indexes, temporary indexes that stay available under release V5R4, can only be used by SQL-Statements executed by the SQE. For all queries executed by the CQE a temporary indexes are created in each job they will be needed and deleted after execution (at least at job's end).

                Birgitta
                Last edited by B.Hauser; January 26, 2007, 11:23 AM.

                Comment


                • #9
                  Re: query builds index?

                  Hi Birgitta:

                  If you run your query about 75 times a day you should consider to create a permanent access path (either DDS described logical file or SQL index).
                  That is the entire problem. MYFILE has the keys that I need to use but the query isn't using them.

                  I did as you suggested with the debug. The results were as expected .... create a perminate access path.....
                  .....MYLIB/MYFILE 6,......

                  ......6 - The keys of the access path did not match the fields specified for the join criteria........


                  Thanks for the response I was not aware of the debug technique.

                  GLS
                  The problem with quotes on the internet is that it is hard to verify their authenticity.....Abraham Lincoln

                  Comment

                  Working...
                  X