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.

Problem

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.


Resolution

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 )

3617.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:


[ES-Environment]
MFCODESET=31
LANG=en-US.iso8859-1

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)


Incident#3111843

DISCLAIMER:

Some content on Community Tips & Information pages is not officially supported by Micro Focus. Please refer to our Terms of Use for more detail.
Top Contributors
Version history
Revision #:
1 of 1
Last update:
‎2017-10-25 09:26
Updated by:
 
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.