Monday, October 25, 2021

ODI 12C - CDC implementation using JKM

Here, we will see how to capture changed data(New inserted data and update data) using JKM and send the data to Target.

Implementation steps:

Step1: import JKM 


Step2: model -- Journalizing -- select Journalizing mode . Here choosing Simple and select KM.


Step3: Open Datastore and constraints -- create primary key as empno.


Step4: EMP datastore -- CDC -- Subscriber -- subscribe. Add one subscriber.


Step5: Add to CDC


Step6: Start Journal ans select subscriber.



Step7: you can check that Subscribers will be created and J$ and JV$ tables.






Step8:
add emp table in the journalized table.

Step9:
do the mapping Nd select subscriber in the logical Journalizing section

Step10:
Add LKM and IKM and flow control as false.



Step11:
select source unit and check the option Journalized Data only.


Friday, October 22, 2021

ODI 12c - Manually run a load plan if schedule not worked automatically

If schedule does not work automatically then we can run the load plan manually.

Steps:

Operator tab →Load plans and scenarios →select your load plan→ right click on and run and select your agent and ok.


ODI 12c - Procedure and use options

Procedure in ODI:

  • A procedure in Oracle Data Integrator (ODI) consists of a series of commands executed in sequence. Commands contain code that can be executed by database engines, the operating system where the Agent is running, or directly by ODI. 
  • You can also define options in the procedure to control its behavior at run time. A very useful property of procedures is that they are reusable and can be inserted into packages. Thus, just as procedures comprise commands, a package can contain several procedures as steps.
  • You use procedures in a package or a scenario, or you can run them directly from the Designer Navigator.

Implementation Steps:

Step1: Designer -- Projects -- your project -- procedure -- new procedure


Step2: Define Procedure name, and source and target technology if needed.


Step3: Tasks -- Add new task


Step4: Created couple of tasks. For 1st task provide name


Step5: Target command -- select technolgy, context, logical schema, and command.


Step6: Need to provide source command if you want to select some fields.

Step7: Mention the same for remaining 2 commands




Step8: For this 3rd command, used source command to select the data from source emp table.


Step9: Assign the selected source fields in the target insert values using :fieldname



Step10: Save and run it.


Note: Select the Ignore Errors check box so that the procedure runs even if this command fails.

Step11: To use options, go to Options and add option


Step12: Added 3 options


Step13: Go to Tasks and option section and select the options to be used for a particular task.

Thursday, October 21, 2021

ODI 12c - database Free space check sql and resize datafile

Query to check the total space in mb, free space in mb and percent free for UNDO_9 tablespace.

Select

a.tablespace_name,

sum(a.bytes)/(1024*1024) total_space_MB,

round(b.free,2) Free_space_MB,

round(b.free/ (sum(a.bytes)/(1024*1024))* 100,2) percent_free

from dba_data_files a,

(Select tablespace_name, sum(bytes)/(1024*1024) free from dba_free_space group by tablespace_name) b

where a.tablespace_name = b.tablespace_name(+)

and a.tablespace_name ='UNDO_9'

group by a.tablespace_name, b.free;





Query to manually resize db datafile for tablespace undo_9

ALTER DATABSE DATAFILE '+Data/...path/datafile/undo_9...' RESIZE 800G

The above command resize the datafile to 800 gb.



ODI 12c - Activate or Deactivate your schedule on load plan

Here we will show the steps to activate or deactivate your schedule created on load plans.

Steps:

Step1: Go to Operator tab -- Scheduling -- All Schedules -- select your schedule and expand and rightclick on Golbal/logical agent -- open


Step2: select status Active or inactive and save.


Step3: Go to Load plan and Scenarios -- double click on your load plan

Step4: Enable or disable the steps. Generally need to disable or enable 1st parent steps.

Why we need to disable or enable steps in load plan:

Sometimes we see that though you have inactivated the schedule, still its running so to avoid this scenario, we can also disable the steps in load plan as well.

Tuesday, October 19, 2021

OIC - Unable to convert a string in the xml to a java.sql.date - while inserting into database

Scenario: We have received an input date as YYYY/MM/DD format and when we are trying to insert it into a database, got below error:

Unable to convert a string in the xml to a java.sql.date.


Solution: Convert the YYYY/MM/ DD format to  YYYY-MM-DD format. You can use translate() function and convert the "/" to "-".

translate(datefield, "/","-")


Monday, October 18, 2021

OIC - validate date format as MM-DD-YYYY

I use this regex for validating MM-DD-YYYY:

function isValidDate(subject){
  if (subject.match(/^(?:(0[1-9]|1[012])[\- \/.](0[1-9]|[12][0-9]|3[01])[\- \/.](19|20)[0-9]{2})$/)){
    return true;
  }else{
    return false;
  }
}

It will match only valid months and you can use / - or . as separators.

Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...