• 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
    DRV Technologies, Inc.

    Get More from Your IBM i

    Many users today struggle to get at the data they need on the IBM i. When users get reports, they look like they were formatted some time last century.

    Some organizations are still printing pre-printed forms and checks on impact printers.

    How often do operators log on to their system to look for messages they hope they don’t find?

    All of these scenarios can affect users’ perception of the IBM platform negatively, but there are simple solutions.

    DRV Technologies Inc. develops innovative solutions that help customers get more from their IBM i systems.

    Solutions include:

    • SpoolFlex spool conversion & distribution
    • FormFlex electronic forms
    • SecureChex MICR laser check printing
    • MessageFlex system monitoring

    FlexTools streamline resources, improve efficiency and enable pro-active system management.

    Better software, better service, DRV Tech.

    Learn how you can get more from your IBM i at www.drvtech.com

    Call 866 378-3366 for a Free Demonstration

    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

Volume 4, Number 7 -- March 3, 2004
THIS ISSUE
SPONSORED BY:

T.L. Ashford
Guild Companies
Client Server Development
WorksRight Sofware
COMMON

Table of Contents

  • Sleep Soundly with Hibernate
  • Practical Use of a Translation Table
  • OS/400 Admin Alert: Five Quick PC5250 Tricks for Administrators
  • OS/400 Alert: Have You Been Snarfed?

Content archive

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

Recent Posts

  • LANSA Developing Business Intelligence Tool
  • Blazing The Trail For VTL In The Cloud
  • Data De-Dupe Gives VTL Customers More Options
  • Four Hundred Monitor, March 29
  • The Big Spending On IT Security Is Only Going To Get Bigger
  • 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

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