Topic
5 replies Latest Post - ‏2014-03-17T11:42:50Z by EarthDog
EarthDog
EarthDog
16 Posts
ACCEPTED ANSWER

Pinned topic Default sort sequence for library?

‏2014-03-15T14:42:45Z |

Hi All!

I am having a problem as i outline here: https://www.ibm.com/developerworks/community/forums/html/topic?id=626500cb-d6e3-459b-acb2-c4bfd1ab8d79&ps=25

I have 4 AS400s running JDEdwards.

Lately a performance issue occured as described above

Although i am not an expert in AS400 , i tried to find out the cause. I think it came down to this:

I run the following SQL in all my systems:

select sort_sequence,index_schema,count(*) from qsys2.sysindexstat group by sort_sequence,index_schema

In 3 of my 4 systems all of them have sort_sequence="BY HEX VALUE"

In my 4th problematic system many indexes have sort_sequence="*LANGIDSHR"

I start to believe that this can cause performance problems as the query optimizer may leave these indexes unused and create temp indexes.

I wish somebody has an idea on where to go on from now and how.

Is there any specific setting that make all the indexes have default sort_sequence?

All my indexes are created massively so this could not happen by mistake.

Updated on 2014-03-15T15:37:15Z at 2014-03-15T15:37:15Z by EarthDog
  • B.Hauser
    B.Hauser
    250 Posts
    ACCEPTED ANSWER

    Re: Default sort sequence for library?

    ‏2014-03-16T10:20:02Z  in response to EarthDog

    The sort sequence to be used is set on job level.

    It can be set explicitly for a program with embedded SQL (at compile time). When creating a SQL routine (Stored Procedure, User Defined (Table) Function, Trigger)  the sort sequence to be used at compile time can be predefined with the CREATE command.

    Birgitta

    • EarthDog
      EarthDog
      16 Posts
      ACCEPTED ANSWER

      Re: Default sort sequence for library?

      ‏2014-03-16T11:49:54Z  in response to B.Hauser

      As i said in the other post i discovered that ODBC settings in my fat clients for the specofoc AS400 installtion is NOT HEX..In all other 3 AS400s FAT clients ODBC setting is *HEX.

      I changed the ODBC setting in one FAT , recreate the index (it was created with *HEX) and one SQL statement that was slow , now was ok....

      I thiknk i have found it but i need to see why this installation does not have *HEX as default setting..

      Thanks!

      • tomliotta
        tomliotta
        38 Posts
        ACCEPTED ANSWER

        Re: Default sort sequence for library?

        ‏2014-03-17T11:39:12Z  in response to EarthDog

        I thiknk i have found it but i need to see why this installation does not have *HEX as default setting..

        What you would need to find is why the connection that was used to create the index did not have *HEX as its connection attribute. You would need to go back to the connection that create the *LANGIDSHR index(es). Most likely, there is no way to go back to determine that.

        Tom

        • EarthDog
          EarthDog
          16 Posts
          ACCEPTED ANSWER

          Re: Default sort sequence for library?

          ‏2014-03-17T11:42:50Z  in response to tomliotta

          There is and i have found it.

          JDEDwards deployment server is the server that is responsible for the object repository and package distribution to the clients.

          There is an ODBCSettings.inf file that servers as a template to the clients.

          In this file the sortsequence odbc property was incorrectly set to 2 instead of 0 (*HEX).

          My problem now is rather internal as i want to find out who did it and when. :)

  • EarthDog
    EarthDog
    16 Posts
    ACCEPTED ANSWER

    Re: Default sort sequence for library?

    ‏2014-03-17T11:04:48Z  in response to EarthDog

    http://dba.stackexchange.com/a/61060/7930