Lapis Lazuli | Level 10

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

1 ACCEPTED SOLUTION

Accepted Solutions
Obsidian | Level 7

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.

11 REPLIES 11
Obsidian | Level 7

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
Lapis Lazuli | Level 10

Hi Joe,

Thank you for your quick response!
Lapis Lazuli | Level 10

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;
Super User

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.

PROC Star

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.

Obsidian | Level 7

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.

PROC Star

@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.

Lapis Lazuli | Level 10

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

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.

Obsidian | Level 7