Forum Stats

  • 3,815,688 Users
  • 2,259,067 Discussions
  • 7,893,207 Comments

Discussions

Create a Data Source from a DB_LINK

701532
701532 Member Posts: 29
edited Oct 5, 2009 7:15PM in Warehouse Builder
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.
Tagged:

Answers

  • David Allan-Oracle
    David Allan-Oracle Member Posts: 2,479 Employee
    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
    701532 Member Posts: 29
    edited Aug 12, 2009 7:06PM
    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
    David Allan-Oracle Member Posts: 2,479 Employee
    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
    620489 Member Posts: 4
    edited Aug 13, 2009 5:17AM
    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
    701532 Member Posts: 29
    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
    David Allan-Oracle Member Posts: 2,479 Employee
    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
    701532 Member Posts: 29
    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
    David Allan-Oracle Member Posts: 2,479 Employee
    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
    701532 Member Posts: 29
    edited Oct 5, 2009 7:15PM
    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
This discussion has been closed.