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

## Re: Teradata Spool Space error

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

## Re: Teradata Spool Space error

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

## Re: Teradata Spool Space error

Hi Joe,

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

## Re: Teradata Spool Space error

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

## Re: Teradata Spool Space error

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

## Re: Teradata Spool Space error

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

## Re: Teradata Spool Space error

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

## Re: Teradata Spool Space error

@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

## Re: Teradata Spool Space error

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

## Re: Teradata Spool Space error

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

## Re: Teradata Spool Space error

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

## Re: Teradata Spool Space error

Discussion stats
• 11 replies
• 4401 views
• 1 like
• 5 in conversation