ERROR: Teradata row not delivered (trget): No more spool space

[PhatRam33]

Good morning everyone and hope all is well.

Has anyone encountered this error before when pulling from Teradata?   Using the following connection string for my pass-through query and using Unix Server in SAS EG.  Is there a fix for this or something I can add to my connection string?  Thanks in advance.

Error message:

Teradata row not delivered (trget): No more spool space in user123

%_log_host(PRDASWE3,tera);

proc sql ;

connect to teradata (&_log_host.

server="PRDASWE3" mode=teradata);

create table work.tbl_01 as

select * from connection to teradata (

[Tom]

Ask your Teradata admin to help you tweak your Teradata query.  

[webart999ARM]

To fix this issue, you may need to increase the amount of space allocated to the user, or try running the query at a different time when there is less demand on the system. Additionally, you may want to try optimizing the query to reduce the amount of space it requires, or breaking it up into multiple smaller queries.

Try to increase the spool space limit for your user account. This can be done by running the following SQL statement on the Teradata server:

GRANT SPOOL ON user123 TO user123 WITH MAXSPOOL SIZE 100000;

[PhatRam33]

Thank you.  Is there a command that would tell me what my current spool size is? Just want to make sure I'm not allocating less than what I have =).

[webart999ARM]

Try this

SELECT USERNAME, CURRENTPERM, SPOOLUSED, SPOOLQUOTA
FROM dbc.dbcinfo
WHERE USERNAME = 'user123';

This will return the current spool space allocation for your user account. You can then adjust the allocation as needed using the ALTER USER command mentioned above.

[Tom]

---

@PhatRam33 wrote:

Thank you.  Is there a command that would tell me what my current spool size is? Just want to make sure I'm not allocating less than what I have =).

---

Probably.  But most of the time you get spool file issue with Teradata the issue is not really the spool size.  Teradata is a parallel processing engine.  It divides the work between a lot of compute nodes.  If the data does not get spread to the the nodes in a balanced way you can have issues even when the total spool space is fine.  Normally it is caused by not using a primary key that allocates the records equally to each one.