This discussion is archived
4 Replies Latest reply: May 14, 2013 2:12 AM by 859515 RSS

Confusing MSSQL server Physical Schema Definition

859515 Newbie
Currently Being Moderated
Hi,

I am trying to learn ODI 11g and currently I am working under Physical Schema Definition Under Topology Navigator. I want to Import data from MS SQL server to Oracle; I have defined the source (MSSQL Server) and target (Oracle) servers. Testes the connections and they are working fine.

While defining the Physical Scheme for MS SQL server I am confused with following fields. Can anybody help me to understand there usage? I will be very greatful

1)     Database (Catalog)
2)     Owner (Schema) Here I have read-only user which I already defined as part of the SQL server definition. It is not appearing in the list.
3)     Database (Work Catalog)
4)     Owner (Work Schema)

Best regards,
Muhammad
  • 1. Re: Confusing MSSQL server Physical Schema Definition
    PeakIndicators_Alastair Guru
    Currently Being Moderated
    Hi,
    Database is the DB name of the actual tables (e.g source data).
    Owner in MSSQL terms is typically dbo (or other if its different in your source db)
    Work Catalog database is the loation of any temporary ODI objects (eg C$, J$, I$ tables etc)
    Work schema is the owner of the database (Work Catalog) value - again , typically dbo.

    Rgrds
    Alastair
  • 2. Re: Confusing MSSQL server Physical Schema Definition
    859515 Newbie
    Currently Being Moderated
    Hi,

    First of all I am really thankful for your prompt response. things are quite clear but a couple of questions.

    Database is the DB name of the actual tables (e.g source data).
    ==I am reading from oasis_ho database so Here oasis_ho will be used.
    Owner in MSSQL terms is typically dbo (or other if its different in your source db)
    ==There are only there users Listed. dbo,guest,itwh; I will use dbo as it is owner of the database
    Work Catalog database is the loation of any temporary ODI objects (eg C$, J$, I$ tables etc)
    == I am using a read only user to read the data. How ODI is creating temporary objects in SQL server. My work and master Repository is in Oracle database.
    Work schema is the owner of the database (Work Catalog) value - again , typically dbo.
    == is dbo a user like sys in oracle???

    regards,
    Muhammad
  • 3. Re: Confusing MSSQL server Physical Schema Definition
    PeakIndicators_Alastair Guru
    Currently Being Moderated
    >
    Work Catalog database is the loation of any temporary ODI objects (eg C$, J$, I$ tables etc)
    == I am using a read only user to read the data. How ODI is creating temporary objects in SQL server. My work and master Repository is in Oracle database.
    The temp tables are used for a number of things, remember that at this point in the Topology - ODI Doesnt know if your SQL Server connection is a source or a target, so it wants all the details just in case. Reasons for Temp tables on source might be :
    Source table joins from different data sets with a staging expliitly set on the source,
    Journalised (Change data capture) objects - Change Tables, Journal views (J$ objects) etc.
    If you only use MSSQL tables as sources , you will proably find there are no temp objects created on the soruce side , the C$ tables will go in the Target Work Schema (in this case Oracle) as will the I$ tables. So in your use case, this is normal.
    Work schema is the owner of the database (Work Catalog) value - again , typically dbo.
    == is dbo a user like sys in oracle???
    Kind of - IIRC any user in the sysadmin role that creates objects have those objects created as dbo by default.
    Im no MSSQL DBA though.
    Think of it as a qualifier that ODI will use when generation the select code based on the interface mappings (e.g select xyz from database.catalog.table) etc.

    Hope that helps

    Edited by: PeakIndicators_Alastair on May 14, 2013 8:53 AM

    Edited by: PeakIndicators_Alastair on May 14, 2013 8:54 AM
  • 4. Re: Confusing MSSQL server Physical Schema Definition
    859515 Newbie
    Currently Being Moderated
    Hi,

    Thanks again for your to the point answer and sharing valuable information.

    Best regards,
    Muhammad

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points