DB Table which will be store the CA Job/Application definition .

[Sathish Kumar R]

Hi All,

DB Table which will be store the CA Job/Application definition, can someone help me which table in DB will be storing the CA Job and Application definition like Schedule, argument,etc..

Like ESP_Generic_job and ESP_Application are storing the runtime data.

[SHARON SHIMANEK]

application / job definitions are in ESP_AMS_DF and event definitions are in ESP_EVENT_DF, but both of these are CLOBs so not easy to get any information out of them. 

I do simple searches on them when I need to least find out what application something is in. We often have the developer ask and they only info they give us is the job argument, so the simple search helps for narrowing down to the application that string is in.

select * from  WA.esp_ams_DF where WA.esp_ams_DF.definition like '%info you are searching%'    - must have % before and after string you are searching since it is a CLOB

Hope that helps

Sharon

[Ravi Kiran Kunduri]

You can also try jobmap cli command - https://techdocs.broadcom.com/us/en/ca-enterprise-software/intelligent-automation/ca-workload-automation-de/12-4/utilities-and-soap-web-services-functions/using-the-cli/applications-and-jobs-cli-commands/jobmap-command-create-a-job-activity-report.html

[Kenneth Chrapczynski]

Satish,

The "ESP_AMS_DF" has all of the job definitions, but all of it is combined in the DEFINITION column so you will need to cut/paste into Notepad++ and do finds on what you are looking for. When searching a single table you can always SELECT TOP 1000 to get the basic format and then modify the SELECT to display the information you are interested in.  

SELECT [ARTIFACTTYPE]
      ,[IDENTIFIER]
      ,[VERSION]
      ,[DEFINITION]
      ,[MODIFIEDTIME]
      ,[MODIFIEDUSER]
      ,[LOCKTIME]
      ,[LOCKUSER]
      ,[LOCKID]
      ,[VERSION_COMMENTS]
  FROM [database].[dbo].[ESP_AMS_DF]
  where [ARTIFACTTYPE] like 'APPLICATION' AND [database].[dbo].[ESP_AMS_DF].definition like '%info you are searching for%';

Depending on what you are searching for I also do an INNER JOIN on the ESP_APPLICATION and ESP_GENERIC_JOB tables to piece together information about most jobs. The majority of job information I seek is in ESP_GENERIC_JOB. For FTP, ORACLE Financials and Peoplesoft info I do a triple join on different tables (I have examples if you would like them). 

SELECT A.[APPL_NAME]

              ,A.[EVENT_NAME]

              ,A.APPL_GEN_NO

              ,B.[JOB_NAME]

              ,B.[JOB_QUALIFIER]

              ,B.USERID

              ,B.TAG

              ,B.AGENT_NAME

              ,B.COMMAND

              ,B.ARGUMENT

              ,B.[START_DATE_TIME]

              ,B.[END_DATE_TIME]

              ,A.[STATE]

           

  FROM [database].[dbo].[ESP_APPLICATION]  A inner join [database].dbo.ESP_GENERIC_JOB B

  on A.APPL_ID = B.APPL_ID

  WHERE B.[JOB_NAME] LIKE '%search info here%' AND B.[START_DATE_TIME]>'7/29/2024 00:00 AM' AND b.END_DATE_TIME<'8/13/2024 11:30 AM'

  ORDER by A.APPL_GEN_NO asc

  ;