Joining DS_JOBS and DS_JOBOBJECTS does not yield correct set

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Post Reply
111111111111111111
Participant
Posts: 9
Joined: Sat Jul 08, 2017 1:33 am

Joining DS_JOBS and DS_JOBOBJECTS does not yield correct set

Post by 111111111111111111 »

am trying to join DS_JOBS and DS_JobOBJECTS table on JOBNO and OBJID respectively. One of my environment is not yielding the correct number of jobs that it ideally should and the reason is the JOBNO and OBJID are not having the same values.

In Dev I am getting 2500 jobs but in Test I am only getting some handful of them. My question is, Is this expected behavior that For one job I can expect different OBJID and JOBNO in the 2 tables?

My expectation was we get all the jobs of projects since Pk-fK relationship is maintained between the 2 tables.

Select OBJID,JOBNO,...... from DS_JOBS,DS_JOBOBJECTS where DS_JOBS.JOBNO = DS_JOBOBJECTS --> Yields different results in different environments with same jobs

More Specifically: select NAME,DS_JOBOBJECTS.OLETYPE,DS_JOBOBJECTS.OBJIDNO, EVAL DS_JOBOBJECTS."@RECORD<3,1>" from DS_JOBOBJECTS where OLETYPE='CJobDefn'; -- Yields just 250 jobs out of 2500
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

You need to join DS_JOBS.JOBNO to DS_JOBOBJECTS.OBJIDNO, not to OBJID
Last edited by ray.wurlod on Sun Jul 09, 2017 9:32 pm, edited 1 time in total.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Looks like you had a spot of trouble with your "J" key...
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

chulett wrote:Looks like you had a spot of trouble with your "J" key...
Hmm. A downside of touch typing.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
111111111111111111
Participant
Posts: 9
Joined: Sat Jul 08, 2017 1:33 am

Post by 111111111111111111 »

ray.wurlod wrote:You need to join DS_JOBS.JOBNO to DS_JOBOBJECTS.OBJIDNO, not to OBJID ...
Apologies, I was joining on OBJIDNO itself and still not getting the entire list of jobs.

Could the reason be the Update that I made to the post i.e.
select NAME,DS_JOBOBJECTS.OLETYPE,DS_JOBOBJECTS.OBJIDNO, EVAL DS_JOBOBJECTS."@RECORD<3,1>" from DS_JOBOBJECTS where OLETYPE='CJobDefn'; -- Yields just 250 jobs out of 2500
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

I don't see your join condition there. With the join you should not need the constraint on OLETYPE.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
111111111111111111
Participant
Posts: 9
Joined: Sat Jul 08, 2017 1:33 am

Post by 111111111111111111 »

Code: Select all

Select OBJIDNO,JOBNO,DS_JOBS.NAME from DS_JOBS,DS_JOBOBJECTS where DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO
If this does not lists all my jobs (which currently is happening with me), then where should I ask my admin to look into.

I know it should, but it is not, I'll close the post after your reply.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Does a query on DS_JOBS alone list all your jobs?

Code: Select all

SELECT NAME, JOBNO FROM DS_JOBS WHERE NAME NOT LIKE '\\%';
Jobs with no design information (e.g. where you have imported only the executable) will not have any records in DS_JOBOBJECTS.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
111111111111111111
Participant
Posts: 9
Joined: Sat Jul 08, 2017 1:33 am

Post by 111111111111111111 »

Cannot see the entire content, But Yes I am getting data out of DS_JOBS accurately.

I would like to incorporate my other post in this only:

Firstly, I am querying DS_JOBS and DS_JOBOBJECTS(using CJobdefn as filter), the Count of 2 doesn't match.

Secondly, If I join the 2 tables on JOBNO and OBJIDNO, the entire job list is not retrieved.

Should I be worried at all ?
What are the potential issues that I can get in my environment?

Just for information, there is no impact on my job runs and this is happening only in one of my Test environments)
Post Reply