Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

error with DG4ODBC while setting up connectivity from oracle to IBM iSeries AS400 version V7R1

YoungDBAJul 11 2013 — edited Jul 12 2013

Hi,

I have Enterprise linux 64-bit hosting Oracle 11gR2 64-bit (11.2.0.1) database. I have access to IBM iSeries AS400 version V7R1. I am trying to connect to IBM iSeries from Oracle Database through ODBC connectivity using Database Link. The following is what I have done.

On the Database (Linux) host Installed iSeriesAccess-7.1.0-1.0.x86_64.rpm provided by IBM.

my odbcinst.ini has the following entries:

[PostgreSQL]

Description = ODBC for PostgreSQL

Driver = /usr/lib/libodbcpsql.so

Setup = /usr/lib/libodbcpsqlS.so

FileUsage = 1

[iSeries Access ODBC Driver]

Description = iSeries Access for Linux ODBC Driver

Driver = /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so

Setup = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so

NOTE1 = If using unixODBC 2.2.11 or later and you want the 32 and 64-bit ODBC drivers to share DSN's,

NOTE2 = the following Driver64/Setup64 keywords will provide that support.

Driver64 = /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so

Setup64 = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so

Threading = 2

DontDLClose = 1

UsageCount = 1

[iSeries Access ODBC Driver 64-bit]

Description = iSeries Access for Linux 64-bit ODBC Driver

Driver = /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so

Setup = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so

Threading = 2

DontDLClose = 1

UsageCount = 1

The odbc.ini has the following entries:

[ODBC Data Sources]

dg4odbc = connection to db2

[dg4odbc]

Driver = /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so

Description=iSeries Access for Linux ODBC Driver

LogonID=

Password=

Address= (IBM host IP Address)

location=Vm520

noprompt=true

system= (IBM host IP Address)

UNICODE=UTF-8

Naming = 0

ConnectionType = 0

CommitMode = 2

ExtendedDynamic = 0

AllowDataCompression = 1

LibraryView = 0

AllowUnsupportedChar = 0

ForceTranslation = 0

Trace = 0

made sure I could connect via ODBC and DSN using the following command:

isql dg4odbc oracle oracle1 -v

+---------------------------------------+

| Connected! |

| |

| sql-statement |

| help [tablename] |

| quit |

| |

+---------------------------------------+

SQL>

SQLRowCount returns -1

600 rows fetched

SQL>

could query the table in a library on AS400.

The $ORACLE_HOME/hs/admin/initdg4odbc.ora has the following entries:

# 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 = dg4odbc

HS_FDS_TRACE_LEVEL = off

HS_FDS_SHAREABLE_NAME=/etc/newlib/libodbc.so

HS_FDS_TRACE_LEVEL = 255

HS_FDS_SUPPORT_STATISTICS=FALSE

# ODBC specific environment variables

set ODBCINI=/etc/odbc.ini

# Environment variables required for the non-Oracle system

The listener entries are as follows:

(SID_DESC =

(SID_NAME = dg4odbc)

(ORACLE_HOME = /opt/oracle/dbs/product/11.2.0/dbhome_1)

(PROGRAM = dg4odbc)

)

Stopped and started the listener.

The TNS entry is as follows:

armcdb2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = vmw-dev-rmsdb.xxx.vmc)(PORT = 1521))

(CONNECT_DATA =

(SID=dg4odbc)

)

(HS=OK)

)

Could tnsping :

oracle@rmsdev[/opt/oracle/dbs/product/11.2.0/dbhome_1/network/admin]$ tnsping armcdb2

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 11-JUL-2013 16:01:23

Used parameter files:

/opt/oracle/dbs/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = vmw-dev-rmsdb.xxx.vmc)(PORT = 1521)) (CONNECT_DATA = (SID=dg4odbc)) (HS=OK))

OK (0 msec)

created a public database link as follows:

SQL> create database link samplink connect to "oracle" identified by "oracle1" using 'armcdb2';

Database link created.

When I run a select query I get errors as follows:

SQL> select * from mdiaz.AREMAIL@samplink;

select * from mdiaz.AREMAIL@samplink

ERROR at line 1:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

408

Below is the Trace file of dg4odbc:

Oracle Corporation --- THURSDAY JUL 11 2013 16:06:03.627

Heterogeneous Agent Release

11.2.0.1.0

Oracle Corporation --- THURSDAY JUL 11 2013 16:06:03.627

Version 11.2.0.1.0

Entered hgogprd

HOSGIP for "HS_FDS_TRACE_LEVEL" returned "255"

Entered hgosdip

setting HS_OPEN_CURSORS to default of 50

setting HS_FDS_RECOVERY_ACCOUNT to default of "RECOVER"

setting HS_FDS_RECOVERY_PWD to default value

setting HS_FDS_TRANSACTION_LOG to default of HS_TRANSACTION_LOG

setting HS_IDLE_TIMEOUT to default of 0

setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"

setting HS_NLS_NCHAR to default of "AL32UTF8"

setting HS_FDS_TIMESTAMP_MAPPING to default of "DATE"

setting HS_FDS_DATE_MAPPING to default of "DATE"

setting HS_RPC_FETCH_REBLOCKING to default of "ON"

setting HS_FDS_FETCH_ROWS to default of "100"

setting HS_FDS_RESULTSET_SUPPORT to default of "FALSE"

setting HS_FDS_RSET_RETURN_ROWCOUNT to default of "FALSE"

setting HS_FDS_PROC_IS_FUNC to default of "FALSE"

setting HS_FDS_CHARACTER_SEMANTICS to default of "FALSE"

setting HS_FDS_MAP_NCHAR to default of "TRUE"

setting HS_NLS_DATE_FORMAT to default of "YYYY-MM-DD HH24:MI:SS"

setting HS_FDS_REPORT_REAL_AS_DOUBLE to default of "FALSE"

setting HS_LONG_PIECE_TRANSFER_SIZE to default of "65536"

setting HS_SQL_HANDLE_STMT_REUSE to default of "FALSE"

setting HS_FDS_QUERY_DRIVER to default of "TRUE"

HOSGIP returned value of "FALSE" for HS_FDS_SUPPORT_STATISTICS

Parameter HS_FDS_QUOTE_IDENTIFIER is not set

setting HS_KEEP_REMOTE_COLUMN_SIZE to default of "OFF"

setting HS_FDS_GRAPHIC_TO_MBCS to default of "FALSE"

setting HS_FDS_MBCS_TO_GRAPHIC to default of "FALSE"

Default value of 64 assumed for HS_FDS_SQLLEN_INTERPRETATION

setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics;gtw$:SQLGetInfo"

setting HS_FDS_DELAYED_OPEN to default of "TRUE"

setting HS_FDS_WORKAROUNDS to default of "0"

Exiting hgosdip, rc=0

ORACLE_SID is "dg4odbc"

Product-Info:

Port Rls/Upd:1/0 PrdStat:0

Agent:Oracle Database Gateway for ODBC

Facility:hsa

Class:ODBC, ClassVsn:11.2.0.1.0_0008, Instance:dg4odbc

Exiting hgogprd, rc=0

hostmstr: 0: HOA After hoagprd

hostmstr: 0: HOA Before hoainit

Entered hgoinit

HOCXU_COMP_CSET=1

HOCXU_DRV_CSET=871

HOCXU_DRV_NCHAR=873

HOCXU_DB_CSET=871

HOCXU_SEM_VER=112000

Entered hgolofn at 2013/07/11-16:06:03

HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/etc/newlib/libodbc.so"

Entered hgolofns at 2013/07/11-16:06:03

libname=/etc/newlib/libodbc.so, funcname=SQLAllocHandle

symbol_peflctx=0xaab7c2f0

hoaerr:0