Archives of the TeradataForum
Message Posted: Tue, 15 Jan 2002 @ 11:03:04 GMT
The other suggestions that you've received on this are good. Here is a clarification of some of the points raised;
- you can only have 1 PI (UPI or NUPI) per table, so you may have tried adding a USI to one/more tables, but I doubt that it would help.
- if you can provide the explain output for the query and the table definitions that would be good
- what size are the tables in the query and what is the maxspool figure for the user running the query?
- I have seen the idea about adding "b.ban=c.ban" before. It doesn't make any logical sense, but I've seen it work. What was meant (i think) is to change your query to use the following
- this is quite possibly a data distribution problem, with a large spool file being built on a single amp. Try the following steps;
(1) for the userid that is going to run the problem query, set the PeakSpool figures to zero (macro dbc.clearpeakdisk should do this for you).
(2) run the query and let it abend with the 2646 spool space error
(3) run the following query to check the distribution of spool space
Select sum(peakspool),100.00*(avg(peakspool)/max(peakspool)) (named spoolpe) From dbc.diskspace Where databasename =
- in the previous query, the 'spoolpe' column is the parallel efficiency of the 'peak spool' figures. Ideally you want that figure to be close to 100%, if it's a lot lower then you're problem is due to a skewed spool file(s).
- on the face of it I can't think why this sort of query should give you skewed spool files (assuming that's what it is). What's the distribution of the tables prior to running this query? If they're skewed then that may be the problem.
Let's see where that takes you.
Ward Analytics Ltd: Information in motion (www.ward-analytics.com)
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|