Script to get the job list which is older and not active.

[Shubham Kumar]

Hi All,

Could you please advise on how to create a script that retrieves a list of older and inactive jobs?

I really appreciate any help you can provide.

Thanks,
Shubham Kumar

[Daryl Brown]

Could you clarify what you mean by "older and inactive jobs"?

Are you looking for a SQL query here?  (Because a SEC_SQLI var might be the easiest way to get what you're after.)  The info you're after would be in the AH table, but that's a massive table, so you'll want to filter it in some way.  Can you be more specific about what you're after?

[Siddharth V]

Hi Daryl, 

We are trying to retrieve the objects which are inactive for last one year in all clients. We're using the VARA.SEC_SQLI object to retrieve the data. Please help us with the script.

Thanks,

Siddharth

[Bernhard Flegel]

Hello Shubham,
here is a oracle sql-statement which lists all jobs with the date of the last run and creationdate older then 20240101... maybe it is a beginning for you:

alter SESSION set NLS_DATE_FORMAT = 'YYYYMMDD' ;


select OH_Client, oh_otype, OH_Name, OH_Crdate, OH_Moddate,
(select MAX(AH_TImestamp4) from AH where AH_OH_Idnr = OH_idnr) as LastRun
from OH
where OH_Deleteflag = 0
and OH_Idnr > 100000
-- objecttype
and oh_otype = 'JOBS'
-- object-creationdate
and oh_crdate < '20240101'
order by 1,6,3 asc;

Best regards

Bernhard

[Siddharth V]

Hi Bernhard, 

We need the data for last one year containing all inactive jobs present in all client.

select OH_Client, oh_otype, OH_Name, OH_Crdate, OH_Moddate,

(select MAX(AH_TImestamp4) from AH where AH_OH_Idnr = OH_idnr) as LastRun, 

(select EH_Status from EH where EH_OH_Idnr = OH_idnr ) as ObjectStatus

from OH

where OH_Deleteflag = 0

and OH_Idnr > 100000

and EH_Status = 1900 ---- Here we are trying to print the status of job which are active or ended ok.

and oh_otype = 'JOBS'

order by 1,6,3 asc

Please modify the script as per your knowledge, so that we can get the data only for the inactive jobs. 

Thanks,

Siddharth

[Daryl Brown]

The EH table lists objects that are still active / not deactivated, so you'll need to start with a query from the AH table (all statistics) and remove those entries that exist in the EH table (active statistics).

Try this:

-- columns = client #, object name, object type, run#, start time (UTC), end time (UTC)
select OH_Client, OH_Name, oh_otype, ah_idnr, ah_timestamp12 ah_timestamp4
from OH,AH
where oh_idnr=ah_oh_idnr
and ah_idnr not in (select eh_ah_idnr from eh)
and oh_otype = 'JOBS'
and ah_timestamp4 < '20240101'
order by 1,3,6

You'll want to modify the date part of the query as needed.

[Marilyn Singley]

I use this SQL query to list jobs / JOBPs that have never run.

You'll want to modify the Client part of the query. 

select oh_client, oh_name, oh_otype

from oh

where not exists (select 1

                  from ah

where ah_oh_idnr = oh_idnr)

and oh_otype in ('JOBS', 'JOBP')

and oh_deleteflag = 0

and oh_client = 300