I run a heavily processing sql script via BTEQ composed of different enrichment steps to get increase kpi's
I got 'Failure 2646 No more spool space in db' in 7th iteration
Which are the solutions to overpass it?
We've collected statistics, enlarge the spool space of the user and last time added 'commit work;' which did not work - Failure 3706 Syntax error: COMMIT WORK not allowed for a DBC/SQL session.
If views are used in selects, the collect statistic is to be done on View'columns or base table columns ?
first you need to find out which step runs out of spool, check dbc.QryLogStepsV for that query.
Compare to Explain to see if the estimated numbers match the actual from the steps.
Regarding the error for COMMIT, you're probably running a Teradata mode session (and there should be no relation to the 2646 anyway).
Stats are collected on the base tables check DIAGNOSTIC HELPSTATS ON FOR SESSION; to find missing stats.
Thank you Dieter!
We are running the statistics recomended by SqlAssistant Explain plan
Then we'll rerun the script.
If i understood correctly, Commit is not of big help for "spool space" error so I'll skip COMMIT statement.
Still same error in iteration 7...
The tables it works during generic iteration are in this order: dropped /created /populated then deleted at the end of iteration.
I don't know if statistics are useful once the content is completely changed; i can see in explain plan step 8 "low confidence to be 15,232,785 rows" as the biggest volume in, but the efective insert result is about 31655 rows
when your final result is only 31655 rows there must be some intermediate steps running out of spool.
If Explain looks ok (no product join with a stupid condition?) you need the data from dbc.QryLogStepsV to compare actual vs. estimated.
For the temporary tables used in each iteration you probably don't need any stats.
Use below query to check spoolusage and other factor:
SUBSTR(TB2.SqlTextInfo,1,1000) AS SqlTextInfo
TB1.QueryID = TB2.QueryID
TB1.ProcID = TB2.ProcID
TB1.SessionID='Enter Session ID here';
Also you can consider following tips:
1) If you are dealing with huge number of records, try adding Compression on the table. This will reduce spool space used if the table is getting redistributed.
2) If the Volatile Tables are used only for intermediary calculations and are not used later then drop them explicitly. Dont wait for session to complete. This will free some much required spool space.
3) If you are facing issues due to non-availability of SPOOL Space, then try optimizing your SQL queries , remove any PRODUCT JOIN and collect stats on columns participating in Joins conditions. ( i think you have already covered this)
4) Which TD version are you using ? In new version, you can even do collect stats on volatile tables . See if this can help you in tuning queries.
Product join exists but is required; I reduced the dataset in small batches using key MOD 32 = 0, / =1 /.. /=31 (processing 32 time the iterations) and worked out. But is a under pressure choice... analysing further...
Thank you for prompt help!
Check for the step/join condition the query spool out and analyze the column/data further:
a. Rows per value (use HELP STAT). Less number of RPV will lead selective AMP's are used during data redistributed
b. Use derive table, to sub-set data or aggregate before joining with next table
c. If PI is different for both the tables, look for applying further filter condition to limit rows processed
d. Look for any default value (like NULL, #, etc.,) and apply filter condition to eliminate those records
Also check for any left over spool "phantom spool" not released from previous sessions. Some times this act as hidden cop.
WHERE DATABASENAME NOT IN (SEL USERNAME FROM DBC.SESSIONINFO)
AND DATABASENAME <> 'DBC'
GROUP BY 1
HAVING SUM(CURRENTSPOOL) > 0 OR SUM(CURRENTTEMP) > 0
ORDER BY 1