IBM i Global

IBM i 

A space for professionals working with IBM’s integrated OS for Power systems to exchange ideas, ask questions, and share expertise on topics like RPG and COBOL development, application modernization, open source integration, system administration, and business continuity.


#Power


#IBMi
#Power
#Operatingsystems
#Servers
 View Only

  • 1.  SQL query to find a specific job

    Posted 08/07/24 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 08/07/24 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





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

    Posted 08/07/24 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 08/07/24 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 08/08/24 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
    ------------------------------



Related Content