DG4MSQL - Where clause does not return data

Problem:
One of the customer was facing issue while querying to SQL Server Database using dblink through DG4MSQL.  They have a view on SQL Server side, when they were querying this view with where clause having string in arabic,it did not return data. Although data was available and it could be seen when using query without where clause.
Customer is using 11g (11.2.0.3) RAC - Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production runs on Microsoft Windows 2008, Gateway is running on same  Database machine.


Reason & Solution:
Following entries of the registry were observed:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\NLS\CodePage\ 

ACP shows as 1252 which is a Western European code page.
The system locale determines the default character set (letters, symbols, and numbers) and font used to enter and display information in programs that don't use Unicode. This allows non-Unicode programs to run on your computer using the specified language. You might need to change the default system locale when you install additional display languages on your computer. Selecting a different language for the system locale doesn't affect the language in menus and dialog boxes for Windows or other programs that do use Unicode.


The data the gateway sends to the SQL Server is not Unicode data, so Windows tries to interpret the data according to the ACP setting and will handle now the string sent by the gateway to the SQL Server as WESTERN EUROPEAN data instead of ARABIC data.
Please change your Windows locale setting of the gateway machine:
How to change the system locale:
* Open Control Panel
* Click to open Region and Language.

Click the Administrative tab, and then, under Language for non-Unicode programs, click Change system locale. If you are prompted for an administrator password or confirmation, type the password or provide confirmation.

Select the language (in your case please make sure to select ARABIC (Saudi Arabia for our customer), and then click OK.

Windows now requests a reboot - please click Restart now.

When the machine is up and running again, check the registry key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\NLS\CodePage\
=> ACP should now be set to 1256

After restart things should be OK.

Tests performed:
A number of tests performed before reaching to the above mentioned solution which are given below also, may be helpful to you for such sort of another issue.

i) setup gateway tracing by addding HS_FDS_TRACE_LEVEL=255 in gateway init file and reviewed the traces for the query which was not returning data
select * from fin_edatt_students_info_vw@dblink where third_name='دخيل'
ii) Ran the query for the tables used in view to confirm that problem was with the view only or it is existing on for the base tables on SQL Server also, it showed the problem existed for the base tables also.
iii) Tried couple of parameter in DG's init file like below.
HS_FDS_TRACE_LEVEL=255 
HS_NLS_NCHAR=UCS2
HS_LANGUAGE=ARABIC_UNITED_ARAB EMIRATES.AR8MSWIN1256
HS_LANGUAGE=ARABIC_UNITED_ ARAB EMIRATES.AR8ISO8859P6
iv) SQL Server Collation was asked to SQL Server team to determine character set compatibility
SQL Server Collation : SQL_Latin1_General_CP1_CI_AS
Database Collation (DB used in DG's confiruation): SQL_Latin1_General_CP1256_CI_AS
v) Checked the hex code on the SQL Server side to confirm the values of Arabic string
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
DECLARE @string char(15),
@output1 varchar(1000),
@output2 varchar(1000)

SELECT @string = XXXCOL
from XXXTABLE
where XXXCOL = 'XXXCOND'

SET @output1 = ''
SET @output2 = ''

select
@output1 = @output1 + SUBSTRING(@string, number, 1) + ', ',
@output2 = @output2 + cast(ASCII(SUBSTRING(@string, number, 1)) as varchar) + ', '
from master..spt_values
where type='p' and number between 1 and LEN(@string)
order by number

PRINT @output1
PRINT @output2
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Replace XXXCOL with the column name,
XXXTABLE with the table name and XXXCOND that selects the row.

vi) For further debugging  created a demo table on SQL Server side:
drop table arabic;
create table arabic (col1 integer, col2 varchar(20) COLLATE SQL_Latin1_General_CP1256_CI_AS );
insert into arabic values (1,'Hello');
insert into arabic values (2,char(229));
insert into arabic values (3,char(205));
insert into arabic values (4,char(207));
insert into arabic values (6,char(229)+char(205)+char(229)+char(207));
select * from arabic;
while query from SQL Server side as below it was working but on Oracle side still was not working
select * from "arabic"@dblink where "col2"='هحهد';

vii) Checked the output from SQL Server Management Studio for the arabic table using belowstatement:
SET TEXTSIZE 0
SET NOCOUNT ON
DECLARE @position int, @string varchar(20)
-- Initialize the variables.
SET @position = 1
select @string = col2 from arabic where col1=6;
WHILE @position <= DATALENGTH(@string)
BEGIN
SELECT ASCII(SUBSTRING(@string, @position, 1)),
CHAR(ASCII(SUBSTRING(@string, @position, 1)))
SET @position = @position + 1
END
SET NOCOUNT OFF
GO