IBM i Global

IBM i Global

Connect, learn, share, and engage with IBM Power.

 View Only

  • 1.  SQL query to find a specific job

    Posted Wed August 07, 2024 12:18 PM

    Hi,

    Is there any way to reduce execution time of below job to find the desired job detail?

    "SELECT *                        
    FROM TABLE(QSYS2.JOB_INFO(      
    JOB_STATUS_FILTER => '*ALL',    
    JOB_TYPE_FILTER => '*ALL',      
    JOB_SUBSYSTEM_FILTER => '*ALL', 
    JOB_USER_FILTER => '*ALL',      
    JOB_SUBMITTER_FILTER => '*ALL'  
    ))                              "

    Apart from  running this SQL query "SELECT JOB_NAME like '%Jobnumber/USERName/JobName%'   FROM TABLE(QSYS2.JOB_INFO) "  ?

    Thanks much..



  • 2.  RE: SQL query to find a specific job

    Posted Wed August 07, 2024 02:33 PM
    If the job is active, use the QSYS2.ACTIVE_JOB_INFO table function and try to restrict the amount of information that you retrieve in the first place:

      select *
      from table(
        active_job_info(
          job_name_filter => 'MYJOB',
          current_user_list_filter => 'USERA,USERB',
          subsystem_list_filter => 'QINTER,QBATCH',
          detailed_info => 'NONE'
        )
      );

    By using the filter parameters you restrict the amount of information that has to be collected. So try to restrict as much as possible before using WHERE conditions. 

    This way the function(s) perform quite fast. 

    HTH
    Daniel



    Original Message


  • 3.  RE: SQL query to find a specific job

    Posted Wed August 07, 2024 03:20 PM

    Thanks,

    As 'Job_Name' already have 'User name' in it along with the job number and Job name so why is it specifically required to mention "

    current_user_list_filter => 'USERA,USERB',     "  in that SQL Query here? 

    ...Also whatif the  job is no longer active in the system (went into 'OutQ' or  'ended' ,'completed' or we want to find out very old job which had failed in the system very long back (months or even years back, or even decades or even the first job which failed in that specific production system  and we are unable to find out it's details using DSPJOB, WRKJOB command etc. ? ) then using same SQL Query how can we reduce it's execution time also I hope the SQL Query which I shared should be able to find out details of  any job( irrespective of it's status active or not active and irrespective of the time when it had failed or completed successfully etc.) in the system (please correct me if my understanding is wrong here)

    but when I try to execute the same it seems to be taking too much time( just noticed when my select SQL query for that particular job runs it starts increasing the CPU%(Checked through WRKACTJOB for that SQL Qry when it started running) , could it cause performance issue( or any record lock or may cause any other job to go into 'MSGW'  etc. in the production system , if this Select SQL Query keeps running for a longer period of time and we keep on waiting it to complete and to  show us the details of the desired job's details here?) 

    Thanks much..


    Original Message


  • 4.  RE: SQL query to find a specific job

    Posted Wed August 07, 2024 04:16 PM
    OK - the job name is only the 10 character name like "QPADEV01234" - what you mean is the qualified(!) job name, which might be "012345/USER/QPADEV01234". There could be multiple jobs with the same job name - but not with the qualified job name.

    But if you search for an already ended job, you need the JOB_INFO function.

    Now to the difference between "filtering" by parameters and selecting rows from the result set. By using more specific filters you restrict the amount of data, that the function has to prepare (by using system APIs) - and this will reduce the amount of time, that the function needs.

    So in your case - you can reduce the runtime by giving filter criteria to the function:

    SELECT *                        
    FROM TABLE(QSYS2.JOB_INFO(      
    JOB_STATUS_FILTER => '*OUTQ',    
    JOB_TYPE_FILTER => '*BATCH',      
    JOB_SUBSYSTEM_FILTER => 'YOURSBS', 
    JOB_USER_FILTER => 'USERNAME'
    ))
    WHERE JOB_NAME_SHORT = 'JOBNAME'

    This way, you reduce the amount of data to be prepared by selecting, that only ended batch jobs from a specific subsystem and a specific user should be collected. 

    The WHERE only selects the jobs with the right name - without LIKE (which is not really fast). 

    So try it out. 

    HTH 
    Daniel



    Original Message


  • 5.  RE: SQL query to find a specific job

    Posted Thu August 08, 2024 08:24 AM

    it does not seem clear what you want to glean from that data.

    you might want to look into job accounting:
    Setting Up Job Accounting (ibm.com)

    -and-

    jobacc53.pdf (ibm.com)

    if all you need is job start/end dates and times you can get from the qsys2.history_log_info  CPF1124/CPF1164



    ------------------------------
    Bryan Dietz
    ------------------------------

    Original Message


Related Content