SPOOL SPACE ISUUE

Tools
Enthusiast

SPOOL SPACE ISUUE

In one my process bteq is failing due to "NO MORE SPOOL SPACE to user”. when i executed it from SQL assitant the select query used in bteq is working fine and returning rows but when the same query is used with insert statement is giving “NO MORE SPOOL SPACE to user”.

Please help on this.

Regards,
Vikas
5 REPLIES
Fan

Re: SPOOL SPACE ISUUE

The login info used in BTEQ and SQL assistant are the same?

If no,
The username used in BTEQ script need more spool space.

If yes,
You are trying to run more that one query using the same user name,That may consume lot of spool space.
Enthusiast

Re: SPOOL SPACE ISUUE

Did you run both queries in the same interval of time? Was there possibly some change to the underlying data?

-pt

Enthusiast

Re: SPOOL SPACE ISUUE

This was due to incorrect data in the source table. Once data is corrected problem get resolved.

Thanks,
Vikas

Re: SPOOL SPACE ISUUE

Everytime I run this query I get the error
No More Spool Space....
any suggestions

DROP TABLE FABSTG.ROP_RM_LOC_MER_EXP_SALES;

CREATE SET TABLE FABSTG.ROP_RM_LOC_MER_EXP_SALES,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
JOB_ID INTEGER NOT NULL,
LOC_NBR_DIV INTEGER NOT NULL,
PUB_ID INTEGER NOT NULL,
LOC_DIM_ID INTEGER NOT NULL,
PROD_VER_ID INTEGER NOT NULL,
WEEKDAY_NBR SMALLINT NOT NULL,
RUN_DT DATE FORMAT 'yy/mm/dd' NOT NULL,
JOB_MEDIA_STAT_NBR INTEGER NOT NULL,
JOB_SUB_TYP1_NBR INTEGER NOT NULL,
JOB_SUB_TYP2_NBR INTEGER,
AMC_YEAR SMALLINT NOT NULL,
AMC_PERIOD SMALLINT NOT NULL,
amc_int_date INTEGER NOT NULL,
LOCK_F CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

JOB_PARTICIPANT_ID INTEGER NOT NULL,
ORIG_KST_ID INTEGER NOT NULL,
ORIG_KST_LVL_NBR INTEGER NOT NULL,
ORIG_MCH_F CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
ALLOC_KST_ID INTEGER NOT NULL,
ALLOC_KST_LVL_NBR INTEGER NOT NULL,
ALLOC_MCH_F CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
JOB_PRTCPNT_STAT_NBR INTEGER NOT NULL,

FUNDING_PARTICIPANT_ID INTEGER NOT NULL,
FUNDING_PARTICIPANT_MCH_F CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
ORIG_ROW_F CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
DISTRIBUTED_F CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
ROW_PROCESSED_F CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
USE_FOR_ALLOC_F CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

PUB_PLAN_DCT_MEDIA_COST DECIMAL(18,9),
PUB_PLAN_INDCT_MEDIA_COST DECIMAL(18,9),
PUB_PLAN_PROD_COST DECIMAL(18,9),
PUB_FCST_DCT_MEDIA_COST DECIMAL(18,9),
PUB_FCST_INDCT_MEDIA_COST DECIMAL(18,9),
PUB_FCST_PROD_COST DECIMAL(18,9),
PUB_REAL_COST DECIMAL(18,9),

JOB_PLAN_DCT_MEDIA_COST DECIMAL(18,9),
JOB_PLAN_INDCT_MEDIA_COST DECIMAL(18,9),
JOB_PLAN_PROD_COST DECIMAL(18,9),
JOB_FCST_DCT_MEDIA_COST DECIMAL(18,9),
JOB_FCST_INDCT_MEDIA_COST DECIMAL(18,9),
JOB_FCST_PROD_COST DECIMAL(18,9),
JOB_REAL_COST DECIMAL(18,9),

PUB_PLAN_DCT_MEDIA_COST_FACT DECIMAL(18,9),
PUB_PLAN_INDCT_MEDIA_COST_FACT DECIMAL(18,9),
PUB_PLAN_PROD_COST_FACT DECIMAL(18,9),
PUB_FCST_DCT_MEDIA_COST_FACT DECIMAL(18,9),
PUB_FCST_INDCT_MEDIA_COST_FACT DECIMAL(18,9),
PUB_FCST_PROD_COST_FACT DECIMAL(18,9),
PUB_REAL_COST_FACT DECIMAL(18,9),

MER_FUND_PLAN_FACT DECIMAL(18,9),
MER_FUND_FCST_FACT DECIMAL(18,9),

ALLOC_MER_PLAN_SALES_FACT DECIMAL(18,9),
ALLOC_MER_ACTL_SALES_FACT DECIMAL(18,9),

PUB_ZIP_FACT DECIMAL(18,9),
PUB_LOC_FACT DECIMAL(18,9),
PLAN_PUB_ZIP_LOC_MER_SLS_FACT DECIMAL(18,9),
ACTL_PUB_ZIP_LOC_MER_SLS_FACT DECIMAL(18,9),

LAST_UPD_USER_ID CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
LAST_UPD_TS TIMESTAMP(6) NOT NULL)

PRIMARY INDEX ROP_RM_LOC_MER_EXP_NUPI ( JOB_ID ,LOC_NBR_DIV ,
PUB_ID ,LOC_DIM_ID ,ORIG_KST_ID ,ORIG_KST_LVL_NBR ,ORIG_MCH_F ,
ALLOC_KST_ID ,ALLOC_KST_LVL_NBR ,ALLOC_MCH_F );

INSERT INTO
FABSTG.ROP_RM_LOC_MER_EXP_SALES
(
JOB_ID,
LOC_NBR_DIV ,
PUB_ID,
LOC_DIM_ID,
PROD_VER_ID,
WEEKDAY_NBR,
RUN_DT,
JOB_MEDIA_STAT_NBR,
JOB_SUB_TYP1_NBR,
JOB_SUB_TYP2_NBR,
AMC_YEAR ,
AMC_PERIOD,
amc_int_date,
LOCK_F,

JOB_PARTICIPANT_ID,
ORIG_KST_ID,
ORIG_KST_LVL_NBR,
ORIG_MCH_F,
ALLOC_KST_ID,
ALLOC_KST_LVL_NBR,
ALLOC_MCH_F,
JOB_PRTCPNT_STAT_NBR,

FUNDING_PARTICIPANT_ID,
FUNDING_PARTICIPANT_MCH_F,
ORIG_ROW_F,
DISTRIBUTED_F,
ROW_PROCESSED_F,
USE_FOR_ALLOC_F,

PUB_PLAN_DCT_MEDIA_COST,
PUB_PLAN_INDCT_MEDIA_COST,
PUB_PLAN_PROD_COST,
PUB_FCST_DCT_MEDIA_COST,
PUB_FCST_INDCT_MEDIA_COST,
PUB_FCST_PROD_COST,
PUB_REAL_COST,

JOB_PLAN_DCT_MEDIA_COST,
JOB_PLAN_INDCT_MEDIA_COST,
JOB_PLAN_PROD_COST,
JOB_FCST_DCT_MEDIA_COST,
JOB_FCST_INDCT_MEDIA_COST,
JOB_FCST_PROD_COST,
JOB_REAL_COST,

PUB_PLAN_DCT_MEDIA_COST_FACT,
PUB_PLAN_INDCT_MEDIA_COST_FACT,
PUB_PLAN_PROD_COST_FACT,
PUB_FCST_DCT_MEDIA_COST_FACT,
PUB_FCST_INDCT_MEDIA_COST_FACT,
PUB_FCST_PROD_COST_FACT,
PUB_REAL_COST_FACT,

MER_FUND_PLAN_FACT,
MER_FUND_FCST_FACT,

ALLOC_MER_PLAN_SALES_FACT,
ALLOC_MER_ACTL_SALES_FACT,

PUB_ZIP_FACT,
PUB_LOC_FACT,
PLAN_PUB_ZIP_LOC_MER_SLS_FACT,
ACTL_PUB_ZIP_LOC_MER_SLS_FACT,
LAST_UPD_USER_ID,
LAST_UPD_TS
)

SELECT
rms.JOB_ID,
rms.LOC_NBR_DIV ,
rms.PUB_ID,
rms.LOC_DIM_ID,
rms.PROD_VER_ID,
rms.WEEKDAY_NBR,
rms.RUN_DT,
rms.JOB_MEDIA_STAT_NBR,
rms.JOB_SUB_TYP1_NBR,
rms.JOB_SUB_TYP2_NBR,
rms.AMC_YEAR ,
rms.AMC_PERIOD,
-- added code to calculate integer value of the amc year and period.
(((rms.amc_year - 1900) * 10000) + (rms.amc_period * 100)) AS amc_int_date,
-- added current date as integer
-- cdf.int_date,

rms.LOCK_F,

rms.JOB_PARTICIPANT_ID,
rms.ORIG_KST_ID,
rms.ORIG_KST_LVL_NBR,
rms.ORIG_MCH_F,
rmf.ADDIV_MAN_ID,
rmf.KST_LVL_NBR,
rmf.MCH_F,
rms.JOB_PRTCPNT_STAT_NBR,

rms.FUNDING_PARTICIPANT_ID,
rms.FUNDING_PARTICIPANT_MCH_F,
'N' ORIG_ROW_F,
'Y' DISTRIBUTED_F,
'Y' ROW_PROCESSED_F,
'Y' USE_FOR_ALLOC_F,

rms.PUB_PLAN_DCT_MEDIA_COST,
rms.PUB_PLAN_INDCT_MEDIA_COST,
rms.PUB_PLAN_PROD_COST,
rms.PUB_FCST_DCT_MEDIA_COST,
rms.PUB_FCST_INDCT_MEDIA_COST,
rms.PUB_FCST_PROD_COST,
rms.PUB_REAL_COST,

rms.JOB_PLAN_DCT_MEDIA_COST,
rms.JOB_PLAN_INDCT_MEDIA_COST,
rms.JOB_PLAN_PROD_COST,
rms.JOB_FCST_DCT_MEDIA_COST,
rms.JOB_FCST_INDCT_MEDIA_COST,
rms.JOB_FCST_PROD_COST,
rms.JOB_REAL_COST,

rms.PUB_PLAN_DCT_MEDIA_COST_FACT,
rms.PUB_PLAN_INDCT_MEDIA_COST_FACT,
rms.PUB_PLAN_PROD_COST_FACT,
rms.PUB_FCST_DCT_MEDIA_COST_FACT,
rms.PUB_FCST_INDCT_MEDIA_COST_FACT,
rms.PUB_FCST_PROD_COST_FACT,
rms.PUB_REAL_COST_FACT,

rms.MER_FUND_PLAN_FACT,
rms.MER_FUND_FCST_FACT,

-- revised logic using integer date
CASE WHEN rms.lock_f = 'N' THEN PLAN_SALES_FACT ELSE 0 END AS plan_sales_fact,
CASE WHEN (rms.lock_f = 'Y' AND cdf.int_date > amc_int_date) THEN ACTUAL_SALES_FACT ELSE 0 END AS actual_sales_fact,

rms.PUB_ZIP_FACT,
rms.PUB_LOC_FACT,

CAST((
CAST(rms.PUB_ZIP_FACT AS DECIMAL (20,9)) *
CAST(rms.PUB_LOC_FACT AS DECIMAL (20,9))*
CAST(PLAN_SALES_FACT AS DECIMAL (20,9))*
CAST(rms.MER_FUND_PLAN_FACT AS DECIMAL (20,9))
) AS DECIMAL (18,9)) PLAN_PUB_ZIP_LOC_MER_SLS_FACT,

CAST((
CAST(rms.PUB_ZIP_FACT AS DECIMAL (20,9)) *
CAST(rms.PUB_LOC_FACT AS DECIMAL (20,9))*
CAST(ACTUAL_SALES_FACT AS DECIMAL (20,9))*
CAST(rms.MER_FUND_FCST_FACT AS DECIMAL (20,9))
) AS DECIMAL (18,9)) ACTL_PUB_ZIP_LOC_MER_SLS_FACT,

USER LAST_UPD_USER_ID,
CURRENT_TIMESTAMP LAST_UPD_TS
-- note I rearranged the tables in the from clause - adding current_date_fact first following by rop_rm_loc-mer_exp
FROM
fadstg.current_date_fact cdf,

fadstg.ROP_RM_LOC_MER_EXP_SALES rms

INNER JOIN

fadstg.ROP_RM_MER_ALLOC_FACTOR rmf

ON rms.AMC_YEAR = rmf.AMC_YEAR
AND rms.AMC_PERIOD = rmf.AMC_PERIOD
AND rms.LOC_NBR_DIV = rmf.DIVN_NBR
Enthusiast

Re: SPOOL SPACE ISUUE

Ok try this turn on DIAGNOSTIC HELPSTATS then run explain on your SELECT. It should help you identify where the issue is.

DIAGNOSTIC HELPSTATS ON FOR SESSION;

EXPLAIN
SELECT RMS.JOB_ID
, RMS.LOC_NBR_DIV
, RMS.PUB_ID
, RMS.LOC_DIM_ID
, RMS.PROD_VER_ID
, RMS.WEEKDAY_NBR
, RMS.RUN_DT
, RMS.JOB_MEDIA_STAT_NBR
, RMS.JOB_SUB_TYP1_NBR
, RMS.JOB_SUB_TYP2_NBR
, RMS.AMC_YEAR
, RMS.AMC_PERIOD
,
-- added code to calculate integer value of the amc year and period.
(((RMS.AMC_YEAR - 1900) * 10000) + (RMS.AMC_PERIOD * 100)) AS AMC_INT_DATE
,
-- added current date as integer
-- cdf.int_date,
RMS.LOCK_F
, RMS.JOB_PARTICIPANT_ID
, RMS.ORIG_KST_ID
, RMS.ORIG_KST_LVL_NBR
, RMS.ORIG_MCH_F
, RMF.ADDIV_MAN_ID
, RMF.KST_LVL_NBR
, RMF.MCH_F
, RMS.JOB_PRTCPNT_STAT_NBR
, RMS.FUNDING_PARTICIPANT_ID
, RMS.FUNDING_PARTICIPANT_MCH_F
, 'N' ORIG_ROW_F
, 'Y' DISTRIBUTED_F
, 'Y' ROW_PROCESSED_F
, 'Y' USE_FOR_ALLOC_F
, RMS.PUB_PLAN_DCT_MEDIA_COST
, RMS.PUB_PLAN_INDCT_MEDIA_COST
, RMS.PUB_PLAN_PROD_COST
, RMS.PUB_FCST_DCT_MEDIA_COST
, RMS.PUB_FCST_INDCT_MEDIA_COST
, RMS.PUB_FCST_PROD_COST
, RMS.PUB_REAL_COST
, RMS.JOB_PLAN_DCT_MEDIA_COST
, RMS.JOB_PLAN_INDCT_MEDIA_COST
, RMS.JOB_PLAN_PROD_COST
, RMS.JOB_FCST_DCT_MEDIA_COST
, RMS.JOB_FCST_INDCT_MEDIA_COST
, RMS.JOB_FCST_PROD_COST
, RMS.JOB_REAL_COST
, RMS.PUB_PLAN_DCT_MEDIA_COST_FACT
, RMS.PUB_PLAN_INDCT_MEDIA_COST_FACT
, RMS.PUB_PLAN_PROD_COST_FACT
, RMS.PUB_FCST_DCT_MEDIA_COST_FACT
, RMS.PUB_FCST_INDCT_MEDIA_COST_FACT
, RMS.PUB_FCST_PROD_COST_FACT
, RMS.PUB_REAL_COST_FACT
, RMS.MER_FUND_PLAN_FACT
, RMS.MER_FUND_FCST_FACT
,
-- revised logic using integer date
CASE
WHEN RMS.LOCK_F = 'N' THEN PLAN_SALES_FACT
ELSE 0
END AS PLAN_SALES_FACT
, CASE
WHEN (RMS.LOCK_F = 'Y'
AND CDF.INT_DATE > AMC_INT_DATE
) THEN ACTUAL_SALES_FACT
ELSE 0
END AS ACTUAL_SALES_FACT
, RMS.PUB_ZIP_FACT
, RMS.PUB_LOC_FACT
, CAST(( CAST(RMS.PUB_ZIP_FACT AS DECIMAL (20, 9)) * CAST(RMS.PUB_LOC_FACT AS DECIMAL (20, 9)) * CAST(PLAN_SALES_FACT AS DECIMAL (20, 9)) * CAST(RMS.MER_FUND_PLAN_FACT AS DECIMAL (20, 9)) ) AS DECIMAL (18, 9)) PLAN_PUB_ZIP_LOC_MER_SLS_FACT
, CAST(( CAST(RMS.PUB_ZIP_FACT AS DECIMAL (20, 9)) * CAST(RMS.PUB_LOC_FACT AS DECIMAL (20, 9)) * CAST(ACTUAL_SALES_FACT AS DECIMAL (20, 9)) * CAST(RMS.MER_FUND_FCST_FACT AS DECIMAL (20, 9)) ) AS DECIMAL (18, 9)) ACTL_PUB_ZIP_LOC_MER_SLS_FACT
, USER LAST_UPD_USER_ID
, CURRENT_TIMESTAMP LAST_UPD_TS
-- note I rearranged the tables in the from clause - adding current_date_fact first following by rop_rm_loc-mer_exp
FROM FADSTG.CURRENT_DATE_FACT CDF
, FADSTG.ROP_RM_LOC_MER_EXP_SALES RMS
INNER JOIN FADSTG.ROP_RM_MER_ALLOC_FACTOR RMF
ON RMS.AMC_YEAR = RMF.AMC_YEAR
AND RMS.AMC_PERIOD = RMF.AMC_PERIOD
AND RMS.LOC_NBR_DIV = RMF.DIVN_NBR