If HIGH-VALUES are used in an SQL Query between Enterprise Server on UNIX and DB2 on the mainframe, the results are incorrect.
If HIGH-VALUES are used in an SQL Query between Enterprise Server on UNIX and DB2 on the mainframe, the results are incorrect. If HIGH-VALUES are replaced by 9's, the SQL Query will return the correct results. What causes incorrect results and how can this be avoided?
This problem was/is only visible in product version 2.3 Update 2 hot fix 8 and previous versions.
The problem is caused by character conversion of HIGH-VALUES (x'FF') between UNIX and the local DB2LUW. The character being supplied to the mainframe DB2 system is not x'FF' so the results will be correct for the value supplied, which will be no rows returned.
The solution is to have a correct codepage conversion in place on the UNIX platform.
To use this on the UNIX machine..
1. Download CS9000.cbl and codeset31.cpy and codeset34.cpy contained in the attached file ( DB2HighValues.zip )
- Copybook codeset31.cpy is for Windows 31 MFCODESET (English)
- Copybook codeset34.cpy is for Windows 34 MFCODESET (Spanish)
2. Rename required copybook to codeset.cpy
3. Compile CS9000.cbl with >cob –z CS9000.cbl
4. Move the CS9000.so to ANY directory of your choice.
Making sure the directory is included in the LD_LIBRARY_PATH.
5. export MFCODESET=9000
6. export LANG=en_US.iso88591
- NOTE -
To use this MFCODESET in an Enterprise Server region..
a) stop your ES Region
b) Update your server /region configuration settings as follows:
- Enterprise Server Administration Panel > RegionName > Edit
- Server tab > Properties tab > General tab > Configuration Information panel
- Enter the Environment variables as follows:
c) restart your ES Region
6. Run application and this will pick up the new codepage and correct the issue
This special MFCODESET is now included in the product delivery for version 2.3 Update 2 hot fix 9 (onwards) and version 3.0 (onwards)