• 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
    Racksquared

    It’s time for IBM Power in the Cloud!

    Stop buying hardware and make the move to the cloud. It’s easier and more cost effective than you might think.

    • IBM Power in the Cloud
    • IBM Power Backup Solutions
    • IBM Power High Availability and DR solutions
    • IBM Power Colocation with Management and Monitoring

    Let’s talk about your business needs.

    Call: 855-380-7225
    Email: Sales@racksquared.com

    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

  • IBM Tweaks Some Power Systems Prices Down, Others Up
  • Disaster Recovery: From OS/400 V5R3 To IBM i 7.4 In 36 Hours
  • The Disconnect In Modernization Planning And Execution
  • Superior Support: One Of The Reasons You Pay The Power Systems Premium
  • IBM i PTF Guide, Volume 25, Number 13
  • IBM i Has a Future ‘If Kept Up To Date,’ IDC Says
  • When You Need Us, We Are Ready To Do Grunt Work
  • Generative AI: Coming to an ERP Near You
  • Four Hundred Monitor, March 22
  • IBM i PTF Guide, Volume 25, Number 12

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 © 2023 IT Jungle