All Forums Database
HateOra 17 posts Joined 10/13
02 Dec 2013
Need guidance

Hi,
 
I have a bteq job which is running MERGE on fact table.
there is a JI bult on my fatc table.
 
while executing the MERGE suddenly the query started failing with spool space error, even thogh there is no load on server.
 
also when i monitored the performance of the query in Viewpoint.. i could see that it was running with 99.6 % CPU skew and 99.6% IO skew..
 
Could you please advice me how to do investigation on this?
 
any canary query that can help me , please provide.

Raja_KT 1246 posts Joined 07/09
02 Dec 2013

It is strange.  Could you run Explain merge into  and you can investigate.
Also,since you run bteq, maybe you can run an os command  with say 'select before' before the merge stmt and after the merge stmt redirecting the output to a file, just to test.
Cheers,
Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
02 Dec 2013

Can you share the definition of both fact tabe and JI, the MERGE and explain?
What's the spool size assigned to your load user?

Dieter

Raja_KT 1246 posts Joined 07/09
03 Dec 2013

You can log the merge errors into an error table. But I dont know from which version, this is available. But 14 I see this option.
Cheers,
Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Kawish_Siddiqui 37 posts Joined 03/07
03 Dec 2013

As Dieter already mentioned, more details are needed to pin point the exact problem. specially Spool size, spool allocation, table structure, Merge Statement and Number of AMPs.
At a glance, it looks spool skewness problem on a particular amp. 

Kawish Siddiqui -

You must sign in to leave a comment.