DSXchange

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.

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.

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?

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.

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,

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.

Yes they are distinctly named hashed files.

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?

Create file for all the Lookups

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

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.

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

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.

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.