Database Software

Hello Oscar

OWB support ether ODBC data sources (via Oracle heterogeneous connectivity) or Transparent gateway products. Second are preferred if exist for the desired platform.
I believe DB2 ODBC driver could be found in “DB2 Connect” software package you’ve mentioned (at least, at IBM web site description for fix pack on this product contain a lot of ODBC driver reference).

Sergey.

thanks

I've installed el odbc... now i'm in warehouse builder and when i try to create a database link i have the follow messages error:


ora-28511: lost RPC connection to heterogeneous remote agent using SID=""
ora-02063: preceding "" from ""

i write:

dblink name:
create public database link:
host name: (server oracle)
port number: (1521)
oracle service name: service name oracle

and user name and password for my user of my oracle database, then i try writing the user name and password of my as400.

Now i don't know how configure my warehouse builder, because i have 3 odbc for my client access...

Any idea?? are the values correct??

Thanks a lot for your help
Alex

Have you checked that your ODBC driver work properly? You may perform the check by connecting to the DB2 over ODBC from for example MS Access or MS Excel.
To connect to the ODBC source from Oracle you have to configure heterogeneous service. On the box that have ODBC driver (and DB2 client, of coarse) installed you have to install and configure Oracle TNS Listener and configure it to bind some SID to ODBC source. After that you’ll be able to create database link to this service (node + SID) to gain access to your ODBC source.
Please refer to the Oracle documentation (Oracle Net -> Heterogeneous services) for the details of configuring this. Also, you may search this forum - there were number of discussions on HS here.

Sergey.

Use QUICK START GUIDE: UNIX - Generic Connectivity using ODBC Doc ID: Note:115098.1 as a starting point. If you cannot connect through the HS ODBC configured generic connectivity, OWB will not work. Once you have this working, setting it up in OWB to use the existing db link is easy. It has been a long time since I set this up, and since I am on vacation, I do not have access to my notes. If you can, log a service request against the HS ODBC component of the database stating you need assistance configuring the connection from sqlplus. If you log this against OWB, the first question my teammates will ask is: "Does the connection work when a query is sent from sqlplus?" As soon as you say no or it hasn't been tested, they will transfer it to the HS group. Client Access and the replacement product generally work well. There have been problems in the past with localizations, but IBM generally responds quickly if you can show it works with the US English version. If your Oracle Database isn't on Unix, but is on Windows, use Note:114820.1.

Having three ODBC configurations does not matter, they will just be three different 'aliases' in the "inithsodbc.ora" file. You would use them as you would any sources.

I don't know if I will have a chance to check this in the next week, but I hope this helps.

Barry

thanks for your help....

I can connect via client access and excel without problems. These are my files:

Listener

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = mxctzap06)(PORT = 1521))
)
)
)



SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = bd10g)
(ORACLE_HOME = D:\oracle_bd)
(PROGRAM = hsodbc)
(ENVS=LD_LIBRARY_PATH=C:\Program Files\IBM\Client Access\Shared)
)
)

hsodbc =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mxctzap06)(PORT = 1521))
(CONNECT_DATA =
(SID=bd10g)
)
(HS=OK)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

Initbd10g.ora file...(i dont know if the name should be inithsodbc.ora)


#
# HS init parameters
#
HS_FDS_CONNECT_INFO = MXCTZAS3 ---- (odbc name in system DNS)
HS_FDS_TRACE_LEVEL = ON
HS_DB_NAME = BD10G



#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>



The definition of my dblink is: (created in SQL*PLUS)

CREATE PUBLIC DATABASELINK conx402
CONNECT TO "source_user"
IDENTIFIED BY "password_source"
USING 'hsodbc';

When i try to read data from my source i have the follow message error:

ORA-12154 TNS:could not resolve service name

I can log me via SQL*PLUS without problems.

If i create my db link with warehouse builder i have the follow message error when the connection is probe it.

probando...
fallo
SQL Exception
Error del repositorio; Exception SQL
Nombre de la clase: CacheMediator.
Nombre del Método:getDDEntryFromDB.
Mensaje de Error del Repositorio: ORA 28545: error
diagnosed by Net8 when connecting to an agent
NCRO: Failed to make RSLV connection
ORA-02063: preceding 2 lines from CONX403

are these files correct? Do I need to do something else??

I followed oracle database heterogeneous connectivity cap 4 and 7.



I'm desperate....
Thanks for your help.
Alex

Mensaje editado por:
Oscar Ramirez

Hello, Oscar

1. Init file name must be "inithsodbc.ora" and it must be placed at %ORACLE_HOME%/hs/admin

2. I don't quite understand your statement "I can log me via SQL*PLUS without problems." Does that mean that you are able to issue for example SELECT * FROM USER_TABLES@hsodbc and it will work proprly?
If so, and you still get ORA-12154 than make sure that all oracle homes at Oracle server box that hosts OWB runtime repository (and design repository - if it different box) has they tnsnames.ora in sync and all they contain description of you 'hsodbc' service.

If you are not willing to keep Net configuration files in different homes in sync (sure you are) - place them (config files - sqlnet.ora, tnsnames.ora ...) at shared location and set TNS_ADMIN environment variable at each home / registry entries (for Windows) to point to this shared location.

Sergey

Serhit:

Finally i can view data in SQL*PLUS. i can:

Select * from table@dblink ... and work fine.
Desc table@dblink ... and work fine.

Now my problem is:

When i want to import my table definitions from a source module in OWB in the available objects ... there aren´t objects.

But i can view all my object in SQL*PLUS.

I´m working with OWB 10.1.0.2 and the same database version. What can i do for solve this?

Thanks a lot.
Alex

Hello, Alex

Could you manage to import object definition from Oracle schema in OWB? I'm asking this as I've faced the issue when OWB failed to import object definition by showing nothing as import wizard.

If you run from sqlplus:
SELECT OBJECT_NAME, OBJECT_TYPE
FROM USER_OBJECTS@DB2_DBLINK
would you see objects of TABLE or VIEW type?

Sergey

Sergey maybe could be the client access driver version... only 2 version are supported.

Version 7.00.02.00 and 6.00.07.00

I solved my problem with this and view all my objects. Unfortunely i had to reinstall my oracle sw and when i have the same problem again.

ORA:28511 lost rpc...

I did the installation with the same parameters orale_home, sid name, and copy the files tnsnames, initsid.ora and listener and nothing.

Do you know if any services in task manager should be started.... like hsodbc??

Thanks for your help.
Alex

did u create the database link in the design repository or the target database.

for u to import the metadata from the as400 system, the link should be in the design repository.

To run gateway you should start listener service - it allows for accepting the connection and starting HS.

Do you have listener configured with the same name as previous?

Sergey.