Solved

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

Posted on 2012-04-12
29
5,238 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:gnivkor
  • 14
  • 6
  • 4
  • +2
29 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37838257
>>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";
0
 
LVL 18

Expert Comment

by:daveslash
ID: 37838289
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
0
 

Author Comment

by:gnivkor
ID: 37838344
same error
0
 

Author Comment

by:gnivkor
ID: 37838346
i tried various variations of selecting from sysibm.sysdummy1
0
 
LVL 1

Expert Comment

by:tdthoa
ID: 37838637
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;
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37838646
>>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.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 37838749
... 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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37838774
Here is the link to the related qutesion where HS was being set up:
http://www.experts-exchange.com/Database/Oracle/Q_27670889.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}
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 37838905
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 ...
0
 
LVL 1

Expert Comment

by:tdthoa
ID: 37838952
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
0
 

Author Comment

by:gnivkor
ID: 37839280
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
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 37839304
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.
0
 

Author Comment

by:gnivkor
ID: 37839314
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

0
 

Author Comment

by:gnivkor
ID: 37839335
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
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:gnivkor
ID: 37839343
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 ?
0
 

Author Comment

by:gnivkor
ID: 37839456
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

0
 

Author Comment

by:gnivkor
ID: 37839520
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

0
 
LVL 68

Expert Comment

by:Qlemo
ID: 37840271
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.
0
 
LVL 1

Expert Comment

by:tdthoa
ID: 37840935
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.
0
 

Author Comment

by:gnivkor
ID: 37843323
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
0
 
LVL 1

Expert Comment

by:tdthoa
ID: 37843754
If HS_FDS_SUPPORT_STATISTICS=TRUE, so try HS_FDS_SUPPORT_STATISTICS=FALSE
0
 

Author Comment

by:gnivkor
ID: 37843943
i had it at false, and turned to true. same results
0
 

Author Comment

by:gnivkor
ID: 37851659
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37851678
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.
0
 

Author Comment

by:gnivkor
ID: 37851868
i do not have oracle support :(
0
 

Accepted Solution

by:
gnivkor earned 0 total points
ID: 37852081
issue is finally resolved.. the problem was with the naming of the initNAME.ora in the hs/admin/ dir, it is case sensitive so i had initAS400.ora and needed initas400.ora,

I think oracle could have done a better job with the ORA-28500 and 02063 in order for easier trouble shooting, the generic message is very vague and does not lead you on the right direction.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37852089
I'm just glad you found a solution.  Feel free to accept your last post as the answer.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37852099
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
0
 

Author Closing Comment

by:gnivkor
ID: 37875067
issue was resolved
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now