top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration

Facebook Login
Site Registration

Search Informatica for text in SQL override

+3 votes

Is there a way to search all the mappings, sessions, etc. in Informatica for a text string contained within a SQL override?

For example, suppose I know a certain stored procedure (SP_FOO) is being called somewhere in an INFA process, but I don't know where exactly. Somewhere I think there is a Post SQL on a source or target calling it. Could I search all the sessions for Post SQL containing SP_FOO ? (Similar to what I could do with grep with source code.)

posted Oct 7, 2014 by Amit Sharma

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

If you have read access to the schema where the informatica repository resides, try this.

SELECT DISTINCT f.subj_name folder, e.mapping_name, object_type_name,
                b.instance_name, a.attr_value
           FROM opb_widget_attr a,
                opb_widget_inst b,
                opb_object_type c,
                opb_attr d,
                opb_mapping e,
                opb_subject f
          WHERE a.widget_id = b.widget_id
            AND b.widget_type = c.object_type_id
            AND (   object_type_name = 'Source Qualifier'
                 OR object_type_name LIKE '%Lookup%'
            AND a.widget_id = b.widget_id
            AND a.attr_id = d.attr_id
            AND c.object_type_id = d.object_type_id
            AND attr_name IN ('Sql Query')--, 'Lookup Sql Override')
            AND b.mapping_id = e.mapping_id
            AND e.subject_id = f.subj_id
            AND a.attr_value is not null
            --AND UPPER (a.attr_value) LIKE UPPER ('%currency%')
answer Oct 8, 2014 by Shweta Singh
Similar Questions
0 votes

I installed informatica PC8.6 on Windows XP and SQL Server 2008 R2 on another machine. How can I access this database from informatica pc.8.6. and is it possible to access?

+1 vote

I have a Time column in my SQL Server table which has data like (14:00:00.0000000).

I am trying to load this type of data in Oracle table which has corresponding column datatype as timestamp. I am trying to convert the time column in SQL server to timestamp column in oracle using Informatica.

Whats the best way to do it?

0 votes

I have Informatica 9 and an Oracle database in my system. Now I want to install a SQL Server database in my system and add this database to Informatica.

Is that possible ?

Purpose: I need to migrate some of the tables from SQL Server to Oracle database using informatica.

Could anyone let me know, after installing SQL Server, how can I add this SQL Server database to informatica for creating mapping?

+2 votes

I have two tables to join with a column (say emp_id).. if emp_id in both the tables have null values, how will SQL Server and Oracle treat???

I read that informatica will neglect the NULL rows when joining..if I handle the null, by substituting -1, a cross-join will happen which i don't want..

What can I do here? (I cannot completely neglect the rows which has NULL)

+1 vote

I want to have compatible SQL for both Oracle database and Microsoft SQL server.

I want a compatible SQL expression that will return true for not null and not empty strings.

If I use:

column <> ''

it will work on Microsoft SQL server but not on Oracle database (as '' is null for Oracle)

If I use:

len(column) > 0

it will work on Microsoft SQL server but not on Oracle database (since it uses length() )

Contact Us
+91 9880187415
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Karnataka INDIA.