Problem solve Get help with specific problems with your technologies, process and projects.

Special features make creating tables simple

With the help of prompting and basing table support, creating conversion tables and sort sequence tables is now a very intuitive process.

Ron Turull

With the help of prompting and basing table support, creating conversion tables and sort sequence tables is now a very intuitive process. No longer must you count your finger along the SEU screen making sure your hex codes are in the correct position. 

 More Information
  • Create tables with SQL
  • Use the SQL Rename command  to make everyone happy 
  • Sorting tables fields containinng Polish characters

Tables are used extensively on the AS/400-iSeries. Although most of their use is under the covers, there are many instances in everyday programming when tables -- both conversion tables and sort sequence tables -- are very handy tools.

Conversion tables

A conversion table is used to convert bytes of data from one encoding scheme to another, for example EBCDIC to ASCII.

You might want to use a conversion table to change mixed-case data to all upper-case before sorting the data. For example, suppose a physical file contains a last-name field and a first-name field. The data in these fields is stored in mixed-case. Using the default sorting sequence of the AS/400-iSeries, "Mackintosh" would be sorted before "MacDonald" because a lowcase k has a lower sort sequence than an uppercase D. To correct this, the data in the last-name and first-name fields can be put through a conversion table to convert the data in the fields to all upper-case before the sorting is performed.

Sort sequence tables

Sort sequence tables are used to alter the normal sequencing used when sorting data. For example, an apostrophe (' or hex 7D) has a lower sort sequence value than any of the letters in the English alphabet (i.e., apostrophes are sorted before any of the letters). If you want apostrophes to appear after the letters, you create an appropriate sort sequence table and specify it when using a sorting function.

How to use the special features to create tables

The two special features -- prompting and basing tables -- are really part of the same thing. That is, basing tables come into play only when you request prompting while creating a table. Prompting allows you to easily specify the table specifications interactively. When you request prompting, the initial values for the prompt screens are based on the basing table you specify.

Figure 1 shows the prompted Create Table command (CRTTBL). To use prompting support, specify *PROMPT for the Source file parameter (as shown). When you press Enter, new parameters will appear where you fill in the basing table information (the actual parameters that appear are dependent on the value you specify for the Table type parameter).

Figure 1:CRTTBL command

After you fill in the basing table information and press Enter again, the interactive prompt screen will appear. Again, depending on the value you specify for the Table type parameter, you will be presented with either the Create Conversion Table prompt screen or the Create Sort Sequence prompt screen, both of which are shown in Figure 2 and Figure 3, respectively.

Figure 2:Create Conversion Table prompt screen

Figure 3:Create Sort Sequence prompt screen

Both of these prompt screens are fairly self-explanatory. On the Create Conversion Table prompt screen, simply specify the values in the Hex Output column you want the corresponding Hex Input values converted to. On the Create Sort Sequence prompt screen, arrange the characters in the desired sequence by specifying values in the Sequence column (similar to sequencing fields in a Query/400 query).

The *LANGIDSHR and *LANGIDUNQ special values

The basing table parameters have a few special values. Instead of building a new table based on an existing table, you can specify one of the special values to supply the prompt screen with preset defaults.

Of particular interest is the *LANGIDSHR special value valid for the Basing sort sequence parameter. This value sets the defaults for the prompt screen to a sort sequence that ignores the case of letters. That is, both the lowercase a and the uppercase A -- as well as all special non-English characters that resemble an a, such as a's with inflection marks have the same sequence number. This is an alternative method to the mixed-case sorting solution described above for the MacDonald/Mackintosh problem.

The *LANGIDUNQ special value is similar to *LANGIDSHR except every character is given a unique sequence number. All the a-like characters are still grouped together, so they are all sorted before any of the other letters, upper or lowercase.

The difference between *LANGIDSHR and *LANGIDUNQ is that with *LANGIDSHR, the system does not distinguish between a lowercase a and an uppercase A; they are given equal weight and therefore will be intermixed in the final sorted data. With *LANGIDUNQ, the lowercase a has a lower sequence value than an uppercase A, so lowercase a's will be sorted before uppercase A's.

DDS keywords you use to specify tables

TRNTBL (Translation Table). Use this field-level keyword in a logical file to translate (i.e., convert) the data in a field before it is sorted, used in a select/omit specification, used to join files, and/or presented to an application program. This is how the first solution to the MacDonald/Mackintosh problem could be implemented.

ALTSEQ (Alternative Collating Sequence). Use this keyword to implement the second solution to the MacDonald/Mackintosh problem. However, this is a file-level keyword that applies to all key fields when sorting the data (and it does not convert the data as the TRNTBL keyword does). To leave an individual key field unaffected, specify the NOALTSEQ keyword on the key field specification. Note, you can also specify an alternative collating sequence at file creation time by specifying a sort sequence table for the Sort sequence parameter of the CRTPF and the CRTLF commands.

Other iSeries functions that use tables

Here are a few of the more common uses for tables:


  • Selecting a collating sequence for a Query/400 query.
  • Selecting the sort sequence used by programs when performing string comparisons (e.g., the SRTSEQ parameter on the CRTCLPGM command).
  • Used on the OPNQRYF command on the %XLATE built-in function to translate data.
  • Using the TRNTBL parameter on the Send User Message command (SNDUSRMSG) to translate the optional reply value.
  • Translation tables are also used when using the Start 3270 Display Emulation (STREML3270) and the Start Printer Emulation (STRPRTEML) commands.
  • ASCII-EBCDIC conversion for almost every TCP/IP application, such as sockets, mail, WebServer, etc.

About the author: Ron Turull is editor of Inside Version 5. He has more than 20 years' experience programming for and managing AS/400-iSeries systems.

Dig Deeper on Performance