Load data From one database to another database or one schema to another schema within the database

Load data From one database to another database or one schema to another schema within the database

1) define the data servers under topology tab for associate technologies-->name and DBA privileged username and password, JDBC connection

2)  define physical schema for source data server---> DB schema name where database tables are present   along with work schema. usually a dedicated work schema is used but one can use DB schema but this is not recommended

3) define physical schema for target data server ---> DB schema name of target database where data will be loaded from source schema along with work schema

4) define logical schema for both physical schemas and link them along with context

5) define model in designer tab for source and target schema and reverse engineering source and target tables with the help of logical schema

6) create a project in designer tab import LKM and IKM for specified technology

7) create a mapping by dragging source and target table from models

8) now go to logical tab within mapping and select integration type under target accordingly ie control append, incremental update, slowly changing dimension

9) go to physical tab (next to logical) and click on staging table and select LKM ie.sql to sql or sql to oracle

10) click on target table and select IKM ie IKM oracle insert.global

11) execute the mapping if done the data will be available in target schema

NOTE-- if LKM and IKM is not imported in project ODI will take default LKM and IKM and mapping may not execute because LKM take default LKM ie LKM oracle to oracle (DBLINK) default IKM does not affect mapping execution

error likely to occur

ODI-1227: Task Create view/table on source-LKM Oracle to Oracle (DBLINK)- fails on the source connection scott source demo.

Caused By: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

ODI-1228: Task Load EMP1-IKM Oracle Insert- fails on the target connection target Hr Demo.

Caused By: java.sql.SQLSyntaxErrorException: ORA-02083: database name has illegal character ' '

how to view the value of variables being used in ODI mapping

how to view the value of variables being used in ODI mapping

Whenever a variable is being used in ODI mapping the log level value should be kept 6 in order to view the variable in operator log.

again if you want to protect the variable value from being displayed in the operator logs you must select the secure value checkbox no matter what is the log level value is being used. 

ODI-1405: Agent OracleDIAgent start failure: the agent is not defined in the topology for master repository.

After successful installation of the standalone agent when I try to run it. it gives error

ODI-1405: Agent OracleDIAgent start failure: the agent is not defined in the topology for master repository.

This error comes when the repositories are not configured properly. Before standalone agent, we were using local agent and repositories that were created during installation of ODI studio and we try to create standalone agent under those repositories. instead of this try to create standalone agent under those repositories that were created during installation of standalone agent and then start the agent 

For this 

1 ) first create the master repository login 

2) provide the details

user-----> name of the repository that was created during installation of standalone agent (Default = DEV1_ODI_REPO) 

password----> provide the password

DBA User----> sys as sysdba

password-----> sys

3) use ODI Authentication 

provide a password for SUPERVISOR User (this is ODI USER)

after successful completion 

4)click on connect to repository and Click on + button

5) Oracle data integration connection 

Provide login name

User= SUPERVISOR 

Password for supervisor

Database connection(Master Repository )

user= provide user name/repository name that was created during installation of standalone agent (Default = DEV1_ODI_REPO) 

provide JDBC connection

Work Repository

click on master Repository only

test connection 

after successful create work repository with same username repository name that was created during installation of standalone agent (Default = DEV1_ODI_REPO) 

then disconnect

again connect to ODI studio this time click on work Repository

click ok

after this step create standalone agent and start it  

Security tab in ODI is not showing

I encountered an issue where Security tab in ODI was not showing after multiple attempts I decided to clear the cache for ODI studio here are the steps

Close ODI Studio.

Navigate to C:\Users\<username>\AppData\Roaming\odi directory

Delete the “system<ODI version>” folder, Corresponding to your version of ODI.

Start ODI Studio.

A new folder system<ODI version> will be created.

After this Security, window start showing in ODI studio

This is the common approach whenever you have some Glitches in ODI studio UI

Result contains more than the "Operator display limit (0 = no limit)" of 100 records.

Result contains more than the "Operator display limit (0 = no limit)" of 100 records.

(To change the "Operator display limit (0 = no limit)" or disable this dialog, please see "Preferences" in the Tools main menu.)

Do you want to display all records?  

One may not found this as an absolute error but this might be extremely annoying. This happens when no of execution exceeds its default values(100). you have to manually change the value to higher limit or alternately delete executions from Agents and Users as well

To change the Value 

Go to Tools--------->Preferences--------->ODI--------->User interface--------->Operator

ODI Installation

Installation step for ODI 12c