Progress/400
Product Guide
User-defined Collation Tables
This section describes how Progress/400 lets you define and use alternate collation sequences against your DB2/400 database. Specifically, it provides information on:
Collation sequences determine the order in which an application sorts data. A user-defined collation table lets you define and control how character data is collated (sorted). Because Progress/400 products use the native DB2/400 database, they are restricted to the collation capabilities provided by OS/400. The advantage of creating your own collation table is that you can define the collation sequence based on the national and cultural requirements specific to your application deployment environment. You can then use your custom collation table to control sorting, which allows you to collate character fields, whether indexed or not, in a user-defined order.
To take advantage of user-defined collation, you create a table and populate it with the hexadecimal value for each character, assigning the values in the order in which you want character data to sort. You can also sort with tables provided by the OS/400, located in the QUSRSYS library. A second table, called the uppercase table, controls lowercase-to-uppercase character conversion. You use a table to look up a character and its corresponding numeric value.
Implementing User-defined Collation Sequences
To use an alternate collation sequence, Progress/400 requires that you specify one of the alternate collating sequence tables that your system provides. If your system does not provide one that addresses your collation requirements, you can create your own.
The Progress/400 DataServer works with four tables. These tables are objects of the *TBL data type that either come with the system or you define:
- Alternate collating sequence table (ALTSEQ)
ALTSEQ must be a DB2/400 table whose source is a source member that contains eight records, each of which contains 64 hexadecimal digits. (The 8 x 64 format is required by DB2/400.) The 64 digits represent 32 values, from 0-225 (xFF). The total of 8 records provide collation values for all 256 EBCDIC characters. Characters are sorted in the order of their collation values.
- Alternate collating sequence case-insensitive table (ALTSEQCI)
ALTSEQCI is a DB2/400 table whose source is a source member that contains eight records, each of which contains 64 hexadecimal digits. (The 8 x 64 format is required by DB2/400.) The 64 digits represent 32 uppercase characters that correspond to the 32 characters represented by that record. This table ensures Progress-like behavior for case-insensitive indexes.
- Progress uppercase table (UPCASE)
UPCASE is a DB2/400 table. The source for the table must be a source member that contains eight records, each of which contains 64 hexadecimal digits. (The 8 x 64 format is required by DB2/400.) The 64 digits represent 32 uppercase characters that correspond to the 32 characters represented by that record.
- Progress lowercase table (LOCASE)
LOCASE is a DB2/400 table. The source for the table must be a source member that contains eight records, each of which contains 64 hexadecimal digits. (The 8 x 64 format is required by DB2/400.) The 64 digits represent 32 lowercase characters that correspond to the 32 characters represented by that record.
To use an alternate collation sequence, you must first build the collation-sequence and uppercase/lowercase tables.
When you install the Progress/400 DataServer, Progress allows you to select a case-insensitive table as the default. When you create a dictionary library, the DUPPRODB utility allows you to override this default table. Each time you connect to your DB2/400 database, Progress looks for the collating and uppercase tables you specified when you created the Progress/400 Dictionary and server schema with the DUPPRODB utility. If the tables exist, Progress reads and writes data based on that information. If they do not exist, Progress uses the default collation sequence.
Creating the Tables. You use the OS/400 Create Table (CRTTBL) command. This is its syntax:
The CRTTBL command expects the source member (SRCMBR) to contain eight records of 64 hexadecimal characters each. An example of a collation table is shown in Figure 8–3.
Figure 8–3: Collation Table Example
![]()
Figure 8–4 illustrates two points. Each point is highlighted by a number to the left; the notes that follow Figure 8–4 describe their collation functions.
Figure 8–4: Specifying a Sort Order in a Collation Table
![]()
You create an uppercase table in the same way you create a collation table, except that in the places that contain lowercase characters, you substitute the hexadecimal value of the equivalent uppercase. For example, in the table shown in Figure 8–4, instead of x81 you place an xC1 so that the fifth line starts with 80C1C2.
Collation Table Limits and Restrictions
There are several limits and restrictions to consider when defining collation tables:
- Progress has a default collation sequence and a default lowercase-to-uppercase table.
- If you do not specify a specific collation sequence, the default is the standard collation sequence for your AS/400.
- When you create an alternate collation sequence, the OS/400 *TBL object requires that you define a collation sequence for all 256 characters in a code page regardless of whether you use them all.
- If your user-defined collation table is not set up correctly, you might receive unpredictable results.
DataServer Considerations
This section explains two important issues for defining alternate collation sequences for use with the Progress/400 DataServer:
Conversion Tables for User-defined Collation. The Progress/400 DataServer provides international character support using a file called
CONVMAP.DAT
. This file resides on the Progress client and consists of entries for ASCII and EBCDIC Code Pages for various languages.When the Progress client connects to the server on the AS/400, the client loads the two tables required for the translation from the
CONVMAP.DAT
file into memory. For example, if the client uses ISO8859-1 and the server uses IBM037, the client loads one table to handle the conversion from ISO8859-1 to IBM037 and a second table to handle the conversion from IBM037 to ISO8859. The client uses the tables to convert its requests into the code page required by the AS/400 server. When the server fulfills and returns the database request, the client usesCONVMAP.DAT
to convert between the AS/400 code page and the client code page. The client handles all code-page conversions. It converts only those fields required by the Progress application.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |