Practical Use of a Translation Table

March 3, 2004 Michael Sansoterra

While working on a Microsoft Access 97 project, I was purging and redownloading a large amount of data every night from an iSeries. I noticed that once the data was downloaded, Access took a while to query the data even though there were proper indexes built on the table.

Some investigation revealed that Access was actually doing a large amount of re-sorting of the data because of the difference in the EBCDIC and ASCII collating sequences. In particular, EBCDIC places numerals at the end of the sort order; whereas, ASCII places them toward the beginning. So Access still had to shuffle all of the numerals from the end of the table back to the beginning. For some reason the indexes weren’t helping.

The workaround was simply to download the data into Access in ASCII order. To accomplish this, I built an index on the iSeries item master table, using the QASCII translation table. This translation table specifies that the index will sort in ASCII order, rather than EBCDIC. To implement this solution I had to do the following:

• Set the sort sequence of my SQL session to use the QASCII translation table.

• Create an index, using the CREATE INDEX statement. The sort sequence of the SQL session determines which translation table, if any, the index is built with.

• Establish the ODBC connection with the sort sequence set to use the QASCII translation table. Don’t forget that the index and the SQL session must both be using the same translation table in order to be effective.

• Select data from the table with an ORDER BY that matched the columns in the index.

• Keep in mind that the sort sequence will be in use for every SQL statement executed under the connection. In this case, other queries that run under the same connection would also look for indexes built with the QASCII translation table.

This technique allowed the download routine to pull down data in the same order in which Access needed it: numbers first, followed by letters. One word of caution: The sort table QASCII may not sort all special characters in the same order that Access will.

RPG can make use of translation tables as well. If, for instance, you had an RPG application that needed to do ASCII-based comparisons with numerals coming before characters, you could simply code the following in the H-specs:

H SrtSeq(‘QASCII’) AltSeq(*Ext)

With this sort sequence specified, the following IF will evaluate as true:

C/Free
    Item=’ABC’;
    // The IF will succeed using the ASCII collating sequence
    // it will fail using the EBCDIC collating sequence
    If Item>’999’;
        ...process

Michael Sansoterra is a programmer/analyst for SilverLake Resources, an IT services firm based in Grand Rapids, Michigan. E-mail: msansoterra@silver-lake.com