5 Replies Latest reply on Sep 8, 2021 6:34 PM by srini komm


    srini komm Guru

      Can anyone suggest sql query to find the mappings, session and  command tasks associated with workflow for all project folders in  PC 10.0?

        • 1. Re: PowerCenter
          Nico Heinze Guru

          Not as easy as a single SELECT query.

          The point is: do your workflows use worklets?

          If not, things are easy, for this case I think I can send you a query to get you all the sessions and mappings used in whichever workflows.

          If yes, you will have to go a more complex road, but I'll describe that only upon request (because it needs to recursively scan parts of the repository, and that's not really easy to do in SQL).


          However, for the command tasks things are more complicated. I well recall 2013 when I was tasked to retrieve all command tasks for thousands of workflows, and in the end I had to scan the XML exports of all those workflows; I didn't succeed in finding a query to get them from the repository itself.

          So what I have done at that time was to build a script and a workflow; the script would export all the requested workflows to XML file and parse these XML exports for the command tasks. Tedious but at least this works safely.




          • 2. Re: PowerCenter
            user136699 Guru

            You can use the Metaquery tool to fetch this information. As Nico suggested, it is not in a single place, but Metaquery offer the option to :

            - list workflow with associated sessions and maps ( the option "workflow session mapping" under "Workflow")

            - list command tasks and their commands ( the option "Commands in command task").


            Metaquery is a free download available on the ftp site in the folder /updates/Metaquery.


            2 of 2 people found this helpful
            • 3. Re: PowerCenter
              user140848 Guru

              Here's the query I use for command tasks matching a certain string (you can of course omit that condition, and I've commented it out below).


              Naturally I can't 100% guarantee the results of this are correct.


              /* Find command tasks with a particular command string */

              select s.subj_name folder, wf.task_name workflow, decode(c.ref_obj_type, 2, 'Pre session', 3, 'Post session success', 4, 'Post session failure', 'Standalone') task_type,

              nvl(ti.instance_name, decode(t.is_reusable, 1, t.task_name || '(' || cti.instance_name || ')', cti.instance_name)) task_name,

              t.is_reusable, tvl.exec_order, tvl.val_name cmd_name, tvl.line_no, tvl.pm_value cmd_string

              from opb_task_val_list tvl

              inner join opb_task t on (tvl.task_id = t.task_id and tvl.version_number = t.version_number)

              inner join opb_subject s on tvl.subject_id = s.subj_id

              left outer join opb_task_inst ti on (t.task_id = ti.task_id and t.version_number = ti.version_number)

              left outer join opb_component c on (t.task_id = c.ref_obj_id and (c.ref_version_number = t.version_number or t.is_reusable = 1))

              left outer join opb_task_inst cti on (c.task_id = cti.task_id and c.version_number = cti.version_number)

              inner join opb_task wf on nvl(ti.workflow_id, cti.workflow_id) = wf.task_id

              where tvl.task_type = 58 /* command task */ and tvl.component_type = 0

              --and (tvl.pm_value like '%gpg%')

              order by folder, workflow, task_name, exec_order, line_no;


              I hope this is helpful for you.


              Regards, Sacha

              1 of 1 people found this helpful
              • 4. Re: PowerCenter
                user140848 Guru

                I should note that in particular, "workflow" should really be "workflow or worklet". I haven't tried to get the real workflow name, just the structure containing either the standalone command task or the session with the pre- or post-session command task. But I feel this is more useful anyway - if, for example, a command task in a worklet needs updating, it's the worklet name you need to know to update it; you don't need to see which workflows it is used in.


                Regards, Sacha

                • 5. Re: PowerCenter
                  srini komm Guru

                  Thanks for all your responses.