We are using some big SP in our projects.We have spacing issue on running those SP's in both teradata studio and BTEQ.We are getting No Spool space error in BTEQ command and teradata studio.We are googled about this issue in forum,they are suggest us to run the query in BTEQ Terminal ,but still issue in not solved.can anyone help us in this issue........it's urgent!!!!!
We have attached the screen shot.
There are some points you need to consider to avoid "no more spool space"
- Verify if the SQL you are using has been written correctly. It means you use the proper PI, PPI, NUSI (in case it appplies). In addition, you may verify if you can create tmp tables in order to resolve the complex queries and then try to join the results by creating a new table, for instance.
- On the other hand, you may ask your DBA if he/she could validate the spool space assigned to the user that executes the process through bteq or assistant.
We have checked the skew factor for single customer table, we are getting below results,
We have set both index (PI and NUSI) key for above table.It's contain more than 20M records.We are getting spool space error particulary this table. There is any chance to reduce this problem.
What does your stored procedure do? Does it perform aggregation? Are you joining tables? have you run explains on the underlying code?
We fetch data from one table and stored that results to global temporary table and we perform some aggregation function and union all the results.Using that result update that into new table.
Allocated space is as follow as for user
Permanent Space : 3700.9999996572733
Spool Space : 2700.999999638647
Temporary Space : 1000.9999997355044
The sample code as follow as,