ODI 12c – Setting CDC/Journalizing

ODI 12c – Setting CDC/Journalizing

In this blog we will see step by step how we can setup the change data capture (CDC)/journalizing in ODI 12c.

Change data capture as the name suggest the process to identify the change in source data whether the records inserted/updated or deleted from the source systems. After identifying the change data & applying the same in targets.  We will explore here the complete process & objects required to setup the CDC in ODI 12c.

ODI  Journalizing Modes:

Simple –  Capture changes for individual data-stores in a model, does not support referential integrity between the data-store.

Consistent Set – It also support the referential integrity between data-stores. Group of datastores journalized in this mode is called ‘Consistent Set’.


Step 1. Import JKM:

Import the Journalizing Knowledge Model – JKM Oracle Simple

JKM

Create the Model for your source data-store. Select the ‘Journalizing Mode‘ as simple & select the correct JKM from drop down. Refer below screenshot.

Model
Model

Reverse engineer your tables, make sure it has some key.

Step 2. Add Subscriber:

Subscriber: It is a application or process which consume the captured change data. When subscribers have consumed the captured changes, later on changes were discarded from the journals.

If you do not want to add any subscriber to your data-store, by default ‘SUNOPSIS‘ will be there.

Subcriber
Add Subscriber

Step 3. Add to CDC:

You are adding your data-store to CDC process.

addtocdc
Add to CDC

If you open your data-store,  go to Journalizing tab. You will see a message that table is included in CDC. Also the clock sign will come on  your data-store.

CDC addeed
Data-store

Step 4. Start Journal:

Start the journal, select the subscriber.  Select context & run. It will create all the objects using which change data can be captured in J$ tables.

startjournal

journal
Journal Steps

Below objects created in database. Two views are created: the JV$ view and the JV$D view.

  • TABLES: J$ACCOUNTS_STAGE & SNP_SUBSCRIBERS
  • TRIGGERT$ACCOUNTS_STAGE
  • VIEWS: JV$ACCOUNTS_STAGE & JV$DACCOUNTS_STAGE

Subscriber Table:

ACCOUNTS_STAGE Table registered as subscriber in SNP_SUBSCRIBERS.

subsciber

J$ Table:

I have inserted 4 records in my staging table ACCOUNTS_STAGE manually. After commit I have noticed that 4 records also inserted in my J$ Tabe i.e. J$ACCOUNTS_STAGE with consume flag as 0 i.e. records not yet consumed. J$ Table J$ACCOUNTS_STAGE has only 5 columns, last one is the key of table. 

  • JRN_SUBSCRIBER
  • JRN_CONSUMED
  • JRN_FLAG
  • JRN_DATE
  • ACCOUNT_ID

j$ table

Above records are inserted by the trigger T$ACCOUNTS_STAGE on ACCOUNTS_STAGE. 

Step 5. Journalizing Filter in Mapping :

You can set your subscriber here in filter.

journalizing filter

Step 6. Journalizing Data Only:

Tick the ‘Check box’ for ‘Journalizing Data Only‘.

journa_data_only

JV$ view:

JV$ACCOUNTS_STAGE is used in the mappings where you select the option Journalized data only. This view is used to insert the records in I$ flow table.

Records from the J$ table ( J$ACCOUNTS_STAGE) are filtered using condition JRN_CONSUMED=1. If you have multiple records for 1 key, then it will pick the latest entry of the that key using JRN_DATE.

select	L.JRN_SUBSCRIBER AS  JRN_SUBSCRIBER,
L.ACCOUNT_ID AS  ACCOUNT_ID,
max(L.JRN_DATE) AS JRN_DATE
from DEV_SCHEMA.J$ACCOUNTS_STAGE L
where L.JRN_CONSUMED = '1'
group by L.JRN_SUBSCRIBER,L.ACCOUNT_ID

Locking of records in J$ table using below SQL update statements. When you start execution of mappings this is the first step.

update	DEV_SCHEMA.J$ACCOUNTS_STAGE
set	JRN_CONSUMED = '1'
where	(1=1)
AND JRN_SUBSCRIBER = 'SUNOPSIS'

JV$D View:

Data view is used to show the change data available for all subscribe in the J$ table without any consume filter, when you select the menu Journal Data from data-store.

I have inserted  new 1 record & 1 updated in my staging table ACCOUNTS_STAGE manually. If you notice the JRN_FLAG is coming as I for update case also, code was present in trigger like that.

updated

journal data jdview

List of changed data if available: It is coming from JV$D view (JV$DACCOUNTS_STAGE)

journal data jdview2

At the end of mapping steps there is one step which cleans journalized table.

delete from J$ACCOUNTS_STAGE
where 	JRN_CONSUMED = '1'
AND JRN_SUBSCRIBER = 'SUNOPSIS' ;

Thanks!

Happy Learning! Your feedback would be appreciated!

ODI 12c | Version Control System Setup – Git

In this blog we will see step by step how we can setup the version control in ODI 12c using git. Git – open source version control system for tracking changes in source code during software development. I have used github.com/ to create a repository for my ODI 12c objects versioning.


Step 1: Created a repository using the github.com/

1

Copy the git url for you repository.

https://github.com/shobhit-singh/odi12c-repo.git

2

Step 2: Open the ODI Studio & change your “Version Control Management System” to git by default it was set to ODI.

3

4

Step 3: Setup the git, go to setting & edit connection.

5

Provide the git url, github repository. Change the authentication to SSL.

6

Accept the server certificate.

7

Step 3: Clone remote repository with local folder.

8

9

10

Step 4: Click on configure to setup the branch. I have selected the master branch & save the settings.

11

12

Adding ODI Object First Time in VCS:

I have created the Model Folder, Sub-Model Folder, Model & Data-store in ODI.

datastore

Right Click on the Model Folder & Add to VCS, as it was new object & going first time.

13

You can select object also if you want to move child objects also. Add comments & Click on OK.

14

15

You can check the ODI Objects in GitHub.

Check the objects

Re-Adding ODI Object in VCS:

I have changed the data-store ‘TEST_TABLE’ in ODI. I have added the column in data-store. Now I have create a new version for this object. See below steps to create new version.

16

Add comments & Click on OK.

17

18

Check the version in GitHub: Now this object has 2 version.

19

 


Thanks!

Happy Learning! Your feedback would be appreciated!

ODI 12c | Master & Work Repository Setup

ODI 12c | Master & Work Repository Setup

In this blog we will see step by step how we can setup the ODI 12c Master & Work Repository:


Prerequisite: Setup the repository schema first in database(Supported by ODI). I have created the schema in the Oracle database. Refer the blog for creating new schema in Oracle schema Oracle Database 11g | Create User

CREATE USER ODI_REPO IDENTIFIED BY welcome;
GRANT CONNECT, RESOURCE, DBA TO ODI_REPO;
GRANT CREATE SESSION TO ODI_REPO;
GRANT DBA TO ODI_REPO;
GRANT ALL PRIVILEGES TO ODI_REPO;

You can create separate schema for master repository as well as work repository, but in this blog we going to use only one schema for both master as well as work repository. Refer this blog for more understanding of ODI Repositories | Master & Work Repository

Checkout this blog for understanding of relationship between ODI Master & Work Repository.

Setup Master Repository

Open the ODI 12c Studio. Go to File -> New. Select “Create  New Master Repository“.

1

Step 1. Provide all the details for repository connection.  Like schema name, password, JDBC details, DBA credential details etc. See below screenshot.

jdbc:oracle:thin:@localhost:1522/orcl.168.56.1

2

After providing all the details, test the connection. Click next.

Step 2: Provide authentication mode using ODI authentication, provide supervisor password & note it down somewhere as it is required for login setup.

3

Click on finish after providing supervisor password. It will start the setup of the master repository. It will take few minutes to complete the setup of master repository.

4

5

master done

Here the master repository has been created. Now you have create login for the master repository: Follow below steps


Setup Master Repository Login

Step 1: Open the ODI 12c Studio. Go to File -> New. Select “Create  New ODI Repository Login“.

master login

Step 2. Provide the detail like login name (provide any name), supervisor name & supervisor password you provided at the time of repository setup.

For Database Connection: Provide details for master reposotory schema details. Select radio button master repository. Test the connection.

login2

Click on ‘Ok’. You are done with the master repository login setup. Now you have to login your master repository & create the work repository inside it.

In ODI 12c Studio. Click on connect repository.

connect

Below screen will come with all pre-populated data, you just have to click ‘Ok’. Now you are inside the master repository. Check the repository details ODI->’Repository Connection Information’

ml2

ml3

Here you have logged in ODI Master Repository. Notice here the designer navigator is by default disabled. Basically you cannot do development work in master repository, you have to create work repository for this master to start the development work.

Now you have to setup work repository under this master repository: Follow below steps


Setup Work Repository:

Checkout this blog for understanding of relationship between ODI Master & Work Repository.

Go to Topology Navigator, Repositories -> New Work Repository

work1

Notice here I have provided the same schema, you can create separate schema for work schema which is recommended.

work2

Provide the password for work repository& select development type. As you are going to develop ETL in this work repository. For productions & UAT generally we select ‘Execution‘ as work repository type where Designer navigator is by default disabled. It means you can develop object in Execution type work repository.

work3

Click on Finish.

work4

It will ask you to setup the login for work repo. Click on yes & provide the login name.

worklogin

worklogin2

workdone

Here the work repository setup &  login has been done & disconnect from master repository and login the work repository.

connect

Check the repository details ODI->’Repository Connection Information‘.

Notice here the designer navigator is not disabled. As this is as development type work repository, you can start the development work by creating new project.

Dataserver, Physical Schema, Logical Schema & Context | Oracle Data Integrator 11g

Model & Datastore| Creating and Reverse-Engineering| Oracle Data Integrator 11g

worklogin3


Thanks!

Happy Learning! Your feedback would be appreciated!

Oracle Database 12c Installation & Setup| Windows 10

Oracle Database 12c  Installation & Setup| Windows 10

In this blog we will see how we can setup the Oracle Database 12c on Windows 10 – 64 Bit PC. First you have to download the latest Oracle Database 12c using below link.

https://www.oracle.com/database/technologies/oracle12c-windows-downloads.html


I have downloaded the Oracle Database 12c (12.2.1.4.0) version.

Go to the database directory & start the setup. It will automatically start the Oracle Universal Installer.

Here creating database as container database
Setup is in-progress
Checking the status of database after installation using lsnrctl status
Oracle Database 12c is now up & running.

Refer this blog for creating pluggable database & schema : Container & Pluggable Database – Oracle 12c


Thanks!

Happy Learning! Your feedback would be appreciated!

PL/SQL Scenario Questions – Part 1

PL/SQL Scenario Questions – Part 1

In this blog we will explore some PL/SQL – Scenario questions.

Visit Part 2: PL/SQL Scenario Questions – Part 2


Scenario 1. Write PL/SQL program to show overloading concept of oops in PL/SQL.

Solution 1: See below example for PL/SQL package PKG_TEST, it has two procedure. Both procedure has same name & same input parameter name. But parameter type is different. This is example of overloading of objects in PL/SQL.

CREATE OR REPLACE PACKAGE PKG_TEST
AS
  PROCEDURE PRC_SAMPLE(INPUT_ID IN VARCHAR2);
  PROCEDURE PRC_SAMPLE(INPUT_ID IN NUMBER);
END PKG_TEST;
CREATE OR REPLACE PACKAGE BODY PKG_TEST
AS
  PROCEDURE PRC_SAMPLE(INPUT_ID IN VARCHAR2)
  IS
  BEGIN
   DBMS_OUTPUT.PUT_LINE ('Calling VARCHAR2 procedure');
  END PRC_SAMPLE;
  PROCEDURE PRC_SAMPLE(INPUT_ID IN NUMBER)
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE ('Calling number procedure');
  END PRC_SAMPLE;
END PKG_TEST;
/
pls0

Scenario 2. Write PL/SQL block which raise the user-defined exception if the day is weekend.

SET SERVEROUTPUT ON;
DECLARE
exp_weeknds EXCEPTION;
BEGIN
IF TO_CHAR(SYSDATE, 'DY')IN ('SUN','SAT') THEN
raise exp_weeknds;
ELSE
DBMS_OUTPUT.put_line( 'Record processed!');
END IF;
EXCEPTION
WHEN exp_weeknds THEN
dbms_output.put_line('Cannot process the record as its weekend!');
END ;
/
pls1

Scenario 3. Write PL/SQL program to show hiding concept of oops in PL/SQL.

Solution 3: See below example for PL/SQL package PKG_TEST, it has two procedure in its body but only one is declared in package spec. The one which is not declared but present in the body is basically hidden from outside world & can be used internally only.

CREATE OR REPLACE PACKAGE PKG_TEST
AS
PROCEDURE PRC_TEST(INPUT_ID IN VARCHAR2);
END PKG_TEST;
CREATE OR REPLACE PACKAGE BODY PKG_TEST
AS
PROCEDURE PRC_INTERNAL(INPUT_ID IN VARCHAR2)
IS
BEGIN
-- This procedure is not declared in package specification.
DBMS_OUTPUT.PUT_LINE ('Calling PRC_INTERNAL procedure');
END PRC_INTERNAL;
PROCEDURE PRC_TEST(INPUT_ID IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('Calling PRC_TEST procedure');
PRC_INTERNAL('100');
END PRC_TEST;
END PKG_TEST;
/
pls3

Now try calling the procedure which was not defined in declaration.

EXEC PKG_TEST.PRC_INTERNAL(‘100’);

pls4

Calling procedure PRC_TEST which is calling the internal procedure PRC_INTERNAL.

pls5

Scenario 4. Can we write commit in PL/SQL exception block.

Solution 4: Yes, but it is not recommended to write the commit. As if you want to logs the exception you can use pragma autonomous transaction.  Below example showing that we can use commit in exception block.

CREATE TABLE err_logs
(
error_number  VARCHAR2(25),
error_message VARCHAR2(1000)
);

DECLARE
lv_err_code VARCHAR2(25);
lv_err_msg  VARCHAR2(1000);
ln_num      NUMBER;
BEGIN
SELECT to_number('X') INTO ln_num FROM dual;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Writing exception logs!');
lv_err_code := SQLCODE;
lv_err_msg  := SQLERRM ;
INSERT INTO err_logs(error_number,error_message)
VALUES(lv_err_code,lv_err_msg);
COMMIT;
END;
/
pls6

Scenario 5. What is pragma autonomous transaction?

Solution 5: Fire an transaction which is independent of calling transaction and return to the calling transaction without affecting it’s state.

CREATE OR REPLACE PROCEDURE prc_log_errors (lv_err_code  IN  VARCHAR2,lv_err_msg IN VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO err_logs(error_number,error_message)
VALUES(lv_err_code,lv_err_msg);
COMMIT;
END prc_log_errors;
/

If you see below example, DML transaction performed before exception occurred. As we have called the procedure with pargma autonomous which has commit in it, still my main translation data is roll backed.

CREATE TABLE test_table (TEST_ID NUMBER);

DECLARE
lv_err_code VARCHAR2(25);
lv_err_msg  VARCHAR2(1000);
ln_num      NUMBER;
BEGIN
INSERT INTO test_table(TEST_ID)
VALUES(1);
INSERT INTO test_table(TEST_ID)
VALUES(2);
SELECT to_number('X') INTO ln_num FROM dual;
commit;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Writing exception logs!');
lv_err_code := SQLCODE;
lv_err_msg  := SQLERRM ;
prc_log_errors(lv_err_code,lv_err_msg);
rollback;
END;
/
pls6
pls5

Scenario 6. Can you fire DDLs in function?

Solution 6: Yes using dynamic sql, but can not use those function in SQL statement. See below example.

CREATE OR REPLACE FUNCTION FNC_SQUARE(
INPUT_ID IN NUMBER)
RETURN NUMBER
IS
ln_out NUMBER;
BEGIN
BEGIN
EXECUTE immediate 'drop table TEST_TABLE';
dbms_output.put_line('Table dropped');
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
EXECUTE immediate 'CREATE TABLE TEST_TABLE (TEST_ID NUMBER)';
dbms_output.put_line('Table created');
ln_out:= INPUT_ID*INPUT_ID;
RETURN ln_out;
END;
/
pls4

Now calling that function in SQL statement:

pls5

Same thing with the DML also you can use in function but cannot call using SQL statement. I have tried calling function from sql which has DML & commit;

pls6

Scenario 7. Can you call procedure from sql statement?

Solution 7: No

CREATE OR REPLACE PROCEDURE PRC_SQUARE(
INPUT_NUM IN NUMBER,OUTPUT_NUM OUT NUMBER )
IS
BEGIN
OUTPUT_NUM:= INPUT_NUM*INPUT_NUM;
END;
/

SELECT PRC_SQUARE(2) FROM dual;
pls6

Call procedure using below method.

declare 
ln_out NUMBER; 
begin 
PRC_SQUARE(6,ln_out); 
dbms_output.put_line('Square is: '|| ln_out); 
end; /
pls5

Function you can using SQL statements. As it has always return type.

create or replace FUNCTION FNC_SQUARE(
INPUT_ID IN NUMBER)
RETURN NUMBER
IS
ln_out NUMBER;
BEGIN
ln_out:= INPUT_ID*INPUT_ID;
RETURN ln_out;
END;
/
pls6

Thanks!

Happy Learning! Your feedback would be appreciated!