How to Resolve Netezza SPU Swap Partition Error

You might have seen Netezza SPU swap partition error when you are performing some complex transformation that may have included many large table with many complex join.

This error does not mean that we have a space issue on any of our Netezza data slices or disks.We get his error when maximum available temporary work space is full. This temporary work space is used by all the temporary tables, sorting, aggregations, joining, implicit data skew (data distribution at run-time) etc. This issue may also occur if the query have cross joins, causing the temporary work space to grow out of control.

How to resolve the Netezza SPU Swap Partition error?

There are many checks we need to perform when we have this kind of error in Netezza and client connecting to it.

1. Watch Out for Skew: The first check that you can perform is to check if the table is skewed, that is, check if Netezza distributed data on single data slice.

Read:

• Netezza Skew and How to avoid it
• Netezza FPGA- FAST Framework in Netezza

You can check the data skew in nzadmin tool or use below query:

SELECT COUNT(*), DATASLICEID
FROM your_table
GROUP BY DATASLICEID
ORDER BY 1;

2. Check Sub-queries: If your query consists of sub-query, create the intermediate table for that sub-query and check the distribution of the sub-query. This way you can check where data is accumulating.
3. Drop TEMP Tables: Use the minimum temporary tables and drop them in the session when not required. Those temporary table may cause issue to subsequent queries in session.
4. Less concurrent queries: Try to run lessor number of concurrent queries which may take up lots of temporary memory.
5. Re-write the queries which involve Cartesian or cross join.

Read:

• Netezza Joins and its Algorithms
• Analyze and Optimize Query Performance

Set up System variables to resolve Netezza SPU swap partition Error

If in case all the above steps doesn’t work then you can set up the below mentioned system variables in the SQL query that you are running.

Note that, these queries are session specific. That is the reason you need to include them in the SQL script that you running.

set enable_mergejoin = 0;
set ENABLE_TRANSFORM_JOINEXPR=0;
set enable_new_case=0;