Sparce Lookup error

snt_ds · Post by **snt_ds** » Fri Jun 22, 2007 12:47 pm

Hi All,

I'm trying to do a sparce lookup on one of the oracle table.
Oracle stage I defined the Sparce lookup. In lookup stage when I try to define the key derivation datastage is not allowing me to do it and it throws one alert box with below message.

key expressions cannot be set on key columns of this link. The connected stage defines the key lookup

Can some one please explain me what is going wrong.

Thanks
Suri

us1aslam1us · Post by **us1aslam1us** » Fri Jun 22, 2007 1:50 pm

It is suggesting that the key is already defined in the table. And as per my understanding the only time you define a key expression in the Lookup stage is when you are doing a lookup against a Data Set. So you don't need to define it again here in this scenario.

snt_ds · Post by **snt_ds** » Sun Jun 24, 2007 6:30 am

Hi,

I'm doing the Sparce Lookup in one of the job.
Job is not throwing any warning or error message but always job is in running mode in director and when I see the performance on DS client it is showing 0 values on the link.

my question is why it is not reading the records, If there is any problem why it is not aborting.

Thanks
Suri

ray.wurlod · Post by **ray.wurlod** » Sun Jun 24, 2007 3:28 pm

Forget the row counts on the link - these might not be updated for a number of reasons.

Is the job running to completion? Are the lookups working? Enable player process row counts and get the row counts into the log file.

snt_ds · Post by **snt_ds** » Sun Jun 24, 2007 3:57 pm

I had a job in which I used four join stages, I was using left outer in all the joins. Two of my right table are much bigger then the left table,
(Approx 20 million and 8 million on my right tables).

Job was running fine and taking 18 minutes and some times getting failed because of resources.It was filling the scratch space.

So I need to change the job design and had to use the sparce lookup.
I modified the jobs as per the need but job is running for ever and not doing any thing. Last night it ran for 6 hours and didn't do anything so I killed the job.

I'm not getting why it is behaving like this.

Can some one please help me to resolve this issue.

Thanks
Suresh

sanjay · Post by **sanjay** » Sun Jun 24, 2007 5:36 pm

Suresh

if you have millions of record dont use sparse lookup . stick to join stage and try to resolve scratch space issue . because sparse lookup hits databae for every row and chance of database level lock is more

Regards
Sanjay

snt_ds wrote:I had a job in which I used four join stages, I was using left outer in all the joins. Two of my right table are much bigger then the left table,
(Approx 20 million and 8 million on my right tables).

Job was running fine and taking 18 minutes and some times getting failed because of resources.It was filling the scratch space.

So I need to change the job design and had to use the sparce lookup.
I modified the jobs as per the need but job is running for ever and not doing any thing. Last night it ran for 6 hours and didn't do anything so I killed the job.

I'm not getting why it is behaving like this.

Can some one please help me to resolve this issue.

Thanks
Suresh

snt_ds · Post by **snt_ds** » Sun Jun 24, 2007 5:49 pm

Thanks Sanjay for the response.

Can you please let me know how to resolve the Scratch space issue. Do i need to request my admin group to increase the space or is there any thing I can take care off in my job configuration.

Thanks
Suresh

sanjay · Post by **sanjay** » Sun Jun 24, 2007 6:53 pm

Suresh

you need to request admin to increase space . and also delete any unwanted files also so that you get space

Sanjay

snt_ds wrote:Thanks Sanjay for the response.

Can you please let me know how to resolve the Scratch space issue. Do i need to request my admin group to increase the space or is there any thing I can take care off in my job configuration.

Thanks
Suresh