Teradata Spool Space error

[Santt0sh]

Hi All,

I getting the Spool space error on Teradata while I'm trying to extract data for a month, so I am trying to spilt the date in an interval of two weeks and extract the data.

i am thinking of spitting the data like strtdate -  '2022-04-01' enddate =   '2022-04-16' but not sure how to calculate the next start date and end date.

Kindly Note that the data will be always extracted for the previous month.

Please suggest if i need to compare the Strtdate  and create a new macro variable and use it the query.  or use a do loop to achieve this.

data _null_;
date = today();
a = intnx('month', Date, 0, 'B');
B = intnx('month', Date, 0, 'E');
CALL SYMPUT (CATS('C'),QUOTE(PUT(INTNX('day',A,0),yymmdd10.),"'"));
CALL SYMPUT (CATS('D'),QUOTE(PUT(INTNX('day',A,15,'E'),yymmdd10.),"'"));
	
put a = date9. B = date9.;
run;
%PUT &C. &D.;

%syslput C= &C.;
%syslput D= &D.;

Regards,

Santosh

[average_joe]

You can use both a monthly or semimonthly intervals for calculating a midpoint in the month with INTNX() function. For a monthly interval, the alignment option of 'M' uses the 15th day as the midpoint. For semimonthly interval, the period is half-month with starting points on the 1st and 16th days of the month, so you can use the appropriate increment and alignment to get the right dates.

Here's one option:

data _null_;
dt = today();
format beg_month mid_month end_month yymmdd10.;

* uning monthly interval;
beg_month = intnx('month', dt, 0);
mid_month = intnx('month', dt, 0, 'm');
end_month = intnx('month', dt, 0, 'e');
put beg_month=  mid_month=  end_month=;

* using semimonth interval;
beg_month = intnx('semimonth', dt, -1);
mid_month = intnx('semimonth', dt, 0);
end_month = intnx('semimonth', dt, 0, 'e');
put beg_month=  mid_month=  end_month=;
run;

Output:

beg_month=2022-04-01 mid_month=2022-04-15 end_month=2022-04-30
beg_month=2022-04-01 mid_month=2022-04-16 end_month=2022-04-30

[Santt0sh]

Hi Joe,

Thank you for your quick response!

[Santt0sh]

Hi Joe,

I am trying to extract data from database to overcome the Spool Space error.
here
SELECT a.actor_id, a.first_name, a.last_name, count(fa.film_id),fa.rdate
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id
AND fa.rdate between &beg_month. and &mid_month.
GROUP BY a.actor_id, a.first_name, a.last_name
ORDER BY count(fa.film_id) ASC;

[Tom]

most of the times I got SPOOL spaces errors using Teradata it was caused by having skewed data.  Make sure your data is distributed properly.  Have your DBA check the explain plan for any query you are trying to run, perhaps they can suggest a better way to format the query.  Teradata sometimes does stupid things with a query and just changing the SQL code to something else that returns the same results makes it work better.

[SASKiwi]

Wouldn't it be more efficient to fix the spool space problem rather than code around it? Talk to your Teradata DBA - maybe he can increase your quota.

[average_joe · Accepted solution]

Sometimes it's not as simple as asking for more space. At my company, most folks have plenty of spool space allocation. Most issues from spool space errors arise from poor query construction, bad data distribution in the tables, or lack of statistics collection. 

[SASKiwi]

@average_joe  - That's where the DBA should be offering advice. If the job is using too much spool space they should be able to offer ways of fixing that.

[Santt0sh]

Hi Joe,

I checked with the DBA, and was told that limits cannot be increased randomly.
Hence I optimized the query, and it’s running fine in the test Environment and hope it does the same on Prod.


Thank you!!

[Kurt_Bremser]

This looks like you do not query a DBMS table, but a DBMS view.

Querying a table would need no space on the DBMS side.

So I suggest you review (or have reviewed) the view code in Teradata.

[average_joe]

In a Teradata database, spool is used whether you query a table or a view,
so your statement is not true in this case.

[Kurt_Bremser]

Ha. Learned something new today.