9 Replies Latest reply on May 21, 2014 3:31 AM by noor

    OPB Query to identify REVERT option enabled transformation

    New Member

      Hello All,

       

      I am looking for a OPB Query to identify REVERT option enabled transformations at session level.

       

      Could you please help me on this.

       

      Regards

      Chaitanya

        • 1. OPB Query to identify REVERT option enabled transformation
          Guru

          Hi Krishna,

           

          Can you please tell why is the requirement of such a query?

           

          Just wanted to get aware of the use case behind your question..

           

          I tried checking for the Repository tables to see if I can get the information required by you but was not able to find any such table..

           

          Lets see if some other experts have any way to figure out the solution for this.. or if I have missed any repository table to be checked for

          • 2. OPB Query to identify REVERT option enabled transformation
            New Member

            Hi Yogesh,

             

            We are building a report which ensures that the developer has follwed the checklist while developing the code.

            So before moving to prod, we need to ensure that there are no "Reverts" (session level changes). As per our standards here, all chages should be happened at base level and no chages at sess/Wf's.

             

            Let me know if this is not clear so that I can try with more details.

            it would be great if you can advise me on this.

             

            Regards

            Chaitanya

            • 3. OPB Query to identify REVERT option enabled transformation
              Guru

              Probably there cannot be a simple query to retrieve this information for a simple reason:

              You will have to compare settings for reusable sessions and for their instance counterparts.

              You can find both of them in the REP_TASK_INST view.

              The properties, however, are distributed over several different views, such as REP_WIDGET_ATTR, REP_SESSION_CNXS, REP_TASK_ATTR, and quite a few more.

              If you want to join all those different views in one SQL statement, I only can say, have fun writing such a query. I wouldn't even bother trying it. Developing such a "comparator" as a mapping is one thing (which will surely be hard to do), but building it as a SQL query doesn't make much sense; you have to join too many different parts of information.

              And honestly I am confident that almost no one can tell you off the top of their head for the majority of session settings which views contain which parts of information. You will have to find out most of them for yourself.

               

              And I am pretty sure that exactly this (namely comparing the session settings step by step) is programmed and done in the Workflow Manager; otherwise they couldn't display the Revert buttons.

               

              Regards,

              Nico

              • 4. OPB Query to identify REVERT option enabled transformation
                New Member

                Hi Nico,

                 

                Thanks for the information.

                But none of the above mentioned tables hold those details.

                 

                Could anyone pls help me on this.

                 

                Regards

                Chaitanya

                • 5. OPB Query to identify REVERT option enabled transformation
                  New Member

                  I too would like a query to get me this information.  Trying to find a quick way to identify sessions that have had options overridden.  Would like a report to list all sessions where the revert button is currently enabled.  There has to be a way to get this out of the repository.  Anybody have a query that would do it?

                  • 6. OPB Query to identify REVERT option enabled transformation
                    Srikanth Vishnuvajhala Guru

                    The best option would be to raise a case with Informatica GCS and request to provide a query. I have done the same few times in the past and am sure they will provide the solution.

                    • 7. OPB Query to identify REVERT option enabled transformation
                      Guru

                      Try this in Oracle. Tested on 9.0.1. Not tested with multiple "session configurations" for a folder.

                      SELECT x.folder,
                           x.workflow_name,
                           x.session_name, --x.task_id,x.instance_id,x.attr_id,
                           MAX (CASE WHEN x.lvl = 1 THEN x.VALUE END) Sess_Instance_val,
                           MAX (CASE WHEN x.lvl = 2 THEN x.VALUE END) Sess_val,
                           MAX (CASE WHEN x.lvl = 3 THEN x.VALUE END) Folder_Default,
                           x.attr_name
                      FROM (SELECT DISTINCT Z.SUBJ_NAME FOLDER,A.TASK_NAME WORKFLOW_NAME,
                          B.INSTANCE_NAME SESSION_NAME,
                          B.TASK_ID,B.INSTANCE_ID,c.ATTR_ID,c.ATTR_VALUE VALUE,1 lvl,ATT.ATTR_NAME
                      FROM OPB_CFG_ATTR C,
                           OPB_TASK_INST B,
                           OPB_TASK A,
                           OPB_SUBJECT Z,
                           OPB_MMD_CFG_ATTR att
                      WHERE     c.attr_id = att.attr_id
                           AND A.SUBJECT_ID = Z.SUBJ_ID
                           AND A.TASK_TYPE IN (70, 71)
                           AND A.TASK_ID = B.WORKFLOW_ID
                           AND B.WORKFLOW_ID = C.WORKFLOW_ID
                           AND B.TASK_ID = C.SESSION_ID
                           AND B.INSTANCE_ID = C.SESSION_INST_ID
                           AND B.TASK_TYPE = 68-- session
                           AND A.IS_VISIBLE = 1
                           AND A.VERSION_NUMBER = B.VERSION_NUMBER
                           AND B.VERSION_NUMBER = C.VERSION_NUMBER
                      UNION
                      SELECT DISTINCT Z.SUBJ_NAME FOLDER,A.TASK_NAME WORKFLOW_NAME,B.INSTANCE_NAME SESSION_NAME,
                          B.TASK_ID,B.INSTANCE_ID,c.ATTR_ID,c.ATTR_VALUE,2 lvl,ATT.ATTR_NAME
                      FROM OPB_CFG_ATTR C,
                           OPB_TASK_INST B,
                           OPB_TASK A,
                           OPB_SUBJECT Z,
                           OPB_TASK T,
                           OPB_MMD_CFG_ATTR att
                      WHERE     c.attr_id = att.attr_id
                           AND A.SUBJECT_ID = Z.SUBJ_ID
                           AND A.TASK_TYPE IN (70, 71)
                           AND A.TASK_ID = B.WORKFLOW_ID
                           AND C.SESSION_ID = B.TASK_ID
                           AND B.TASK_TYPE = 68
                           AND C.SESSION_ID <> 0
                           AND C.SESSION_INST_ID = 0
                           AND C.WORKFLOW_ID = 0
                           AND A.IS_VISIBLE = 1
                           AND T.TASK_TYPE = 68 -- session
                           AND B.TASK_ID = T.TASK_ID
                           AND A.VERSION_NUMBER = B.VERSION_NUMBER
                           AND T.IS_VISIBLE = 1
                           AND T.VERSION_NUMBER = C.VERSION_NUMBER
                      UNION
                      SELECT DISTINCT Z.SUBJ_NAME FOLDER,A.TASK_NAME WORKFLOW_NAME,
                           B.INSTANCE_NAME SESSION_NAME,B.TASK_ID,
                           B.INSTANCE_ID,c.ATTR_ID,c.ATTR_VALUE,3 lvl,ATT.ATTR_NAME
                      FROM OPB_CFG_ATTR C,
                           OPB_TASK_INST B,
                           OPB_TASK A,
                           OPB_SUBJECT Z,
                           OPB_SESSION_CONFIG SC,
                           OPB_TASK T,
                           OPB_COMPONENT M,
                           OPB_MMD_CFG_ATTR att
                      WHERE     c.attr_id = att.attr_id
                           AND A.SUBJECT_ID = Z.SUBJ_ID
                           AND A.TASK_TYPE IN (70, 71)
                           AND A.TASK_ID = B.WORKFLOW_ID
                           AND B.TASK_TYPE = 68
                           AND Z.SUBJ_ID = SC.SUBJECT_ID
                           AND SC.CONFIG_TYPE = 72
                           AND SC.CONFIG_ID = C.CONFIG_ID
                           AND C.SESSION_ID = 0
                           AND C.SESSION_INST_ID = 0
                           AND C.WORKFLOW_ID = 0
                           AND T.TASK_ID = B.TASK_ID
                           AND B.TASK_ID = M.TASK_ID
                           AND M.OBJECT_TYPE = 72
                           AND M.REF_OBJ_ID = C.CONFIG_ID
                           AND A.IS_VISIBLE = 1
                           AND A.VERSION_NUMBER = B.VERSION_NUMBER
                           AND SC.IS_VISIBLE = 1
                           AND SC.VERSION_NUMBER = C.VERSION_NUMBER
                           AND T.IS_VISIBLE = 1
                           AND T.VERSION_NUMBER = M.VERSION_NUMBER
                                       AND T.TASK_TYPE = 68) x
                         WHERE folder like '%'
                      GROUP BY x.folder,x.workflow_name,x.session_name,
                               x.task_id,x.instance_id,x.ATTR_ID,x.ATTR_NAME
                      ORDER BY 1, 2, 3, 4;
                      

                      • 8. OPB Query to identify REVERT option enabled transformation
                        New Member

                        Thanks for the query.. will see if this helps me.

                        • 9. Re: OPB Query to identify REVERT option enabled transformation
                          Guru

                          Hello,

                           

                          Any update on this? Anyone has a readymade on the original request - find all 'transformations' that have revert enabled at session level.

                           

                          Few developers might have done override a lookup at session levelf for testing and forgot to 'unrevert' it.

                           

                          Would be helpful if anyone has a readymade query.

                           

                          ###The query shared in this conversation by user152629 gives overriden session attributes taking the session config as the baseline. But what Chaitanya asked is to find reverts enables at transformation level .

                           

                          Thanks,

                          Noor.