7 Replies Latest reply on Aug 7, 2012 10:41 AM by rajendrashahane

    Query to check “Update else Insert”

    Guru

      Hi Experts,

       

      We have changed our load strategy from “Update else Insert” to “Update as Update” for all our sessions. Could anyone provide a query to check quickly if any session still has “Update else Insert” property?

       

      Thanks, SK

        • 1. Query to check “Update else Insert”
          Active Member

          Hello SK

           

          I do not have the exact query on top of my head right now but i can get you started. Please look at OPB tables related to session like OP_SESS_EXTNS, OPB_SESS_CONFIG, OPB_SWIDGET_ATTR also maybe OPB_WIDGET_ATTR

           

          And also look at MX views.

           

          Thanks

          Sandeep

          1 of 1 people found this helpful
          • 2. Query to check “Update else Insert”
            Guru

            Metaquery 3.0 download is your best starting point. This isn't perfect, but try it. Not for versioned repositories. Should be ok in 8.6 to at least 9.0.

            SELECT *
              FROM (SELECT Z.SUBJ_NAME,
                           A.TASK_NAME WORKFLOW_NAME,
                           B.INSTANCE_NAME SESSION_NAME,
                           C.INSTANCE_NAME TARGET_INSTANCE_NAME,
                           R.ATTR_name, DECODE (D.ATTR_VALUE,  '0', 'NOT SELECTED',  '1', 'SELECTED')   OPT
                      FROM OPB_TASK A,
                           OPB_TASK_INST B,
                           OPB_SWIDGET_INST C,
                           OPB_EXTN_ATTR D,
                           OPB_SUBJECT Z, OPB_MMD_EXTN_ATTR r
                     WHERE    d.object_type=R.OBJECT_type
                            and d.object_subtype=R.OBJECT_SUBTYPE
                            and d.ATTR_ID = r.attr_id
                            and Z.SUBJ_ID = A.SUBJECT_ID
                           AND A.TASK_TYPE = 71
                           AND A.TASK_ID = B.WORKFLOW_ID
                           AND B.TASK_TYPE = 68
                           AND D.SESSION_INST_ID = B.INSTANCE_ID
                           AND B.TASK_ID = C.SESSION_ID
                           AND C.WIDGET_TYPE = 2
                           AND C.WIDGET_ID IN (SELECT TARGET_ID
                                                 FROM OPB_TARG
                                                WHERE FILE_ID = 0)
                           AND B.TASK_ID = D.SESSION_ID
                           AND C.SESSION_ID = D.SESSION_ID
                           AND C.SESS_WIDG_INST_ID = D.SESS_WIDG_INST_ID
                           AND D.ATTR_ID = 7
                           --AND Z.SUBJ_NAME = 'F_MetricBased'
                    UNION
                    SELECT Z.SUBJ_NAME,
                           A.TASK_NAME WORKFLOW_NAME,
                           B.INSTANCE_NAME SESSION_NAME,
                           C.INSTANCE_NAME TARGET_INSTANCE_NAME,
                           R.ATTR_name, DECODE (D.ATTR_VALUE,  '0', 'NOT SELECTED',  '1', 'SELECTED')   OPT
                      FROM OPB_TASK A,
                           OPB_TASK_INST B,
                           OPB_SWIDGET_INST C,
                           OPB_EXTN_ATTR D,
                           OPB_SUBJECT Z, OPB_MMD_EXTN_ATTR r
                     WHERE    d.object_type=R.OBJECT_type
                            and d.object_subtype=R.OBJECT_SUBTYPE
                            and d.ATTR_ID = r.attr_id
                            AND    Z.SUBJ_ID = A.SUBJECT_ID
                           AND A.TASK_TYPE = 71
                           AND A.TASK_ID = B.WORKFLOW_ID
                           AND B.TASK_TYPE = 68
                           AND D.SESSION_INST_ID = 0
                           AND D.WORKFLOW_ID = 0
                           AND B.TASK_ID = C.SESSION_ID
                           AND C.WIDGET_TYPE = 2
                           AND C.WIDGET_ID IN (SELECT TARGET_ID
                                                 FROM OPB_TARG
                                                WHERE FILE_ID = 0)
                           AND B.TASK_ID = D.SESSION_ID
                           AND C.SESSION_ID = D.SESSION_ID
                           AND C.SESS_WIDG_INST_ID = D.SESS_WIDG_INST_ID
                           AND D.ATTR_ID = 7
                           AND (B.TASK_ID) NOT IN
                                  (SELECT B.TASK_ID
                                     FROM OPB_TASK A,
                                          OPB_TASK_INST B,
                                          OPB_SWIDGET_INST C,
                                          OPB_EXTN_ATTR D,
                                          OPB_SUBJECT Z
                                    WHERE     Z.SUBJ_ID = A.SUBJECT_ID
                                          AND A.TASK_TYPE = 71
                                          AND A.TASK_ID = B.WORKFLOW_ID
                                          AND B.TASK_TYPE = 68
                                          AND D.SESSION_INST_ID = B.INSTANCE_ID
                                          AND B.TASK_ID = C.SESSION_ID
                                          AND C.WIDGET_TYPE = 2
                                          AND C.WIDGET_ID IN (SELECT TARGET_ID
                                                                FROM OPB_TARG
                                                               WHERE FILE_ID = 0)
                                          AND B.TASK_ID = D.SESSION_ID
                                          AND C.SESSION_ID = D.SESSION_ID
                                          AND C.SESS_WIDG_INST_ID = D.SESS_WIDG_INST_ID
                                          AND D.ATTR_ID = 7)) DUAL
             WHERE SUBJ_NAME LIKE '%' AND OPT = 'SELECTED'
            
            1 of 1 people found this helpful
            • 3. Query to check “Update else Insert”
              Guru

              Sorry, but I have access to views only

              • 4. Query to check “Update else Insert”
                Guru

                Then export your XML and search for the property that way.

                • 5. Query to check “Update else Insert”
                  Active Member

                  This attribute is somewhere in the repository views but they hide it in fields with generic names that are really hard to figure out. The easiest thing to do is just put in a low level support ticket and ask. When you get the answer out of them please post it here because I'd like to see it too.

                  • 6. Query to check “Update else Insert”
                    MdAdilShariff Guru

                    Hi SK

                     

                    Metaquery 3.0 works for versioned repositories as well

                     

                    I would suggest PMREP MassUpate command which is mainly built for hassle free mass update.

                     

                    Regards

                    Adil

                    • 7. Query to check “Update else Insert”
                      New Member

                      Hi

                       

                      You can use the informatica query builder

                       

                      Go the repository manager and in the menu you can find the query option in that you need to create a query and execute

                       

                      Try exploring it , you may get the object what you are looking for

                       

                      Regards

                      Rajendra S.