<

Unique Technique of working with Informatica in Oracle

Published on
6,304 Points
3,304 Views
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,
                                                        mapping_name,
                                                        target_name
                                                       )
AS
   SELECT NVL (s.source_name, 'NO SOURCE') source_name, t.mapping_name,
          t.target_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
   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.
0
Author:oleggold
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