Unique Technique of working with Informatica in Oracle

Published on
6,363 Points
Last Modified:
You may know or may not that Informatica has pecular repository and methods of change propagations that are not all so "simple" least said.

Informatica is ETL, extraction transformation language, a tool serving wide array of data transformation tasks.

Here I'm trying to facilitate its repository which is basically Informatica's internal database . The problem is that this database is not very easily accessible. There are tools provided by Informatica which cost a lot and not widely used because of that. I'm trying here to show a way around to use the repository.

If Your Informatica is installed on Oracle Server this is what You can do:

1. Create dblink to your Informatica server
2. Create directories on your Informatica server:
create directory R_BAD as '/etl/stage001/BadFiles/R/'

Open in new window

3. Create some structures to automatically create external tables in the directory

Then You can use following code to ncreate views:
create view v_infa_source_logs (source_name,
   SELECT NVL (s.source_name, 'NO SOURCE') source_name, t.mapping_name,
     FROM informatica.rep_src_mapping@p_etl s,
          informatica.rep_targ_mapping@p_etl t,
          informatica.rep_sess_tbl_log@p_etl l
   WHERE  t.mapping_name = s.mapping_name
      AND s.subject_area = 'PROD'
      AND s.subject_area = l.subject_area
      AND s.source_name = l.table_name

Open in new window

Now you have unique ability to run the comparisons of your database data with the Informatica server data (see example) and create views pointing there.
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free