Why are there multiple rows for the same joid and job_name in the ujo_job table?

Products

• Autosys Workload Automation

Issue/Introduction

When querying the ujo_job table for a list of joid's and the job_name associated with them, multiple rows are returned for the same joid/job_name pairs. Since it is not possible to duplicate a job name, why would there be multiple rows in ujo_job for the same job?

Environment

Release: 11.3 and higher

Resolution

AutoSys contains functionality for auditing purposes called job versioning. When a job is updated, a new row for that job is added to the ujo_job table with a different job version number assigned to it. The original row is retained for a default period of 7 days before it is archived by the archive_jobs command run by DBMaint. Therefore, it is normal for a query to report multiple rows for the same joid and job_name if the query is not including some filtering for job versions.

There are three columns in the ujo_job table that can be included in your where clause to filter out older versions of a job...

job_ver - the job version number represented by the ujo_job row

is_currver - flag to indicate whether the row represents the latest version of the job (will be 1 or 0)

is_active - flag to indicate whether the row represents a job that is active. This will be 0 for job versions that are not current.

When you insert a new job, this is an example of what those fields would look like...

joid    job_name    job_ver  is_currver  is_active

==================================================

129 test_jobver   1       1          1

Since it is a new job, the job_ver is 1, is_currver is 1 because it is the latest version, and is_active is 1 because the row reprents the version of the job that will run if executed.

If the job is updated, you would then see these column values...

joid    job_name    job_ver  is_currver  is_active

==================================================

129 test_jobver   1       0          0

129 test_jobver   2       1          1

The original row for job_ver 1 now has is_currver and is_active set to 0 because it is no longer the current version and is not the version that will run if executed. A new row is added with the same joid and job_name values, but now has a job_ver value of 2. The is_currver and is_active flags are set to 1 because this row is now the latest and active version. The original row will go away after it is archived by the archive_jobs command.