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 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.
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.
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.
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 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.
This controls character conversion to ASCII for data written by Sequel to PC files. SQTRN is not involved in producing PDF output.
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
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.
Enter the translation table name, library, primary client CCSID and Secondary Client CCSID if needed and press Enter twice.
| 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 | |
| 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 |
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.
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.
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.
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.
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.
