HI All,
I dont know this setting will work or not but I am trying and hoping you guys can help me get the answer. I am setting up the Redshift ODBC Oracle gateway (12.2.0) but run into problem. I can verify the Redhsift ODBC by using isql -v <DNS> <USER> <PWD> to get connection to redshift and run sql to count on a table successfully but getting error below when testing dblink. It looks like the HS_LANGUAGE parameter is having character set translation problem with in HS. Please help and thank you very much,
Garry
odbc.ini
[cdidw]
Description = Amazon Redshift (x64)
Driver = /opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so
SERVER = odaa-sand-redshift-1.caiy5yvz8wkb.us-east-1.redshift.amazonaws.com
PORT = 5439
Database = cdidw
CHARSET = utf8
odbcinst.ini
[Redshift]
Description=Amazon Redshift ODBC Driver (64-bit)
Driver=/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so
FileUsage=1
initcdidw.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = cdidw
#HS_FDS_TRACE_LEVEL = 255
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
#HS_LANGUAGE=AL32UTF8
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
dblink test error message:
*********************************************************************
Link : "REDSHIFT_CDIDW"
Error : ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 25P02] ERROR: current transaction is aborted, commands ignored until end of transaction block
{25P02,NativeErr = 30}
ORA-02063: preceding 3 lines from REDSHIFT_CDIDW
*********************************************************************