The default sort order used in a IBM i Access data transfer is *HEX. What *HEX means is that the order applied to ordered columns is based on the order of the characters in the code page (CCSID) that the host job is running under. The job is always using an EBCDIC code page so numbers will be after characters and lowercase letters are ahead of uppercase letters.

There are a few options for setting the sort order applied to the result and they are set and modified by clicking "Format Options..." in the main data transfer panel then selecting the "Misc" tab. (See Fig. 1) In the Access Client Solutions data transfer the process is similar, press "Format Options" then click the "Other" tab (See Fig. 2).


Note the Access Client Solutions interface gives you two options that results in the default sort order (IBM i Default and Hexadecimal). The other options are to pick a unique weight table or shared weight table and then select a language. The difference between shared and unique weight is how the case of a letter is treated. In a shared weight table, uppercase and lowercase letters are treated equally so the case of the letters does not affect the order of the sorted rows.  See Fig 4 as an example of how ordering the result on the DATA column and by using a shared weight column can have what look like varying results due to the shared weight sort. A unique weight table assigns different values to uppercase and lowercase letters and typically all uppercase letters are either ahead of all the lowercase letters or vice versa.

Use option 'User Specified Table' or "Unique specified table' in the client to select a specific sort sequence table from a library on IBM i that defines the sort sequence to be used. You can create your own sort sequence but the ones provided for you with the operating system are likely to be all that you ever need.

So where are these tables and how do I know what they are? What I do to see a list of them is to use the command WRKTBL Q* (you could view all the tables but the standard sort sequence tables all start with a Q). That nets you a list of two types of tables, sort sequence tables (what you are looking for, and conversion tables).  The sort sequence tables all start their text description with "SRTSEQ"; the remainder of the description text on the tables explains what type of sequence it has.  Note, there are many that have the same or similar text descriptions. Use option 5 on the WRKTBL command to view the table and you can see how the characters are ordered and weighted for a particular table.  Let me give you some examples.

Our assignment is to sort the results so that numbers ahead of letters as they would be in ASCII, ANSI or Unicode, and uppercase letters are ahead of lowercase letters. Let's try the QSYS/QALA101F4S sort table first; it's a shared weight sort sequence based on ASCII. First step is to set the Sort sequence option to use a specific table and then specify the table (see Fig. 3).



This simple example uses a table that I created with two columns, an integer primary key that is also the order that the data was entered and a character column named DATA. The query is simple: SELECT * FROM MIKSWENS/SORTTEST ORDER BY DATA
Here is the result from using the QALA101F4S sort sequence table:



With this shared weight sort order, the capitalization of letters makes no difference.  We didn't want that result, we want uppercase letters ahead of lowercase so the last two rows of that result are in the wrong order. Try the table QSYS/QBLA101F4U whose description says, "SRTSEQ FOR LATIN 1 UNIQUE REPRESENTING ASCII". The result is shown in Fig 5. Note, the order of the rows now matches what we were looking for.



To put lowercase letters ahead of uppercase letters and numbers ahead of letters is a bit trickier.  You need to pick a Latin 1 sort sequence that is based on a language whose code page puts the lowercase letter ahead of the uppercase, like German.  A quick scan of the available tables shows me one with the text, "SRTSEQ FOR LATIN 1 UNIQUE CCSID 00273" on table QSYS/QLA10111U.  I set that to be my sort table and here's my output:

Creating your own sort sequence table

A circumstance might arise in which none of the existing tables sort the data in the manner required. In that case, it is possible to make your own table. It is likely that there is already a table that is very close to what you need. It is easiest to start by retrieving the source of the one that is close to what you need and modifying your copy.

For my contrived example, I want to sort with a unique weight but sort uppercase letters in ascending order (A-Z) and lowercase letters in descending order (z-a). I start with the existing table, QSYS/QBLA20366U. I want to retrieve the source of this table so I need to have a source physical file to put that into:
CRTSRCPF MIKSWENS/MYTBLSRC

Then, I retrieve the source into a member in that table:
RTVTBLSRC TBL(QSYS/QBLA20366U) SRCFILE(MIKSWENS/MYTBLSRC)

I rename the source member to call it BACKWARDS

If I view the sort sequence (option 5 from the WRKTBL command), the first couple of screens looks like figures 7 and 8, easy to read and understand.




But when we view the source for that sort sequence table, it is anything but simple to comprehend (See Fig. 9).



The key to editing these tables is to grasp that they represent a grid of the code page. Since this sort table represents CCSID 870 (Note, the display of the sort sequence says that in the upper right part of the display), it helps to have a copy of that code page handy too. You can download it in PDF form from here: http://www-01.ibm.com/software/globalization/cp/cp00870.html
This grid is laid out in 8 rows of 32 characters the characters are determined by the row and column position and their order in the sort is the value at that grid position.
So for example look for the lowercase 'a' that has a hex value in CCSID 870 (and almost every EBCDIC CCSID) of 0x81. Each row has 32 characters (0x20 characters) so to quickly find that position, we simply divide 0x81 by 0x20 and get 4 full rows plus 1 position. Look that Fig. 9 and that is the row with sequence number 0000.05 and it would be the second value in that row. The first value is 'F4' so the position in the sort for the letter 'a' in our original sort sequence is '81', 'b' is in sort position '82', and so on. The EBCDIC code page splits the alphabet into 3 parts so a-i are in consecutive positions from 0x81-0x89, j-r are at positions 0x91-0x99, and s-z are at positions 0xA2-0xA9. Those characters are the positions circled in Fig. 10. Once again, those circled values represent the order that the characters a-z has in this sort order. What we want to do is reverse them so that where the value 81 is, we put 9A, where 82 is, we put 99, and so on, until it looks like the Fig. 11





Now I need to compile my new sort sequence table by using the CRTTBL command:
CRTTBL TBL(QSYS/BACKWARDS) SRCFILE(MIKSWENS/MYTBLSRC) TBLTYPE(*SRTSEQ) TEXT('QBLA20366U with a-z reversed')

View this table by using the command WRKTBL BACKWARDS. Use option 5 to display the table. Note in Fig. 12 the order of the lowercase letters is reversed.



Finally, I inserted a few more rows into this small table to prove the effect of this new sort sequence. It still sorts capital letters from A-Z but small letters are sorted in the reverse order as in Fig. 13.