We have a requirement to develop a SQL query that will select Objects executed over a certain time period, display their statuses, and display the Users who executed those Objects.
The Query below works fine, except for one point. The User field represents the User who manually executed the Object, but it also lists Objects that were automatically executed by the Schedule. in the latter case, the User is the last person who saved a change to the Schedule Object. So, this is not very helpful.
How could the query be modified to differentiate between Users who manually executed the Object, as apposed to those Objects that were automatically executed by a Schedule?
SELECT UNIQUE ah_name as "Object Name", ah_RetCode as "Return Code",
CAST((FROM_TZ(CAST(ah_timestamp2 AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Central') AS DATE) as "Start Date Time",
CAST((FROM_TZ(CAST(ah_timestamp4 AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Central') AS DATE) as "End Date Time",
usr_FirstName as "First Name", usr_LastName as "Last Name"
FROM uc4.ah, uc4.oh, uc4.usr
WHERE USR_OH_Idnr = AH_USR_Idnr
AND AH_OH_Idnr = OH_Idnr
AND ah_timestamp2 < SYSDATE -1
AND ah_timestamp2 > SYSDATE -2
AND ah_oh_idnr IN (SELECT oh_idnr
WHERE oh_otype in ('JOBP','JOBS','EVNT','SCRI','JOBF'))
ORDER BY ah_name ;
I would suggest you to try DB Reporting tool. It'll be easier for you.
Check some related posts in this community will also help you to understand about this tool.
Thanks, I'll try it!
I use the DB Reporting tool whenever I can. SQL is always subject to breakage with the next product upgrade.
Looking at your SQL problem however, the ah table has an attribute of ah_stype that tells you what type of object your object was initiated from.
However this is a recursive problem. As an example, your object could be initiated from a JOBP that could be initiated from a JOBP that could be initiated from a JOBP that could be initiated from a JSCH. To analyze all of these relationships, you'd need to use the attribute AH_ParentAct which gives you the runid of the immediate parent object, and check for JSCH values in ah_stype recursively.
And being Monday morning, it makes my brain hurt to think about how one would code this!
Thanks for the advise. Helpful.