How can I determine which job starts a Tidal jobstream run when it isn't clear from the gantt chart?

Products

• Automic Automation Intelligence

Issue/Introduction

Tidal jobstream runs will begin with the first job that runs within that jobstream.

If you have the parameter client.allowGroupStartJobstreamRun set to True which is the default setting, it will start with the first job regardless of whether it is a Group job or not.

With client.allowGroupStartJobstreamRun set to false, a jobstream run will start with the first non  Group job that runs.

If there are many nested groups, and you have the parameter above set to false, it may be difficult to determine which job is the first job that triggers to jobstream run to start.

Environment

Release : 6.1.0

Component : AUTOMIC AUTOMATION INTELLIGENCE ENGINE

Resolution

For a SQL server database you can use a query like below, by specifying the jobstream name where indicated, and the timestamp in milliseconds for jobs that started after and before a certain time frame.

 

You can convert a regular date and time into an epoch milliseconds using this website: https://www.epochconverter.com/

selectCONVERT(DATETIME,jsr.creationTime/86400000.0)+'1970' as "Jobstream RUN Start time in GMT",

CONVERT(DATETIME,jr.startTime/86400000.0)+'1970' as "Job run Start Time in GMT", jj2.jobTypeId,jr.startTime, jj2.jobName, jr.*, jsr.*

from JawsJob jj, JobStream js, JobStreamRun jsr, JobRun jr, JawsJob jj2, JobStreamRun_JobRun jsrjr

where js.description = '<Jobstream Name>'

and jj.jobId = js.targetJobId

and js.jobStreamId = jsr.jobStreamId

and jsr.creationTime > <Start timestamp in miliseconds>

and jsr.creationTime < <End timestamp in miliseconds>

and jsr.jobStreamRunId = jsrjr.jobStreamRunId

and jsrjr.jobRunId = jr.jobRunId

and jr.jobId = jj2.jobId

order by jsr.creationTime, jr.startTime;

The first column will be the time the jobstream started, in GMT so for EDT it is offset by 4 hours from what you will see in the Gantt chart like below

The "Job run Start Time in GMT" column will be the time each individual job runs in GMT.

The jobTypId column will show you if it is a Tidal_Group, Tidal_Job, Tidal_File, or other job type.

When you find the first non Tidal_group job, that should be the jobs that starts the jobstream run.

 

If that job is Tidal_file, you will not see this in the standard Gantt view, however you can see them if you click the grey database looking icon at the top left of the chart like below:

If you select a file and right click on it you will be able to select the file details like below:

If you select the "Events" tab you will see the time the File was created. 

Below is a query you could run for the 12/21 date which was another Monday where you saw the two jobstream runs.

The example query below would be for a jobstream named "jobstream1", and would show all job runs within that jobstream that started between 10/5/2020 at midnight and 10/6/2020.

 

selectCONVERT(DATETIME,jsr.creationTime/86400000.0)+'1970' as "Jobstream RUN Start time in GMT",CONVERT(DATETIME,jr.startTime/86400000.0)+'1970' as "Job run Start Time in GMT", jj2.jobTypeId,jr.startTime, jj2.jobName, jr.*, jsr.*

from JawsJob jj, JobStream js, JobStreamRun jsr, JobRun jr, JawsJob jj2, JobStreamRun_JobRun jsrjr

where js.description = 'jobstream1'

and jj.jobId = js.targetJobId

and js.jobStreamId = jsr.jobStreamId

and jsr.creationTime > 1608526800000

and jsr.creationTime < 1608613200000

and jsr.jobStreamRunId = jsrjr.jobStreamRunId

and jsrjr.jobRunId = jr.jobRunId

and jr.jobId = jj2.jobId

order by jsr.creationTime, jr.startTime;