Solved

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

Posted on 2012-04-12
29
5,428 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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 69

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 69

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 69

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
 

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 69

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
constraint check 2 40
pl/sql - query very slow 26 61
Mongo DB 18 46
Creating a record in a database with static data from MVC to SQL (Entity) 2 4
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

810 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