5 Nisan 2016 Salı

ODI 12c - creating MySQL master repository


In this entry i will show how to create a master repository over MySQL db.

Version :  MySQL Server 5.6

At first , logging into Master repository creation wizard and i have following menu

I am gonna create my master repository over  test database so

JDBC URL :    jdbc:mysql://

*If you are using MySQL (release 5.5.14 or higher) to setup a repository, and the database charset is set to utf8, you will need to add the following parameters to the repository's jdbc URL to operate non-ASCII data correctly in Oracle Data Integrator Studio
( Source : http://docs.oracle.com/cd/E28280_01/install.1111/e16453/manual.htm#ODING492  )

JDBC URL :   jdbc:mysql://

Registry Database Name : test   ( Db  i am gonna use for master )

User:    Any user you create with admin privileges over MySQL
( root user cant work here bcoz ODI makes it uppercase automatically so it raise a connection error )

DBA User :  You can use root user  in here or the user you create above.

Result  will be look like this :

ODI 12c might raise warning for some of the parameters in MySQL.In order to avoid this ,you can set following parameters in your my.ini file (located under your mysql  installation folder) .

collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

1 Nisan 2016 Cuma

ODI 12c - Implementing For Loops


in  previous versions of ODI   i had a chance to create  "for loops"  in packages , so now i am gonna explain how we can implement a for loop in ODI 12c universe.

First i am gonna reverse a table that contains branch information.

Table structure as follows:

What i want to do is  to get all data except for the one with maximum ORDER_NUM ( Normally we can do this by using bulk SQL but this time i am gonna create  a for loop and making  row by row insertion.  )

As usual i need variable objects  to implement a standart for loop.

Variable:MAX_COUNT ( holds total number of rows in our source table )

Variable: ORDER_ID_VAR ( set to 0 )

and now I am gonna create an interface that let me transfer data one by one into my target table.Inside the mapping , filter object help me to associate interface with my variables.

Lastly i will create a package to implement my for loop.As you can see below , at first  i am refreshing my variables to assign initial values then ORDER_ID_VAR_INC object ( set variable ) is used for incrementing ORDER_ID_VAR.

ORDER_ID_CHECK_1 (Evaluate variable)  is used for checking whether ORDER_ID_VAR less than MAX_COUNT or not. If it is true , our interface will be called for execution otherwise beep object will be started to finish execution.

target table will be look like this

and operator log for execution