Creating a JOBS to verify if SAP job are in failure

[stefano giambaconi]

Hello,

I would like to have a little help to realize a JOBS (or JOBP) that need to:

connect to SAP and verify if all the SAP job named for example SD*, executed during the night (01.00 to 06.00) terminated in abort, or cancel or any failure state (probably the check you can do in SAp directly with the SM37 SAP transaction)


Do you think it is possible to realize something similar to this requirement ?

Thank you very much in advance

Stefano Giambaconi

[Arun Verma]

Hi Stefano,

I believe for this particular verification you will need to query the UC4 database for the SAP job type based on their failure status with a DB VARA, you can get the oh_name of jobs failed within a certain window.
I will look into this and see if I can replicate this and will get back to you!

Arun Verma

[Pete Wirfs]

Maybe something like this?

We are on SQLServer.

select oh_name as Name
     , ah_alias as Alias
	 , ah_idnr as RunID
	 --, convert(datetime, ah_timestamp1 AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time') as Acivation
     , convert(datetime, ah_timestamp2 AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time') as Start_Time
	 , convert(datetime, ah_timestamp4 AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time') as end_time
     , ah_runTime as Duration_Seconds
	 , ah_client as client
     , ah_Hostdst as Host
     , ah_queue as Queue
     , oh_otype as Type
     , ah_status Status
	 , (select Y.oh_name from uc4.dbo.oh Y where y.oh_idnr = b.ah_usr_idnr) as Uc4User
from uc4.dbo.oh A
   , uc4.dbo.ah B
where oh_idnr = ah_oh_idnr
and oh_name like 'SD%'  
and ah_status <> 1900 -- filters out succesful completions
and not oh_otype in('JOBP', 'HOST', 'SYNC', 'JSCH', 'SERV', 'CLNT', 'QUEUE', 'USER')
and convert(datetime, ah_timestamp4 AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time') > '2022-01-10 01:00:00.000' --FROM
and convert(datetime, ah_timestamp2 AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time') < '2022-01-10 06:59:59.999' --TO
order by 4 desc

------------------------------
Pete Wirfs
SAIF Corporation
Salem Oregon USA
------------------------------

[Arun Verma]

Hi Pete,

Yes excellent, this can be adapted and depending on the database type used by Stefano it cab be modified based on the requirement.


Arun Verma

[Peter Grundler]

Hi Stefano,

there are some options available:

1. use the DB.Reporting tool to list all the jobs named "SD*" (not sure if it's possible to limit by time but definitely by days)
2. use SM37/R3_GET_JOBS
3. use an SQL query (already posted by Pete)

Because you have posted "connect to SAP and verify if all the SAP job named ..." option (2) is best because only this method connects to the SAP target system.

regards,
Peter