spool space error in where statements

Question

I was given this query to run but it returned spool error. I was told to take the bolded where statement out and use it to create categoryG exclusions table and then add another new step to join that table with the rest of the table. I'm new to Teradata and got very lost. Hoping someone can help. Thank you!

CREATE VOLATILE TABLE A
    ,NO FALLBACK
    ,NO BEFORE JOURNAL
    ,NO AFTER JOURNAL AS 
(
    SELECT 'TX' AS STATE
        ,ADD_MONTHS(li.column7 - EXTRACT(DAY FROM li.Column21) + 1, 1) - 1 AS column12
        ,ADD_MONTHS(li.Column22 - EXTRACT(DAY FROM li.Column22) + 1, 1) - 1 AS column13
        ,EXTRACT(YEAR FROM column13) * 12 + EXTRACT(MONTH FROM column13) - EXTRACT(YEAR FROM column12) * 12 - EXTRACT(MONTH FROM column12) AS offset
        ,column8
        ,CASE 
            WHEN clm.Column20 = '01strong text'
                AND li.Column23 = '1'
                THEN 'INP'
            WHEN clm.Column20 = '01'
                AND li.Column23 IN (
                    '2'
                    ,'3'
                    )
                THEN 'OUT'
            WHEN clm.Column20 = '02'
                AND CHARACTER_LENGTH(TRIM(Column24)) <> 5
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND SUBSTRING(TRIM(Column24) FROM 1 FOR 1) BETWEEN 'A'
                    AND 'Z'
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND SUBSTRING(TRIM(Column24) FROM 2 FOR 1) BETWEEN 'A'
                    AND 'Z'
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND SUBSTRING(TRIM(Column24) FROM 3 FOR 1) BETWEEN 'A'
                    AND 'Z'
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND SUBSTRING(TRIM(Column24) FROM 4 FOR 1) BETWEEN 'A'
                    AND 'Z'
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND SUBSTRING(TRIM(Column24) FROM 5 FOR 1) BETWEEN 'A'
                    AND 'Z'
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND Column24 BETWEEN '00000'
                    AND '99999'
                THEN 'PRO'
            WHEN clm.Column20 = '02'
                THEN 'OME'
            WHEN clm.Column20 = '03'
                THEN 'PDP'
            WHEN clm.Column20 = '04'
                THEN 'DEN'
            ELSE ''
            END AS column9
        ,CASE 
            WHEN fcg.column15 IN (
                    '000166915'
                    ,'000166916'
                    ,'000166913'
                    ,'000168717'
                    ,'000168718'
                    )
                THEN 'Y'
            WHEN fcg.column15 IN (
                    '000168719'
                    ,'000166920'
                    ,'000166917'
                    ,'000166919'
                    ,'000166914'
                    ,'000168716'
                    )
                THEN 'N'
            WHEN SUBSTRING(fcg.column15 FROM 6 FOR 1) = '1'
                THEN 'Y'
            WHEN SUBSTRING(fcg.column15 FROM 6 FOR 1) = '2'
                THEN 'N'
            ELSE ''
            END AS column10
        ,CASE 
            WHEN fcg.Column25 IN (
                    '0004'
                    ,'0005'
                    ,'0006'
                    )
                THEN 'Y'
            ELSE 'N'
            END column11
        ,SUM(li.Column26) AS Column26
        ,SUM(li.Column27) AS Column27
        ,SUM(li.Column28) AS paid_amt
        ,SUM(CASE 
                WHEN clm.Column20 = '01'
                    THEN li.Column26 - li.non_covd_amt - li.Column30
                ELSE li.Column29
                END) AS amount 
    FROM DatabaseLi li 
        INNER JOIN DatabaseFCG fcg 
            ON fcg.column30 = li.column30
            AND li.disp_cd = 'A' 
        INNER JOIN Database11 clm 
            ON clm.column30 = li.column30 
        INNER JOIN Database11_mbr MBR 
            ON li.column30 = MBR.column30 
        LEFT JOIN / CategoryG exclusion / 
            (
                SELECT column30
                FROM Database11_src
                WHERE Column50IN('MA', 'H8')
                GROUP BY 1
            ) src 
            ON li.column30 = src.column30 
        INNER JOIN 
            (
                SELECT column15
                    ,CASE 
                        WHEN product_id LIKE '%apple%'
                            THEN 'apple'
                        WHEN product_id LIKE '%orange%'
                            THEN 'orange'
                        WHEN product_id LIKE '%banana%'
                            THEN 'apple'
                        ELSE ''
                        END AS column8
                FROM DatabaseDD
                WHERE lob IN ('Categrory3')
                    AND product_id IS NOT NULL
                    AND product_id NOT LIKE '%LV%'
                GROUP BY 1,2
            ) AS a 
            ON a.column15 = fcg.column15 
    /*This is the WHERE statement to remove*/
    WHERE /* Excluding ALL CategoryG after 2014 - 01 - 01 */

        li.column7 >= '2014-01-01'
        AND src.column30 IS NULL        
        AND clm.Column20 <> '04'
        AND NOT MBR.column55 IN (
                SELECT DISTINCT a.column55
                FROM DatabaseA a
                INNER JOIN DatabaseB b ON a.column1 = b.column1
                    AND a.column2 = b.column2
                    AND a.column3 = b.column3
                    AND a.column4 = b.column4
                    AND a.column5 = '11'
                    AND a.column14 IN (
                        SELECT DISTINCT column15
                        FROM DatabaseDD
                        WHERE lob LIKE '%Categrory3%'
                        )
                    AND a.column6 <> 'IND'
                )           
        AND NOT fcg.column15 IN (
                SELECT DISTINCT a.column14
                FROM DatabaseA a
                INNER JOIN DatabaseB b ON a.column1 = b.column1
                    AND a.column2 = b.column2
                    AND a.column3 = b.column3
                    AND a.column4 = b.column4
                    AND a.column5 = '11'
                    AND a.column14 IN (
                        SELECT DISTINCT column15
                        FROM DatabaseDD
                        WHERE lob LIKE '%Categrory3%'
                        )
                    AND a.column6 <> 'IND'
                )
        AND li.column7 < '2016-01-01'
    GROUP BY 1,2,3,4,5,6,7,8
    )
    WITH DATA UNIQUE PRIMARY INDEX (
            STATE
            ,column8
            ,column9
            ,column10
            ,column11
            ,column12
            ,column13
            ) ON COMMIT PRESERVE ROWS;

I tried the explain for the first chunk of select but not sure what it means - explain SELECT DISTINCT (xxx) 1) First, we lock xxx for access, we lock xxx in view xxx for access, and we lock xxx in view xxx for access. 2) Next, we do an all-AMPs RETRIEVE step from xxx in view xxx by way of an all-rows scan with a condition of (xxx)into Spool 1 (all_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 225,735,188 rows (9,255,142,708 bytes). The estimated time for this step is 11.64 seconds. 3) We execute the following steps in parallel. 1) We do an all-AMPs RETRIEVE step from xxx in view xxx by way of an all-rows scan with a condition of (xxx) into Spool 1 (all_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 241,718,108 rows (9,910,442,428 bytes). The estimated time for this step is 1.21 seconds. 2) We do an all-AMPs RETRIEVE step from xxx in view xxx by way of an all-rows scan with a condition of (xxx) into Spool 5 (all_amps) (compressed columns allowed) fanned out into 10 hash join partitions, which is redistributed by the hash code of (xxx) to all AMPs. The size of Spool 5 is estimated with low confidence to be 44,346,884 rows ( 2,172,997,316 bytes). The estimated time for this step is 11.23 seconds. 4) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of an all-rows scan with a condition of (xxx) into Spool 6 (all_amps) (compressed columns allowed) fanned out into 10 hash join partitions, which is redistributed by the hash code of (xxx) to all AMPs. The size of Spool 6 is estimated with low confidence to be 241,718,108 rows ( 9,910,442,428 bytes). The estimated time for this step is 8.49 seconds. 5) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of an all-rows scan, which is joined to Spool 6 (Last Use) by way of an all-rows scan. Spool 5 and Spool 6 are joined using a hash join of 10 partitions, with a join condition of (xxx) The result goes into Spool 4 (all_amps) (compressed columns allowed), which is built locally on the AMPs. The size of Spool 4 is estimated with low confidence to be 45,125,207 rows ( 1,037,879,761 bytes). The estimated time for this step is 1.00 seconds. 6) We do an all-AMPs SUM step to aggregate from Spool 4 (Last Use) by way of an all-rows scan , grouping by field1 ( xxx). Aggregate Intermediate Results are computed globally, then placed in Spool 2. The size of Spool 2 is estimated with low confidence to be 18,209,884 rows (528,086,636 bytes). The estimated time for this step is 1.34 seconds. 7) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request. -> The contents of Spool 2 are sent back to the user as the result of statement 1. The total estimated time is 33.70 seconds.

Answer 1

In addition to the sound advice JNevill offers in his answer, I would recommend working with your DBA to evaluate the DBQL Step Info for the query to understand exactly where the error occurs in the query. You may be able to find that additional statistics could influence the optimizer's plan and avoid the spool error.

Because this is a VOLATILE table, it is created in the user's spool space. If the query is not failing on an intermediate step of the query plan, it may be due to insufficient spool space to materialize the VOLATILE TABLE in spool. If this is one statement among others that are being executed in the user's session and spawning additional volatile tables, it may be the sum of the parts that is causing the problem.

Answer 2

Spool space is where intermediate result sets are stored. These often come from subqueries (but also tables that are being pre-filtered before a join and a million other things). So to get around your spool space issue, a good first step is removing your subqueries to volatile tables of their own. Then join those volatile tables in.

This might look something like:

CREATE MULTISET VOLATILE TABLE volatile_1 AS
(
    SELECT DISTINCT a.column55
    FROM DatabaseA a
    INNER JOIN DatabaseB b ON a.column1 = b.column1
        AND a.column2 = b.column2
        AND a.column3 = b.column3
        AND a.column4 = b.column4
        AND a.column5 = '11'
        AND a.column14 IN (
            SELECT DISTINCT column15
            FROM DatabaseDD
            WHERE lob LIKE '%Categrory3%'
            )
        AND a.column6 <> 'IND'
) WITH DATA ON COMMIT PRESERVE ROWS;

CREATE MULTISET VOLATILE TABLE volatile_2 AS
(
    SELECT DISTINCT a.column14
    FROM DatabaseA a
    INNER JOIN DatabaseB b ON a.column1 = b.column1
        AND a.column2 = b.column2
        AND a.column3 = b.column3
        AND a.column4 = b.column4
        AND a.column5 = '11'
        AND a.column14 IN (
            SELECT DISTINCT column15
            FROM DatabaseDD
            WHERE lob LIKE '%Categrory3%'
            )
        AND a.column6 <> 'IND'
) WITH DATA ON COMMIT PRESERVE ROWS;

CREATE MULTISET VOLATILE TABLE volatile_src AS
(
    SELECT column30
    FROM Database11_src
    WHERE Column50IN('MA', 'H8')
    GROUP BY 1
) WITH DATA ON COMMIT PRESERVE ROWS;

CREATE MULTISET VOLATILE TABLE volatile_src AS
(
    SELECT column15
        ,CASE 
            WHEN product_id LIKE '%apple%'
                THEN 'apple'
            WHEN product_id LIKE '%orange%'
                THEN 'orange'
            WHEN product_id LIKE '%banana%'
                THEN 'apple'
            ELSE ''
            END AS column8
    FROM DatabaseDD
    WHERE lob IN ('Categrory3')
        AND product_id IS NOT NULL
        AND product_id NOT LIKE '%LV%'
    GROUP BY 1,2
) WITH DATA ON COMMIT PRESERVE ROWS;

CREATE VOLATILE TABLE A
    ,NO FALLBACK
    ,NO BEFORE JOURNAL
    ,NO AFTER JOURNAL AS 
(
    SELECT 'TX' AS STATE
        ,ADD_MONTHS(li.column7 - EXTRACT(DAY FROM li.Column21) + 1, 1) - 1 AS column12
        ,ADD_MONTHS(li.Column22 - EXTRACT(DAY FROM li.Column22) + 1, 1) - 1 AS column13
        ,EXTRACT(YEAR FROM column13) * 12 + EXTRACT(MONTH FROM column13) - EXTRACT(YEAR FROM column12) * 12 - EXTRACT(MONTH FROM column12) AS offset
        ,column8
        ,CASE 
            WHEN clm.Column20 = '01strong text'
                AND li.Column23 = '1'
                THEN 'INP'
            WHEN clm.Column20 = '01'
                AND li.Column23 IN (
                    '2'
                    ,'3'
                    )
                THEN 'OUT'
            WHEN clm.Column20 = '02'
                AND CHARACTER_LENGTH(TRIM(Column24)) <> 5
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND SUBSTRING(TRIM(Column24) FROM 1 FOR 1) BETWEEN 'A'
                    AND 'Z'
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND SUBSTRING(TRIM(Column24) FROM 2 FOR 1) BETWEEN 'A'
                    AND 'Z'
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND SUBSTRING(TRIM(Column24) FROM 3 FOR 1) BETWEEN 'A'
                    AND 'Z'
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND SUBSTRING(TRIM(Column24) FROM 4 FOR 1) BETWEEN 'A'
                    AND 'Z'
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND SUBSTRING(TRIM(Column24) FROM 5 FOR 1) BETWEEN 'A'
                    AND 'Z'
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND Column24 BETWEEN '00000'
                    AND '99999'
                THEN 'PRO'
            WHEN clm.Column20 = '02'
                THEN 'OME'
            WHEN clm.Column20 = '03'
                THEN 'PDP'
            WHEN clm.Column20 = '04'
                THEN 'DEN'
            ELSE ''
            END AS column9
        ,CASE 
            WHEN fcg.column15 IN (
                    '000166915'
                    ,'000166916'
                    ,'000166913'
                    ,'000168717'
                    ,'000168718'
                    )
                THEN 'Y'
            WHEN fcg.column15 IN (
                    '000168719'
                    ,'000166920'
                    ,'000166917'
                    ,'000166919'
                    ,'000166914'
                    ,'000168716'
                    )
                THEN 'N'
            WHEN SUBSTRING(fcg.column15 FROM 6 FOR 1) = '1'
                THEN 'Y'
            WHEN SUBSTRING(fcg.column15 FROM 6 FOR 1) = '2'
                THEN 'N'
            ELSE ''
            END AS column10
        ,CASE 
            WHEN fcg.Column25 IN (
                    '0004'
                    ,'0005'
                    ,'0006'
                    )
                THEN 'Y'
            ELSE 'N'
            END column11
        ,SUM(li.Column26) AS Column26
        ,SUM(li.Column27) AS Column27
        ,SUM(li.Column28) AS paid_amt
        ,SUM(CASE 
                WHEN clm.Column20 = '01'
                    THEN li.Column26 - li.non_covd_amt - li.Column30
                ELSE li.Column29
                END) AS amount 
    FROM DatabaseLi li 
        INNER JOIN DatabaseFCG fcg 
            ON fcg.column30 = li.column30
            AND li.disp_cd = 'A' 
        INNER JOIN Database11 clm 
            ON clm.column30 = li.column30 
        INNER JOIN Database11_mbr MBR 
            ON li.column30 = MBR.column30 
        LEFT JOIN volatile_src src 
            ON li.column30 = src.column30 
        INNER JOIN volatile_a AS a 
            ON a.column15 = fcg.column15 
    WHERE / Excluding ALL CategoryG after 2014 - 01 - 01 /      
        li.column7 >= DATE '2014-01-01'
        AND src.column30 IS NULL        
        AND clm.Column20 <> '04'
        AND NOT MBR.column55 IN (SELECT * FROM volatile_1)
        AND NOT fcg.column15 IN (SELECT * FROM volatile_2)
        AND li.column7 < '2016-01-01'
    GROUP BY 1,2,3,4,5,6,7,8
    )
    WITH DATA UNIQUE 
    PRIMARY INDEX 
        (
            STATE
            ,column8
            ,column9
            ,column10
            ,column11
            ,column12
            ,column13
        ) 
    ON COMMIT PRESERVE ROWS;

If you are still running into spool issues, then break the query down. Remove all of the joins and any of the references to the joined tables in teh SELECT and WHERE clause, and run it. Does it spool out? If not, add the next one and it's related SELECT and WHERE clause items back in. Run it. Does it spool out? If not, add in the next table. Continue doing it until you spool out. You can zone in on how much data you can push through this one query.

You may have to break this out into a few more statements using UPDATE statements to write more data into the records from the joins.

You may have to beg your DBA for more spool. If this is a one time thing, perhaps someone with more SPOOL available can run it for you?