7 Replies Latest reply on Apr 21, 2014 9:31 PM by user147600

    How to find details of table used in lookup

    Guru

      All,

       

      When we want to check if a table is used in mapping then we see the dependency on that table. In this case if the table is used as a a source or target in one of the mapping then we see an output but when the table is only used in lookup we never get any output.

       

      Is there a good way using which I can identify a table being used in anywhere including lookup in Informatica?

       

       

      Thanks,

      Vaibhav

        • 1. Re: How to find details of table used in lookup
          Guru

          Hi Vaibhav,

           

          The table name used for Lookup transformation can be verified under the property Lookup table name.

          Also, Metaquery tool can be used for extracting all the lookup table names in the repository.

          https://mysupport.informatica.com/docs/DOC-7679

           

          Hope this helps.

           

          Thanks and Regards,

          Sindhu

          • 2. Re: How to find details of table used in lookup
            Guru

            Thanks Sindhu, I know about Metadata query tool but the problem is that we have our repository metadata stored in TRD schema against the default pc_repo schema for which this tool is configured. I have even opened SR for this but haven't got any success in getting it configured.

             

            Though I did take the metadata query which the tool was firing to obtain details of the table but that query only looks into the mapping level query not at session level and if there is any override at session level then the data is not reliable.

             

             

            Thanks,

            Vaibhav

            • 3. Re: How to find details of table used in lookup
              Seasoned Veteran

              Try Meta data Query ..if not use this below thing

              *********************

               

              If you want to see that if it is used in that mapping any where...you can do like this.

               

              1) Export the mapping to XML

              2)open the XML  in Notepad++ or any other  notepad tools . find  all for that particular word. so you will get that hints..I personally prefer notepad++..good one and it is free.

               

              ************

              If you are not sure about the Mapping but sure about the informatica folder. You can export folder and create XML ...which can be further used for perfoming the same steps..

               

              Steps for creating folder export

               

              1) open command prompt

               

              2)cd C:\Informatica\PowerCenter8.6.0\client\bin   ----> Give Bin path of the client. It depends on your machine path..change as necessary

              3)Connecting and exporting ( pmrep connect changes based on native user or LDAP user)   change the details of  RN--repository name ; domain - Your domain  and Username and Password..MYFOLDER-- to the folder you want.

               

              pmrep connect -r RN -d domain -n USERNAME  -x password

              pmrep objectexport -f MYFOLDER -u MYFOLDER.xml

               

              If using LDAP use this for connecting

              pmrep connect -r IAP****** -d domain_PRD -n USERNAME -s LDAP -x password

               

              4) Now  go to C:\Informatica\PowerCenter8.6.0\client\bin  and find the XML .using which you can do your analysis.

               

               

              This is  for doing impact analysis.. if your regualr ways are not working.

              • 4. Re: How to find details of table used in lookup
                Guru

                Thanks for your input Rajanala, but this means I need to export entire folder(s) export and do a grep/search on that. I think this is a very manually process and something which I am not looking since, everytime there is a change I need to reexport entire folder which is again a heavy process. I did a similar export last time and the query which came out of lookup was of mapping level, I might have done something wrong in exporting I guess.

                 

                 

                I am looking for a query at repository database level solution which is very dynamic against export option for folder.

                 

                Thanks,

                Vaibhav

                • 5. Re: How to find details of table used in lookup
                  Seasoned Veteran

                  Yep. I do agree this is manual process and not suitable for  this. Yep repository Query willl be good in such casess. But helpful in any cases whcih we cant get using rep queries

                   

                  check this out..if you can get any help from here..

                   

                  ETL Tech Blog: INFORMATICA REPOSITORY QUERIES - PART I

                   

                  (http://aambarish.blogspot.in/2012/01/normal-0-false-false-false-en-us-x-none.html)

                  1 of 1 people found this helpful
                  • 6. Re: How to find details of table used in lookup
                    Shannon Heustess Guru

                    SELECT

                        *

                    FROM

                        (

                    SELECT

                        DISTINCT A.SUBJ_NAME,

                        C.MAPPING_NAME,

                        D.INSTANCE_NAME LOOKUP_NAME_IN_MAPPING,

                        E.ATTR_VALUE LKP_TABLE_NAME

                    FROM

                        OPB_SUBJECT A,

                        OPB_MAPPING C,

                        OPB_WIDGET_INST D,

                        OPB_WIDGET_ATTR E

                    WHERE

                        A.SUBJ_ID=C.SUBJECT_ID AND

                        C.MAPPING_ID=D.MAPPING_ID AND

                        D.WIDGET_TYPE=11 AND

                        D.WIDGET_ID=E.WIDGET_ID AND

                        D.MAPPING_ID=E.MAPPING_ID AND

                        D.INSTANCE_ID=E.INSTANCE_ID AND

                        E.WIDGET_TYPE=11 AND

                        E.ATTR_ID=2 AND

                        C.IS_VISIBLE=1 AND

                        C.VERSION_NUMBER=D.VERSION_NUMBER AND

                        E.VERSION_NUMBER=C.VERSION_NUMBER

                    UNION

                    SELECT

                        DISTINCT A.SUBJ_NAME,

                        C.MAPPING_NAME,

                        D.INSTANCE_NAME LOOKUP_NAME_IN_MAPPING,

                        E.ATTR_VALUE LKP_TABLE_NAME

                    FROM

                        OPB_SUBJECT A,

                        OPB_MAPPING C,

                        OPB_WIDGET_INST D,

                        OPB_WIDGET_ATTR E,

                        OPB_WIDGET W

                    WHERE

                        A.SUBJ_ID=C.SUBJECT_ID AND

                        C.MAPPING_ID=D.MAPPING_ID AND

                        D.WIDGET_TYPE=11 AND

                        D.WIDGET_ID=E.WIDGET_ID AND

                        E.MAPPING_ID=0 AND

                        E.INSTANCE_ID=0 AND

                        E.WIDGET_ID=W.WIDGET_ID AND

                        W.WIDGET_TYPE=11 AND

                        D.INSTANCE_ID NOT IN(

                    SELECT

                        INSTANCE_ID

                    FROM

                        OPB_WIDGET_ATTR

                    WHERE

                        WIDGET_TYPE=11 AND

                        WIDGET_ID=D.WIDGET_ID AND

                        MAPPING_ID=D.MAPPING_ID)AND E.WIDGET_TYPE=11 AND

                        E.ATTR_ID=2 AND

                        C.IS_VISIBLE=1 AND

                        C.VERSION_NUMBER=D.VERSION_NUMBER AND

                        W.IS_VISIBLE=1 AND

                        E.VERSION_NUMBER=W.VERSION_NUMBER) DUAL

                        ORDER BY 1,2,3

                    • 7. Re: How to find details of table used in lookup
                      Guru

                      This is exactly the same query which I was looking for. Thanks a ton Shannon. BTW, I still want one more answer from your side, if I have a lookup override at session level which has query on some other table then I might not get correct result right?

                       

                       

                      Thanks,

                      Vaibhav