Query To Find a Job with a search string

[Warren Tolentino]

i know that this can be done by using query to find a job with a search string. i need to find a job that that has this 'PERFEVAL.txt' as the keyword on the query. basically the job can be a normal job or FTP or SFTP job. we had hundreds of jobs and i attempted to look for each one manually and could not find them maybe i might missed. i know how to construct a query however because we do not have access to the tidal database i can't see what are the tables and columns to look for. please help what is the query to (tables and column) to find a job that will look for a particular string. it may be in the command, command parameter, filename, new filename, path, remote path, or notes. i send this query to our database group to run it for me.

thank you,

warren

[Marc Clasby]

try this I added what the table columns are used as from a definition window in case you need to search for other criteria

Marc

remember job groups are type 1 and jos are type 2 but FTP = 6

SELECT

dbo.jobmst.jobmst_id, dbo.jobmst.jobmst_prntname, dbo.jobmst.jobmst_name, dbo.jobmst.jobmst_type, dbo.jobdtl.jobdtl_cmd AS LOCAL_DIR, dbo.jobdtl.jobdtl_envfile as REMOTE_DIR,

dbo

.jobdtl.jobdtl_extinfo AS FTP_PROTOCOL, dbo.jobdtl.jobdtl_workdir AS QUOTE, dbo.jobdtl.jobdtl_params AS FTP_CMDS

FROM

dbo.jobmst INNER

JOIN

dbo

.jobdtl ON dbo.jobmst.jobdtl_id = dbo.jobdtl.jobdtl_id

WHERE

(dbo.jobmst.jobmst_type > 2) AND jobdtl_params like

'%PERFEVAL.txt%'

[Warren Tolentino]

thank you. does the column jobdtl_params covers the items filename and new filename in the FTP job; and the command and command parameters in normal job including the notes tab?

[Marc Clasby · Accepted solution]

Parameters shoudl return several FTP related components deopending on selections

but original name PERFEVAL.txt and new name PERFEVAL_NEW.TX woudl be in there\

example:

0,"","PERFEVAL.txt","PERFEVAL_NEW.TXT",N,N,N

Add jobmst_desc and jobmst_runbook for notes

SELECT

dbo.jobmst.jobmst_id, dbo.jobmst.jobmst_prntname, dbo.jobmst.jobmst_name, dbo.jobmst.jobmst_type, dbo.jobdtl.jobdtl_cmd AS LOCAL_DIR,

dbo

.jobdtl.jobdtl_envfile AS REMOTE_DIR, dbo.jobdtl.jobdtl_extinfo AS FTP_PROTOCOL, dbo.jobdtl.jobdtl_workdir AS QUOTE,

dbo

.jobdtl.jobdtl_params AS FTP_CMDS, dbo.jobmst.jobmst_runbook AS Runbook, dbo.jobmst.jobmst_desc AS Notes

FROM

dbo.jobmst INNER

JOIN

dbo

.jobdtl ON dbo.jobmst.jobdtl_id = dbo.jobdtl.jobdtl_id

WHERE

(dbo.jobmst.jobmst_type > 2) AND (dbo.jobdtl.jobdtl_params LIKE '%PERFEVAL.txt%'

)

[Warren Tolentino]

does the jobdtl_params column represents all of the items in the job definition window? thanks.

[Warren Tolentino]

thank you that helps.