Hi All,
I am trying to extract the job dependency list from sequence job by using the below query in universe stage, but i am getting the below error.
Query:
SELECT DS_JOBS.NAME, EVAL DS_JOBOBJECTS.'@RECORD<31>' FMT '32L' COL.HDG 'DEPENDENT_JOBS'
FROM DS_JOBS, DS_JOBOBJECTS
WHERE DS_JOBS.NAME = '<<SequenceName>>'
AND DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO
AND DS_JOBOBJECTS.OBJTYPE = 'J'
AND DS_JOBOBJECTS.OBJNAME = 'ROOT'
AND DS_JOBS.JOBTYPEIND = '2';
Error: syntax error. Unexpected verb. Token was "SELECT".
Scanned command was SELECT
Can you please help me how to overcome this error?
Thanks,
ureddy
Job dependency report
Moderators: chulett, rschirm, roy
I declared 2 columns and then added a Job Parameter SequenceName and then edited your SELECT to read "SELECT DS_JOBS.NAME, DS_JOBOBJECTS.NAME FROM DS_JOBS, DS_JOBOBJECTS WHERE DS_JOBS.NAME = '#SequenceName#' AND DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO AND DS_JOBOBJECTS.OBJTYPE = 'J' AND DS_JOBOBJECTS.OBJNAME = 'ROOT' AND DS_JOBS.JOBTYPEIND = '2'; "
(all on one line).
(all on one line).
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Its working after writing the query on one line. Thanks ArndW
I cannot list all the dependent jobs from the master sequence,few jobs are not listing.
Master_seq
|
JobA
|
JobB
/ \
JobC JobD
|
JobE
Note: JobB to JobC and JobD
Except JobD I am getting all the jobs in dependency list. I checked the JobD and is in compiled state, but still its missing in the report.
I could see all the jobs(JobA,JobB,JobC,JobE and JobF) in JobProperties->Dependency for Master_seq job except JobD.
Any idea why the job is missing from the report?
Thanks in advance.
Regards,
ureddy
I cannot list all the dependent jobs from the master sequence,few jobs are not listing.
Master_seq
|
JobA
|
JobB
/ \
JobC JobD
|
JobE
Note: JobB to JobC and JobD
Except JobD I am getting all the jobs in dependency list. I checked the JobD and is in compiled state, but still its missing in the report.
I could see all the jobs(JobA,JobB,JobC,JobE and JobF) in JobProperties->Dependency for Master_seq job except JobD.
Any idea why the job is missing from the report?
Thanks in advance.
Regards,
ureddy
Reddy
Field 31 is a multivalue one which is expanded correctly when I perform this query manually in my environment and all elements are returned. In your case somehow the first and last elements are skipped. Is it the same behaviour if you choose another job sequence?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
If you are running this through a job then you can use this routine to extract the real dependencies.
Code: Select all
* -----------------------------------------------------------------
* EtlStatsGetDependJobs(SeqJobName, LeaveInvocationID)
* Decription: Returns Jobs run by SeqJobName.
* Written by: Kim Duke
* Notes:
* -----------------------------------------------------------------
Ans = ''
RoutineName = "EtlStatsGetDependJobs"
Usage = RoutineName:'(SeqJobName)'
* -----------------------------------------------------------------
* initialize variables
* -----------------------------------------------------------------
True = 1
False = 0
Other = True
Forever = True
Today = oconv(@DATE, "D4-")
ErrMsg = ''
NullRec = ''
CrLf = char(13):char(10)
JobList = ''
Cnt = 0
* -----------------------------------------------------------------
DebugSw = @FALSE
* LeaveInvocationID = @TRUE
FileName = 'DS_JOBS'
open FileName to FilePtr else
ErrMsg = 'Can not open ':FileName:' file.'
goto ErrRtn
end
DsJobsPtr = FilePtr
* -----------------------------------------------------------------
FileName = 'DS_JOBOBJECTS'
open FileName to FilePtr else
ErrMsg = 'Can not open ':FileName:' file.'
goto ErrRtn
end
DsJobObjectsPtr = FilePtr
* ----------------------------------------------------------------------
dummy = ""
TheSeqJobName = field(SeqJobName, '.', 1)
read JobRec From DsJobsPtr, TheSeqJobName else
ErrMsg = 'No JOB: ':TheSeqJobName
goto ErrRtn
end
JobNo = JobRec<5>
* ----------------------------------------------------------------------
Id = 'J\':JobNo:'\ROOT'
if dummy = "X" then goto TheEnd
* ----------------------------------------------------------------------
read DsObjectsRec From DsJobObjectsPtr, Id else
ErrMsg = Id:' not found in ':TheSeqJobName:' DS_JOBOBJECTS ':JobNo
goto ErrRtn
end
read JobRec From DsJobsPtr, TheSeqJobName then
JobNo = JobRec<5>
ObjId = 'J\':JobNo:'\ROOT'
read ObjRec From DsJobObjectsPtr, ObjId then
SourceCode = convert(CrLf, @FM, ObjRec<11>)
Occurrence = 1
AttrNo = 1
MvNo = 1
SvNo = 1
* ----------------------------------------------------------------------
* Looking for:
* jb$V0S0 = "Affiliate_03_E_AS400"
* h$V0S0 = DSAttachJob(jb$V0S0, DSJ.ERRNONE)
* ----------------------------------------------------------------------
FindJobs:
FindStr " = DSAttachJob(" In SourceCode, Occurrence Setting AttrNo, MvNo, SvNo Then
Cnt += 1
* ----------------------------------------------------------------------
* Backup 1 line:
* jb$V0S0 = "Affiliate_03_E_AS400"
* ----------------------------------------------------------------------
DependJob = SourceCode<AttrNo - 1, 1, 1>
* ----------------------------------------------------------------------
* Strip off junk:
* "Affiliate_03_E_AS400"
* ----------------------------------------------------------------------
DependJob = field(DependJob, "=", 2)
DependJob = trim(DependJob)
DependJob = convert('"', '', DependJob)
* ----------------------------------------------------------------------
* Strip off junk:
* ETL_Checkpoint:'.':(CheckpointInvocationIDBegin)
* ----------------------------------------------------------------------
if LeaveInvocationID then
DependJob = convert("':()", '', DependJob)
end else
* ----------------------------------------------------------------------
* Strip off InvocationID:
* ETL_Checkpoint:'.':(CheckpointInvocationIDBegin)
* ----------------------------------------------------------------------
DependJob = field(DependJob, ':', 1)
end
JobList<Cnt> = DependJob
Occurrence += 1
goto FindJobs
end
if Cnt > 0 then Ans = JobList
end
end
ExitRtn:
goto TheEnd
* ----------------------------------------------------------------------
ErrRtn:
if DebugSw then print
if DebugSw then print ErrMsg
if DebugSw then print
* Ans = ErrMsg
* ----------------------------------------------------------------------
TheEnd:
if DebugSw then print
if DebugSw then print
if DebugSw then print Ans
if DebugSw then print
Mamu Kim
-
- Participant
- Posts: 45
- Joined: Sat May 08, 2010 11:07 pm
- Location: banglore
This is in response to an message sent to me.
When you use designer to build a sequence it will build out a dependency field in the ROOT record. You can see what is stored in this field in the job properties page. You can edit this field and make it so it is not accurate. Why someone would do that, I do not know. The first post will will work most if time unless someone has manually changed the dependent items under job properties.
The code I posted below will look through the code generated by the Designer. You can see this code under the code tab in job properties. The FindStr will look for any string within another string. Kind of like the index function. It is looking for DSAttachJob because that is when it runs another job from this sequence. All you need to is turn this into a DataStage routine. Test it. Make sure it works. Then write you a Server job which loops through all the jobs and writes this list out to a table with sequence and depend job. Once in a table you can write any report you need.
Otherwise you can take the SELECT in the first post and remove the ";" and run it. This SELECT is NOT a SQL select therefore does not need a ";" on the end.
Cut and paste the code above into a DataStage routine. Give it the 2 arguments listed in the comments. Test it. Posts results. If you need more help then let us know.
There is a job in EtlStats which does all this. You can try to load it and run it. The job name has DEPEND in it. There are lots of jobs in EtlStats which pull this kind of metadata. Some will no longer work because EtlStats was written 10 years ago. But it should help you get started.
When you use designer to build a sequence it will build out a dependency field in the ROOT record. You can see what is stored in this field in the job properties page. You can edit this field and make it so it is not accurate. Why someone would do that, I do not know. The first post will will work most if time unless someone has manually changed the dependent items under job properties.
The code I posted below will look through the code generated by the Designer. You can see this code under the code tab in job properties. The FindStr will look for any string within another string. Kind of like the index function. It is looking for DSAttachJob because that is when it runs another job from this sequence. All you need to is turn this into a DataStage routine. Test it. Make sure it works. Then write you a Server job which loops through all the jobs and writes this list out to a table with sequence and depend job. Once in a table you can write any report you need.
Otherwise you can take the SELECT in the first post and remove the ";" and run it. This SELECT is NOT a SQL select therefore does not need a ";" on the end.
Cut and paste the code above into a DataStage routine. Give it the 2 arguments listed in the comments. Test it. Posts results. If you need more help then let us know.
There is a job in EtlStats which does all this. You can try to load it and run it. The job name has DEPEND in it. There are lots of jobs in EtlStats which pull this kind of metadata. Some will no longer work because EtlStats was written 10 years ago. But it should help you get started.
Mamu Kim