asked on

ORA-02063 and ORA-28500 - Database Link to AS400

after a huge headache and lots of help from this community, i was able to configure my oracle machine to read from the AS400, i know it works because i can connect to the as400 using isql

isql points to the appropriate odbc settings and i can actually see the tables on the as400 when in isql.. when i created a database link using

CREATE DATABASE LINK "as400.mycompany.com" CONNECT TO "orcl" IDENTIFIED BY orcl USING 'as400';

Open in new window

I can create the link successfully.

but when i try to select from dual using

select * from dual@"as400.mycompany.com";

Open in new window

i get the following error

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from AS400.mycompany.COM
28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:"
*Cause: The cause is explained in the forwarded message.
*Action: See the non-Oracle system's documentation of the forwarded
message.
Error at Line: 3 Column: 19

-------------

I spent all night researching this error and trying different things to no avail, anyone with experience in database links in a HS environment, your help would be greatly appreciated

slightwv (䄆 Netminder)

>>select * from dual@"as400.mycompany.com";

I didn't see this in your previous question. Does DB2 have a DUAL table? I thought that was specific to Oracle.

A quick Google says the equivelent is sysibm.sysdummy1

http://www.dbforums.com/db2/975637-dual-table-db2-udb.html

Try:
select 'test' from sysibm.sysdummy1@"as400.mycompany.com";

Member_2_2484401

What happens when you select from an actual table on the AS/400?

Since Oracle is the only database vendor that uses "dual", it might be getting confused with that. DB2 uses SYSDUMMY1 in schema SYSIBM.

HTH,
DaveSlash

gnivkor

ASKER

same error

gnivkor

ASKER

i tried various variations of selecting from sysibm.sysdummy1

tdthoa

Hello gnivkor,

1/ Please try, I want test the connect to as400 by user orcl
connect orcl/orcl@as400
select 3+4 from dual;

2/ if step 1 is OK then try
CREATE DATABASE LINK dblink CONNECT TO orcl IDENTIFIED BY orcl USING 'as400';
select 3+4 from dual@dblink;

slightwv (䄆 Netminder)

>>Please try, I want test the connect to as400 by user orcl

as400 is a Heterogeneous Services Link to a DB2 database not another Oracle database.

Qlemo

... and I'm not certain how the asker can be sure the link works at all. Testing the ODBC connection assigned to the HS configuration isn't done with isql. The HS seems to get invoked, as the non-Oracle message shows, but I'm missing the DB2 specific error message.

slightwv (䄆 Netminder)

Here is the link to the related qutesion where HS was being set up:
https://www.experts-exchange.com/questions/27670889/Database-link-to-AS400.html

There is a slightly different error message at the end of it but it didn't make sense to me:
(unixODBC)(Driver Manager) Data Source Name {I}

Qlemo

Not sure about anything here. On Windows I would enable ODBC tracing in ODBC Manager to see what happens, but with Linux I do not know. Maybe try to use intentionally wrong data, like a non-existent DSN, for troubleshooting ...

tdthoa

Maybe Cause The problem is caused by a corruption of the DG4ODBC connect string. Please add to your initdg4odbc.ora file located in the Oracle_Home/hs/admin directory the parameter HS_LANGUAGE=american_america.we8iso8859p1. Then retry the connection from a new SQL*PLUS session

gnivkor

ASKER

tdthoa, i tried that, did not work..

qlemo, im not saying that the link on oracle was properly setup, im saying that odbc is properly configured...

when i do isql as400 i can see my as400 tables and schema

Qlemo

I understood that well. To see if the dblink works and accesses the ODBC DSN, ODBC tracing is one of some tools one can use. Using a network capture tool like WireShark to filter relevant IP traffic is another. And using intentionally wrong info in DSN or dblink definitions is another to verify that all settings are honoured.

gnivkor

ASKER

not sure if the follow is relevant, but i got this from listener alert log .xml

 <txt>11-APR-2012 10:08:49 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=orcl)(USER=root))(COMMAND=status)(ARGUMENTS=64)(SERVICE=xe)(VERSION=186647040)) * status * 0
 </txt>
</msg>
<msg time='2012-04-11T10:08:52.675-04:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='orcl'
 host_addr='127.0.0.1'>
 <txt>WARNING: Subscription for node down event still pending
 </txt>
</msg>
<msg time='2012-04-11T10:08:52.675-04:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='orcl'
 host_addr='127.0.0.1'>
 <txt>11-APR-2012 10:08:52 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=orcl)(USER=root))(COMMAND=status)(ARGUMENTS=64)(SERVICE=as400)(VERSION=186647040)) * status * 0
 </txt>
</msg>
<msg time='2012-04-11T10:09:48.334-04:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='orcl'
 host_addr='127.0.0.1'>
 <txt>11-APR-2012 10:09:48 * (CONNECT_DATA=(SID=xe)(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=John T))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.3.190)(PORT=54696)) * establish * xe * 0
 </txt>
</msg>
<msg time='2012-04-11T10:09:50.092-04:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='orcl'
 host_addr='127.0.0.1'>
 <txt>11-APR-2012 10:09:50 * service_update * XE * 0
 </txt>
</msg>
<msg time='2012-04-11T10:10:02.107-04:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='orcl'
 host_addr='127.0.0.1'>
 <txt>11-APR-2012 10:10:02 * service_update * XE * 0
 </txt>
</msg>
<msg time='2012-04-11T10:10:32.139-04:00' org_id='oracle' comp_id='tnslsnr'

Open in new window

gnivkor

ASKER

i did try intentionally wrong settings on odbc to see if i can connecting using the dsn via isql and when i used the wrong settings isql would not work, not sure how to go about testing that on the oracle end if from my understanding oracle is calling those odbc settings that works with isql

gnivkor

ASKER

not sure if this is relevant, but

11-APR-2012 10:09:48 * (CONNECT_DATA=(SID=xe)(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=John T))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.3.190)(PORT=54696)) * establish * xe * 0

Open in new window

John T is my windows username, why is this information being sent to oracle at all ?

gnivkor

ASKER

also, when i check " /u01/app/oracle/diag/rdbms/xe/XE/trace/alert_XE.log"

i see

Thu Apr 12 14:50:31 2012
CJQ0 started with pid=22, OS id=6517
Thu Apr 12 14:50:57 2012
HS:  Created new FDS class definition in server DD
HS:    Class id = 175, class name = ODBC11.2.0.2.0_0008
HS:  Created new FDS class definition in server DD
HS:    Class id = 176, class name = ODBC
HS:  Created new FDS instance definition in server DD
HS:    Instance id = 88, instance name = as400 (class ODBC11.2.0.2.0_0008)
Thu Apr 12 14:55:30 2012
Starting background process SMCO
Thu Apr 12 14:55:30 2012
SMCO started with pid=28, OS id=6556

Open in new window

gnivkor

ASKER

another reason why i think the ODBC link is working is because i purposely created a DB link with the wrong credentials and i got

An error was encountered performing the requested operation:

ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from ODBC2
01017. 00000 -  "invalid username/password; logon denied"
*Cause:    
*Action:
Vendor code 1017

Open in new window

Qlemo

You still get ORA errors, not DB2 error messages. Oracle should not check the credentials, that's the task of DB2 resp. the ODBC (or whatever is used) driver. You should get an generic ORA error, and as detail something that looks like the error message you get with isql and the wrong DSN.

tdthoa

Hello gnivkor, please make sure the user orcl with password orcl exists in the as400 database, then try again create database link as
CREATE DATABASE LINK "as400.mycompany.com" CONNECT TO "orcl" IDENTIFIED BY "orcl" USING 'as400';
If you still get errors, This is a problem with the AS400 configuration and not the Gateway. The Change DDM TCP/IP Attributes (CHGDDMTCPA) command provides an interface to configure parameters for the use of DDM or DRDA over TCP/IP.
Set the CHGDDMTCPA option -
Password required (PWDRQD)
to something other than *NO.
The *NO option means -
Do not require a password on a DDM connection request. If a password is sent, it is ignored. See also *VLDONLY description.

gnivkor

ASKER

It is not a problem at the AS400,

I can connect and select data on the AS400 via ODBC using access on the windows machine, ODBC on the as400 side is working fine

tdthoa

If HS_FDS_SUPPORT_STATISTICS=TRUE, so try HS_FDS_SUPPORT_STATISTICS=FALSE

gnivkor

ASKER

i had it at false, and turned to true. same results

gnivkor

ASKER

anyone else, i am still tuck on this.. i built a new linux system on opensuse, followed instructions, same issue.. i know my odbc to as400 works, i think the problem is at the oracle config level

slightwv (䄆 Netminder)

Probably at this point I would open an SR with Oracle Support. They will have the tools the help troubleshoot this.

I will send out a call for help to see if we can get additional Experts involded.

gnivkor

ASKER

i do not have oracle support :(

ASKER CERTIFIED SOLUTION

gnivkor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

I'm just glad you found a solution. Feel free to accept your last post as the answer.

slightwv (䄆 Netminder)

Although I appreciate the points, I'm afraid the post you accepte cannot be the solution since it didn't address the question.

I have unaccepted the question. Please accept your solution as the answer.

slightwv
Zone Advisor

gnivkor

ASKER

issue was resolved