The mystical Simplified Chinese setup and configuration for Xe....

Hi listers

ok - heres a perplexing one, and i'm hoping someone out there has simplified chinese.

My customer is running OneWorld Xe (B7333) SP23K1. Primary platform is AS/400 (V5R3). I think thats all the info you need about the platform (though they ARE running central objects on a SQL Server 2005 on the deployment server).

ok - they have two instances of oneworld. The first instance is set up using standard JDE with CCSID 37 and contains all the single-byte languages (English, French, Italian etc etc). The second instance runs on a separate port and uses CCSID 939 for Japanese users. I know, they could have combined the two as a single instance - but back when all this was created, there might have been additional doublebyte languages.

Now, strangely enough, they created a data dictionary using CCSID 65535 (unicode) - which, of course, is not right. But, we're not touching production working code - especially if its working !

Anyway - they get an urgent requirement to implement Simplified Chinese - so the decision is made to create a new separate instance using CCSID 935. So, I install a new instance on a new port, create new pathcodes (for the chinese language pack), create a new Data Dictionary - CHINA datasource and upload the china DD - and build a new data set (DVCDTA and DVCCTL for example) using an iSeries system user with CCSID 935. Everything initially looks ok.

The F0101, for example, is a CCSID 935 table, and I have a user with a CCSID 935 system user that read/writes with no issue.

BUT, then during testing its revealed that there are a handful of missing tables. Specifically the F3002. So, I add this to OMW and attempt to generate the table using my "JDESCH" oneworld user (which has a system user "JDESCH" with 935 CCSID).

I get an error trying to generate the table as follows :

[ QUOTE ]

5288/6140 Sun Jan 10 23:14:06 2010 jdb_ctl.c2763
Starting OneWorld

5288/6480 Sun Jan 10 23:14:27 2010 jdb_ctl.c6179
JDB9900155 - Failed to get non - zero AB Number.

5288/6480 Sun Jan 10 23:14:48 2010 jdb_ctl.c6179
JDB9900155 - Failed to get non - zero AB Number.

5288/6480 Sun Jan 10 23:15:41 2010 ODBC_U1.C1124
ODB0000175 - Columns not in cache in Table F3002
Column IXF$RP
Database Business Data - PD7333

5288/6480 Sun Jan 10 23:15:41 2010 JDBODBC.C1515
ODB0000055 - Describe table failed for table F3002.

5288/6480 Sun Jan 10 23:15:41 2010 jdb_drvm.c839
JDB9900168 - Failed to initialize db request

5288/6480 Sun Jan 10 23:15:41 2010 JTP_CM.C1090
JDB9909007 - Unable to obtain driver request handle

5288/6480 Sun Jan 10 23:15:41 2010 JDBODBC.C5470
ODB0000071 - SQLPrepare failed

5288/6480 Sun Jan 10 23:15:41 2010 JDBODBC.C5470
[IBM][System i Access ODBC «˝∂Ø≥ÖÚ][DB2 i5/OS ∞Ê]SQL0113 - ≤ª‘ –Ì√˚≥∆IXF$RP°£ - SQLSTATE: 37000

5288/6480 Sun Jan 10 23:15:41 2010 jdb_drvm.c938
JDB9900401 - Failed to execute db request

5288/6480 Sun Jan 10 23:15:41 2010 jdb_exem.c481
JDB21000091 - Failed to create table F3002 in data source Business Data - DVC7333

5288/6480 Sun Jan 10 23:15:41 2010 tccopy.c568
TCE000009 - CopyTable create output table failed.


[/ QUOTE ]
When I look, all the tables that failed to create ALSO have the "$" symbol in their column names. So, I am presuming that these are being interpreted as chinese characters.

I capture the create table command using JDEDEBUG - and log into the AS/400 directly with a CCSID 935 user. I am then ABLE to create the table directly - so the use of "$" isn't actually an issue.

ok - so this table doesn't actually need to contain chinese characters, since it just contains numbers. So, I generate the table as a CCSID37 table as a test.

I log in using my JDESCH user ID, with its JDESCH System ID (default CCSID 935) - and its NOT able to read the F3002 I just generated.

So, I change my system user to default to CCSID37. Now, it can read/write to the F3002 AND it can see chinese data in, for example, the F0101.

My question is - is this right ? are my Simplified Chinese oneworld enduser system user ID's supposed to all be CCSID 37?

What is my Data Dictionary supposed to be generated at - I presume CCSID935 - but I'm unsure given the shared English/Japan one being at CCSID65535 !

Could someone who has Simplified Chinese on an AS/400 please look and see if our setup is right ?

I just installed Simplified Chinese, Japanese, and German for an 8.11 SP1 client. They have been using JDE for a few years, so the DD and other tables were already in place, and I just did a Unicode conversion on the Business Data and Control Table libraries.

Checking them just now, the common tables are all using CCSID 13488. This includes DD811, OL811, SY811, and SVM811. The Business Data and Control Tables are at CCSID 37, as is the JDE user. (I didn't do the original implementation.)

I don't know what 13488 is. I'm sure you'll be looking it up, just as I will be doing ...

I believe that is unicode. Xe doesn't support unicode - it only supports the old Doublebyte (which equates to CCSD935).

However, that IS weird that the Business data and control tables are at CCSID37. Wouldn't you expect them to be a unicode CCSID?

Hi Jon,

65535 is actually the binary or "Open" CCSID and not Unicode (13488) Under 65535 there is no translation done.

I haven't done Simplified Chinese under XE on the AS/400 but have setup a number of Japanese and Korean systems. The same principles should apply. Please excuse me if I leave anything out as it has been a few years since I have touched these systems.

In all cases I set the proxy user to a CCSID of 037. Note that the underlying physical files had to be setup with the appropriate CCSID (937 JP or 933 KR) and the two DBCS languages could not co-exist if hosted from the same JDE instance.

It seems that the IBM database apis handle conversions so that a 037 configured user can fetch/update double-byte data. The presentation of the data on the Fat Client and processing by the JDE kernels/UBE engine is a bit more complicated in that you must have the appropriate Windows support for display and you must have the LocalCodeSet JDE.INI parameter configured properly for both client and server instance. The JDE runtime then uses the ICONV apis to make codepage conversion happen as needed. It seems that you have the LocalCodeSet working as you are able to see the SC data when all is said and done.

I have not run into the table generation issue you experienced. I did generate tables using a proxy user with the appropriate CCSID. Initially I was generating from the 037 configured user and then changing the CCSID from the AS/400 command line which was a hassle. Perhaps the issue with the $ being converted into another character and causing the SQL0113 error only crops up with Chinese.

Here is some info extracted from oti-01-0231 that describes what CCSIDs you can expect in a double-byte setup:

Table CCSID
Data Dictionary 65535
Central Object (F98306 Only) Appropriate CCSID
Central Object (Others) 37
Other tables Appropriate CCSID
INI 65535
PRINTQUEUE 65535

Note that DD tables are configured for 65535 when you have chosen the Double Byte option in the initial AS/400 XE installation program. Your English/Euro/Japanese Data Dictionary tables seem to be properly configured for the situation.

I also recall the Data Dictionary having to be set at 65535 for double byte implementations. Here's the oti-01-0231 document that was referenced by JEMILLER.

Hi Justin

This is perfect - it was the whitepaper I was looking for, thankyou. Evidently, the $ issue is a known issue with IBM for Simplified Chinese - which Oracle pointed me towards - so I have a call open with IBM and will update this thread on this information.

Changing the DD to 65535 is actually not difficult. For future reference, changing the CCSID of a file can be done as follows :

CHGPF FILE(DDC7333/F9200) LANGID(*SAME) CCSID(65535)

As far as the F3002 is concerned, I am experimenting with removing the physical file constraint as follows :

1. Generate the file using CCSID 37
2. RMVPFCST FILE(DVCDTA/F3002) CST(*ALL)
3. CHGPF FILE(DVCDTA/F3002) LANGID(*SAME) CCSID(935)

Does anyone see any issue with removing the physical file constraint as above ?

Thankyou for the attached document - its extremely handy, and I'm glad you posted it. I owe you and cncjunior a beer in Vegas in April!

So here is some more information.

First of all, the whitepaper talks about modifying the .tabl files - and states that the method is in Appendix 3 - but there is no appendix 3 in the document !

I raised a call with Oracle - who really didn't help that much initially - but they explained the issue more thoroughly as follows :

[ QUOTE ]

Bug 4844930: "$" in S-Chinese and CA/400

The dollar sign(x'5B)in EBCDIC are mapped to x'7F in ASCII, which is an undefined character found in EDCDIC to ASCII translation table(037a7056a.tbl) for the Simplified Chinese on Client Access. It causes all "$" in SChinese.mdb to be converted to x'7F when PVC loads the translated data from AS/400(JDEJ Machine) to the MDB file by Table Conversion.

In OneWorld®, some UDC, data items and column headers in a table contain a dollar sign "$" as part of a variable n and it causes some problems in Simplified Chinese environments on AS/400®.

Most CCSID have the "$" at the code point x'5B in EBCIDIC, however Simplified Chinese (CCSID 935) has the "$" at the code point x'E0. x'E0 happens to be the same code point for back slash "\" in CCSID 37.

Also the "$" in EBCDIC is mapped to x'7F in ASCII which is a undefined character in the EDCDIC to ASCII table (037a7056a. for Simplified Chinese on Client Access. This causes a problem in our language mastering process. However, these issues cause following problems on Simplified Chinese live environment:

1. The "$" in UDC and Data Dictionary in SChinese.mdb are converted to an undefined character (x'7F) when loading the translated data to MDB file from AS/400® by table conversion. Even if the SChinese.mdb is built correctly, the "$" in ASCI converted to x'E0 in EBCIDIC. This causes the translated UDC and DD, which contain "$", to never match to the corresponding UDC and DD with the domestic data value.

2. The tables that contain "$" in column header are not generated during the environment workbench because the "$" (x24) in AS400 converts to character xE0 in EBCDIC on the Simplified Chinese environment (CCSID 935), and the character xE0 is a "\" in CCSID 37 which is an invalid character for the column header

The SAR is closed as a fix is being pursued from IBM side.


[/ QUOTE ]

So, I talked to IBM Rochester about this issue further. They scratched their heads and passed me about a bit - but finally I got hold of someone who worked with the JDE alliance team and who remembered the issue. They specifically stated that the issue was that JDE had used "$" signs in their columns AGAINST their express instructions (although, I presume, its a perfectly valid character on every other database). And that JDE had a fix that "hacked" the tables for Simplified Chinese users.

SO, I had to go BACK to Oracle, and after feeling like the piggy in the middle, I have someone researching the information further.

However - does anyone on the list have the Appendix 3 information still ? Evidently, the document talks about using the DOS command "debug" to modify the 037a7056a.tabl

Much thanks in advance ! More Beers to go - I've just booked my hotel in Vegas, so I'll be buying a round or two !

Well. We're still struggling with this issue. We've gotten it escalated through Oracle - but its a difficult experience.

What we have realized is that this IS a known issue on the AS/400 for Simplified Chinese customers. I managed to finally get a hold of the orginal whitepaper with the missing Appendix 3 (see attached). As can be seen, there WAS a workaround (very kludge - modifying the .TBL files under Client Access) - BUT these are for V5R3 client access, and we're running V6R1 - so the .tbl files are missing/different.

However, I've been able to downgrade our clients to V5R3 with Simplified chinese - and the correct information in the document IS now there - so I can move forward. I'm just amazed at how much a kludge this is. Of course, this was under Xe - so no doubt very different with 8.x and above !

How are other companies installed on Simplified Chinese with Xe doing this ? We are also suspecting issues similar to this with a printing issue as well, where the fonts aren't being picked up because, we think, the AS/400 is using the "\" character stored somewhere as a "$" or a Yen character. If I find out more on that side, I'll keep the community informed for future reference !