10 Replies Latest reply on Mar 9, 2012 10:27 AM by sanket_das@ahm.honda.com

    Find mappings not being used by any workflows

    New Member

      Hi All,

       

      I am using pmrep to clean up many unwanted workflows in a folder. Post that, I want to cleanup the mappings that are no longer used ( mostly used by these deleted workflows) . Is there a way to find out which mappings are no used by any workflows, except for going to individual mappings and viewing the dependencies as i want to do it for an entire folder with more than 1000 mappings?

       

      Any pointers/ queries would be helpful

       

      Thanks

      Sanket

       

      [--moved to subforum Metadata Reporter. Regards, Nico]

        • 1. Find mappings not being used by any workflows
          Guru

          If I recall correctly the Metadata Exchange View (MXView) REP_LOAD_SESSIONS contains most of the information you need, namely the relation between a mapping and its session instances. Now you only need to examine the MXView REP_TASK_INST in order to find out which workflow this session belongs to.

           

          Be warned that way back in 2007 we stumbled across a very nasty bug with pmrep deleteObject; removing any shortcut would not only remove the shortcut but also the original object (in your case, the mapping) but leaving the object (here: mapping name) intact.

          Meaning that after having removed a mapping shortcut we could neither edit the mapping (because it was gone) nor remove it (because the metadata already were gone) nor recreate it (because the name already existed). The only remedy was to restore a repository backup.

          Again, this was with shortcuts only

          Furthermore I don't know whether this bug still exists in 9.1, I only can tell for 8.0 and 8.1.x that it existed.

           

          Regards,

          Nico

          • 2. Find mappings not being used by any workflows
            gauravsanghi Active Member

            The below query will return all your mappings in every folder

             

            SELECT DISTINCT parent_subject_area, parent_mapping_name FROM rep_all_mappings ORDER BY 1, 2

             

            The following query should return all your folder, workflow, session, mapping information which are present. You can select just the distinct folder, mappings to list your mappings which are used by any workflow.

             

            SELECT   su.subj_name folder, t2.task_name workflow_name, t1.task_name session_name, m.mapping_name

                FROM opb_task t1, opb_task t2, opb_task_inst inst, opb_session s, opb_mapping m, opb_subject su

               WHERE t1.task_type = 68

                 AND t1.task_id = s.session_id

                 AND t1.version_number = s.version_number

                 AND s.mapping_id = m.mapping_id

                 AND t1.subject_id = su.subj_id

                 AND t1.is_visible = 1

                 AND m.is_visible = 1

                 AND inst.task_type = 68

                 AND inst.task_id = t1.task_id

                 AND t2.task_type IN (70, 71)

                 AND inst.workflow_id = t2.task_id

                 AND inst.version_number = t2.version_number

                 AND t2.is_visible = 1

            ORDER BY su.subj_name, session_name

             

             

            Do a minus (SQL 1 - SQL 2) should give you all mappings in a folder which are not used in a workflow in that folder.

             

            If you have shared objects, jsut go by mapping name, and also make sure you are running the query for a given repository schema only (in case you have multiple repositories in the same database, it is safe to prefix schema name to avoid any issues with your public/private synonyms).

             

            Let me know if that helps.

             

            Regards,

             

            Gaurav

            • 3. Find mappings not being used by any workflows
              Active Member

              Hi Sanket,
              Here is the SQL you are looking for We are using 8.6.1
              Displays mappings that do not have any sessions defined by repository and  folder.
              In the SQL Below relace folder name @ "ALL_MAPPINGS.SUBJECT_AREA LIKE '%Folder Name%

              "

               

               

               

                SELECT   MAPPING_REPOSIT_INFO.REPOSITORY_NAME,

                         ALL_MAPPINGS.SUBJECT_AREA,

                         ALL_MAPPINGS.MAPPING_NAME,

                         ALL_MAPPINGS.MAPPING_DESCRIPTION,

                         ALL_MAPPINGS.MAPPING_VERSION_NUMBER,

                         CASE

                            WHEN ALL_MAPPINGS.MAPPING_VERSION_STATUS = 0

                            THEN

                               'Active'

                            ELSE

                               CASE

                                  WHEN ALL_MAPPINGS.MAPPING_VERSION_STATUS = 9

                                  THEN

                                     'Logically Deleted'

                                  ELSE

                                     CASE

                                        WHEN ALL_MAPPINGS.MAPPING_VERSION_STATUS = 10

                                        THEN

                                           'Physically Deleted'

                                        ELSE

                                           NULL

                                     END

                               END

                         END,

                         ALL_MAPPINGS.MAPPING_LAST_SAVED,

                         CASE WHEN ALL_MAPPINGS.IS_SHORTCUT = 1 THEN 'Yes' ELSE 'No' END,

                         CASE

                            WHEN ALL_MAPPINGS.PARENT_MAPPING_IS_VALID = 1 THEN 'Yes'

                            ELSE 'No'

                         END,

                         (COUNT (DISTINCT (MAPPING_SESS_PARTITION_DEF.SESSION_ID)))

                  FROM   REP_REPOSIT_INFO MAPPING_REPOSIT_INFO,

                            REP_ALL_MAPPINGS ALL_MAPPINGS

                         LEFT OUTER JOIN

                            REP_SESS_PARTITION_DEF MAPPING_SESS_PARTITION_DEF

                         ON ALL_MAPPINGS.PARENT_MAPPING_ID =

                               MAPPING_SESS_PARTITION_DEF.MAPPING_ID

                 WHERE   (ALL_MAPPINGS.PARENT_MAPPING_ID <>

                             MAPPING_REPOSIT_INFO.REPOSITORY_ID)

                         AND (ALL_MAPPINGS.SUBJECT_AREA LIKE '%Folder Name%')

              GROUP BY   MAPPING_REPOSIT_INFO.REPOSITORY_NAME,

                         ALL_MAPPINGS.SUBJECT_AREA,

                         ALL_MAPPINGS.MAPPING_NAME,

                         ALL_MAPPINGS.MAPPING_DESCRIPTION,

                         ALL_MAPPINGS.MAPPING_VERSION_NUMBER,

                         CASE

                            WHEN ALL_MAPPINGS.MAPPING_VERSION_STATUS = 0

                            THEN

                               'Active'

                            ELSE

                               CASE

                                  WHEN ALL_MAPPINGS.MAPPING_VERSION_STATUS = 9

                                  THEN

                                     'Logically Deleted'

                                  ELSE

                                     CASE

                                        WHEN ALL_MAPPINGS.MAPPING_VERSION_STATUS = 10

                                        THEN

                                           'Physically Deleted'

                                        ELSE

                                           NULL

                                     END

                               END

                         END,

                         ALL_MAPPINGS.MAPPING_LAST_SAVED,

                         CASE WHEN ALL_MAPPINGS.IS_SHORTCUT = 1 THEN 'Yes' ELSE 'No' END,

                         CASE

                            WHEN ALL_MAPPINGS.PARENT_MAPPING_IS_VALID = 1 THEN 'Yes'

                            ELSE 'No'

                         END

                HAVING   (COUNT (DISTINCT (MAPPING_SESS_PARTITION_DEF.SESSION_ID))) = 0

              ORDER BY   1,

                         2,

                         3,

                         4,

                         5,

                         6,

                         7,

                         8,

                         9

              • 4. Find mappings not being used by any workflows
                New Member

                Hi Nico,

                 

                Our folder does not have any shortcut to mappings. However, we do use shortcut to sourc,targets,mapplets,transformations etc defined in another shared folder.

                 

                I hope these wont be impacted if we delete mappings using these shortcuts. However, just in case you have seen any issues with that too, please let me know.

                 

                Thanks

                Sanket

                • 5. Find mappings not being used by any workflows
                  New Member

                  Thanks Gaurav,

                   

                  I saw an issue that the query is some how not listing all workflows in the folder. Is there any specific condition to filter those out.

                  • 6. Find mappings not being used by any workflows
                    gauravsanghi Active Member

                    I don't think so. If you have identified such a workflow, can you please let me know if there is anything unusual about it? It might be the one which might just have task instances (like timer, command, email) which might not show up since we are looking for mapping specific workflows. I have been using this and never got a problem, but if there is, I would be more than happy to fix it myself too

                    • 7. Find mappings not being used by any workflows
                      Guru

                      A good way to get rid of unused objects (or unwanted versions) is to create a new folder and drag all the workflows into that new folder (select all workflows in Repo Mgr using shift-mouse-click)

                      • 8. Find mappings not being used by any workflows
                        New Member

                        Hi Gaurav,

                         

                        The pmrep 'listobject' option does not give the wflow, session, mapping list as given by your  query.

                         

                        However, it is true that the query does not somehow list all the wflows ( with sessions, mappings etc )in the folder.

                         

                        Strange but true. So, I am looking at the option of using ListObject and ListObjectDependencies for this purpose.

                         

                        Thanks

                        • 9. Re: Find mappings not being used by any workflows
                          premaswarup naru New Member

                          Hi,

                           

                          Go to Repository manager

                           

                          select tools --> Queries ---> New

                           

                          The below query will return all your unused mappings in every folder from that Repository

                           

                          Object type equal to Mapping

                           

                          AND

                           

                          Object Used Status equal to Unused

                           

                          This will give all unused mapping in any wrokflow.

                           

                          Message was edited by: premaswarup naru