<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Unique Technique of working with Informatica in Oracle

Published on
6,240 Points
3,240 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
Comment
Author:oleggold
  • 2
2 Comments
LVL 21

Author Comment

by:oleggold
mrjoltcola,
Thanks for Your comments.
I do think this article almost adhers to the guidlins,let me put those inside ,I agree,I wrote it for experts,I'll try to refrase and yes,English is not my 1st language.
Ok,let's try:
1. A statement of the problem being resolved or issue being discussed
You may know or may not that Informatica has pecular repository and methods of change propagations that are not all so "simple" least said.
That meaning Informatica is ETL,extraction transformation language, tool serving wide array of data transformation tasks.
Here I'm trying to facilitate it's repository which is basically  Informatica 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 gthe repository.
2. A description of the resolution of the problem
Let's say You've created a  dblink to Your Infa repository.
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


3. A summary of the above two segments.
In summary.we can use simple Oracle methods to explore Infa repository and have it handy to the users
0
LVL 21

Author Comment

by:oleggold
oh,and sorry,the most important for novice would be that the above query can be used in an excel worksheet but that in itself a topic for another article,sorry ,very little time:)
0

Featured Post

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Join & Write a Comment

This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month