Useful CCSID tables

These are useful links for IBM i CCSID mapping DB2, etc. Note PASE requires secondary languages to be installed for more code sets (unlike ILE).

• PASE locales - PASE - What supported CCSIDs? Install what language group?
• ILE CCSID ILE - What supported CCSIDs?
• CCSID Mapping Both - What CCSID maps to what CCSID
• AIX locales - When you want to find PASE side CCSID mapping for env var PASE_CCSID.
• CCSIDs and code pages — CCSID to code page table (off topic TXSeries, but really handy)

PASE Languages and CCSIDs

PASE is an ASCII environment. ILE is an EBCDIC environment. DB2 tables are 99% EBCDIC. *PGMS are 99% EBCDIC. *CMDS are 99% EBCDIC. Data Queues are 99% EBCDIC. Data Areas are 99% EBCDIC. PASE applications are 100% ASCII. So we obviously have to start thinking through the machine status. To wit, data flowing between PASE and ILE or DB2 (or most anything), must be converted EBCDIC<>ASCII for anything to work (always). So, problem #1 removing CCSID 65535 from your IBM i environment (topic below). However after eliminating basic 65535 problem, you will still need to work harder to become aware of CCSID transitions in your new PASE scripting languages.

PASE library DB2 driver (libd400.a) …

Most new PASE scripting languages offer a DB2 driver or language extension. IBM i PASE provides a CLI driver (libdb400.a), to interact with DB2. As mention previous, DB2 tables are almost exclusively EBCDIC CCSID for string data (CHAR, VARCHAR, CLOB, etc.). RPG native DB2 data interactions are also exclusively EBCDIC. Under covers of PASE DB2 driver libdb400.a, syscalls make calls to IBM DB2 (ILE programs) using CLI APIs (SQLConnect, SQLPrepare, SQLExecute, etc.). Therefore, conversion between ASCII and EBCDIC must occur. if PASE CCSID env var is set 1208 (UTF-8), DB2 proper handles various conversions (ILE side). Generally a good thing. When PASE env var CCSID is non-Unicode, or, missing entirely (*), libdb400 CLI driver converts some string data using iconv for CCSIDs and DB2 handles other parts (mixed). Mostly works as PASE applications have been running for years. In DB2 case, not much needs to be done in general, because setting in your JOB CCSID are mapped and convert rules are already in place. However, if you have issues the first place to start is try setting the PASE CCSID env var, or, PASE_ENV env var from ILE side prior to PASE start.

(*) When no PASE env var CCSID set, PASE picks a “best fit” ASCII CCSID to match your current job CCSID. Default PASE CCSID can be override by your scripting language db2 driver/extension using PASE only API extern int SQLOverrideCCSID400(int newCCSID) prototyped in PASE header as400_protos.h (contact your c code driver provider in languages). BTW — IF you are a new language c provider/programmer for db2, SQLOverrideCCSID400 must be called first, aka, before any other SQL functions to set the environment correctly. This is why languages like PHP with driver ibm_db2 set customer over ride ccsid via ini file (ibm_db2.ini), aka, first thing before any other SQL operations. This works bot server mode and non-server mode.

There are basically two types of database connections possible using libdb400 in PASE languages. First, most common, connecting via ‘server mode’, wherein, a pre-started QSQSRVR job is ‘attached’ to act as proxy for database requests calls to ILE DB2 (CLI/ODBC requests). In server mode, CCSID factors are influenced by the user profile you choose in your script connection, aka, inheriting system QCCSID, or jobd, so on. Second, uncommon, you may avoid ‘server mode’ and directly talk to ILE DB2, aka, call in-memory via syscall (ILECALL) to DB2. Howvere, very few PASE languages offer this mode, because there are ‘one transaction’ restrictions that don’t work well with thread languages or web languages in general. To point, most PASE new languages default to ‘server mode’, therein, you will see QSQSRVR jobs using CPU in wrkactjob. However, nodejs, has a bolder design allowing the user to control the use of ‘server mode’. So, while these two modes ‘should’ offer the same result, the fact that only ‘one job’ maybe involved in a nodejs DB2 session may prove to be a deciding factor in setting up the correct ILE/PASE side CCSIDs.

Think PASE as AIX (Linux, Unix) …

We need accept PASE is ASCII environment (python, node, php, etc.), and, legacy DB2 data is EBCDIC (RPG, etc.). To that end, i find often useful to simply think of the PASE application as running on a another machine, a ASCII machine, and, libdb400.a driver is in the middle marshaling data to IBM i side DB2, a EBCDIC machine. To wit, there will be a conversion between the two environments. However, on IBM i, these ‘two machine’ are sharing the same job, therefore, you must work at getting administrative setting on both sides correct for ‘best match’ conversion PASE<>DB2.

When you choose CCSID settings for your PASE applications, you should really be looking in AIX manuals for CCSID support (or perhaps Linux DB2, etc). That is to say, PASE CCSID env var should come from AIX manual AIX locales .

When ILE CCSID and PASE CCSID collide …

PASE en var is CCSID (export CCSID=1208), but, may conflict with ILE env var of same name, so in wrkenvvar use PASE_CCSID. There are other settings for LANG, PASE_LANG, etc.

Toolkits …

Next, most new PASE scripting languages offer a toolkit based on XMLSERVICE, a good example to discuss larger IBM i CCSID picture in a hybrid PASE/ILE world.

First, we have observed, most XMLSERVICE based language toolkits operate in various countries ok. That is to say, when given scripting language is widely used in a country, then XMLSERVICE based toolkit often works. However, with so many possible NLS configurations it is impossible to predict exact behaviours for every toolkit call in every country. Therefore, each user/administrator must work to understand nature of moving data between EBCDIC ILE (DB2, *PGM, *CMD, QSH), and ASCII PASE (PHP, Python, NodeJs, etc.). Programmers and administrators needed to work together to set appropriate CCSIDs in various configurations, including user profiles, Apache configurations, fastcgi configurations, shell env vars of CCSID and PASE_CCSID, so on.

Complicating CCSID factors, XMLSERVICE supports a wide range of functions (*PGM, *SRVPGM, *CMD, shell, DB2, …), over a wide range of transports (REST, DB2, memory, …). Generally speaking XMLSERVICE functions directly calling *PGM, *SRVPGM, *CMD, work fine in most languages and transports. However, shell related XMLSERVICE functions, which toolkits often described as 5250 or interactive commands (dsplibl, wrkactjob, etc.), and PASE utilities (ps, ls, etc.), may have difficulties, or, may simply not work in a given complex environment. Also, IBM i has a unique set of commands that support input/output parameters. XMLSERVICE uses a REXX program to call these special types of commands using qtemp file buffers for in/out because these not able to run from the command line directly (RTVJOBA, etc.). For REXX to work ‘generically’, XMLSERVICE also uses shells to start the function, therefore like 5250 and PASE shells, may have difficulties, or, may simply not work in a given complex environment (*). Bottom line, a programmer must work through his country machine and understand what XMLSERVICE transports/functions work in scripts, and, how to configure all the working parts that make up XMLSERVICE toolkits.

(*) There are other APIs to mimick REXX and call functions like RTVJOBA, but in/out commands that cannot be run via command line are so alien to a modern systems, author xmlservice did not waste time playing with optimization. Basically, you should stop using these IBM i ‘almost commands’ and find alternatives.

DBCS: Recommend using PASE Unicode 1208 CCSID option. Also, some users have used a XMLSERVICE specific setting ctl='*pase(1208)'.

IBM i default CCSID 65535 problem

IBM i default system setting is CCSID 65535, also known as *HEX or no convert, can be one of your greatest barriers to successful IBM i modernization. Simply stated, CCSID 65535 does not work well with IBM i PASE ASCII environment, PHP, Java, Ruby, etc, therefore IBM i administrator action is required (see actions below).

DSPSYSVAL SYSVAL(QCCSID)
 Coded character set
   identifier . . . . . :   65535      1-65535

IBM i administrator actions CCSID 65535

1. Best solution change system wide CCSID
   

   CHGSYSVAL SYSVAL(QCCSID) VALUE(37)
   Note: 37 is example, use appropriate CCSID, anything better than 65535
   

2. Change CCSID Apache web applications (will not fix command line)
   

   Example is PHP, but applies to any web server.
      /www/zendsvr/conf/httpd.conf <-- (ILE Apache side)
      DefaultFsCCSID 37
      CGIJobCCSID 37
      Note: 37 is example, use appropriate CCSID, anything better than 65535
   
      /www/zendsvr/conf/fastcgi.conf <-- (PASE side, SETENV each application line)
      CCSID=819 and LANG=C
      -- or --
      CCSID=1208 and LANG=C
      Note: This file must be ASCII 819, be careful EDTF
   

3. Change CCSID user profile for DB2 connect, scripts, etc.
   

   CHGUSRPRF USRPRF(IAM37) CCSID(37)
   Note: 37 is example, use appropriate CCSID, anything better than 65535
   

4. Change CCSID specific job (not recommended)
   

   CHGJOB JOB(836270/MYID/MYJOB) CCSID(37) 
   Note: 37 is example, use appropriate CCSID, anything better than 65535
   

Questions?

• Why is CCSID 65535 rough on PASE?
  PASE is an ASCII environment. ILE is an EBCDIC environment. DB2 tables are 99% EBCDIC. Data flowing between PASE and ILE or DB2, must be converted for anything to work (always). IBM i default CCSID 65535, also known as *HEX or no convert, completely shuts off IBM i natural conversions in DB2, shells, etc., and, nothing works.
• Inconsistent symptoms of IBM i CCSID 65535?
  Many people experience general chaos understanding ‘PASE failures’. PASE applications work, other PASE applications fail. PASE applications work in one environment and fail in another environment. Worse, unfortunately, individual IBM i applications / products have taken on CCSID 6535 problem inconsistently (works in client access, not work in PHP, etc.). A long story of CCSID 65535 woe, but have faith, 99 out of 100 times, when everything is going wrong with your PASE application(s), XMLSERVICE, PHP, Java, Ruby, Node.js, etc, real issue is default setting CCSID 65535 (*HEX).