How the GEN job tables connect

, ,

The GEN job tables provide detailed information about job execution, allowing for tracking and troubleshooting. Each table serves a specific function in recording various aspects of the job lifecycle, from submission through execution to completion. This structure ensures clear mapping of relationships and dependencies among the tables, which is essential for monitoring and managing batch processes efficiently allowing admins to be able to trace job progress and diagnose issues at both macro and micro levels.

🔹 Job Table Relationships

JOBQUEUE (one row per submitted job)

|

|–< JOBSTEP (one or more steps per job)

|       |

|       |–< JOBSTEPLOG (messages for each step)

|

|–< JOBLOG (high-level messages/logs for the job)

|

|–< JOBPARM (parameters used when submitting job)

🔹 GEN tables related to jobs:

JOBQUEUE.JobQueue = primary key (ties everything together).

JOBSTEP.JobQueue → foreign key to JOBQUEUE.

JOBLOG.JobQueue → foreign key to JOBQUEUE.

JOBPARM.JobQueue → foreign key to JOBQUEUE.

Each job in the system is uniquely identified by the JobQueue value, which acts as the primary key and links related records across all main tables. By using foreign keys, the JOBSTEP, JOBLOG, and JOBPARM tables maintain referential integrity, allowing for efficient tracking and management of job execution details and associated parameters.

To retrieve comprehensive job execution data, a query can be constructed that joins the JOBQUEUE table with JOBSTEP, JOBLOG, and JOBPARM using the JobQueue field. This ensures all relevant information, such as job parameters, execution steps, and log messages, can be efficiently accessed and analyzed for each job instance.

Here is an example of a query to find who ran the GL199 with any messages found in the joblog. The results will show

  • Who ran it → UserName.
  • When it ran → StartDate + StartTime.
  • Job status → Status (Success, Error, etc.).
  • Parameters used → from JOBPARM.
  • Messages → from JOBLOG.

 

SELECT     JQ.JobName,    JQ.Program,    JQ.UserName,    JQ.StartDate,    JQ.StartTime,    JQ.EndDate,    JQ.EndTime,    JQ.Status,    JP.ParameterName,    JP.ParameterValue,    JL.Message

FROM GEN.dbo.JOBQUEUE JQ

LEFT JOIN GEN.dbo.JOBPARM JP ON JQ.JobQueue = JP.JobQueue

LEFT JOIN GEN.dbo.JOBLOG JL ON JQ.JobQueue = JL.JobQueue

WHERE JQ.Program = ‘GL199’

ORDER BY JQ.StartDate DESC, JQ.StartTime DESC;