• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    ARCAD Software

    Embrace VS Code for IBM i Development

    The IBM i development landscape is evolving with modern tools that enhance efficiency and collaboration. Ready to make the move to VS Code for IBM i?

    Join us for this webinar where we’ll showcase how VS Code can serve as a powerful editor for native IBM i code and explore the essential extensions that make it possible.

    In this session, you’ll discover:

    • How ARCAD’s integration with VS Code provides deep metadata insights, allowing developers to assess the impact of their changes upfront.
    • The role of Git in enabling seamless collaboration between developers using tools like SEU, RDi, and VS Code.
    • Powerful extensions for code quality, security, impact analysis, smart build, and automated RPG conversion to Free Form.
    • How non-IBM i developers can now contribute to IBM i projects without prior knowledge of its specifics, while ensuring full control over their changes.

    The future of IBM i development is here. Let ARCAD be your guide!

    Watch Now

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    PeopleSoft Announces RFID Software As Target Issues Mandate IBM Unveils iSeries.mySeries Marketing Campaign

    Leave a Reply Cancel reply

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle