Is there a query that can list all jobs together with the job details such as command executed, schedule, owner, etc?
The information you are looking for is stored in the ESP_GENERIC_JOB table. See link below for more details.
Database Schema - CA Workload Automation DE - 12.0 - CA Technologies Documentation
DE DATABASE SCHEMA:
Thanks for the information. This is really very helpful.
The tables mentioned are for jobs that have executed/active, is there an equivalent table for those applications/jobs that are uploaded and not necessarily just those that have executed or still active? Like jibs that only runs every two months, or certain dates?
ESP_GENERIC_JOB table will hold jobs that have run or running. If you are looking for all the jobs in the DE, you will have to export ESP_AMS_DF table and parse through the information.
DSeries stores application definition in ESP_AMS_DF table's DEFINITION field as a CLOB. However, the data stored in this table is not for the public's consumption and caution should be used. I would advice that you open an enhancement request for this functionality. Others have requested similar information.
where ESP_AMS_DF.ARTIFACTTYPE = 'APPLICATION'
Retrieving data ...