Encoding schemes (the bit pattern that represents a given character) vary among platforms (IBM i, Windows) and also between languages (Chinese, English, Spanish, etc.). For example, the character '¢' has many different encodings. On an IBM i we can find it in US English as x'4A', most Latin languages as x'B0', and Japanese as x'B1'. It is encoded on the IBM PC code page as x'9B', on the PC Multilingual code page as x'BD' and in Windows as x'A2'.

In order for characters to appear properly across the various platforms and languages, they need to be translated from their stored encoding to the encoding used by the observer. IBM i accomplishes this automatically through the use of CCSID values. Characters that are "tagged" with a particular CCSID are automatically translated whenever they are accessed by a job with a different CCSID value. This means that a '¢' stored by a U.S. user will appear as a '¢' on a workstation accessed by a user in Quebec, Canada even though the character has a different encoding for each of them.

Although translation is automatic among IBM i based jobs, Sequel software must manage the translation when sending results to stream files or a personal computer. This translation occurs whenever the EXECUTE command is used to create results in email, IFS stream files, HFS documents, and PC files or results windows displayed via ViewPoint. REPORT results sent to an IFS file or converted to PDF format must also be converted to a PC based encoding, as are PRINT results that are emailed.

This document describes the method of the Sequel directed translation and how to configure Seque so that the translation is performed correctly.

User Settings

User settings are all of the user specific language and region settings that support working in a particular language environment. These settings are interdependent and must be compatible with one another in order to process character data correctly. Since IT departments routinely provide users with computers that are properly configured for the users language, there is usually no need to change them for Sequel.

IBM i user profile

The user profile attributes for LANGID and CCSID determine the CCSID for most iSeries jobs connected to a user. This includes green screen, ViewPoint and QZDASOINIT jobs used by Sequel Web Interface. These settings may default to *SYSVAL, but that may not be appropriate for all users. Correct settings for Host LANGID and Host CCSID for users can be found in the Language Lookup table below. The SEQUELWI jobs used by host based SWI may need to be set using the SWISTART initialization program documented elsewhere.

PC settings

  • Client Access/Access Client Solutions Properties OEM code page on Language tab. See the Language Lookup table and the ‘ViewPoint Character Translation’ section below for details.

  • Emulator Host Code Page. Must match or be compatible with the host job ccsid and can be obtained from the Euro or non-Euro Host ccsid columns in the Language Lookup table below.
  • PC Region and Language from control panel

You will typically not change this setting but use it to discover the language your PC is configured to use. This of course assumes your pc is properly configured for your language of choice.

Product Settings

Product settings for Sequel and Esend are contained in 2 data areas. These settings are critical for emailing spooled output and for writing data to ascii based PC files. Product settings are global and shared by users.

SQTRN

This controls character conversion to ASCII for data written by Sequel to PC files. SQTRN is not involved in producing PDF output.

EMTRN

This controls creation of email headers and attachments and conversion of spooled data to the target ASCII file type. EMTRN affects production of all PDF output

Sequel and Esend Product settings

The SQTRN data area in the SEQUEL library and the EMTRN data area in the ESEND library are critical to proper character translation. SQTRN controls the creation of PC file results while EMTRN controls the conversion of spool file data and the creation of email messages and PC file results. The SQTRN data area specifies a translation table and the target PC CCSID for the character conversion. EMTRN has the same structure as SQTRN, but only the Primary target CCSID is used from EMTRN. Users must have *USE authority to these data areas.

If multiple local languages are in use on a given system, additional SQTRN and EMTRN data areas must be created and each user's library list will need to be arranged so that SEQUEL and ESEND find the data area with the appropriate specification.

The structure of the data area is as follows:

Position Description Type/Length Initial Setting
1 Translation table name char(10) QA6YBFA93
11 Translation table library char(10) QUSRSYS
21 Primary target CCSID zoned(5,0) 01252
26 Secondary target CCSID zoned(5,0) 01252

 

SQTRN is shipped with the initial settings shown above. EMTRN is similar but only uses the Primary target CCSID. This provides proper translation between US English encoded data and the Microsoft Windows codepage. If SEQUEL is used with a language requiring different settings, the data areas must be changed accordingly.

The Language Lookup table below is used to determine compatible entries for SQTRN. All translation table names are found in library QUSRSYS. For example, a suitable Italian translation table is QUSRSYS/QA63BFA93. This provides translation between the Italian (Euro) 1144 CCSID and the PC-Windows 1252 CCSID.

To set SQTRN and EMTRN, find the row in the Language Lookup table that matches your Language.

  • Use the commands ESEND/EMTRN and ESEND/SQTRN to update the data areas.
  • Enter the translation table name, library, primary client CCSID and Secondary Client CCSID if needed and press Enter twice.

Language Lookup Table

Translation Table name
in QUSRSYS
Host
CCSID
(euro)
Host
CCSID
(non-Euro)
Primary Target CCSID & OEM Code Page Secondary Target
CCSID
Host Lang. ID Description
QA6YBFA93 1140 37, 1047     ENU, NLD, PTG, PTB,ENA, ENP,AFR USA, Netherlands, Portugal, Afrikaans
QA6ZBFA93 1141 273     DEU Austria, Germany
QA61BFA93 1142 277     DAN, NON,NOR Denmark, Norway
QA62BFA93 1143 278     FIN, SVE Finland, Sweden
QA63BFA93 1144 280     ITA Italy
QA64BFA93 1145 284     ESP, CAT Spain, Latin America
QA65BFA93 1146 285     ENG, GAE United Kingdom
QA66BFA93 1147 297     FRA France
QA67BFA93 1148 500     DES, FRB,FRC, FRS,ITS, NLB,SQI Belgium, French Canada,Switzerland (Fr/Gr),MNCS (Multinational Character set)
QA68BFA93 1149 871     ISL Iceland
QA7BBFA91 1153 870     CSY, HRV,HUN, PLK,ROM, SKY,SLO, SRL Czech, Poland, Romania, Slovakia,Slovenia, Hungary, Croatia, Serbia
QA7DBFA95 1155 1026     TRK Turkey
Q875BFA94 4971 875     ELL Greece
QA7CBFA92 1154 1251 BEL, BGR, MKD, SRB, RUS Belarus, Russia, Macedonia, Bulgaria, Serbia/Montenegro
Q424BFA96 424 424 1255 HEB Israel
Q420BFA97 420 420 1256 ARA Arabic
QA7EBFA98 1156 1156 1257 LVA, LTU Latvia, Lithuania
QA7EBFA98 1157 1157 1257 EST Estonia
QA7MBFA99 1164 1164 1258 VIE Vietnam
Ignored 937 937 13488 OEM=default 950 1 CHT Traditional Chinese
Ignored 935 935 13488 OEM=default 950 2 CHS Simplified Chinese
Ignored 5035 5035 13488/950 OEM=default 950 3 JPN Japan
  1. Requires Windows Region Locale setting = Traditional Chinese for proper rendering of report output in ViewPoint
  2. Requires Windows Region Locale setting = Simplified Chinese for proper rendering of host report output in ViewPoint. (may also require IBM i Access Client Solutions upgrade from Client Access)
  3. Requires Windows Region Locale setting = Simplified Japanese for proper rendering of host report output in ViewPoint. (may also require IBM i Access Client Solutions upgrade from Client Access)

Viewpoint Character Translation

Character translation in ViewPoint is accomplished using iSeries Access APIs. When a ViewPoint connection to a target system is made, the character converter is created based on the HOST job CCSID and the "OEM code page value" in the Language tab of the iSeries Access for Windows Properties panel. Set your OEM code page using the value from the Language Lookup table column for Primary target CCSID and OEM code page.

DBCS and Asian Language Support

Some languages include more characters than can be specified in a single byte of storage. These languages use two bytes of storage to represent a single character and are known as DBCS (Double Byte Character Set) languages. Storing DBCS data on an IBM i based system prior to V5R3M0 required a special version of the operating system that could accommodate double byte operations. Since V5R3M0 and later versions, DBCS capability is automatically enabled.

Even though a DBCS version of the operating system is installed, an emulated session will not necessarily be able to view double byte character data. In order to view DBCS data, the device that is being used must support a double byte character set.

When using the older IBM i Client Access product, if only "Standard PC5250" is chosen for the emulator during iSeries Access installation, users will not be able to display graphical characters. Instead, choose "Traditional Chinese" or another DBCS capable type as the emulator to obtain DBCS capability when installing the software.

When using IBM Access Client Solutions, no special installation options are necessary. The PC will need to have appropriate DBCS language support from Windows.

The Windows Region settings are important for proper rendering of host reports in ViewPoint. The Region setting for System Locale (Current language for non-Unicode programs) is of particular importance for host reports.

DBCS data handling notes

The Client Report Option (CRO) does not support double byte results when requested from Viewpoint or a host job, because Crystal Reports reads our reults from a dBase file and dBase doesn't support Unicode or East Asian double byte characters. However, double byte reports ARE supported when requested from SWS running in Repository mode.

XLS and XLSX output supports both Unicode and double byte character sets. The target CCSID in the SQTRN data area is ignored during XLS and XLSX output creation; all worksheet data will be converted to Unicode. . The translation table is still used for performance reasons and must properly reflect the codepage of the current system.

SEQUEL can perform translations using double byte characters with Unicode as a target CCSID for stream file output. If the Primary CCSID value in the SQTRN data area is 13488, SEQUEL will produce Unicode (big-endian) results for text, RTF, and PDF files produced through EXECUTE and REPORT.

When the primary CCSID is set to 13488 Unicode, then it is also necessary to specify an appropriate single byte sec- ondary CCSID in positions 26 – 30 as noted in the Language Lookup table above. This secondary CCSID is used to create the mime header in each e-mail mes- sage, and as a "fall back" code page in the event that the PDF conversion is unable to match the current job's CCSID to one of the built-in PDF Unicode types.

GRAPHIC/Unicode encoded input data

Sequel can process data stored in GRAPHIC fields with Unicode encoding. Sequel ALWAYS creates views that translate UCS-2, UTF-8 and UTF-16 data to EBCDIC character fields. While this works well for DISPLAY, REPORT, etc. it can have a negative implication in the case of EXECUTE because the created physical file's field definition (EBCDIC) will not match the input file's field definition (Unicode).

Unfortunately, we are forced into this behavior by IBM's Create File API (QZRCRTPF) because it is unable to cre- ate files with Unicode fields in them. Although the file seems to create properly, and displays properly with DSPFD/DSPFFD, a function check occurs in QDBCRTME when adding a member.

SEQUEL Spooled output:

In order to work with and print DBCS data, Sequel device files must be DBCS enabled. This is controlled by the IGCDTA attribute on each device file. To facilitate managing DBCS capability of all device files in the Sequel product line, the CHGSEQIGC command is provided in the ASCSUPPORT library. Use this command to set IGCDTA either on or off for device files in the products you use.

Asian character output produced by the IBM i typically uses a double wide graphic character and a single wide Latin character.There is also a space at the beginning and ending location of an Asian character string. Conversion of these results from spooled output to PDF causes alignment problems because the PC fonts consistently use a single wide character.

To fix this, extra spaces are inserted to the right of an Asian character string so that columns to the right of the Asian characters will continue to be properly aligned. The spaces are inserted at the location of the first space character fol- lowing the Asian string.

In a similar manner, some care must be exercised in creating RTF results from spooled files. SEQUEL uses the CPYSPLIFS command to convert spooled results to RTF format and uses the values stored by the ESNDUSR com- mand to determine the font name and size of the results. The font name specified in the RTF settings must specify a fixed pitch font that consistently represents Asian characters as twice the width of Latin characters. The Microsoft TrueType modern font named MS Gothic is a good choice for this.

Text files and XLS results are not changed to preserve alignment nor are space characters inserted into the results in place of the Shift In/Out control characters. Translation is performed according to the rules of the chosen target CCSID.


Still have questions? We can help. Submit a case to Technical Support.

Last Modified On: May 20, 2019