This may or may not be helpful, but I've found it quite useful to extract information from the XML files that Workload Automation exports using the imexutil.
These XML files have all the information about the applications and jobs. After loading the XML into an xml variable you can use the select-xml command to pull just about any information you want out of the application.
Example using PowerShell:
[xml]$xmlData = Get-Content -Path 'D:\ACP010'
Select-Xml -Xml $xmlData -XPath "//ns:script_definition" -Namespace @{ns="http://dto.wa.ca.com/application"} | foreach {write-host -ForegroundColor Green $_.Node.ParentNode.ParentNode.ParentNode.ParentNode.Name -NoNewline; Write-Host "`t`t`t`t " $_.node.innertext "`n"}
I load the application ACP010 into an xml variable called $xmlData
Since all inline Javascript is stored in XML tags called <script_definition> I can specify that in the -XPath argument, then pipe that to a foreach command that prints the job name, a few tabs, and the content of the <script_definition> element (the actual JavaScript).
The output looks like this:
I can see that this application has two jobs with inline JavaScript; ACPDLYR and ACPANNR, and I can see the content of those scripts.
Similar reports can be done with anything you are looking for in your applications. It's not as direct as a SQL query, but you can inspect the XML at will to determine what you are looking for, and there is a certain comfort (for me) knowing that everything I'm doing is offline from the Workload Automation database. I use PowerShell, but this could just as easily be done with Perl.
I can provide more examples and explanation if you want.
------------------------------
Andy Reimer
------------------------------
Original Message:
Sent: 06-05-2020 07:08 AM
From: Kenneth Chrapczynski
Subject: CAWA DE DB Schema
Hi Ravi,
Are there tables specific to FTP and ORACLE job definitions? Like the columns/information I can extract from ESP_GENERIC_JOB?
I'm specifically looking for Remote File Name, Local File Name, Agent , Server Address. And for ORACLE jobs I'm looking for OA Application Name, Program Name and the OA Parameters (User Name, Responsibility, Program Arguments).
I would like to create a queries similar to this, but for FTP information, and ORACLE Job information.
SELECT A.[APPL_NAME]
,A.[EVENT_NAME]
,B.[JOB_NAME]
,B.[JOB_QUALIFIER]
,B.[START_DATE_TIME]
,B.[END_DATE_TIME]
,A.[STATE]
FROM [CAWorkload_Prod].[dbo].[ESP_APPLICATION] A inner join [CAWorkload_Prod].[dbo].[ESP_GENERIC_JOB] B on A.APPL_ID = B.APPL_ID
WHERE A.APPL_NAME LIKE 'PS_%%' AND A.[STATE] LIKE 'Complete' AND B.[START_DATE_TIME]>'1/23/2020 15:00 PM' AND b.END_DATE_TIME<'1/24/2020 07:00 AM' ORDER by START_DATE_TIME asc
;
Original Message:
Sent: 06-05-2020 01:30 AM
From: KUNDURI Kiran
Subject: CAWA DE DB Schema
Hi,
All jobs in an application are stored as xmls in ESP_AMS_DF table.
Ravi Kiran
Original Message:
Sent: 06-04-2020 07:24 AM
From: Kenneth Chrapczynski
Subject: CAWA DE DB Schema
Does anyone know which Database Schema's/Tables FTP job information, and ORACLE Financial job information are stored?