Job returns different values for the same query

suresh.narasimha · Post by **suresh.narasimha** » Tue Dec 12, 2006 6:54 am

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.

DSguru2B · Post by **DSguru2B** » Tue Dec 12, 2006 7:17 am

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.

chulett · Post by **chulett** » Tue Dec 12, 2006 7:46 am

suresh.narasimha wrote:This is a serious issue and please help me with a solution ASAP.

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.

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...

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...

If you run the jobs manually one at a time in a consistent order - do you get the results you expect run over run?

DSguru2B · Post by **DSguru2B** » Tue Dec 12, 2006 7:56 am

chulett wrote: Not sure what made him think there was a 'static source' involved...

I think you answered your own curiosity by saying:

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.

Thats what i wanted to make sure. Maybe the OP's source is changing and hence he/she sees different results.

suresh.narasimha · Post by **suresh.narasimha** » Tue Dec 12, 2006 8:06 am

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,

DSguru2B · Post by **DSguru2B** » Tue Dec 12, 2006 8:09 am

What about the hashed file names? Make sure they are different for different hashed files and same for both building the hashed file and while reading it.

suresh.narasimha · Post by **suresh.narasimha** » Tue Dec 12, 2006 8:20 am

Yes they are distinctly named hashed files.

DSguru2B · Post by **DSguru2B** » Tue Dec 12, 2006 8:24 am

What options do you have specified within the hashed file stage properties. Is it 'create file' and 'delete file before create' option or just clear file?

suresh.narasimha · Post by **suresh.narasimha** » Tue Dec 12, 2006 8:25 am

Create file for all the Lookups

DSguru2B · Post by **DSguru2B** » Tue Dec 12, 2006 8:31 am

Create file AND 'clear' or create file and 'Delete file before create' option?

ray.wurlod · Post by **ray.wurlod** » Tue Dec 12, 2006 3:26 pm

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.

suresh.narasimha · Post by **suresh.narasimha** » Tue Dec 12, 2006 11:12 pm

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_Server_Whale · Post by **I_Server_Whale** » Wed Dec 13, 2006 12:28 am

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,

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 !!!!!

Earlier you said that you got a wrong result as well as correct result when you run the job manually.

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?

Whale.

ray.wurlod · Post by **ray.wurlod** » Wed Dec 13, 2006 1:08 am

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.

DSXchange