Topic
  • 2 replies
  • Latest Post - ‏2013-06-01T08:04:07Z by B.Hauser
good_idea
good_idea
1 Post

Pinned topic Order By RRN() does not work every time

‏2013-05-31T15:15:23Z |

I'm having a problem using the SQL  RRN() scalar function in the Order By clause of a SQL Server Select statement.

The SQL Server puts a request "Select * from Logicalfile where .... Order By RRN(Logicalfile)" to the IBM i.

But the record headers that are in RRN order always before the record detail, sometimes get picked up after the record detail is (contrary to the Order By RRN() displaying on the IBM i ).

This happens usually once a night where DB monitor shows a ID code=3002 of deciding it's own access path.

The logicalfile is not needed as the SQL statement handles the full selection in the Where clause. In Interactive SQL, using Order By RRN(logicalfile) or RRN(physicalfile) always returns RRN() sort order. I don't know if going an extra step of adding a pseudo-column of RRN() to the SQL statement and then Order By on that pseudo-column? The Order By RRN() theoretically should work 100% of the time.

My IBM i  is at v7r1.

  • krmilligan
    krmilligan
    450 Posts

    Re: Order By RRN() does not work every time

    ‏2013-05-31T16:30:49Z  

    If you have reproducible cases where the ORDER BY is not being honored, then you should probably open a PMR with IBM Support.  Before doing that, I would try loading the latest 7.1 Database Group PTF onto your system.

    I'm not a big fan of using the RRN function to sort data.  I'd suggest looking at the Row_Number expression introduced with the V5R4 release - http://ibm.com/systems/resources/systems_i_software_db2_pdf_rcte_olap.pdf

    In general, it's not a good idea to reference logical files on the FROM clause so I'd also consider changing the statement to reference the physical file.

     

     

  • B.Hauser
    B.Hauser
    281 Posts

    Re: Order By RRN() does not work every time

    ‏2013-06-01T08:04:07Z  

    If you have reproducible cases where the ORDER BY is not being honored, then you should probably open a PMR with IBM Support.  Before doing that, I would try loading the latest 7.1 Database Group PTF onto your system.

    I'm not a big fan of using the RRN function to sort data.  I'd suggest looking at the Row_Number expression introduced with the V5R4 release - http://ibm.com/systems/resources/systems_i_software_db2_pdf_rcte_olap.pdf

    In general, it's not a good idea to reference logical files on the FROM clause so I'd also consider changing the statement to reference the physical file.

     

     

    >> I'd also consider changing the statement to reference the physical file.

    Or ... if your logical file contains join and/or SELECT/OMIT clauses, create an SQL view including all joins and SELECT/OMIT clauses translated to WHERE conditions. You may also consider to add additional columns containing the relative record no of the based physical files/tables. Then use the view instead of the DDS described logical file within your SELECT statement.

    ... also I'm not sure what "RRN" is returned for the logical file, since only the based phyiscal files/tables can have a real RRN. IMHO RRN for a logical file will return only a running no based on the access paths used when exeuting the query.

    Birgitta