Saturday, July 5, 2014

What is Simulation in ODI?

Simulation option will be available at Mappings(12c) or Interfaces in 10g or 11g.

If we select this option while execution time it will give us complete report or background process
for your mapping or interface.  In Below example I have enable CDC on Source table.

Source Table:  HR. EMP1  ( JKM Simple CDC enabled this table)
Target Table:  DEV. EMP1
Knowledge Moduels:

--------------------------
LKM:   LKM SQL to SQL
IKM:  IKM SQL Incremental Update
Options:  FLOW_CONTROL=false ( CKM is not required)
bcz i am not validating Constraint data in this example.

































Generated Session

Session Name: EMP1_DS_SESS
Context Code: DEV
 

Step

Step Name: EMP1_DS_STEP
Step No: 10
Step Type: MAPPING
Ok Exit: 0
 

Task

Task Type : EX_UNIT_BEGIN
Task Name 1: SERIAL
Task Name 2: MAP_MAIN
 

Task

Task Type : EX_UNIT_BEGIN
Task Name 1: SERIAL
Task Name 2: EU
Task Name 3: S_HR_LOGICAL_UNIT
 

Task

Task Type : EX_UNIT_BEGIN
Task Name 1: Drop work table
Task Name 2: LKM SQL to SQL
Source Logical Schema Name: S_HR_LOGICAL
Source Isolation Level: DEFAULT
Target Logical Schema Name: T_DEV_LOGICAL
Target Connection Name: T_DEV_DS
Target Command: 
drop table DEV.C$_0EMP1
 

Task

Task Type : EX_UNIT_BEGIN
Task Name 1: Create work table
Task Name 2: LKM SQL to SQL
Source Logical Schema Name: S_HR_LOGICAL
Source Isolation Level: DEFAULT
Target Logical Schema Name: T_DEV_LOGICAL
Target Connection Name: T_DEV_DS
Target Command: 
create table DEV.C$_0EMP1
(
 EMPLOYEE_ID NUMBER(6,0) NULL,
 FIRST_NAME VARCHAR2(20) NULL,
 LAST_NAME VARCHAR2(25) NULL,
 EMAIL VARCHAR2(25) NULL,
 PHONE_NUMBER VARCHAR2(20) NULL,
 HIRE_DATE DATE NULL,
 JOB_ID VARCHAR2(10) NULL,
 SALARY NUMBER(8,2) NULL,
 COMMISSION_PCT NUMBER(2,2) NULL,
 MANAGER_ID NUMBER(6,0) NULL,
 DEPARTMENT_ID NUMBER(4,0) NULL,
 JRN_SUBSCRIBER VARCHAR2(50 CHAR) NULL,
 JRN_FLAG CHAR(1) NULL,
 JRN_DATE DATE NULL
)
 

Task

Task Type : EX_UNIT_BEGIN
Task Name 1: Lock journalized table
Task Name 2: LKM SQL to SQL
Source Logical Schema Name: S_HR_LOGICAL
Source Connection Name: S_HR_DS
Target Logical Schema Name: T_DEV_LOGICAL
Target Isolation Level: DEFAULT
Source Command: 
 
update  HR.J$EMP1 
set  JRN_CONSUMED = '1'
where (1=1)
AND JRN_SUBSCRIBER = 'SUNOPSIS' /* AND JRN_DATE < sysdate */
 

Task

Task Type : EX_UNIT_BEGIN
Task Name 1: Load data
Task Name 2: LKM SQL to SQL
Source Logical Schema Name: S_HR_LOGICAL
Source Connection Name: S_HR_DS
Source Commit Indicator: NO_COMMIT
Target Logical Schema Name: T_DEV_LOGICAL
Target Connection Name: T_DEV_DS
Source Command: 
 
select 
 EMP1.EMPLOYEE_ID  EMPLOYEE_ID,
 EMP1.FIRST_NAME  FIRST_NAME,
 EMP1.LAST_NAME  LAST_NAME,
 EMP1.EMAIL  EMAIL,
 EMP1.PHONE_NUMBER  PHONE_NUMBER,
 EMP1.HIRE_DATE  HIRE_DATE,
 EMP1.JOB_ID  JOB_ID,
 EMP1.SALARY  SALARY,
 EMP1.COMMISSION_PCT  COMMISSION_PCT,
 EMP1.MANAGER_ID  MANAGER_ID,
 EMP1.DEPARTMENT_ID  DEPARTMENT_ID,
 JRN_SUBSCRIBER  JRN_SUBSCRIBER,
 JRN_FLAG  JRN_FLAG,
 JRN_DATE  JRN_DATE
from HR.JV$EMP1 EMP1
where (1=1)
AND JRN_SUBSCRIBER = 'SUNOPSIS' /* AND JRN_DATE < sysdate */
Target Command: 
insert into DEV.C$_0EMP1
(
 EMPLOYEE_ID,
 FIRST_NAME,
 LAST_NAME,
 EMAIL,
 PHONE_NUMBER,
 HIRE_DATE,
 JOB_ID,
 SALARY,
 COMMISSION_PCT,
 MANAGER_ID,
 DEPARTMENT_ID,
 JRN_SUBSCRIBER,
 JRN_FLAG,
 JRN_DATE
)
values
(
 :EMPLOYEE_ID,
 :FIRST_NAME,
 :LAST_NAME,
 :EMAIL,
 :PHONE_NUMBER,
 :HIRE_DATE,
 :JOB_ID,
 :SALARY,
 :COMMISSION_PCT,
 :MANAGER_ID,
 :DEPARTMENT_ID,
 :JRN_SUBSCRIBER,
 :JRN_FLAG,
 :JRN_DATE
)
 

Task

Task Type : EX_UNIT_BEGIN
Task Name 1: SERIAL
Task Name 2: EU
Task Name 3: T_DEV_LOGICAL_UNIT
 

Task

Task Type : EX_UNIT_BEGIN
Task Name 1: Drop flow table
Task Name 2: IKM SQL Incremental Update
Source Logical Schema Name: T_DEV_LOGICAL
Source Isolation Level: DEFAULT
Target Logical Schema Name: T_DEV_LOGICAL
Target Connection Name: T_DEV_DS
Target Command: 
drop table DEV.I$_EMP1
 

Task

Task Type : EX_UNIT_BEGIN
Task Name 1: Create flow table I$
Task Name 2: IKM SQL Incremental Update
Source Logical Schema Name: T_DEV_LOGICAL
Source Isolation Level: DEFAULT
Target Logical Schema Name: T_DEV_LOGICAL
Target Connection Name: T_DEV_DS
Target Command: 
create table DEV.I$_EMP1
(
 EMPLOYEE_ID NUMBER(6,0) NULL,
 FIRST_NAME VARCHAR2(20) NULL,
 LAST_NAME VARCHAR2(25) NULL,
 EMAIL VARCHAR2(25) NULL,
 PHONE_NUMBER VARCHAR2(20) NULL,
 HIRE_DATE DATE NULL,
 JOB_ID VARCHAR2(10) NULL,
 SALARY NUMBER(8,2) NULL,
 COMMISSION_PCT NUMBER(2,2) NULL,
 MANAGER_ID NUMBER(6,0) NULL,
 DEPARTMENT_ID NUMBER(4,0) NULL,
 IND_UPDATE  
)
 

Task

Task Type : EX_UNIT_BEGIN
Task Name 1: Insert flow into I$ table
Task Name 2: IKM SQL Incremental Update
Source Logical Schema Name: T_DEV_LOGICAL
Source Isolation Level: DEFAULT
Target Logical Schema Name: T_DEV_LOGICAL
Target Connection Name: T_DEV_DS
Target Command: 
insert into DEV.I$_EMP1
(
 EMPLOYEE_ID,
 FIRST_NAME,
 LAST_NAME,
 EMAIL,
 PHONE_NUMBER,
 HIRE_DATE,
 JOB_ID,
 SALARY,
 COMMISSION_PCT,
 MANAGER_ID,
 DEPARTMENT_ID,
 IND_UPDATE
)
 
select  
 EMP1_A.EMPLOYEE_ID,
 EMP1_A.FIRST_NAME,
 EMP1_A.LAST_NAME,
 EMP1_A.EMAIL,
 EMP1_A.PHONE_NUMBER,
 EMP1_A.HIRE_DATE,
 EMP1_A.JOB_ID,
 EMP1_A.SALARY,
 EMP1_A.COMMISSION_PCT,
 EMP1_A.MANAGER_ID,
 EMP1_A.DEPARTMENT_ID,
 JRN_FLAG IND_UPDATE
from DEV.C$_0EMP1 EMP1_A
where (1=1)
 

Task

Task Type : EX_UNIT_BEGIN
Task Name 1: Synchronize deletions from journal
Task Name 2: IKM SQL Incremental Update
Source Logical Schema Name: T_DEV_LOGICAL
Source Isolation Level: DEFAULT
Target Logical Schema Name: T_DEV_LOGICAL
Target Connection Name: T_DEV_DS
Target Command: 
delete from  DEV.EMP1
where  exists (
  select  'X'
  from DEV.I$_EMP1  I
  where DEV.EMP1.EMPLOYEE_ID = I.EMPLOYEE_ID
  and IND_UPDATE = 'D'
  )
 

Task

Task Type : EX_UNIT_BEGIN
Task Name 1: Remove deleted rows from flow table
Task Name 2: IKM SQL Incremental Update
Source Logical Schema Name: T_DEV_LOGICAL
Source Isolation Level: DEFAULT
Target Logical Schema Name: T_DEV_LOGICAL
Target Connection Name: T_DEV_DS
Target Command: 
delete from DEV.I$_EMP1
where IND_UPDATE = 'D'
 

Task

Task Type : EX_UNIT_BEGIN
Task Name 1: Flag rows for update
Task Name 2: IKM SQL Incremental Update
Source Logical Schema Name: T_DEV_LOGICAL
Source Isolation Level: DEFAULT
Target Logical Schema Name: T_DEV_LOGICAL
Target Connection Name: T_DEV_DS
Target Command: 
update DEV.I$_EMP1 
set IND_UPDATE = 'U'
where  exists (
  select  'X'
  from DEV.EMP1  T
  where DEV.I$_EMP1.EMPLOYEE_ID = T.EMPLOYEE_ID 
  )
 

Task

Task Type : EX_UNIT_BEGIN
Task Name 1: Flag useless rows
Task Name 2: IKM SQL Incremental Update
Source Logical Schema Name: T_DEV_LOGICAL
Source Isolation Level: DEFAULT
Target Logical Schema Name: T_DEV_LOGICAL
Target Connection Name: T_DEV_DS
Target Command: 
update DEV.I$_EMP1
set IND_UPDATE = 'N'
where  exists (
  select 'X'
  from DEV.EMP1  T
  where DEV.I$_EMP1.EMPLOYEE_ID = T.EMPLOYEE_ID
  and ((DEV.I$_EMP1.FIRST_NAME = T.FIRST_NAME) or (DEV.I$_EMP1.FIRST_NAME IS NULL and T.FIRST_NAME IS NULL)) 
  and ((DEV.I$_EMP1.LAST_NAME = T.LAST_NAME) or (DEV.I$_EMP1.LAST_NAME IS NULL and T.LAST_NAME IS NULL)) 
  and ((DEV.I$_EMP1.EMAIL = T.EMAIL) or (DEV.I$_EMP1.EMAIL IS NULL and T.EMAIL IS NULL)) 
  and ((DEV.I$_EMP1.PHONE_NUMBER = T.PHONE_NUMBER) or (DEV.I$_EMP1.PHONE_NUMBER IS NULL and T.PHONE_NUMBER IS NULL)) 
  and ((DEV.I$_EMP1.HIRE_DATE = T.HIRE_DATE) or (DEV.I$_EMP1.HIRE_DATE IS NULL and T.HIRE_DATE IS NULL)) 
  and ((DEV.I$_EMP1.JOB_ID = T.JOB_ID) or (DEV.I$_EMP1.JOB_ID IS NULL and T.JOB_ID IS NULL)) 
  and ((DEV.I$_EMP1.SALARY = T.SALARY) or (DEV.I$_EMP1.SALARY IS NULL and T.SALARY IS NULL)) 
  and ((DEV.I$_EMP1.COMMISSION_PCT = T.COMMISSION_PCT) or (DEV.I$_EMP1.COMMISSION_PCT IS NULL and T.COMMISSION_PCT IS NULL)) 
  and ((DEV.I$_EMP1.MANAGER_ID = T.MANAGER_ID) or (DEV.I$_EMP1.MANAGER_ID IS NULL and T.MANAGER_ID IS NULL)) 
  and ((DEV.I$_EMP1.DEPARTMENT_ID = T.DEPARTMENT_ID) or (DEV.I$_EMP1.DEPARTMENT_ID IS NULL and T.DEPARTMENT_ID IS NULL))
  )
 

Task

Task Type : EX_UNIT_BEGIN
Task Name 1: Update existing rows
Task Name 2: IKM SQL Incremental Update
Source Logical Schema Name: T_DEV_LOGICAL
Source Connection Name: T_DEV_DS
Target Logical Schema Name: T_DEV_LOGICAL
Target Connection Name: T_DEV_DS
Target Commit Indicator: NO_COMMIT
Source Command: 
select EMPLOYEE_ID  EMPLOYEE_ID,
 FIRST_NAME  FIRST_NAME,
 LAST_NAME  LAST_NAME,
 EMAIL  EMAIL,
 PHONE_NUMBER  PHONE_NUMBER,
 HIRE_DATE  HIRE_DATE,
 JOB_ID  JOB_ID,
 SALARY  SALARY,
 COMMISSION_PCT  COMMISSION_PCT,
 MANAGER_ID  MANAGER_ID,
 DEPARTMENT_ID  DEPARTMENT_ID
from DEV.I$_EMP1 
where IND_UPDATE = 'U'
Target Command: 
update DEV.EMP1 
set  FIRST_NAME = :FIRST_NAME,
 LAST_NAME = :LAST_NAME,
 EMAIL = :EMAIL,
 PHONE_NUMBER = :PHONE_NUMBER,
 HIRE_DATE = :HIRE_DATE,
 JOB_ID = :JOB_ID,
 SALARY = :SALARY,
 COMMISSION_PCT = :COMMISSION_PCT,
 MANAGER_ID = :MANAGER_ID,
 DEPARTMENT_ID = :DEPARTMENT_ID
 
where EMPLOYEE_ID = :EMPLOYEE_ID
 

Task

Task Type : EX_UNIT_BEGIN
Task Name 1: Insert new rows
Task Name 2: IKM SQL Incremental Update
Source Logical Schema Name: T_DEV_LOGICAL
Source Isolation Level: DEFAULT
Target Logical Schema Name: T_DEV_LOGICAL
Target Connection Name: T_DEV_DS
Target Commit Indicator: NO_COMMIT
Target Command: 
insert into  DEV.EMP1 
( 
 EMPLOYEE_ID,
 FIRST_NAME,
 LAST_NAME,
 EMAIL,
 PHONE_NUMBER,
 HIRE_DATE,
 JOB_ID,
 SALARY,
 COMMISSION_PCT,
 MANAGER_ID,
 DEPARTMENT_ID 
  
) 
select  EMPLOYEE_ID,
 FIRST_NAME,
 LAST_NAME,
 EMAIL,
 PHONE_NUMBER,
 HIRE_DATE,
 JOB_ID,
 SALARY,
 COMMISSION_PCT,
 MANAGER_ID,
 DEPARTMENT_ID  
   
from DEV.I$_EMP1 
where IND_UPDATE = 'I'
 

Task

Task Type : EX_UNIT_BEGIN
Task Name 1: Commit transaction
Task Name 2: IKM SQL Incremental Update
Source Logical Schema Name: T_DEV_LOGICAL
Source Isolation Level: DEFAULT
Target Logical Schema Name: T_DEV_LOGICAL
Target Connection Name: T_DEV_DS
Target Commit Indicator: COMMIT
Target Command: 
/* commit */
 

Task

Task Type : EX_UNIT_BEGIN
Task Name 1: Drop flow table
Task Name 2: IKM SQL Incremental Update
Source Logical Schema Name: T_DEV_LOGICAL
Source Isolation Level: DEFAULT
Target Logical Schema Name: T_DEV_LOGICAL
Target Connection Name: T_DEV_DS
Target Command: 
drop table DEV.I$_EMP1
 

Task

Task Type : MAP_CLEANUP
Task Name 1: SERIAL
Task Name 2: MAP_CLEANUP
 

Task

Task Type : MAP_CLEANUP
Task Name 1: SERIAL
Task Name 2: EU
Task Name 3: S_HR_LOGICAL_UNIT
 

Task

Task Type : MAP_CLEANUP
Task Name 1: Cleanup journalized table
Task Name 2: LKM SQL to SQL
Source Logical Schema Name: S_HR_LOGICAL
Source Connection Name: S_HR_DS
Target Logical Schema Name: T_DEV_LOGICAL
Target Isolation Level: DEFAULT
Source Command: 
 
delete from HR.J$EMP1 
where JRN_CONSUMED = '1' 
AND JRN_SUBSCRIBER = 'SUNOPSIS' /* AND JRN_DATE < sysdate */
 

Task

Task Type : MAP_CLEANUP
Task Name 1: Drop work table
Task Name 2: LKM SQL to SQL
Source Logical Schema Name: S_HR_LOGICAL
Source Isolation Level: DEFAULT
Target Logical Schema Name: T_DEV_LOGICAL
Target Connection Name: T_DEV_DS
Target Command: 
drop table DEV.C$_0EMP1
 










Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)