3 Replies Latest reply on Jun 13, 2018 7:57 AM by Bill Robinson

    SQL Query to find the jobs with unlimited parallelism

    Santhosh Kurimilla

      Bill Robinson Parag DesaiMuthuraman Renganathan

       

      IIRC, there was a SQL Query to find all the jobs for which "Number of Targets to Process in Parallel" is set to unlimited. Can you please share it if you have it handy?

        • 1. Re: SQL Query to find the jobs with unlimited parallelism
          Bill Robinson
          WITH temp1 (group_id, path) AS  
          (  
                      select group_id, cast(name as varchar(1000))  
                        from blgroup bg1  
                        where parent_group_id = 0  
                        UNION ALL  
                    select bg1.group_id,  
                              cast(bg2.path || '/' || cast(bg1.name as varchar(100)) as varchar(1000))  
                        from blgroup bg1  
                        inner join temp1 bg2 on bg1.parent_group_id = bg2.group_id  
          )  
          select temp1.path || '/' || j.name,bo.name,jov.option_value from temp1  
                inner join job j on temp1.group_id = j.group_id  
                join job_option_value jov on j.job_id = jov.job_id and j.job_version_id = jov.job_version_id  
                join bloption bo on jov.option_id = bo.option_id  
                where jov.option_id = 301  
                and j.is_latest_version = 1  
                and j.is_saved_explicitly = 1  
                and j.is_deleted = 0  
          ; 
          

           

           

          should be this.  unlimited is either 0 or 2,147,483,647 so you can add a clause to only show results where jov.option_value > somenumber.    you can also filter on the job types w/ j.object_type_id if this is only for certain jobs. remember that some job types show as unlimited i think but they are not - for example a type 2 (run once pass targets as arg) i think shows as unlimited in the option_value but it's actually 1 because of how it works.

          • 2. Re: SQL Query to find the jobs with unlimited parallelism
            Santhosh Kurimilla

            Thank you, Bill.

             

            Can we also add one more condition- "any job set to unlimited parallelism and with more than 30 targets in it"?

            • 3. Re: SQL Query to find the jobs with unlimited parallelism
              Bill Robinson

              30 targets directly in the job probably.  30 targets via a smart group, no.?