struggling with the issue SMGR Service name vs. AE process name described here:
I discovered a workaround lately -- unfortunately its only a half workaround....
In DB Table HOST the field HOST_SMDISPLAYNAME holds the SMGR Service name entry for the agent or WP and CPe.g. UC4_WP1, UC4_WP3!
so the SQL for identifying SMGR Service Names AND AE process names would be:
select HOST_SMDISPLAYNAME,ah_processid,ah_info,HOST.HOST_HOSTATTRTYPE from ah, HOSTwhere ah_otype = 'SERV'and ah_timestamp4 is null--disable line below if you want CPs in result--and ah_info is not nulland ah_oh_idnr = host_oh_idnr
Limitations:Unfortunately#1 for JPWs the field HOST_SMDISPLAYNAME is empty.Unfortunately#2 for JPWs the field AH_PROCESSID does not have leading zeros to the Process number, all other types do haveleading zeros.Unfortunately#3 for CPs the field AH_INFO is empty.
hope it helps.
Thanks for the suggestion. I tried at my end, and...
Unfortunately#1 for JPWs the field HOST_SMDISPLAYNAME is empty.
I do not have this issue under 12.2 w/ PostGre. Maybe it's version-related ?
Unfortunately#2 for JPWs the field AH_PROCESSID does not have leading zeros to the Process number, all other types do haveleading zeros.
You can overcome this with LPAD - format the output so it also contains leading zeros.
Unfortunately#3 for CPs the field AH_INFO is empty.
True, but AH_Name on the other hand, contains the desired information.
I did a few adjustments to your query:
SELECT HOST_SMDISPLAYNAME,AH_Name, HOST.HOST_HOSTATTRTYPE, lpad (AH_ProcessID, 10,'0') PROCESS_IDfrom dbo.ah, dbo.HOSTwhere ah_otype = 'SERV'and ah_timestamp4 is nulland ah_oh_idnr = host_oh_idnr;
And here are the results :
Tested on PostGre, the syntax might be different on MS SQL / DB2 / Oracle.
Hope this helps.