1 / 37

Designing Mappings with the Oracle Data Integration Enterprise Edition License

Designing Mappings with the Oracle Data Integration Enterprise Edition License. Objectives. After completing this lesson, you should be able to: Explain how to define new code templates for heterogeneous connectivity to non-Oracle sources Work with execution units

anisa
Télécharger la présentation

Designing Mappings with the Oracle Data Integration Enterprise Edition License

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Designing Mappings with the Oracle Data Integration Enterprise Edition License

  2. Objectives • After completing this lesson, you should be able to: • Explain how to define new code templates for heterogeneous connectivity to non-Oracle sources • Work with execution units • Use the execution view and logical view of the mapping • Deploy CT mappings • Manage the privileges required of Java 2, Enterprise Edition users and agents for placing agents in sources

  3. Lesson Agenda • Comparing traditional OWB mappings with CT mappings • Using code templates in mappings • Combining traditional OWB mapping functionality with CT functionality • Deploying CT mappings • Privileges for J2EE users and agents • Starting the default agent • Setting up credentials for the default agent • Mapping modules and agent locations

  4. Traditional Versus CT Mappings • When creating new mappings, you first need to consider whether you will be using code templates. • This determines under which node to create the mapping. Code template mappings Traditional mappings

  5. Integrate CTs into an OWB Mapping Framework OWB OWB UI OWBRepository AddOWBxforms Deploymap toJ2EE Startmap Maps Codetemplates ApplyCT tomap Deploymap toJ2EE Startmap CTs Import some CTs

  6. Additional Tasks for Using CTs in Mappings 1. Create project. Minimum set of metadata necessary toconnect to a source or target system 2. Define a platform. 3. Import code templates (CTs) that you will be using. Use code templates for direct, native access to non-Oracle systems. 4. Construct mapping. 4.a. Create execution units, assign code template to each execution unit. 5. Validate mapping. 6. Generate mapping. 7. Deploy mapping. 8. Execute mapping.

  7. Lesson Agenda • Comparing traditional OWB mappings with CT mappings • Using code templates in mappings • Combining traditional OWB mapping functionality with CT functionality • Deploying CT mappings • Privileges for J2EE users and agents • Starting the default agent • Setting up credentials for the default agent • Mapping modules and agent locations

  8. Execution Units in a CT Mapping Unit Unit SQL Serversourceexecution unit Oracle targetexecution unit Unit DB2 sourceexecution unit Code Template Code template

  9. Codetemplate SQL to Oracle Codetemplate SQL to SQL Codetemplate Data Pump Execution View Versus Logical View Code template mapping Logical view The active configuration brings into play its execution view. Alternate Execution view(physical design) Defaultconfiguration(active) Execution Unit Execution Unit Execution Unit Target execution unit Assign each execution unit a code template. Source execution unit - SQL to Oracle- Oracle target - Parallel - . . . . Codetemplate Oracle target

  10. Execution unit Execution unit LCT_SQL_TO_ORACLE Execution unit DEFAULT_ORACLE_TARGET_CT LCT_SQL_TO_SQL Default Configuration and Its Execution View Default configuration

  11. Execution unit Execution unit LCT_ORACLE_TO_ORACLE_DBLINK DEFAULT_ORACLE_TARGET_CT Execution unit LCT_SQL_TO_SQL Alternate Configuration and Its Execution View Alternate configuration

  12. LCT_SQL_TO_ORACLE DEFAULT_ORACLE_TARGET_CT LCT_SQL_TO_SQL Load Mechanics of Template-Based Mappings Customers Cust Info Integrate GIS Details Target Staging

  13. OWB 11g Release 2 offers many location properties, logically grouped among six tabs: Details Registration Advanced Prefix Mask Connection Pool New Properties in the Location Dialog Box Support Code Template Mappings

  14. Location Information for CT-Based Mappings Customers Cust Info GIS Details Target Staging

  15. Staging Table Prefixes

  16. Create a List of Favorite Operators Operators used in execution units that have code templates assigned for loading or integration

  17. Quiz • Which of the following statements are true? • Each configuration can have its own unique execution view. • An execution unit can be assigned multiple, concurrent code templates. • Each execution unit is assigned a single code template.

  18. Lesson Agenda • Comparing traditional OWB mappings with CT mappings • Using code templates in mappings • Combining traditional OWB mapping functionality with CT functionality • Deploying CT mappings • Privileges for J2EE users and agents • Starting the default agent • Setting up credentials for the default agent • Mapping modules and agent locations

  19. Convert a Classic Mapping to a CT Mapping That Utilizes Data Pump Classic set-based SQL mapping Logical view:Move Oracle table between two Oracleinstances Generationresults:INSERTstatement

  20. Copy and Paste a Classic Mapping to a Template Mapping Node From the classic mapping node… … to the template mapping node

  21. Execution View of a Code Template Version of the Mapping We can replacethis defaultOracle target CTwith one that usesData Pump.

  22. Assign the USE_DATA_PUMP Code Template Execution unit with Data Pump template assigned

  23. Lesson Agenda • Comparing traditional OWB mappings with CT mappings • Using code templates in mappings • Combining traditional OWB mapping functionality with CT functionality • Deploying CT mappings • Privileges for J2EE users and agents • Starting the default agent • Setting up credentials for the default agent • Mapping modules and agent locations

  24. Warehouse Builder 1. 2. 3. 4. Platform properties Data typemapping design Connectivity(JDBC, ODBC, and Native) Code templatesin mapping Metadata infrastructure Reminder of CT-Based Mapping Architecture • Code template–based mapping =Mapping design + code templates + platform definition

  25. C$ = prefix for load table for staging DS = data source credentials (location details) ExU = execution unit Client OWB Control Center Service Control Center Agent Design Client Oracle DB DB2 Repository Local OC4J OWB Code Source Tables Design Workspace1 Audit Tables Local OC4J SQL Server Browser Server Data Schema TGT1 Source Tables Design Reports Audit Reports TGT2 OWB Browser OWB Topology with CCA on Oracle Target Server Deploy Deploy DS1 DS3 Map1 Execute DS2 Execute ExU 1 ExU2 Remote SQL Execute C$EXU1 ExU3 ExU3 C$EXU2 Stage Loads data from staging to target Load CT Integrate CT

  26. Privileges for J2EE Users and Agents CT-driven mappingsin heterogeneous environmentsrequire agents to which to deploy.

  27. 1. Starting the Default Agent In Linux, open a terminal session, navigate to <OWB_HOME>/owb/bin/unix, and perform the following: We will use welcome as the OC4J Administrator’s password In Windows, navigate to <OWB_HOME>\owb\bin\win32 and execute: ccastart.bat

  28. 2. Setting Up Credentials for Default Agent welcome

  29. 3. Mapping Modules and Agent Locations CT-based heterogeneous mappings must be created in the top-level Template Mappings node.

  30. Agents Need Information About Locations

  31. Installing the OWB Agent on Another System

  32. Execute Remote SQL Load CT Integration CT OWB Infrastructure with Agent on Oracle Target OWB design client 1 Deploy EAR file to agent.Contains all SQL. 5 Audit System A - SQL Server Source 2 System B – Oracle Target Execute Local SQL Agent 3 SQL Server Database Oracle Database 3 Move data over JDBC to staging table 4 Loads data from staging to target 6 6

  33. Load CT Integration CT OWB Infrastructure with Agent on Remote Source OWB design client 1 Deploy EAR file to agentContains all SQL Audit 5 2 Execute SQL System A - SQL Server Source Agent System B – IBM DB2 Target Execute Remote SQL 3 SQL Server Database Oracle Database 3 Move data over via SQL Loader 4 Loads data from staging to target 6 6

  34. Monitoring Deployments with HRAB Reports • Heterogeneous Repository Audit Browser (HRAB) reports are useful for monitoring deployments to heterogeneous databases. • Scripts are provided for you to audit the deployment and execution of mappings to Oracle, DB2, and SQL Server environments. • The HRAB reports are a subset of the Repository Browser reports, with fewer details.

  35. Quiz • CT-driven mappings in heterogeneous environments require which of the following for successful deployment? • Pluggable mappings • Database links • Agents • All of the above

  36. Summary • In this lesson, you should have learned how to: • Explain how to define new code templates for heterogeneous connectivity to non-Oracle sources • Work with execution units • Use the execution view and logical view of the mapping • Deploy CT mappings • Manage the privileges required of Java 2, Enterprise Edition users and agents for placing agents in sources

  37. Targetmodule MDL-importedproject Targettable CT-basedmapping Sourcemodule Sourcetables Sourceflat file Practice 4-1 Overview: Using Code Templates in Mappings • This practice covers the examination and modification of a code template–based mapping.

More Related