Create a Data Source from a DB_LINK

701532

Hello,

I want know the way to create a Data Source from a DB_LINK, i have try some many ways and anyone works. I will appreciate your help to do this. I already check my db link and works fine.

Thanks,

Best Regards.

Carlos Robles.

David Allan-Oracle

Hi Carlos

When you create the location for your source module there is a type property that by default is HOST:PORT:SERVICE but you can change it to Database Link, you get to choose a location where the db link is defined, then you get to choose the database link.

Cheers
David

701532

Ok, i already done that i select DB_LINK the problem was on the Schema Name prompt ????. I think i am creating bad the Source from a DB Link i suposse i have to use a new Non Oracle Source or i'm wrong?

I create a source from a DB Link from a new Non oracle source but i don't know how import all the objects, when i try to do this i have a error message.

http://img233.imageshack.us/i/schemar.jpg/

http://img232.imageshack.us/i/sourcewithsqlserverfrom.jpg/

http://img80.imageshack.us/i/errorb.jpg/

Thanks.

David.

Edited by: Carlos Robles on 12-ago-2009 15:58

David Allan-Oracle

Hi Carlos

Edit the module and on the Metadata Location tab set the location to the one you have defined. There are 2 locations defined for a module one is for metadata (used by import when reverse engineering tables etc.) and the other a data location used when reading and writing data.

Cheers
David

620489

Hi Carlos!
I had a problem as you.
SRC: SQLDB (in SQL Server 2005) SQLDB_LOCATION1
TARGET: ABC (in Oracle 10.2.0.4) ABC_LOCATION1
I created 3rd location for module in use
in my situation , i have to create 3rd location ABC_LOCATION2 for a module ABC which i want to load data into from SQLDB.
After that, u need REGISTER ABC_LOCATION1, SQLDB_LOCATION1, ABC_LOCATION2 in CONTROL CENTER MANAGER.

Gd luck!
KhanhND (ZyK)

701532

Hi,

Thanks, for your support both i can't made a source module works, i don't know if the problem is with the Workflow or the Control Center Manager. I do the following:

I create a new Source Module of Non oracle Database in my case SQL Server, so next i used the previous Source Module Location for the DB Link because this DB LINKS is owned by the Original Source Module.

In my case the Source Module Owner is called OFSA and the DB Link Source Module is called MSHS.

So i dont have problems to create that Source module with DB Link i pick on the From Location Combo the OFSA source and the DB LINKS owned by this conection appears i pick MSHS , now i want to import the metada to this DB LINK source module i go modify and set all the iformation of the database owner of this DB LINK.

When i try to import i can see any object of the DB LINK, or got the API2421 error.

Thanks for your help.

Carlos Robles.

David Allan-Oracle

Can you get the db link created in the OWB user you are connected to the repository with when you do the import?

If you run the owbclient.bat or owbclient.sh scripts it dumps out the query it performs to list the tables. If you run this using SQLPlus when connected as the OWB user this is what the import process is doing. You may need a db link in that user for doing the import AND in the target user where the map executes.

Cheers
David

701532

Ok, so if i understand well you told me, i have to create a 2 new DB LINKS one for the Repository Owner of the metada and another to the target user.

I created the PUBLIC DB LINK so all the users have access to this DB LINK, so i'm wrong with this???

Thanks,

Carlos Robles.

David Allan-Oracle

Hi Carlos

A public database link will work well, with this follow the following steps...

There is a bit of a OMB hack to setup the metadata location since you have noticed you get the no metadata msg pop up. Here is a quick overview.

Define your non-Oracle location using the database link type, select an Oracle location that can access the database link, in the example I have a database link named SS_DBL_LOCATION that points to a SQLServer instance;
!http://blogs.oracle.com/warehousebuilder/owb_dbl1.JPG!

Create a SQLServer module and set the location to the location we just created;
!http://blogs.oracle.com/warehousebuilder/owb_dbl2.JPG!

When you hit finish on the module dialog you will get the no metadata error;
!http://blogs.oracle.com/warehousebuilder/owb_dbl3.JPG!

To fix this, the UI does not let you pick the location you just defined and assigned (it is set OK for the data location value of the module BUT not the metadata), go to the OMBPlus panel and set the metadata location for your module (below my module name was SS_DATA and I changed context into the project DBLINK_BASED_CONNECTIVITY in order for this to work);
!http://blogs.oracle.com/warehousebuilder/owb_dbl4.JPG!

Save at this point.

After this you can import objects from SQLServer using the database link you have pre-created. When you use these objects in a map the database link you have selected should be used when referencing the tables rather than an OWB generated one.

Cheers
David

701532

Hi David,

Thanks, your answer was right. I made all the changes and now i can import the metada from a DB Link, thank you so much.

Best regards,

Carlos Robles.

Edited by: Carlos Robles on 05-oct-2009 16:15