Job returns different values for the same query
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 81
- Joined: Mon Nov 21, 2005 4:17 am
- Location: Sydney, Australia
- Contact:
Job returns different values for the same query
Hi Gurus,
I have a sequence in which 26 lookup jobs which are executed at once.
Few jobs returned Zero rows even if there is a result set for the selection at source.
When I run the same jobs manually these are returning 20000 rows and the same when i run again its returning 40000 rows.
All three different results.
This is a serious issue and please help me with a solution ASAP.
Thanks in Advance.
I have a sequence in which 26 lookup jobs which are executed at once.
Few jobs returned Zero rows even if there is a result set for the selection at source.
When I run the same jobs manually these are returning 20000 rows and the same when i run again its returning 40000 rows.
All three different results.
This is a serious issue and please help me with a solution ASAP.
Thanks in Advance.
SURESH NARASIMHA
Your going to get a lot of
remarks because of 'ASAP'. Try avoid using such words that demand for help rather than request.
Now for your question, i have some followups, Do the sources change in between every runs, make sure your hashed file names are distinct. You have keys defined properly both during hashed file build and hashed file read. The columns are in the same order during the hashed file build and the hashed file read. How many cpus do you have, is it a simple lookup job with three or four stages or does is the design complex.
For static source, the hashed file should not behave in this manner, unless something is going wrong somewhere.
![Evil or Very Mad :evil:](./images/smilies/icon_evil.gif)
Now for your question, i have some followups, Do the sources change in between every runs, make sure your hashed file names are distinct. You have keys defined properly both during hashed file build and hashed file read. The columns are in the same order during the hashed file build and the hashed file read. How many cpus do you have, is it a simple lookup job with three or four stages or does is the design complex.
For static source, the hashed file should not behave in this manner, unless something is going wrong somewhere.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Re: Job returns different values for the same query
OK, you already got the ASAP lecture, although a little toned down from what the Grand Poobah would have said. We are all volunteers here who help when we can help - people sleep, people work, but someone will always get to you As Soon As Practical without you demanding it. Keep that in mind, please. If you need immediate help, sign up for Uber Support from IBM or something.suresh.narasimha wrote:This is a serious issue and please help me with a solution ASAP.
Also, for someone with a 'serious issue' there is a serious lack of useful information in your post. There's not nearly enough information for anyone to help you without doing like DSGuru just did - firing off WAGs from the hip. Not sure what made him think there was a 'static source' involved...
![Confused :?](./images/smilies/icon_confused.gif)
We know nothing of your source or how you are pulling from it, that's the first place I'd look. With the information you've posted I would guess your source changes over time, you'd have to convince us otherwise if that's not the case. Speaking of WAGs...
![Question :?:](./images/smilies/icon_question.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: Job returns different values for the same query
I think you answered your own curiosity by saying:chulett wrote: Not sure what made him think there was a 'static source' involved...![]()
Thats what i wanted to make sure. Maybe the OP's source is changing and hence he/she sees different results.chulett wrote: With the information you've posted I would guess your source changes over time, you'd have to convince us otherwise if that's not the case.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Premium Member
- Posts: 81
- Joined: Mon Nov 21, 2005 4:17 am
- Location: Sydney, Australia
- Contact:
Job returns different values for the same query
Hi DSguru and Chulett,
Very Very Sorry For " ASAP ". I was in a tension for that. Please excuse me.
I have 26 Lookup Jobs which are executed parallely, 4 lookups use the same table as a source with different where clause and likewise there are 2 sets of lookups which use the same table and the columns are the same sometimes.
My data will change month on month and all hashed files are recreated for every run.
My database is DB2.
If i run these lookup jobs individually i got a false result once and correct result once ..
So, what might be the problem ? How can i trust my jobs consistency ?
Thanks & regards,
Very Very Sorry For " ASAP ". I was in a tension for that. Please excuse me.
I have 26 Lookup Jobs which are executed parallely, 4 lookups use the same table as a source with different where clause and likewise there are 2 sets of lookups which use the same table and the columns are the same sometimes.
My data will change month on month and all hashed files are recreated for every run.
My database is DB2.
If i run these lookup jobs individually i got a false result once and correct result once ..
So, what might be the problem ? How can i trust my jobs consistency ?
Thanks & regards,
SURESH NARASIMHA
-
- Premium Member
- Posts: 81
- Joined: Mon Nov 21, 2005 4:17 am
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 81
- Joined: Mon Nov 21, 2005 4:17 am
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
If it's the query that's returning different results at different times, that's not a DataStage issue, it's a database issue. Or it may be that it's not the same query - that the parameters used in the WHERE clause change between runs, for example. You have not provided enough information for cogent diagnosis.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 81
- Joined: Mon Nov 21, 2005 4:17 am
- Location: Sydney, Australia
- Contact:
HI,
I'm using the Hashed file for only two options - Create File and Clear file before writing.
Ray,
I suppose it might not be the fault with DB because it picks up the result set based on the Business date and why is that when i run the job manually its giving me the correct result.
Is it something problem that i'm trying to create 26 hashed files at once ?
Confused !!!!!
I'm using the Hashed file for only two options - Create File and Clear file before writing.
Ray,
I suppose it might not be the fault with DB because it picks up the result set based on the Business date and why is that when i run the job manually its giving me the correct result.
Is it something problem that i'm trying to create 26 hashed files at once ?
Confused !!!!!
SURESH NARASIMHA
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
suresh.narasimha wrote:If i run these lookup jobs individually i got a false result once and correct result once ..
So, what might be the problem ? How can i trust my jobs consistency ?
Thanks & regards,
Earlier you said that you got a wrong result as well as correct result when you run the job manually.suresh.narasimha wrote:
I suppose it might not be the fault with DB because it picks up the result set based on the Business date and why is that when i run the job manually its giving me the correct result.
Is it something problem that i'm trying to create 26 hashed files at once ?
Confused !!!!!
Now you are saying that it gives you correct result when you run it manually.
Which statement is true? Or have I understood it wrong?
![Confused :?](./images/smilies/icon_confused.gif)
Whale.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You are going to need to debug it systematically.
Are the correct and consistent number of rows being extracted from source? If not, then whether you have zero or 100 lookups is irrelevant; you are going to have to solve this issue first.
Then start adding lookups one at a time, and observing the behaviour. Are the hashed files themselves populated consistently?
And so on... You know your environment better than we ever can.
Are the correct and consistent number of rows being extracted from source? If not, then whether you have zero or 100 lookups is irrelevant; you are going to have to solve this issue first.
Then start adding lookups one at a time, and observing the behaviour. Are the hashed files themselves populated consistently?
And so on... You know your environment better than we ever can.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.