Reg: spool space error

General
Enthusiast

Reg: spool space error

Hi, i have a query where it exteracts week data from teradata, its been working fine for last 6 to 7 months but all of sudden it started giving an error "NO SPOOL SCACE ....". Please can any one help ne on this, i came to know that i need to optimize it but I don't know how to do. I am posting my query here ...

--TD_Parts_Dispatches_Weekly_New

select a.SVC_DSPCH_ID,a.SVC_BU_ID,a.SVC_ACTN_CD,a.CURR_STAT,a.DSPCH_CRT_DT,a.DSPCH_CRT_DTS,a.ORIG_ORD_BU_ID,a.LCL_CHNL_CD

,a.ORIG_ORD_NBR,a.ORIG_INV_DT,a.ASST_ID,a.SYS_ITM_CLS_CD,a.OWR_FLG,a.FIR_FLG,a.DOSD_FLG,a.VISIT_CNT,a.CRU_CMPLNC_FLG

,a.FRU_CMPLNC_FLG,a.CRU_ITM_CNT,a.FRU_ITM_CNT,a.MAJ_PART_CNT,a.SR_NBR,a.MDR_DSPCH_FLG,a.MDR_QUALIFY_FLG,a.MDR_LBR_DSPCH_FLG

,a.MDR_PART_DSPCH_QTY,a.SYS_EXCH_FLG,a.ONSITE_DSPCH_FLG,a.DUMMY_SVC_TAG_FLG,a.REPEAT_DSPCH_CRT_DTS,a.REPEAT_DSPCH_DEFECT_FLG

,a.REPEAT_DSPCH_QUALIFY_FLG,a.REPEAT_DSPCH_NBR,a.ASSOC_BDGE_NBR,a.NEW_PART_SHIP_FLG,a.EVER_CNCLLED_FLG,a.PROB_DESC,a.CUST_NBR

,a.SHIPD_AGE_DAYS,a.ITMS_SHIPD_CNT,a.WHOLE_UNIT_DSPCH_FLG,b.PROD_TYPE_DESC,b.LOB_DESC,b.PROD_LN_DESC

,c.ASSOC_FULL_NM,c.ASSOC_NTWK_LOGIN_NM,c.ASSOC_LOC_NM,c.ASSOC_LOC_CITY_NM,c.FRST_MGR_BDGE_NBR,c.SECND_MGR_BDGE_NBR

,c.FRST_MGR_FRST_NM,c.FRST_MGR_LAST_NM,c.SECND_MGR_FRST_NM,c.SECND_MGR_LAST_NM,c.BUS_RPTG_DEPT_NM,c.BUS_RPTG_GRP_NM,

c.BUS_RPTG_QUEUE_NM,c.BUS_RPTG_TEAM_NM,c.BUS_RPTG_SUBRGN_NM,c.BUS_RPTG_CNTCT_MTHD_NM,c.BUS_RPTG_CATG_NM,d.TYPE_DESC,

e.ITM_NBR,e.SVC_ORD_QTY

--,OReplace(d.CUST_TYPE_DESC,' ','') --will be use intead of Type_Desc

from

SVC_PKG.GBL_SVC_DSPCH_FACT as a

left join

SVC_PKG.GBL_SVC_DSPCH_ITM_FACT as e

on

a.SVC_DSPCH_ID = e.SVC_DSPCH_ID

left join

ITM_PKG.BASE_PROD_HIER_DIM as b  

on

a.SYS_ITM_CLS_CD = b.ITM_CLS_CODE and a.orig_ord_bu_id in (7777,2020,5858,11,1435,4747,3232,7878,1415,707,9595,8585,3737, 3696)

---a.tmzn_loc_id=2  -----for Brio EMEA

and DSPCH_CRT_DTS between (Current_Date - 11) and (Current_date - 5)

left join

PARTY_PKG.ASSOC_DIM as c

on

a.ASSOC_BDGE_NBR = c.ASSOC_BDGE_NBR and a.DSPCH_CRT_DTS between c.SRC_EFF_STRT_DT and c.SRC_EFF_END_DT

left join

COMN_PKG.CHNL_HIER_DIM as d

on a.LCL_CHNL_CD = d.LCL_CHNL_CODE and a.ORIG_ORD_BU_ID = d.BU_ID

Thanks,

Chaitanya

7 REPLIES
Senior Apprentice

Re: Reg: spool space error

Hi Chaitanya,

did you check *why* there was a no more spool space?

Is the processing skewed, did the plan change or was there just an increase of rows processed?

Are statistics up to date?

Could you post the DDL, stats info and Explain?

Dieter

Enthusiast

Re: Reg: spool space error

Hi Dieter,

First of all thanks for your response.

I don't have much experience with tera, i have checked with DBA's who are taking care of tera, they said there is no change in spool allocating to adhoc users. we don't have permissions on tables so i don't no how to check skew on a tabe, but i am sure that there is no change in plan as i said we have been using the same query for last 7 months. I don't no how to run stats, i think DBA's run stats, i will ask DBA's do that. Please let me know if i can reun stats even if I don't have permissions on tables.

I don't no how to check DDL on a query but I am attaching Explain here ....

Explanation

  1) First, we lock SVC_PKG_T.GBL_SVC_DSPCH_FACT for access, we lock

     SVC_PKG_T.GBL_SVC_DSPCH_ITM_FACT for access, we lock

     itm_pkg_t.BASE_PROD_HIER_DIM for access, we lock

     PARTY_PKG_T.ASSOC_DIM for access, and we lock

     CORP_DRM_PKG_T.CHNL_HIER for access.

  2) Next, we execute the following steps in parallel.

       1) We do an all-AMPs RETRIEVE step from

          SVC_PKG_T.GBL_SVC_DSPCH_FACT by way of an all-rows scan with

          a condition of (

          "((CAST((SVC_PKG_T.GBL_SVC_DSPCH_FACT.DSPCH_CRT_DTS) AS

          DATE))>= DATE '2013-06-22') AND

          (((CAST((SVC_PKG_T.GBL_SVC_DSPCH_FACT.DSPCH_CRT_DTS) AS

          DATE))<= DATE '2013-06-28') AND

          (((SVC_PKG_T.GBL_SVC_DSPCH_FACT.ORIG_ORD_BU_ID (FLOAT, FORMAT

          '-9.99999999999999E-999'))= 2.02000000000000E 003) OR

          (((SVC_PKG_T.GBL_SVC_DSPCH_FACT.ORIG_ORD_BU_ID (FLOAT, FORMAT

          '-9.99999999999999E-999'))= 7.77700000000000E 003) OR

          (((SVC_PKG_T.GBL_SVC_DSPCH_FACT.ORIG_ORD_BU_ID (FLOAT, FORMAT

          '-9.99999999999999E-999'))= 5.85800000000000E 003) OR

          (((SVC_PKG_T.GBL_SVC_DSPCH_FACT.ORIG_ORD_BU_ID (FLOAT, FORMAT

          '-9.99999999999999E-999'))= 1.10000000000000E 001) OR

          (((SVC_PKG_T.GBL_SVC_DSPCH_FACT.ORIG_ORD_BU_ID (FLOAT, FORMAT

          '-9.99999999999999E-999'))= 1.43500000000000E 003) OR

          (((SVC_PKG_T.GBL_SVC_DSPCH_FACT.ORIG_ORD_BU_ID (FLOAT, FORMAT

          '-9.99999999999999E-999'))= 4.74700000000000E 003) OR

          (((SVC_PKG_T.GBL_SVC_DSPCH_FACT.ORIG_ORD_BU_ID (FLOAT, FORMAT

          '-9.99999999999999E-999'))= 3.23200000000000E 003) OR

          (((SVC_PKG_T.GBL_SVC_DSPCH_FACT.ORIG_ORD_BU_ID (FLOAT, FORMAT

          '-9.99999999999999E-999'))= 7.87800000000000E 003) OR

          (((SVC_PKG_T.GBL_SVC_DSPCH_FACT.ORIG_ORD_BU_ID (FLOAT, FORMAT

          '-9.99999999999999E-999'))= 1.41500000000000E 003) OR

          (((SVC_PKG_T.GBL_SVC_DSPCH_FACT.ORIG_ORD_BU_ID (FLOAT, FORMAT

          '-9.99999999999999E-999'))= 7.07000000000000E 002) OR

          (((SVC_PKG_T.GBL_SVC_DSPCH_FACT.ORIG_ORD_BU_ID (FLOAT, FORMAT

          '-9.99999999999999E-999'))= 9.59500000000000E 003) OR

          (((SVC_PKG_T.GBL_SVC_DSPCH_FACT.ORIG_ORD_BU_ID (FLOAT, FORMAT

          '-9.99999999999999E-999'))= 8.58500000000000E 003) OR

          (((SVC_PKG_T.GBL_SVC_DSPCH_FACT.ORIG_ORD_BU_ID (FLOAT, FORMAT

          '-9.99999999999999E-999'))= 3.73700000000000E 003) OR

          ((SVC_PKG_T.GBL_SVC_DSPCH_FACT.ORIG_ORD_BU_ID (FLOAT, FORMAT

          '-9.99999999999999E-999'))= 3.69600000000000E 003

          )))))))))))))))") into Spool 13 (all_amps) (compressed

          columns allowed), which is built locally on the AMPs.  Then

          we do a SORT to order Spool 13 by the hash code of (

          SVC_PKG_T.GBL_SVC_DSPCH_FACT.LCL_CHNL_CD,

          SVC_PKG_T.GBL_SVC_DSPCH_FACT.ORIG_ORD_BU_ID (FLOAT, FORMAT

          '-9.99999999999999E-999')(FLOAT)).  The size of Spool 13 is

          estimated with no confidence to be 7,723,607 rows (

          3,359,769,045 bytes).  The estimated time for this step is

          1.94 seconds.

       2) We do an all-AMPs RETRIEVE step from CORP_DRM_PKG_T.CHNL_HIER

          by way of an all-rows scan with no residual conditions into

          Spool 14 (all_amps) (compressed columns allowed), which is

          duplicated on all AMPs.  Then we do a SORT to order Spool 14

          by the hash code of (CORP_DRM_PKG_T.CHNL_HIER.BU_ID (INTEGER)

          (FLOAT), TRANSLATE((CORP_DRM_PKG_T.CHNL_HIER.LCL_CHNL_CODE

          )USING LATIN_TO_UNICODE)(CHAR(5), CHARACTER SET UNICODE, NOT

          CASESPECIFIC)).  The size of Spool 14 is estimated with high

          confidence to be 6,002,880 rows (672,322,560 bytes).  The

          estimated time for this step is 0.46 seconds.

  3) We execute the following steps in parallel.

       1) We do an all-AMPs JOIN step from Spool 13 (Last Use) by way

          of a RowHash match scan, which is joined to Spool 14 (Last

          Use) by way of a RowHash match scan.  Spool 13 and Spool 14

          are left outer joined using a merge join, with a join

          condition of ("((ORIG_ORD_BU_ID (FLOAT, FORMAT

          '-9.99999999999999E-999'))= (BU_ID (INTEGER) )) AND

          (LCL_CHNL_CD = (TRANSLATE((LCL_CHNL_CODE )USING

          LATIN_TO_UNICODE)))").  The result goes into Spool 15

          (all_amps) (compressed columns allowed), which is built

          locally on the AMPs.  Then we do a SORT to order Spool 15 by

          the hash code of (

          SVC_PKG_T.GBL_SVC_DSPCH_FACT.SYS_ITM_CLS_CD).  The size of

          Spool 15 is estimated with no confidence to be 7,723,607 rows

          (4,008,552,033 bytes).  The estimated time for this step is

          0.44 seconds.

       2) We do an all-AMPs RETRIEVE step from

          itm_pkg_t.BASE_PROD_HIER_DIM by way of an all-rows scan with

          no residual conditions into Spool 16 (all_amps) (compressed

          columns allowed), which is duplicated on all AMPs.  Then we

          do a SORT to order Spool 16 by the hash code of (

          TRANSLATE((itm_pkg_t.BASE_PROD_HIER_DIM.ITM_CLS_CODE )USING

          LATIN_TO_UNICODE)(CHAR(5), CHARACTER SET UNICODE, NOT

          CASESPECIFIC)).  The size of Spool 16 is estimated with high

          confidence to be 18,408,000 rows (1,399,008,000 bytes).  The

          estimated time for this step is 0.72 seconds.

  4) We execute the following steps in parallel.

       1) We do an all-AMPs JOIN step from Spool 15 (Last Use) by way

          of a RowHash match scan, which is joined to Spool 16 (Last

          Use) by way of a RowHash match scan.  Spool 15 and Spool 16

          are left outer joined using a merge join, with a join

          condition of ("SYS_ITM_CLS_CD = (TRANSLATE((ITM_CLS_CODE

          )USING LATIN_TO_UNICODE))").  The result goes into Spool 17

          (all_amps) (compressed columns allowed), which is

          redistributed by the hash code of (

          SVC_PKG_T.GBL_SVC_DSPCH_FACT.SVC_DSPCH_ID) to all AMPs.  Then

          we do a SORT to order Spool 17 by row hash.  The size of

          Spool 17 is estimated with no confidence to be 15,428,039

          rows (8,917,406,542 bytes).  The estimated time for this step

          is 4.13 seconds.

       2) We do an all-AMPs RETRIEVE step from

          SVC_PKG_T.GBL_SVC_DSPCH_ITM_FACT by way of an all-rows scan

          with no residual conditions into Spool 18 (all_amps)

          (compressed columns allowed), which is redistributed by the

          hash code of (SVC_PKG_T.GBL_SVC_DSPCH_ITM_FACT.SVC_DSPCH_ID)

          to all AMPs.  Then we do a SORT to order Spool 18 by row hash.

          The size of Spool 18 is estimated with high confidence to be

          73,554,636 rows (3,457,067,892 bytes).  The estimated time

          for this step is 2.95 seconds.

  5) We do an all-AMPs JOIN step from Spool 17 (Last Use) by way of a

     RowHash match scan, which is joined to Spool 18 (Last Use) by way

     of a RowHash match scan.  Spool 17 and Spool 18 are left outer

     joined using a merge join, with a join condition of (

     "SVC_DSPCH_ID = SVC_DSPCH_ID").  The result goes into Spool 19

     (all_amps) (compressed columns allowed), which is redistributed by

     the hash code of (SVC_PKG_T.GBL_SVC_DSPCH_FACT.ASSOC_BDGE_NBR) to

     all AMPs.  Then we do a SORT to order Spool 19 by row hash.  The

     size of Spool 19 is estimated with no confidence to be 146,926,662

     rows (87,274,437,228 bytes).  The estimated time for this step is

     38.89 seconds.

  6) We do an all-AMPs JOIN step from PARTY_PKG_T.ASSOC_DIM by way of a

     RowHash match scan with no residual conditions, which is joined to

     Spool 19 (Last Use) by way of a RowHash match scan.

     PARTY_PKG_T.ASSOC_DIM and Spool 19 are right outer joined using a

     merge join, with condition(s) used for non-matching on right table

     ("NOT (ASSOC_BDGE_NBR IS NULL)"), with a join condition of (

     "((CAST(({RightTable}.DSPCH_CRT_DTS) AS DATE))<=

     PARTY_PKG_T.ASSOC_DIM.SRC_EFF_END_DT) AND

     (((CAST(({RightTable}.DSPCH_CRT_DTS) AS DATE))>=

     PARTY_PKG_T.ASSOC_DIM.SRC_EFF_STRT_DT) AND (ASSOC_BDGE_NBR =

     PARTY_PKG_T.ASSOC_DIM.ASSOC_BDGE_NBR ))").  The result goes into

     Spool 12 (group_amps), which is built locally on the AMPs.  The

     result spool file will not be cached in memory.  The size of Spool

     12 is estimated with no confidence to be 3,289,626,154 rows (

     2,835,657,744,748 bytes).  The estimated time for this step is 2

     minutes and 18 seconds.

  7) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> The contents of Spool 12 are sent back to the user as the result

     of statement 1.  The total estimated time is 3 minutes and 4

     seconds.

Thanks,

Chaitanys.

Senior Apprentice

Re: Reg: spool space error

Hi Chaitanya,

you get the DDL of all tables  using a SHOW before the SELECT and stats info is returned by "HELP STATS tablename".

According to explain there are CASTs on bi_id columns due to wrong datatypes:

SVC_PKG_T.GBL_SVC_DSPCH_FACT.ORIG_ORD_BU_ID (FLOAT, FORMAT

          '-9.99999999999999E-999'))= 2.02000000000000E 003)

This results in higher CPU usage and stats are lost, you better rewrite using the correct datatype, which seems to be a varchar:

a.orig_ord_bu_id in ('7777','2020','5858','11','1435','4747','3232','7878','1415','707','9595','8585','3737', '3696')

Additionally there are some TRANSLATEs indicating different character sets (LATIN/UNICODE) on your join columns, which is a problem in your data model :-)

Dieter

Enthusiast

Re: Reg: spool space error

hI Dieter,

here i have runned HELP STATS and here are the results,

Date    Time    Unique Values    Column Names

13/07/02    22:25:36               9,709,547    SVC_DSPCH_ID

13/07/02    22:25:45                   3,979    DSPCH_CRT_DT

13/07/02    22:26:07               8,066,375    DSPCH_CRT_DTS

13/07/02    22:26:28               1,603,438    CUST_NBR

13/07/02    22:26:51               5,006,659    ASST_ID

13/07/02    22:27:17               4,711,121    SR_NBR

13/07/02    22:27:24               9,709,554    SVC_DSPCH_ID,SVC_BU_ID

13/07/02    22:27:33                  33,406    CUST_BU_ID,CUST_LCL_CHNL_CD

13/07/02    22:27:36               6,810,682    SVC_CALL_TYPE_CD,TMZN_LOC_ID

13/07/02    22:27:39                       8    MDR_DSPCH_FLG,TMZN_LOC_ID

13/07/02    22:27:59               3,042,104    DSPCH_CRT_DT,ASSOC_BDGE_NBR

13/07/02    22:28:14                  38,521    DSPCH_CRT_DT,WRNTY_PROD_OFFRG_CD

what i understood from this is stats have not runed of this table from longs, please correct me if am wrong.

and am trying to implement your idea in query.

Thanks,

Chaitanya

Teradata Employee

Re: Reg: spool space error

The date is (20)13-07-02 (02 July 2013), so not that long.

But as Dieter noted, the type mismatches and character set TRANSLATEs can result in stats not being used, even if they are current.

What about the DDL (SHOW SELECT ...)?

Enthusiast

Re: Reg: spool space error

Hi Fred,

sorry for late replay ... it was because, we are adhoc users for the teradata...

I have runned SHOW on my query...

--TD_Parts_Dispatches_Weekly_New

show select a.SVC_DSPCH_ID,a.SVC_BU_ID,a.SVC_ACTN_CD,a.CURR_STAT,a.DSPCH_CRT_DT,a.DSPCH_CRT_DTS,a.ORIG_ORD_BU_ID,a.LCL_CHNL_CD

,a.ORIG_ORD_NBR,a.ORIG_INV_DT,a.ASST_ID,a.SYS_ITM_CLS_CD,a.OWR_FLG,a.FIR_FLG,a.DOSD_FLG,a.VISIT_CNT,a.CRU_CMPLNC_FLG

,a.FRU_CMPLNC_FLG,a.CRU_ITM_CNT,a.FRU_ITM_CNT,a.MAJ_PART_CNT,a.SR_NBR,a.MDR_DSPCH_FLG,a.MDR_QUALIFY_FLG,a.MDR_LBR_DSPCH_FLG

,a.MDR_PART_DSPCH_QTY,a.SYS_EXCH_FLG,a.ONSITE_DSPCH_FLG,a.DUMMY_SVC_TAG_FLG,a.REPEAT_DSPCH_CRT_DTS,a.REPEAT_DSPCH_DEFECT_FLG

,a.REPEAT_DSPCH_QUALIFY_FLG,a.REPEAT_DSPCH_NBR,a.ASSOC_BDGE_NBR,a.NEW_PART_SHIP_FLG,a.EVER_CNCLLED_FLG,a.PROB_DESC,a.CUST_NBR

,a.SHIPD_AGE_DAYS,a.ITMS_SHIPD_CNT,a.WHOLE_UNIT_DSPCH_FLG,b.PROD_TYPE_DESC,b.LOB_DESC,b.PROD_LN_DESC

,c.ASSOC_FULL_NM,c.ASSOC_NTWK_LOGIN_NM,c.ASSOC_LOC_NM,c.ASSOC_LOC_CITY_NM,c.FRST_MGR_BDGE_NBR,c.SECND_MGR_BDGE_NBR

,c.FRST_MGR_FRST_NM,c.FRST_MGR_LAST_NM,c.SECND_MGR_FRST_NM,c.SECND_MGR_LAST_NM,c.BUS_RPTG_DEPT_NM,c.BUS_RPTG_GRP_NM,

c.BUS_RPTG_QUEUE_NM,c.BUS_RPTG_TEAM_NM,c.BUS_RPTG_SUBRGN_NM,c.BUS_RPTG_CNTCT_MTHD_NM,c.BUS_RPTG_CATG_NM,d.TYPE_DESC,

e.ITM_NBR,e.SVC_ORD_QTY

--,OReplace(d.CUST_TYPE_DESC,'    ','') --will be use intead of Type_Desc

 from

SVC_PKG.GBL_SVC_DSPCH_FACT a  

---a.tmzn_loc_id=2  -----for Brio EMEA

left join

SVC_PKG.GBL_SVC_DSPCH_ITM_FACT as e

on

a.SVC_DSPCH_ID = e.SVC_DSPCH_ID and a.orig_ord_bu_id in (7777,2020,5858,11,1435,4747,3232,7878,1415,707,9595,8585,3737, 3696) and DSPCH_CRT_DTS between (Current_Date - 10) and (Current_date - 4)

left join

 ITM_PKG.BASE_PROD_HIER_DIM as b

on

a.SYS_ITM_CLS_CD = b.ITM_CLS_CODE

left join

PARTY_PKG.ASSOC_DIM as c

on

a.ASSOC_BDGE_NBR = c.ASSOC_BDGE_NBR  and a.DSPCH_CRT_DTS between c.SRC_EFF_STRT_DT and c.SRC_EFF_END_DT

left join

COMN_PKG.CHNL_HIER_DIM as d

on

a.LCL_CHNL_CD = d.LCL_CHNL_CODE and a.ORIG_ORD_BU_ID = d.BU_ID

CREATE MULTISET TABLE SVC_PKG_T.GBL_SVC_DSPCH_FACT ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      SVC_DSPCH_ID VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      SVC_BU_ID VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      SVC_DSPCH_BU_ID VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,

      SVC_ACTN_CD VARCHAR(30) CHARACTER SET UNICODE NOT CASESPECIFIC,

      SVC_CALL_TYPE_CD VARCHAR(30) CHARACTER SET UNICODE NOT CASESPECIFIC,

      CURR_STAT VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      DSPCH_CRT_DT DATE FORMAT 'yyyy-mm-dd',

      DSPCH_CRT_DTS TIMESTAMP(6),

      CUST_BU_ID INTEGER COMPRESS 0 ,

      CUST_NBR VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      CUST_LCL_CHNL_CD CHAR(5) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ('CI   ','DHS  ','GI   ','HI   ','ICM  ','ICP  ','ICS  ','IEL  ','IES  ','IRS  ','IRT  ','PAD  ','PUB  ','TECH ','TRANS'),

      ORIG_ORD_BU_ID VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,

      LCL_CHNL_CD CHAR(5) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ('CI   ','DHS  ','GI   ','HI   ','ICM  ','ICP  ','ICS  ','IEL  ','IES  ','IRS  ','IRT  ','PAD  ','PUB  ','TECH ','TRANS'),

      ORIG_ORD_NBR VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      ORIG_INV_DTS TIMESTAMP(6),

      ORIG_INV_DT DATE FORMAT 'yyyy-mm-dd',

      ASST_ID VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      SYS_ITM_CLS_CD CHAR(5) CHARACTER SET UNICODE NOT CASESPECIFIC,

      SHIPD_AGE_DAYS INTEGER,

      SYS_AGE_DAYS INTEGER,

      SR_PROD_OFFRG_CD VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,

      WRNTY_PROD_OFFRG_CD VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,

      WRNTY_UPSELL_FLG BYTEINT COMPRESS (0 ,1 ),

      OWR_FLG BYTEINT COMPRESS (0 ,1 ),

      FIR_FLG BYTEINT COMPRESS (0 ,1 ),

      DOSD_FLG BYTEINT COMPRESS (0 ,1 ),

      GBL_PRVDR_ID VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

      FRST_EXCPTN_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      LAST_EXCPTN_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      VISIT_CNT INTEGER COMPRESS 0 ,

      LBR_COST_USD_AMT DECIMAL(18,3),

      LGST_COST_USD_AMT DECIMAL(18,3),

      B2C_FLG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),

      B2D_FLG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),

      BILL_REF_DESC VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      FSD_BILT_TYPE_CD VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      CRU_CMPLNC_FLG BYTEINT COMPRESS (0 ,1 ),

      FRU_CMPLNC_FLG BYTEINT COMPRESS (0 ,1 ),

      CFI_ITM_CNT INTEGER,

      PFR_ITM_CNT INTEGER,

      CRU_ITM_CNT INTEGER,

      FRU_ITM_CNT INTEGER,

      EHD_ITM_CNT INTEGER,

      SRCD_ITM_CNT INTEGER,

      ITMS_BKD_CNT INTEGER,

      ITMS_SHIPD_CNT INTEGER,

      HDD_SFTWR_ITM_CNT INTEGER,

      MAJ_PART_CNT INTEGER,

      ITM_COST_USD_AMT DECIMAL(18,3),

      PART_PRC_USD_AMT DECIMAL(18,3),

      PROB_DESC VARCHAR(250) CHARACTER SET UNICODE NOT CASESPECIFIC,

      RSLTN_CD VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,

      RSLTN_DESC VARCHAR(150) CHARACTER SET UNICODE NOT CASESPECIFIC,

      SR_NBR VARCHAR(64) CHARACTER SET LATIN NOT CASESPECIFIC,

      MDR_DSPCH_FLG BYTEINT COMPRESS (0 ,1 ),

      MDR_QUALIFY_FLG BYTEINT COMPRESS (0 ,1 ),

      MDR_LBR_DSPCH_FLG BYTEINT COMPRESS (0 ,1 ),

      MDR_PART_DSPCH_QTY INTEGER COMPRESS 0 ,

      SHRT_ORD_MDR_FLG BYTEINT COMPRESS (0 ,1 ),

      SYS_EXCH_FLG BYTEINT COMPRESS (0 ,1 ),

      USED_EXCH_MDR_FLG BYTEINT COMPRESS (0 ,1 ),

      LOB_USED_EXCH_EXCPTN_FLG BYTEINT COMPRESS (0 ,1 ),

      WHOLE_UNIT_DSPCH_FLG BYTEINT COMPRESS (0 ,1 ),

      NEW_PART_SHIP_CNT INTEGER,

      ONSITE_DSPCH_FLG BYTEINT COMPRESS (0 ,1 ),

      EVER_CNCLLED_FLG BYTEINT COMPRESS (0 ,1 ),

      CNCL_SCC_FLG BYTEINT COMPRESS (0 ,1 ),

      CNCL_SHP_FLG BYTEINT COMPRESS (0 ,1 ),

      DUMMY_SVC_TAG_FLG BYTEINT COMPRESS (0 ,1 ),

      REPEAT_DSPCH_CRT_DTS TIMESTAMP(6),

      REPEAT_DSPCH_DEFECT_FLG BYTEINT COMPRESS (0 ,1 ),

      REPEAT_DSPCH_QUALIFY_FLG BYTEINT COMPRESS (0 ,1 ),

      REPEAT_DSPCH_NBR VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      SECND_REPEAT_DSPCH_CRT_DTS TIMESTAMP(6),

      SECND_REPEAT_DSPCH_NBR VARCHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC,

      SECND_REPEAT_TM_GAP_SECND INTEGER,

      DW_LD_GRP_VAL DECIMAL(18,2) NOT NULL COMPRESS 0.00 ,

      DW_INS_UPD_DTS TIMESTAMP(6) NOT NULL,

      DW_SRC_SITE_ID SMALLINT NOT NULL COMPRESS (1 ,2 ,3 ,4 ),

      DW_SRC_EXTRC_DTS TIMESTAMP(6),

      DSPCH_SLA_CD VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

      CUST_LINK_NBR INTEGER,

      CUST_LINK_NBR_DESC VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      CUST_LINK_NBR_STAT CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,

      SVC_PRVDR_TECH_ID VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      ALT_CNTCT_NM VARCHAR(200) CHARACTER SET UNICODE NOT CASESPECIFIC,

      ALT_CNTCT_PH_NBR VARCHAR(80) CHARACTER SET LATIN NOT CASESPECIFIC,

      ASSOC_BDGE_NBR VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      DSPCH_APPRVD_DTS TIMESTAMP(6),

      TMZN_LOC_ID SMALLINT NOT NULL COMPRESS (1 ,2 ,3 ,4 ),

      CUST_NM VARCHAR(250) CHARACTER SET UNICODE NOT CASESPECIFIC,

      SVC_CITY_NM VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC,

      SVC_ST_NM VARCHAR(50) CHARACTER SET UNICODE NOT CASESPECIFIC,

      SVC_PSTL_CD VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC,

      SVC_CTRY_NM VARCHAR(30) CHARACTER SET UNICODE NOT CASESPECIFIC,

      FSD_OWR_FLG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),

      DSPCH_AWAIT_ACK_STAT_DTS TIMESTAMP(6),

      DSPCH_ACK_STAT_DTS TIMESTAMP(6),

      DSPCH_QUEUE_STAT_DTS TIMESTAMP(6),

      DSPCH_APPRVD_BY_BDGE_NBR VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      NEW_PART_SHIP_FLG BYTEINT COMPRESS (0 ,1 ),

      RSN_CD VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

      ONSITE_VISIT_CNT SMALLINT,

      X_ISP_CMPLTCARE_FLG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),

      X_ISP_KYHD_FLG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),

      ACTVY_ID VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,

      X_ISP_CFI_FLG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),

      SRC_PRSN_HIST_GEN_ID INTEGER,

      ASST_DSPCH_CNT INTEGER,

      ITM_USED_COST_USD_AMT DECIMAL(18,3))

PRIMARY INDEX NUPI_GBL_SVC_DSPCH_FACT ( SVC_DSPCH_ID ,SVC_BU_ID );

CREATE MULTISET TABLE SVC_PKG_T.GBL_SVC_DSPCH_ITM_FACT ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      SVC_DSPCH_ID VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      SVC_BU_ID VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      ITM_NBR VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      FSD_PART_ORD_ID VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      PART_ORD_LN_NBR INTEGER COMPRESS 0 ,

      ORD_BU_ID VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,

      ORD_NBR VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      CCN_NBR VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      ORD_STAT_CD CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('BO   ','CL   ','HL   ','IN   ','IP   ','PD   ','PI   ','PP   ','RJ   ','SC   ','WF   '),

      ORD_CRT_DTS TIMESTAMP(6),

      ORD_CRT_DT DATE FORMAT 'YYYY-MM-DD',

      INV_DTS TIMESTAMP(6),

      INV_DT DATE FORMAT 'YYYY-MM-DD',

      SHIP_DTS TIMESTAMP(6),

      SHIP_DT DATE FORMAT 'YYYY-MM-DD',

      SVC_ORD_QTY INTEGER COMPRESS 0 ,

      ORD_ITM_SHIP_QTY INTEGER COMPRESS 0 ,

      SUBSTT_ITM_NBR VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      SHIP_ITM_NBR VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      MFG_ITM_NBR VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      ITM_CLS_CD CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,

      LGST_PRVDR_ID CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('184       ','202       ','251       ','28UPS     '),

      NEW_PART_FLG BYTEINT COMPRESS (0 ,1 ),

      SVC_RTN_RCVD_DT DATE FORMAT 'YYYY-MM-DD' COMPRESS ,

      DW_LD_GRP_VAL DECIMAL(18,2) NOT NULL COMPRESS 0.00 ,

      DW_SRC_SITE_ID SMALLINT NOT NULL COMPRESS (1041 ,1042 ,20 ,1046 ,3018 ),

      DW_INS_UPD_DTS TIMESTAMP(6) NOT NULL,

      DW_SRC_EXTRC_DTS TIMESTAMP(6) NOT NULL,

      SYS_FLG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','U','Y'),

      ORD_RTN_QTY INTEGER COMPRESS 0 ,

      TMZN_LOC_ID SMALLINT NOT NULL COMPRESS (1 ,2 ,3 ,4 ),

      MOST_XPSV_COMDTY_FLG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC)

PRIMARY INDEX NUPI_SVC_DSPCH_ITM_FACT ( SVC_DSPCH_ID ,SVC_BU_ID )

INDEX NUSI_ITM_NBR ( ITM_NBR )

INDEX NUSI_CCN_NBR ( CCN_NBR );

CREATE MULTISET TABLE itm_pkg_t.BASE_PROD_HIER_DIM ,FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      ITM_CLS_CODE VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      ITM_CLS_DESC VARCHAR(250) CHARACTER SET LATIN NOT CASESPECIFIC,

      CHSS_CODE VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,

      CHSS_DESC VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      PROD_ROLLUP_CODE VARCHAR(45) CHARACTER SET LATIN NOT CASESPECIFIC,

      PROD_ROLLUP_DESC VARCHAR(150) CHARACTER SET LATIN NOT CASESPECIFIC,

      OEM_FLAG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,

      SYS_FLAG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,

      ACT_BASE_PROD_FLAG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,

      PROD_TYPE_CODE VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,

      PROD_TYPE_DESC VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      PROD_GRP_CODE VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,

      PROD_GRP_DESC VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      LOB_CODE VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,

      LOB_DESC VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      PROD_LN_CODE VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,

      PROD_LN_DESC VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      FMLY_PARNT_CODE VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      FMLY_PARNT_DESC VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      FMLY_CODE VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,

      FMLY_DESC VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC FORMAT 'X(30)',

      BASE_CODE VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,

      BASE_DESC VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      ORCL_PROD_CODE VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,

      HPF_PARNT_CODE VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

      EXTRNL_RPTG_LOB_ABBR_CODE VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,

      EXTRNL_RPTG_LOB_CODE VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      PRCSR_CODE VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      PRCSR_DESC VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      PRCSR_PARNT_CODE VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      PRCSR_PARNT_DESC VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      PRCSR_SPEED_NUM INTEGER,

      SCRN_SIZE_NUM_DESC VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      SCRN_TYPE_DESC VARCHAR(150) CHARACTER SET LATIN NOT CASESPECIFIC,

      SERIES_DESC VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      DW_BASE_EXTRC_DTTM TIMESTAMP(6) NOT NULL,

      DW_PKG_IUD_CODE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ('D','I','U'),

      DW_IUD_ACCT_NAME VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      DW_PKG_INS_DTTM TIMESTAMP(6) NOT NULL,

      DW_PKG_UPD_DTTM TIMESTAMP(6) NOT NULL,

      LD_SEQ_NUM DECIMAL(16,2) NOT NULL,

      SCM_LOB_CD INTEGER COMPRESS (0 ,5 ,20 ,21 ,30 ,35 ,40 ,41 ,42 ,43 ,45 ,46 ,47 ,49 ,55 ,57 ,58 ,60 ,65 ,70 ,75 ,82 ,85 ,90 ))

PRIMARY INDEX XPKBASE_PROD_HIER_DIM ( ITM_CLS_CODE );

CREATE MULTISET TABLE PARTY_PKG_T.ASSOC_DIM ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      ASSOC_BDGE_NBR VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      SRC_EFF_STRT_DT DATE FORMAT 'yyyy-mm-dd' NOT NULL,

      SRC_EFF_END_DT DATE FORMAT 'yyyy-mm-dd' NOT NULL COMPRESS (DATE '9999-12-31'),

      ASSOC_FRST_NM VARCHAR(80) CHARACTER SET UNICODE NOT CASESPECIFIC,

      ASSOC_MDL_INIT CHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

      ASSOC_LAST_NM VARCHAR(50) CHARACTER SET UNICODE NOT CASESPECIFIC,

      ASSOC_FULL_NM VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC,

      ASSOC_PRFRD_NM VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC,

      ASSOC_STAT_ID SMALLINT COMPRESS (1 ,2 ,3 ,4 ,5 ,6 ),

      ASSOC_STAT_NM CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('Active                        ','No Record Available           ','Terminated                    ','Unknown                       '),

      ASSOC_TYPE_ID SMALLINT COMPRESS (2 ,3 ,4 ,6 ,11 ),

      ASSOC_TYPE_NM CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('Contractor                    ','Regular                       ','Temporary                     ','Unknown                       ','Vendor                        '),

      ASSOC_FRST_HIRE_DT DATE FORMAT 'yyyy-mm-dd' COMPRESS (DATE '9999-12-31'),

      ASSOC_LAST_HIRE_DT DATE FORMAT 'yyyy-mm-dd' COMPRESS (DATE '9999-12-31'),

      ASSOC_EMAIL_ADDR VARCHAR(150) CHARACTER SET LATIN NOT CASESPECIFIC,

      ASSOC_NTWK_LOGIN_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      ASSOC_NTWK_DOMAIN_NM CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('AMERICAS            ','ASIA-PACIFIC        ','EUROPE              ','JAPAN               ','UNKN                '),

      ASSOC_PH_NBR VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC,

      ASSOC_PAGER_NBR VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC,

      SRC_PRSN_HIST_ID INTEGER NOT NULL,

      SRC_PRSN_HIST_GEN_ID INTEGER NOT NULL,

      SRC_PRSN_ID INTEGER NOT NULL,

      ASSOC_PTNR_ID SMALLINT NOT NULL,

      ASSOC_PTNR_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      ASSOC_PTNR_OSP_FLG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),

      ASSOC_LOC_ID SMALLINT NOT NULL COMPRESS (1 ,2 ,3 ,4 ,5 ,6 ),

      ASSOC_LOC_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      ASSOC_LOC_CITY_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      ASSOC_LOC_US_ST_CD CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ('TN','TX','ZZ'),

      ASSOC_LOC_CTRY_CD CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('CA','CN','IE','IN','MY','PH','US','ZZ'),

      ASSOC_LOC_TMZN_ID SMALLINT NOT NULL COMPRESS (1 ,2 ,17 ,19 ,20 ,29 ,51 ,63 ,68 ),

      ASSOC_LOC_TMZN_NM VARCHAR(60) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      PRPHRL_ID INTEGER COMPRESS ,

      PRPHRL_CD VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

      ASSOC_PART_TM_FLG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),

      PH_EXT_ACD_LOGIN_ID CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

      DPS_LOGIN1_ID INTEGER COMPRESS ,

      DPS_LOGIN2_ID INTEGER COMPRESS ,

      DOMS_LOGIN_ID INTEGER COMPRESS ,

      NET_AGNT_LOGIN_ID INTEGER COMPRESS ,

      KANA_USER_LOGIN_ID INTEGER COMPRESS ,

      SKILL_TRGT_ID INTEGER COMPRESS ,

      FRST_MGR_BDGE_NBR VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,

      FRST_MGR_FRST_NM VARCHAR(80) CHARACTER SET UNICODE NOT CASESPECIFIC,

      FRST_MGR_LAST_NM VARCHAR(50) CHARACTER SET UNICODE NOT CASESPECIFIC,

      SECND_MGR_BDGE_NBR VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,

      SECND_MGR_FRST_NM VARCHAR(80) CHARACTER SET UNICODE NOT CASESPECIFIC,

      SECND_MGR_LAST_NM VARCHAR(50) CHARACTER SET UNICODE NOT CASESPECIFIC,

      BUS_RPT_FLG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),

      HR_FLG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),

      SRC_UPDT_DTS TIMESTAMP(6),

      SRC_UPDT_USER_NM VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      DW_SRC_SITE_ID INTEGER NOT NULL COMPRESS (3001 ,3033 ),

      DW_PKG_INS_UPD_DTS TIMESTAMP(6) NOT NULL,

      DW_LD_GRP_VAL DECIMAL(18,2),

      JOB_FUNC_CD VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,

      JOB_FUNC_DESC VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      BUS_RPTG_CATG_ID INTEGER COMPRESS (2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ),

      BUS_RPTG_CATG_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      BUS_RPTG_CNTCT_MTHD_ID INTEGER COMPRESS (2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ),

      BUS_RPTG_CNTCT_MTHD_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      BUS_RPTG_DEPT_ID INTEGER COMPRESS (2 ,580 ,346 ,359 ,364 ,643 ,442 ,760 ),

      BUS_RPTG_DEPT_NM VARCHAR(50) CHARACTER SET UNICODE NOT CASESPECIFIC,

      BUS_RPTG_EWFM_DEPT_CD VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

      BUS_RPTG_FUNC_CLS_ID INTEGER COMPRESS (2 ,6 ,7 ,8 ,9 ,10 ,11 ,13 ,14 ),

      BUS_RPTG_FUNC_CLS_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      BUS_RPTG_FUNC_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      BUS_RPTG_FUNC_NM_ID INTEGER COMPRESS (2 ,3 ,531 ,532 ,2111 ,2141 ,735 ,490 ,767 ),

      BUS_RPTG_FUNC_TYPE_ID INTEGER COMPRESS (2 ,3 ,24 ,26 ,66 ,71 ,72 ,74 ,75 ),

      BUS_RPTG_FUNC_TYPE_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      BUS_RPTG_GRP_ID INTEGER COMPRESS (2 ,6 ,8 ,11 ,12 ,15 ,27 ,28 ,65 ),

      BUS_RPTG_GRP_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      BUS_RPTG_QUEUE_CD VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

      BUS_RPTG_QUEUE_DESC VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      BUS_RPTG_QUEUE_ID INTEGER COMPRESS (2 ,1626 ,1683 ,1181 ,1186 ,3290 ,1247 ,737 ),

      BUS_RPTG_QUEUE_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      BUS_RPTG_QUEUE_STAT_DESC VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      BUS_RPTG_RGN_ID INTEGER COMPRESS (2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ),

      BUS_RPTG_RGN_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      BUS_RPTG_SUBGRP_ID INTEGER COMPRESS (2 ,5 ,9 ,11 ,14 ,28 ,57 ),

      BUS_RPTG_SUBGRP_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      BUS_RPTG_SUBRGN_ID INTEGER COMPRESS (2 ,3 ,14 ,16 ,19 ),

      BUS_RPTG_SUBRGN_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      BUS_RPTG_TEAM_CD VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

      BUS_RPTG_TEAM_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      PAID_ID_BY_CC_NBR VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

      CTRY_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      DOMS_LOGIN2 INTEGER COMPRESS ,

      DOMS_LOGIN3 INTEGER COMPRESS ,

      TRGT_ORDS_PER_HOUR_NBR DECIMAL(6,2),

      PAIDBY_CC_NM VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC,

      PAIDBY_CC_RGN VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

      PAIDBY_CC_SUB_RGN VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      PAIDBY_CC_AREA VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      PAIDBY_CC_BUS_CLS VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      PAIDBY_CC_BUS_GRP VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      PAIDBY_CC_SEG VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      PAIDBY_CC_FUNC VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      HR_JOB_FMLY_ID INTEGER,

      HR_JOB_FMLY_CD VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

      HR_JOB_FMLY_DESC VARCHAR(60) CHARACTER SET LATIN NOT CASESPECIFIC,

      HR_JOB_FUNC_ID INTEGER,

      HR_STAT_CD VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      HR_TYPE VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      HR_PRSN_ID VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,

      HR_PAIDBY_CC_ID INTEGER,

      BUS_FRST_MGR_BDGE_NBR VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,

      BUS_FRST_MGR_FRST_NM VARCHAR(80) CHARACTER SET LATIN NOT CASESPECIFIC,

      BUS_FRST_MGR_LAST_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      BUS_SECND_MGR_BDGE_NBR VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,

      BUS_SECND_MGR_FRST_NM VARCHAR(80) CHARACTER SET LATIN NOT CASESPECIFIC,

      BUS_SECND_MGR_LAST_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC)

PRIMARY INDEX NUPI_ASSOC_DIM ( ASSOC_BDGE_NBR );

CREATE MULTISET TABLE CORP_DRM_PKG_T.CHNL_HIER ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      BU_ID DECIMAL(15,0) NOT NULL,

      LCL_CHNL_CODE CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      EFF_STRT_DTTM TIMESTAMP(6) NOT NULL,

      TYPE_CODE VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      TYPE_DESC VARCHAR(250) CHARACTER SET LATIN NOT CASESPECIFIC,

      TYPE_ABBR VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,

      SLS_MTHD_CODE VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      SLS_MTHD_DESC VARCHAR(250) CHARACTER SET LATIN NOT CASESPECIFIC,

      SLS_MTHD_ABBR VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,

      CUST_TYPE_CODE VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      CUST_TYPE_DESC VARCHAR(250) CHARACTER SET LATIN NOT CASESPECIFIC,

      CUST_TYPE_ABBR VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,

      SEG_CODE VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      SEG_DESC VARCHAR(250) CHARACTER SET LATIN NOT CASESPECIFIC,

      SEG_ABBR VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,

      VERT_CODE VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      VERT_DESC VARCHAR(250) CHARACTER SET LATIN NOT CASESPECIFIC,

      LCL_CHNL_DESC VARCHAR(250) CHARACTER SET LATIN NOT CASESPECIFIC,

      PRFT_CTR_FLAG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),

      ARB_FLAG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),

      INTER_CO_FLAG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),

      RPTG_BU_ID DECIMAL(15,0),

      PRIM_EMRG_MKT_FLAG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),

      EMRG_MKT_FLAG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),

      EFF_END_DTTM TIMESTAMP(6),

      DW_CURR_FLAG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),

      EXTRC_DTTM TIMESTAMP(6))

UNIQUE PRIMARY INDEX XPKCHANNEL_HIERARCHY ( BU_ID ,LCL_CHNL_CODE );

REPLACE VIEW SVC_PKG.GBL_SVC_DSPCH_FACT AS LOCKING ROW FOR ACCESS SELECT * FROM SVC_PKG_T.GBL_SVC_DSPCH_FACT;

REPLACE VIEW SVC_PKG.GBL_SVC_DSPCH_ITM_FACT AS LOCKING ROW FOR ACCESS

SELECT * FROM SVC_PKG_T.GBL_SVC_DSPCH_ITM_FACT;

replace VIEW itm_pkg.BASE_PROD_HIER_DIM

AS locking itm_pkg_t.BASE_PROD_HIER_DIM for access

SELECT * FROM itm_pkg_t.BASE_PROD_HIER_DIM;

REPLACE VIEW PARTY_PKG.ASSOC_DIM AS LOCKING ROW FOR ACCESS SELECT * FROM PARTY_PKG_T.ASSOC_DIM;

REPLACE VIEW CORP_DRM_PKG.CHNL_HIER AS

LOCKING CORP_DRM_PKG_T.CHNL_HIER FOR ACCESS

SELECT * FROM CORP_DRM_PKG_T.CHNL_HIER

;

REPLACE VIEW COMN_PKG.CHNL_HIER_DIM

(BU_ID,

LCL_CHNL_CODE,

EFF_STRT_DTTM,

TYPE_CODE,

TYPE_DESC,

TYPE_ABBR,

SLS_MTHD_CODE,

SLS_MTHD_DESC,

SLS_MTHD_ABBR,

CUST_TYPE_CODE,

CUST_TYPE_DESC,

CUST_TYPE_ABBR,

SEG_CODE,

SEG_DESC,

SEG_ABBR,

VERT_CODE,

VERT_DESC,

LCL_CHNL_DESC,

PRFT_CTR_FLAG,

ARB_FLAG,

INTRCO_FLAG,

RPTG_BU_ID,

PRIM_EMRG_MKT_FLAG,

EFF_END_DTTM,

DW_CURR_FLAG,

EXTRC_DTTM)

AS

 SELECT

cast(bu_id as integer),

CORP_DRM_PKG.CHNL_HIER.LCL_CHNL_CODE,

CORP_DRM_PKG.CHNL_HIER.EFF_STRT_DTTM,

CORP_DRM_PKG.CHNL_HIER.TYPE_CODE,

CORP_DRM_PKG.CHNL_HIER.TYPE_DESC,

CORP_DRM_PKG.CHNL_HIER.TYPE_ABBR,

CORP_DRM_PKG.CHNL_HIER.SLS_MTHD_CODE,

CORP_DRM_PKG.CHNL_HIER.SLS_MTHD_DESC,

CORP_DRM_PKG.CHNL_HIER.SLS_MTHD_ABBR,

CORP_DRM_PKG.CHNL_HIER.CUST_TYPE_CODE,

CORP_DRM_PKG.CHNL_HIER.CUST_TYPE_DESC,

CORP_DRM_PKG.CHNL_HIER.CUST_TYPE_ABBR,

CORP_DRM_PKG.CHNL_HIER.SEG_CODE,

CORP_DRM_PKG.CHNL_HIER.SEG_DESC,

CORP_DRM_PKG.CHNL_HIER.SEG_ABBR,

CORP_DRM_PKG.CHNL_HIER.VERT_CODE,

CORP_DRM_PKG.CHNL_HIER.VERT_DESC,

CORP_DRM_PKG.CHNL_HIER.LCL_CHNL_DESC,

CORP_DRM_PKG.CHNL_HIER.PRFT_CTR_FLAG,

CORP_DRM_PKG.CHNL_HIER.ARB_FLAG,CORP_DRM_PKG.

CHNL_HIER.INTeR_CO_FLAG,

CAST (RPTG_BU_ID AS INTEGER),

CORP_DRM_PKG.CHNL_HIER.PRIM_EMRG_MKT_FLAG,

CORP_DRM_PKG.CHNL_HIER.EFF_END_DTTM,

CORP_DRM_PKG.CHNL_HIER.DW_CURR_FLAG,

CORP_DRM_PKG.CHNL_HIER.EXTRC_DTTM

  FROM CORP_DRM_PKG.CHNL_HIER;

Senior Apprentice

Re: Reg: spool space error

As is said, wrong datatypes in conditions plus different character sets for the same kind of information.

And you don't join both fact tables on the PI columns.

Based on the stats you posted SVC_BU_ID hardly changes the number of distinct value to SVC_PKG_T.GBL_SVC_DSPCH_FACT, so this is either a bad PI (better only SVC_DSPCH_ID) or you should join on both PI columns.

As an end user you can only change the condition on orig_ord_bu_id to the right datatype, then stats on orig_ord_bu_id might help a bit.

Otherwise the main problem seems to be due to a bad datamodel/physical implementation, lots of queries will show bad performance until this is fixed :-(

Dieter