z/OS sever does not return Arabic data if substr is used for LIKE predicate. String transformation is needed as part of proce

z/OS sever does not return Arabic data if substr is used for LIKE predicate. String transformation is needed as part of processing.

Host: z10 – z/OS V1R11
DB2 for z/OS version 8.xx (PUT level is current as site is in the process of migrating to DB2 version 10). V10 software is already on-site and installation activities are proceeding in a sandbox environment. Following are excerpts from V8 zParm/decp:

Decimal point option (DECIMAL) PERIOD
Default delimiter (DELIM) DEFAULT
Default SQL delimiter (SQLDELI) DEFAULT
Distributed SQL string delimiter APOST
EBCDIC GBCS CCSID (GCCSID) N/A
EBCDIC MBCS CCSID (MCCSID) N/A
EBCDIC SBCS CCSID (SCCSID) 420
ASCII GBCS CCSID (AGCCSID) N/A
ASCII MBCS CCSID (AMCCSID) N/A
ASCII SBCS CCSID (ASCCSID) 1256
UNICODE GBCS CCSID (UGCCSID) 1200
UNICODE MBCS CCSID (UMCCSID) 1208
UNICODE SBCS CCSID (USCCSID) 367
Default character set (CHARSET) ALPHANUM
Default encoding scheme (ENSCHEME) EBCDIC
Locale LC_CTYPE (LC_CTYPE) 'BLANK'
Application encoding default(APPENSCH) EBCDIC

Clients: Microsoft Platform (x86—32/64 BIT)
Gateway or Connect Servers - Windows Server 2008 R2
DB2 connect (app server ed) 9.7 FP5
App Server being used is IIS co-located on the gateways.
App Development Clients Windows XP Professional (v2002) SP3
Microsoft Visual Studio 2008
.NET 2.0, 3.5, 4.0 Frameworks
OLE drivers used are derived from DB2 connect package DS_Client 9.7 FP5.
JDBC drivers (JAVA) are not used in apps.

Connection scenario: devt client  connect gateway  host
90% of the business data is on the HOST server and most business are serviced via legacy CICS applications. However, site is deploying more WEB-based applications

Problem Description :

One of the main requirements of our applications is a simple name search using a substring of an ARABIC name. But, z/OS sever does not return data if substr is used for like predicate.

As a example, let us consider a word mahmoud which translates into Arabic word 'محمود' and substring of it, mahm which translates into Arabic word 'محم'.

There is a difference between the complete word "mahmoud" and the half word "mahm" used in the where clause of the SQL query. Arabic letters have four shaping forms according to the character location
in the word. So, that means the fourth character "m" in the complete word "mahmoud" is different to the fourth character "m" in the half word "mahm" because in the first word the character "m" is located in the middle of the word but in the second word the character "m" is located at the end of the word.

The problem is, we need to reorder and shape the Arabic characters on client side. But the windows code page (cp1256) does not support the Arabic characters shaping which means the windows codepage cp1256 does not understand the shaped Arabic characters of UTF8 and only understand the unshaped Arabic characters in UTF8. There are differences in text processing between the Windows and z/OS environments. So, basically, we need to reorder and shape the Arabic characters on client side.

The possible fix of this problem would be, to have some processing on the text in order to search using part of the word like Character normalization into isolated form. Currently z/OS does not support Arabic transformation.

The proposed fix can be implemented on z/OS.

This is a limitation of product. Workaround to this issue suggests, if string transformation is needed as part of processing and server or client CCSID uses shaped text ( string type 4, 8,9, 7 and 12), application should
use "full word" in like predicate. In other words, it says, do not use substrings.