1. Why multiple versions of jobs are managed even when jobID is given in a query.
2. Is Multiple versions contributing to db space usage?
3. When AutoSys utilities are working fine here why not our customer sql queries not working as expected?
4. Doesn’t DBMaint clear multiple versions?
Workload Automation AE (AutoSys)
If you create a new job one row is created in the ujo_job table with
job_ver=1,is_active=1,is_currver=1
If you then update that job, the existing row becomes
job_ver=1,is_active=0,is_currver=0
and you get a new row with
job_ver=2,is_active=1,is_currver=1
If you update the job again, the original row remains the same, the second one changes to 0 for is_active and is_currver and you get a new third row for the new job definition
job_ver=1,is_active=0,is_currver=0
job_ver=2,is_active=0,is_currver=0
job_ver=3,is_active=1,is_currver=1
This is the way the product is coded/designed to function so if needed clients could go back and see which job definition version executed during a specific run.
(job_ver exists in ujo_job and ujo_job_runs).
The different job versions remain in the ujo_job table until ALL the runs and events associated with that joid + job_ver are purged from the db.
Only then will the older job definitions be archived via archive_jobs which is run via DBMaint.
NOTE - if you disable DBMaint and do not manually run or schedule archive_jobs to run, the older job definitions will remain in the db forever.
Those rows do factor into how much space the autosys tables consume in the db.
For more details about archive_jobs see:
https://techdocs.broadcom.com/us/en/ca-enterprise-software/intelligent-automation/autosys-workload-automation/12-0-01/scheduling/ae-scheduling/manage-your-jobs/delete-obsolete-job-versions.html
https://techdocs.broadcom.com/us/en/ca-enterprise-software/intelligent-automation/autosys-workload-automation/12-0-01/reference/ae-commands/maintain-system/archive-jobs-command-delete-obsolete-job-versions.html
https://techdocs.broadcom.com/us/en/ca-enterprise-software/intelligent-automation/autosys-workload-automation/12-0-01/troubleshooting/troubleshooting-ae/when-delete-job-is-run-via-jil-the-job-is-still-in-the-ujo-job-table-in-the-database-is-this-expected.html
Be aware there is a fix for archive_jobs in cum1 for 11.3.6 sp8.
https://techdocs.broadcom.com/us/en/ca-enterprise-software/intelligent-automation/workload-automation-ae-and-workload-control-center/11-3-6-SP8/release-notes/ae-release-notes/ae-release-11-3-6-sp8/Fixed-Issues---11_3_6-SP8-CUM1.html
ARCHIVE_JOBS DOES NOT REMOVE JOB DEFINITIONS
Problem Number: 4804
When a cross-instance job has same joid as a job in main instance and the job in main instance is deleted but the events from the cross-instance job continue to come in, then the archive_jobs does not remove contents of the deleted job from main instance thinking that the job still has events in the ujo_proc_event table.