Sunopsis White Paper - Import Export Best Practices

Sunopsis v3, Sunopsis Data Conductor v4.1 & Oracle Data Integrator Import Export Best Practices 3.2.x / 4.1.01.x / 10.1.3

May 2007

Import Export Best Practices Copyright © 2006, Oracle. All rights reserved. The Programs (which include both the software and documentation) contain proprietary information; they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright, patent, and other intellectual and industrial property laws. Reverse engineering, disassembly, or decompilation of the Programs, except to the extent required to obtain interoperability with other independently created software or as specified by law, is prohibited. The information contained in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. This document is not warranted to be error-free. Except as may be expressly permitted in your license agreement for these Programs, no part of these Programs may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose. If the Programs are delivered to the United States Government or anyone licensing or using the Programs on behalf of the United States Government, the following notice is applicable: U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the Programs, including documentation and technical data, shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement, and, to the extent applicable, the additional rights set forth in FAR 52.227-19, Commercial Computer Software--Restricted Rights (June 1987). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065. The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup, redundancy and other measures to ensure the safe use of such applications if the Programs are used for such purposes, and we disclaim liability for any damages caused by such use of the Programs. Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. The Programs may provide links to Web sites and access to content, products, and services from third parties. Oracle is not responsible for the availability of, or any content provided on, third-party Web sites. You bear all risks associated with the use of such content. If you choose to purchase any products or services from a third party, the relationship is directly between you and the third party. Oracle is not responsible for: (a) the quality of third-party products or services; or (b) fulfilling any of the terms of the agreement with the third party, including delivery of products or services and warranty obligations related to purchased products or services. Oracle is not responsible for any loss or damage of any sort that you may incur from dealing with any third party.

Import Export Best Practices for SCB

2/17

Table of Contents Table of Contents .............................................................................................................................. 3 Summary ........................................................................................................................................... 4 Understanding Oracle Data Integrator Object Export/ Import ........................................................... 5 How Oracle Data Integrator Calculates IDs for New Objects........................................................ 5 Understanding Relationships between Objects............................................................................. 6 Export Strategies ........................................................................................................................... 9 Exporting an Object with its Child Components......................................................................... 9 Exporting an Object without it’s Child Components ................................................................. 10 Example of Partial Export/Import ............................................................................................. 10 Import in “Synonym Mode” or Duplication and Impact on Object IDs ......................................... 10 Knowing Which Objects to Move Between Repositories............................................................. 13 Importing Objects in Target Repository ................................................................................... 16 Sharing Developed Objects across Repositories ........................................................................ 17

Import Export Best Practices for SCB

3/17

Summary Oracle Data Integrator, and its predecessors, Sunopsis v3 and Sunopsis Data Conductor, is a multiuser tool for developing and deploying Data Integration Projects. Often the development teams are geographically dispersed. The approaches that may be possible to take with the repositories include: •

Connecting to a single central repository set up over the wide area networks

•

Using remote desktop facilities (such as Terminal Services or VNC)

•

To have multiple repositories

Each of these approaches have issues: •

Wide area database connections are not always as reliable as is required for the transactional nature of development in ODI. Experience has shown that these wide area connections introduce “unexpected” consequences in the development process, which detract from effective development.

•

Remote desktop can be painfully slow over wide areas. Experience has shown that “unexpected” consequences happen in this environment too – due to the time delay introduced in navigating the user interface.

•

With multiple repositories, keeping these repositories in sync can be challenging if procedures and best practice are not followed.

This document details a set of best practice, which if followed should reduce the occurrence of problems in any implementation. These procedures and best practice are derived from real-life experience with customers who have chosen to implement in this distributed fashion. The options This document outlines Oracle’s recommended architecture to handle several development sites on different locations. It applies to Sunopsis v3, Sunopsis Data Conductor 4.1 and Oracle Data Integrator 10.1.3. In the following, Sunopsis v3 and Sunopsis Data Conductor may be referred to as Oracle Data Integrator or OracleDI.

Import Export Best Practices for SCB

4/17

Understanding Oracle Data Integrator Object Export/ Import How Oracle Data Integrator Calculates IDs for New Objects To ensure object uniqueness across several work repositories, Oracle DI uses a specific mechanism to construct unique IDs for Objects. At creation time, every work repository has an ID called the “Work Repository ID”. This ID must be unique across all work repositories of an Oracle DI installation. Objects IDs within a given work repository are constructed as follows: <WorkID> Where: •

is a unique ID in the current work repository, issued from the table of IDs (SNP_ID)

•

<WorkID> is the ID of the current work repository th

For example, if you create the 20 Interface in the work repository ID 229, its unique ID would be 20229. The next Interface in the same repository will have ID 21229. Every time a new object ID is constructed the last unique number for this object is incremented in the table SNP_ID. In our example, this table will contain 21 in the field related to the Interfaces unique number. All objects created within the same repository have the same 3 last digits. This mechanism is implemented for 2 reasons: 1. Avoid any ID conflicts when exporting and importing from one work repository to another 2. Understand the provenance of every object, simply by looking at its Internal ID. The 3 last digits always refer to the repository where it was created. The figure below gives an example of the contents of 2 different work repositories:

Import Export Best Practices for SCB

5/17

Figure 1: Example of the content of 2 different work repositories In repository 777, Interface “Refresh Country” has ID 123777. It references the “Country” target table that has ID 370777. When this interface is imported in “Synonym mode” in repository ID 229, its unique ID is preserved and does not conflict with other Interface Ids. Of course, to make it import successfully, the table “Country” with ID 370777 must have been imported previously.

Understanding Relationships between Objects Oracle Data Integrator stores all objects in a relational database schema with dependencies between rd objects. Following the rules of 3 normal form modeling, tables that store these objects in the relational schema have dependencies at the ID level. For example, when you drag and drop a target Datastore into an Interface, only the reference to the ID of this Datastore is stored in the Interface object. If you want to export this Interface, and import it in “Synonym mode” into another work repository, a Datastore with the same ID must already exist in this other work repository. Therefore, the Model or Sub-model holding this Datastore needs to be exported and imported in “Synonym mode” prior to importing the Interface. There are also dependencies between Work Repository objects and Master Repository objects. These dependencies are “Code-based” rather than “ID-based”. This means that only the code names of the Master Repository objects are referenced within the Work Repository. The following table lists all Exportable objects from a Work Repository and their dependencies. It is important to import objects in the appropriate order to preserve these dependencies. Object Type

Dependencies on other objects of Work Repository when importing in Synonym Mode

Model Folder

•

(Parent/Child) Model Folder: Parent Model Folder referenced by current folder needs to be imported first

Model

•

(Parent/Child) Model Folder: Model Folder holding this Model needs to be imported first

Dependencies on objects of the Master Repository None

•

Technology Code

•

Logical Schema Name

Import Export Best Practices for SCB

6/17

Object Type

Dependencies on other objects of Work Repository when importing in Synonym Mode •

(Reference) Model: Models containing References at the Datastore level to other Datastores of another Model need to be imported first

Dependencies on objects of the Master Repository •

Context Code

•

Data Type Codes

•

None

•

(Reference) Knowledge Modules of a Project: When importing a Model, if the Knowledge Modules referenced by this Model are not found, the reference will be set to “null”. Therefore this dependency is a loose one that does not cause the import to fail.

•

(Parent/Child) Model: Model holding this Sub-model needs to be imported first

•

(Parent/Child) Sub-Model: Parent SubModels referenced by current SubModel need to be imported first

Global Variable

•

Global Variables do not depend on any other object

•

Logical Schema Name

Global Sequence

•

Global Sequences do not depend on any other object

•

Logical Schema Name

Global Function

•

Global Functions do not depend on any other object

•

Technology Code

Project

•

(Reference) Model/Sub-Model: all Models/Sub-Models holding Datastore definitions referenced by the Project need to be imported first. Datastore definitions including Columns, Data Types, Primary Keys, Foreign Keys (references), Conditions must be exactly the same as the ones used by the exported project

•

Technology Codes

•

Context Codes

•

Logical Schema Names

•

Data Type Codes

•

Physical Server Names of the Optimization Contexts of Interfaces

•

Technology Codes

•

Context Codes

•

Logical Schema Names

Sub-Model

•

(Reference) Global Variables, Sequences and Functions used within the project need to imported first

States

•

States, in Sunopsis v3, states need to be imported, as the proceeding objects may reference them. Not applicable to Sunopsis Data Conductor or Oracle Data Integrator.

Knowledge Module

•

(Parent/Child) Project: Project holding the Knowledge Module needs to be imported first.

Variable

•

(Parent/Child) Project: Project holding the Variable needs to be imported first.

•

Logical Schema Name

Sequence

•

(Parent/Child) Project: Project holding

•

Logical Schema Name

Import Export Best Practices for SCB

7/17

Object Type

Dependencies on other objects of Work Repository when importing in Synonym Mode

Dependencies on objects of the Master Repository

the Sequence needs to be imported first. Function

•

(Parent/Child) Project: Project holding the Function needs to be imported first.

•

Technology Code

Folder

•

(Parent/Child) Project: Project holding the Folder needs to be imported first.

•

Technology Codes

•

Context Codes

•

(Parent/Child) Parent Folder referenced by current folder needs to be imported first

•

Logical Schema Names

•

Data Type Codes

•

Physical Server Names of the Optimization Contexts of Interfaces

•

Technology Codes

•

Context Codes

•

Logical Schema Names

•

Data Type Codes

•

Physical Server Names of the Optimization Contexts of Interfaces

Interface

•

(Reference) Model/Sub-Model: all Models/Sub-Models holding Datastore definitions referenced by the Folder need to be imported first. Datastore definitions including Columns, Data Types, Primary Keys, Foreign Keys (references), Conditions must be exactly the same as the ones used by the exported folder

•

(Reference) Global Variables, Sequences and Functions used within the Folder need to imported first

•

(Reference) Local Variables, Sequences and Function used within the Folder need to imported first

•

(Reference) Knowledge Modules referenced within the Folder need to be imported first

•

(Reference) Any Interface, Procedure, Variable, Sequence, Function referenced outside of the Folder need to be imported first

•

(Parent/Child) Folder: Folder holding this Interface needs to be imported first.

•

(Reference) Model/Sub-Model: all Models/Sub-Models holding Datastore definitions referenced by the Interface need to be imported first. Datastore definitions including Columns, Data Types, Primary Keys, Foreign Keys (references), Conditions must be exactly the same as the ones used by the exported Interface

•

(Reference) Global Variables, Sequences and Functions used within the Interface need to imported first

•

(Reference) Local Variables, Sequences and Function used within the Interface need to imported first

•

(Reference) Knowledge Modules

Import Export Best Practices for SCB

8/17

Object Type

Dependencies on other objects of Work Repository when importing in Synonym Mode

Dependencies on objects of the Master Repository

referenced within the Interface need to be imported first

Procedure

Package

Scenario

•

(Reference) Any Interface used as source in the current Interface needs to be imported first

•

(Parent/Child) Folder: Folder holding this Procedure needs to be imported first.

•

Technology Codes

•

Context Codes

•

(Reference) Global Variables, Sequences and Functions used within the Procedure need to imported first

•

Logical Schema Names

•

(Reference) Local Variables, Sequences and Function used within the Procedure need to imported first

•

(Parent/Child) Folder: Folder holding this package needs to be imported first.

•

Technology Codes

•

Context Codes

•

(Reference) Interfaces referenced in the Package need to be imported first

•

Logical Schema Names

•

(Reference) Procedures referenced in the Package need to be imported first

•

Data Type Codes

•

Physical Server Names of the Optimization Contexts of Interfaces

•

Technology Codes

•

Context Codes

•

Logical Schema Names

•

(Reference) Model/SubModel/Datastores: all Models/SubModels/Datastores referenced by the package for static check, journalizing or reverse engineering need to be imported first.

•

(Reference) Global Variables, Sequences and Functions used within the Package need to imported first

•

(Reference) Local Variables, Sequences and Function used within the Package need to imported first

Although scenarios appear under folders of a project, they are not tightly linked to this folder. They can be imported independently

Export Strategies When exporting an object you can choose to do it with “Child Component Export” or not. This option allows you to fine tune your exports if you plan to do partial imports of work repository objects.

Exporting an Object with its Child Components This option is the most common when you want to export an object. It allows you to export all subcomponents of the current object along with the object itself. For example, when you choose to export a Project with its child components, the export will contain the Project definition as well as all objects included in the Project, such as Folders, Interfaces, Procedures, Packages, Knowledge Modules, Import Export Best Practices for SCB

9/17

Variables, Sequences, Functions, etc. However, this export will not contain dependent objects referenced which are outside of the Project itself. (such as Datastores and Columns, as defined previously in chapter “Understanding Relationships between Objects”.)

Exporting an Object without its Child Components This option can be useful in some particular situations where you would want to take control of the import process. It allows you to export only the top-level definition of an object without any of its subobjects. For example, if you choose to export a Model without its children, it will only contain the Model definition but not the underlying Sub-models and Datastores when you import this model to a new repository.

Example of Partial Export/Import If you have a very large project that contains thousands of interfaces and you only want to export a subset of these to another Work Repository, you can either export the entire Project and then import it, or choose to do a partial manual export/import as follows: 1. Export all Models referenced by the sub-items of your project and import them in “Synonym mode” in the new repository to preserve their IDs 2. Export the Project without its children and import it in “Synonym mode”. This will simply create the empty Project in the new repository (with the same IDs as in the source). 3. Export every first level Folder you want, without its children, and import them in “Synonym mode”. The empty Folders will be created in the new repository. 4. Export and Import all States (for Sunopsis v3), Knowledge Modules, Variables, Sequences, etc. that are referenced by every object you plan to export, and import them in “Synonym mode”. Refer to section “Import in “Synonym Mode” or Duplication and Impact on Object IDs” for special caution regarding import of Knowledge Modules in Synonym Mode. 5. Finally, export the Interfaces you are interested in and import them in “Synonym mode” in the new repository.

Import in “Synonym Mode” or Duplication and Impact on Object IDs Exported objects keep references to other object IDs in the exported XML files. When an object is exported with its child components, all “container-dependent” objects – those who have a direct parent/child relationship - are also exported. However, referenced objects are not exported. For example, when exporting a Project with its children to an XML file, the file would contain all Folders, Knowledge Modules, Variables, Interfaces, etc. but would not contain any information regarding the referenced source and target tables. Only the referenced ID of these objects will be exported. The figure below gives an example of the content of several export XML files for some objects:

Import Export Best Practices for SCB

10/17

Figure 2: Example of "Container-Dependencies" and "References" in Several XML Export Files 1. Project 21555 was exported including its children. Objects referenced inside the project itself either through parent/child relationships or through reference are consistent inside the scope of the XML file. However, references to tables (Datastores) are not contained in the XML file. 2. Model Folder 1555 was exported without its child components. It only contains an empty Model Folder. 3. Model 1 ID 323555 and Model 2 ID 324555 were exported with their children Now, suppose that we plan to import Project 21555 to a new repository that has a different ID (333, for example). If we simply attempt to import the Project XML file, it would fail with message “Interface 101555 references a table ID (190555) that does not exist in you repository”. So to make sure that the import will succeed, we first need to import all “externally-referenced” objects. In other words, we need to do the imports in this precise order: 1. Import the Model Folder 2. Import Models 3. Finally import the Project To import the Model Folder, we have the choice between “Duplication” and “Synonym mode”. To understand the difference between the 2 modes, here is what would happen if we import the Model Folder ID 1555 to the new work repository ID 333 in either mode: •

Duplication Mode: the Model Folder in the new repository will have a freshly constructed ID: 7333. Therefore when we attempt to import Model 1 or Model 2 that both reference a Model Folder ID 1555, the import will fail with message “Model 323555 references a Model Folder ID (1555) that does not exist in your repository”.

Import Export Best Practices for SCB

11/17

•

Synonym Mode: the Model Folder will keep its initial object ID (1555) in the new repository. Therefore, importing Models will succeed, as their references to the Model Folder will not be violated.

A general definition of import modes can be: •

Importing an object in Duplication Mode: The object’s internal ID is reconstructed inside the new work repository. Container dependencies such as parent/child relationships are recalculated to match the new parent IDs. External reference IDs are not recalculated.

•

Importing an object in Synonym Mode: The object’s internal ID is kept “as is” inside the new work repository. Container dependencies such as parent/child relationships are not recalculated. External references IDs are not recalculated. The sub mode (INSERT, INSERT_UPDATE, UPDATE) will have the following behavior: o

INSERT: Imported object will be treated as a new object. If an object with the same ID exists in the repository the import will have no effect (it will not be replaced).

o

UPDATE: Imported objects will be treated as an already existing object. Its properties and child components will be updated accordingly. If an object with the same ID does not exist in the new repository, the import will have no effect.

o

INSERT_UPDATE: The imported object will be either created, or updated to exactly match the content of the XML file. If the object already exists, this option is not recommended when the export of the initial object was done without its child components as it will potentially delete all sub-components of the existing object.

Following our example, to successfully import our objects to the new work repository 333, we would need to: 1. Import Model Folder 1555 in “Synonym Mode”: This would create a new Model Folder with the same ID inside work repository 333. 2. Import Models 323555 and 324555 in “Synonym Mode”: Models and underlying datastores, columns, constraints, etc. will keep exactly the same IDs thus allowing the Project to be successfully imported as it contains external references to these objects. 3. Import Project 21555 in “Synonym Mode” to allow future updates of the same project. Although Duplication Mode would have worked, it would have not allow you to update your project with future releases from the 555 work repository, because your Project objects would have new IDs ending with 333.

IMPORTANT NOTE: Oracle Data Integrator does not allow import of Knowledge Modules in Synonym Mode. However, this action is necessary when planning to do partial manual imports of Projects. You can do Synonym import of Knowledge Modules by following this procedure: 1. Locate the Knowledge Module XML file on your file system and rename its prefix to “TRT_” instead of “KM_” 2. Right-click on any Folder of your Project and select “Import…> Import Procedure..” menu 3. Select “Synonym Mode INSERT_UPDATE”, go to the directory where your KM is stored on your file system and select your KM, then click OK.

Import Export Best Practices for SCB

12/17

Knowing Which Objects to Move Between Repositories To allow development of new objects in one repository, not all objects of the Target work repository need to be exported. You should decide of the most relevant subset of objects that require development to avoid having a large amount of export files. If you plan to export Folders and Interfaces, it may be useful to automatically determine the list of dependent Models, Variables, Sequences, Functions and Knowledge Modules referenced by your Interfaces. If you execute the following SQL Query on the Target Work Repository, it will return you the Objects that are referenced by a list of Interfaces of a particular Folder. These objects will need to be exported as well if you want to import the Interfaces of that particular Folder. Note that this query only displays objects used by Interfaces. It does not include Sequences dependencies. To run this query, make sure to replace the $$FOLD_ID$$ tag with the actual value of the Folder you want to inspect: select 'Model Used as Target' "Dependency Desc.", M.I_MOD "Object ID", M.MOD_NAME "Object Name" from SNP_MODEL M, SNP_TABLE T, SNP_POP PO where M.I_MOD = T.I_MOD and PO.I_TABLE = T.I_TABLE and PO.I_FOLDER = $$FOLD_ID$$ union select 'Model Used as Source', M.I_MOD, M.MOD_NAME from SNP_MODEL M, SNP_TABLE T, SNP_POP PO, SNP_SOURCE_TAB ST where M.I_MOD = T.I_MOD and T.I_TABLE = ST.I_TABLE and PO.I_POP = ST.I_POP and PO.I_FOLDER = $$FOLD_ID$$ union select 'Loading KM', T.I_TRT, T.TRT_NAME from SNP_TRT T, SNP_POP PO where PO.I_TRT_KLM = T.I_TRT and PO.I_FOLDER = $$FOLD_ID$$ union select 'Integration KM', T.I_TRT, T.TRT_NAME from SNP_TRT T, SNP_POP PO where PO.I_TRT_KIM = T.I_TRT and PO.I_FOLDER = $$FOLD_ID$$ union select 'Check KM', T.I_TRT, T.TRT_NAME from SNP_TRT T, SNP_POP PO where PO.I_TRT_KCM = T.I_TRT and PO.I_FOLDER = $$FOLD_ID$$ union select 'Loading KM', T.I_TRT, T.TRT_NAME from SNP_TRT T, SNP_POP PO, SNP_SRC_SET SS

Import Export Best Practices for SCB

13/17

where PO.I_POP = SS.I_POP and SS.I_TRT_KLM = T.I_TRT and PO.I_FOLDER = $$FOLD_ID$$ union select 'Variable Used in a Mapping', V.I_VAR, V.VAR_NAME from SNP_VAR V, SNP_TXT_CROSSR CX, SNP_POP_COL PC, SNP_POP P where PC.I_POP =P.I_POP and CX.I_TXT = PC.I_TXT_MAP and CX.I_VAR = V.I_VAR and P.I_FOLDER = $$FOLD_ID$$ union select 'Variable Used in a Filter/Join', V.I_VAR, V.VAR_NAME from SNP_VAR V, SNP_TXT_CROSSR CX, SNP_POP_CLAUSE PC, SNP_POP P where PC.I_POP =P.I_POP and CX.I_TXT = PC.I_TXT_SQL and CX.I_VAR = V.I_VAR and P.I_FOLDER = $$FOLD_ID$$ union select 'Function Used in a Mapping', F.I_UFUNC, F.UFUNC_NAME from SNP_UFUNC F, SNP_TXT_CROSSR CX, SNP_POP_COL PC, SNP_POP P where PC.I_POP =P.I_POP and CX.I_TXT = PC.I_TXT_MAP and CX.I_UFUNC = F.I_UFUNC and P.I_FOLDER = $$FOLD_ID$$ union select 'Function Used in a Filter/Join', F.I_UFUNC, F.UFUNC_NAME from SNP_UFUNC F, SNP_TXT_CROSSR CX, SNP_POP_CLAUSE PC, SNP_POP P where PC.I_POP =P.I_POP and CX.I_TXT = PC.I_TXT_SQL and CX.I_UFUNC = F.I_UFUNC and P.I_FOLDER = $$FOLD_ID$$

Once executed, this query will return a result as the following: Dependency Desc. Check KM Integration KM Model Used as Source Model Used as Source Model Used as Target Function Used in a Mapping Variable Used in a Mapping Variable Used in a Filter/Join …

Object ID 117,229 169,229 15,229 35,229 13,229 1,339 84,229 175,229

Object Name CKM SQLs IKM_Teradata Incremental Update KEY MAP_MNRE PDM FN_MY_FUNCTION VAR_ALS_ACCT_MODIFIER_NUM VAR_ALS_SOURCE_SYSTEM_CD

This means that Interfaces of the Folder you have selected reference the list of objects returned by the query. Therefore, if you plan to export all interfaces of that Folder, you would need to export all the listed objects to make sure the import in the second repository will be consistent.

Import Export Best Practices for SCB

14/17

Exporting Model Objects Once you have identified the list of dependent Models of your Interfaces, you can follow this procedure to export these Models: 1. Export the top level Model Folder (for example “MDL_TD_SCBEDW”) and make sure to uncheck the “Export Child Components” check box so that only the Model Folder is exported without all its underlying Models. 2. Export other Model Folders without their child components 3. Export Models with their child components At this stage you should have a list of XML files in your file system, some starting with “MFOL_” for Model Folders, others with “MOD_” for Models. Note that because Model Folders were exported without their child components, their size is significantly smaller than the Model export files. Important Note: Models can reference each other if you have created Foreign Key references between 2 Datastores of 2 different Models. If this is the case, you’ll have to export the “referenced” Models as well.

Exporting Project Objects Every object that you have identified as a dependant object needs to be exported. The global procedure for doing the exports can be: 1. Export the main project definition without its child components - make sure to uncheck the “Export Child Components” check box. 2. Export every dependant object of the list below with its child components: a. States (for Sunopsis v3) b. Knowledge Modules c.

Variables (Global and Local)

d. User Functions (Global and Local) e. Sequences (Global and Local) 3. Export every Parent Folder without its child components. For example, if the Interfaces you want to export are in Folder “/RELEASE2/EXIMBILL/STG_TO_TRG”, make sure to export Folders “RELEASE2”, “EXIMBILL” and “STG_TO_TRG” without their child components. 4. Finally, export all Interfaces of your folder with their child components. At this stage, you should have export files starting with the following prefixes: •

“PROJ_”: Project – small file size as it was exported without its children

•

“KM_”: Knowledge Modules

•

“VAR_”: Variables

•

“UFN_”: User Defined Functions

•

“SEQ_”: Sequences

•

“FOLD_”: Folders – small file size as they were exported without their children

•

“POP_”: Interfaces

Automating the Export of Several Objects using APIs You may find it tedious to do the exports of every interface manually, and you may want to automate it.

Import Export Best Practices for SCB

15/17

To do so, you can use Oracle Data Integrator API SnpsExportObject to export a set of objects from your work repository. The following example illustrates how to export all Interfaces of a Folder using an executable Procedure: 1. Create a Logical Schema in your current context that points to your Work Repository database connection. 2. Create a Procedure and call it EXPORT_INTERFACES 3. Create 2 Options for this Procedure and name them FOLDER_ID and EXPORT_DIRECTORY 4. Create one step in this Procedure and enter the following commands: •

Command on Source, executed on the Logical Schema of your Work Repository database: select I_POP, POP_NAME from <%=snpRef.getObjectName("L", "SNP_POP", "D")%> where I_FOLDER=<%=snpRef.getOption("FOLDER_ID")%>

•

Command on Destination, Sunopsis API Command: SnpsExportObject "-I_OBJECT=#I_POP" "-CLASS_NAME=SnpPop" "FILE_NAME=<%=snpRef.getOption("EXPORT_DIRECTORY")%>/POP_#POP_NAME.xml" "-FORCE_OVERWRITE=YES" "-RECURSIVE_EXPORT=YES" "-XML_VERSION=1.0" "XML_CHARSET=ISO-8859-1" "-JAVA_CHARSET=ISO8859_1"

If you execute this Procedure by setting the options to a valid Folder Id and a valid Directory on your file system, it will query the Work Repository to retrieve the name and Id of every Interface of your Folder, and export each of them to the Directory you have specified. The names of the XML export files will be POP_.xml

Importing Objects in Target Repository Now that you have exported all relevant objects from the Target Repository, you can import them safely in the new Repository. To do so, you can follow this procedure: 1. Connect to new Work Repository 2. Import the Model Folders in the order in which they appear in Target Repository. For example, if you have exported 3 Model Folders “MFOLD1/MFOLD2/MFOLD3” without their child components, you need to import MFOLD1 before MFOLD2 and so forth. Make sure to set the import mode to: a. “Synonym Mode INSERT” if your Model Folder does not exist in the new repository b. “Synonym Mode UPDATE” if your Model Folder already exists in the new repository c.

DO NOT USE “Synonym Mode INSERT_UPDATE” if your Model Folder already exists.

3. Import your Models in “Synonym Mode INSERT_UPDATE”. If a Model depends on another Model, for example through Foreign Key references between datastores of the 2 models, make sure to import the referenced Model first. 4. Import your Project that was exported without its child components and make sure to use: a. “Synonym Mode INSERT” if the Project does not exist b. “Synonym Mode UPDATE” if the Project already exists c.

DO NOT USE “Synonym Mode INSERT_UPDATE” if your Project already exists.

5. Import Project Folders in the order in which they appear in Bangkok’s Repository. For example, if you have exported 3 Folders “/RELEASE2/EXIMBILL/STG_TO_TRG” without their child components, you need to import “RELEASE2” before “EXIMBILL” and so forth. Make sure to set the import mode to: a. “Synonym Mode INSERT” if your Folder does not exist in the new repository b. “Synonym Mode UPDATE” if your Folder already exists in the new repository c.

DO NOT USE “Synonym Mode INSERT_UPDATE” if your Folder already exists.

6. Import your Knowledge Modules as follow:

Import Export Best Practices for SCB

16/17

a. Locate the Knowledge Module XML files on your file system and rename their prefix to “TRT_” instead of “KM_” b. Right-click on any Folder of your Project and select “Import…> Import Procedure..” menu c.

Select “Synonym Mode INSERT_UPDATE”,

d. go to the directory where your KMs are stored on your file system e. Select your KMs, then click OK. 7. Import your Variables, Sequences and Functions in “Synonym Mode INSERT_UPDATE” 8. Finally, import your Interfaces in “Synonym Mode INSERT_UPDATE” By following this procedure you will successfully setup your environment in the new repository to continue your developments in parallel.

Sharing Developed Objects across Repositories Future export and import operation between any repositories should follow the same guidelines as described in this document: Releasing developments from the new repository to the Target would simply require exporting new Folders without child components and Interfaces. Releasing developments from the new repository to any other Work Repository would simply require following this procedure.

Import Export Best Practices for SCB

17/17