Err0r 5781- cannot collect stats on more than 32 groups of non-indexed columns teradata

UDA
Enthusiast

Err0r 5781- cannot collect stats on more than 32 groups of non-indexed columns teradata

Hi all!

I have never come across this error. I am trying to fine tune a query and the EXplain plan suggests some Collect Stats.
But when I try to do run the third collect stat, it gives me this error. I am using T13.
COLLECT STATISTICS ACTVTY_F COLUMN (CREATED_DT , DSRC_N_ID,X_RECORD_TYPE ,X_PR_PROD);
COLLECT STATISTICS ACTVTY_F COLUMN (CREATED_DT ,DSRC_N_ID,X_RECORD_TYPE ,X_PR_PROD ,X_CTGRY)
COLLECT STATISTICS ACTVTY_F COLUMN (CREATED_DT ,DSRC_N_ID,X_RECORD_TYPE ,X_PR_CONTACT)
COLLECT STATISTICS ACTVTY_F COLUMN (CREATED_DT ,DSRC_N_ID,X_RECORD_TYPE ,X_CTGRY)

This is the query I am trying to optimize. Can you please help me get rid of this error or fine tune this? Any help is appreciated.

SELECT CAST( CAST( F.crt_dt AS CHAR ( 8 ) ) AS DATE FORMAT 'YYYYMMDD' ) ,
PX.X_PIN , P.FST_NAME , P.LAST_NAME , P.STATE , P1.PART_NUM ,
P1.X_FTYPE
FROM ACTVTY_F F , PRDCT_D P1, PRDCT_D P2 , PRSN_D P ,
W_PRSN_DX PX
WHERE F.X_RECORD_TYPE = 'Ill'
AND F.DATASOURCE_NUM_ID = 1
AND F.crt_dt = CAST( CAST( (DATE (FORMAT 'YYYY-MM-DD',
TITLE 'Current_Date()')) - 1 AS DATE FORMAT 'yyyymmdd' ) AS CHAR ( 8 ) )
AND F.X_PR_PROD = P1.ROW
AND F.X_CATGRY = P2.ROW
AND F.X_PR_CONTACT = P.ROW
AND F.X_PR_CONTACT = PX.ROW
AND P2.PART_NUM = 'L Ill'
AND P1.PART_NUM IN ( 'FO Applctn (10YLT)' , 'FO Applctn (15YLT)' ,
'FO Applctn (20YLT)' , 'FO Applctn (30YLT)' , 'FO Applctn (ART)' ,
'FO Replcmnt Form (10YLT)' , 'FO Replcmnt Form (15YLT)' ,
'FO Replcmnt Form (20YLT)' , 'FO Replcmnt Form (30YLT)' ,
'FO Replcmnt Form (ART)' )
ORDER BY F.crt_dt

Thanks!
3 REPLIES
Junior Contributor

Re: Err0r 5781- cannot collect stats on more than 32 groups of non-indexed columns teradata

According to the error message there seem to be a lot of stats on that table already.

Be careful, DIAGNOSTIC HELPSTATS might suggest too many stats which are not really useful.

And a re-collection of all those stats might consume a lot resources.

Back to your problem:
Instead of those 4 stats a single stat on (CREATED_DT ,DSRC_N_ID,X_RECORD_TYPE) should be enough.

What's the datatype of F.crt_dt ?

Could you post the actual explain and the table DDLs?
Looks like you got a large fact table and some dimensions.

Dieter
Enthusiast

Re: Err0r 5781- cannot collect stats on more than 32 groups of non-indexed columns teradata

Thanks Dieter! I did try the collect stats and it did not help. Also tried some suggested secondary indexes which did not help either.
Thsi is the explain and DDL:
1) First, we lock .PRD_D for access, we lock
.PRSN_DX for access, we lock .PRSN_D for access,
and we lock .ACTVTY for access.
2) Next, we execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from .PRD_D by way
of an all-rows scan with a condition of (
"(.PRD_D.PRT_NO = 'Applctn (10YLT)') OR
((.PRD_D.PRT_NO = 'Applctn (15YLT)') OR
((.PRD_D.PRT_NO = 'Applctn (20YLT)') OR
((.PRD_D.PRT_NO = 'Applctn (30YLT)') OR
((.PRD_D.PRT_NO = 'Applctn (ART)') OR
((.PRD_D.PRT_NO = 'Replmt Form (10YLT)')
OR ((.PRD_D.PRT_NO = 'Replmt Form
(15YLT)') OR ((.PRD_D.PRT_NO = 'Replmt
Form (20YLT)') OR ((.PRD_D.PRT_NO = 'FO
Replmt Form (30YLT)') OR (.PRD_D.PRT_NO =
'Replmt Form (ART)')))))))))") into Spool 2
(all_amps) (compressed columns allowed), which is duplicated
on all AMPs. The size of Spool 2 is estimated with high
confidence to be 2,100 rows. The estimated time for this
step is 0.01 seconds.
2) We do an all-AMPs RETRIEVE step from a single partition of
.ACTVTY with a condition of ("DSRC_ID =
1") with a residual condition of ("(NOT
(.ACTVTY.PR_PROD IS NULL )) AND
(((.ACTVTY.CRTD_DT )= (TRANSLATE((TRIM(BOTH
FROM (DATE )- 1 (CHAR(8), CHARACTER SET LATIN, NOT
CASESPECIFIC, FORMAT 'yyyymmdd'))(CHAR(8), CHARACTER SET
LATIN, NOT CASESPECIFIC))USING LATIN_TO_UNICODE)(FLOAT,
FORMAT '-9.99999999999999E-999'))) AND
((.ACTVTY.DSRC_ID = 1) AND
(.ACTVTY.REC_TYP = 'Illustration')))") into
Spool 3 (all_amps) (compressed columns allowed), which is
built locally on the AMPs. The size of Spool 3 is estimated
with low confidence to be 27,287 rows. The estimated time
for this step is 0.00 seconds.
3) We do an all-AMPs RETRIEVE step from .PRD_D by way
of an all-rows scan with a condition of (
".PRD_D.PRT_NO = 'Life Sales Illustration'")
locking for access into Spool 4 (all_amps) (compressed
columns allowed), which is duplicated on all AMPs. The size
of Spool 4 is estimated with high confidence to be 300 rows.
The estimated time for this step is 0.00 seconds.
3) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an
all-rows scan, which is joined to Spool 3 (Last Use) by way of an
all-rows scan. Spool 2 and Spool 3 are joined using a single
partition hash join, with a join condition of ("PR_PROD =
ROWID"). The result goes into Spool 5 (all_amps) (compressed
columns allowed), which is built locally on the AMPs. The size of
Spool 5 is estimated with low confidence to be 356 rows. The
estimated time for this step is 0.01 seconds.
4) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an
all-rows scan, which is joined to Spool 5 (Last Use) by way of an
all-rows scan. Spool 4 and Spool 5 are joined using a single
partition hash join, with a join condition of ("CTGRY =
ROWID"). The result goes into Spool 6 (all_amps) (compressed
columns allowed), which is redistributed by hash code to all AMPs.
Then we do a SORT to order Spool 6 by row hash. The size of Spool
6 is estimated with low confidence to be 8 rows. The estimated
time for this step is 0.01 seconds.
5) We do an all-AMPs JOIN step from .PRSN_D by way of a
RowHash match scan with no residual conditions, which is joined to
Spool 6 (Last Use) by way of a RowHash match scan.
.PRSN_D and Spool 6 are joined using a merge join, with a
join condition of ("PR_CONTACT = .PRSN_D.ROWID").
The input table .PRSN_D will not be cached in memory, but
it is eligible for synchronized scanning. The result goes into
Spool 7 (all_amps) (compressed columns allowed), which is built
locally on the AMPs. The size of Spool 7 is estimated with low
confidence to be 8 rows. The estimated time for this step is 0.03
seconds.
6) We do an all-AMPs JOIN step from .PRSN_DX by way of a
RowHash match scan with no residual conditions, which is joined to
Spool 7 (Last Use) by way of a RowHash match scan.
.PRSN_DX and Spool 7 are joined using a merge join, with
a join condition of ("(PR_CONTACT =
.PRSN_DX.ROWID) AND (.PRSN_DX.ROWID =
ROWID)"). The input table .PRSN_DX will not be cached
in memory, but it is eligible for synchronized scanning. The
result goes into Spool 1 (group_amps), which is built locally on
the AMPs. Then we do a SORT to order Spool 1 by the sort key in
spool field1. The size of Spool 1 is estimated with low
confidence to be 8 rows. The estimated time for this step is 0.01
seconds.
7) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 0.06 seconds.

CREATE SET TABLE ACTVTY
(
ACNT_GEO INTEGER NOT NULL DEFAULT 0 COMPRESS ( 0,123, ...),
ACNT INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
ACT_END INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
ACT_STRT INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
ACT_TYPE INTEGER NOT NULL DEFAULT 0 COMPRESS ( 0,123, ...),
AST_PROD INTEGER NOT NULL DEFAULT 0 COMPRESS ( 0,123, ...),
AST INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
CTGRY INTEGER NOT NULL DEFAULT 0 COMPRESS ( 0,123, ...),
CLM INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
CNTCT INTEGER NOT NULL DEFAULT 0 ,
CON_GEO INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
CRTD_BY_ORG INTEGER NOT NULL DEFAULT 0 COMPRESS ( 0,123, ...),
CRTD_DT INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
DSRC_ID BYTEINT NOT NULL,
DUE INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
ETL_PROC INTEGER NOT NULL,
INSCLM_ELMNT INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
INTGRN_ID VARCHAR(30) NOT CASESPECIFIC NOT NULL,
OPTY INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
OWNER_ORG INTEGER NOT NULL DEFAULT 0 COMPRESS ( 0,123, ...),
OWNER INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
PLN_END INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
PLN_STRT INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
PRIORITY INTEGER NOT NULL DEFAULT 0 COMPRESS ( 0,123, ...),
PGM INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
REGION INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
RESLN INTEGER NOT NULL DEFAULT 0 COMPRESS ( 0,123, ...),
ROW INTEGER NOT NULL,
SR INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
STATUS INTEGER NOT NULL DEFAULT 0 COMPRESS ( 0,123, ...),
ACT_END_DT TIMESTAMP(0),
ACT_STRT_DT TIMESTAMP(0),
BILL CHAR(1) COMPRESS ( 0,123, ...),
CALL_DUR DECIMAL(22,7) COMPRESS 0.0000000 ,
CST DECIMAL(22,7) COMPRESS ( 0,123, ...),
OVER_DUELG CHAR(1) COMPRESS ( 0,123, ...), TRVL_TM_MIN DECIMAL(22,7),
PIN VARCHAR(8) ,
CRNT_STAT CHAR(11) COMPRESS ( 0,123, ...), RQST_LOG_DT TIMESTAMP(0),
PRT_CLS_STAT CHAR(11) COMPRESS '8118 ',
WPID VARCHAR(10) ,
JULIAN_END_DT DECIMAL(22,7) COMPRESS 2454862.1037731 ,
JULIAN_STRT_DT DECIMAL(22,7) COMPRESS 2453061.4370833 ,
CALL_RPT_IND DECIMAL(10,0) COMPRESS ( 0,123, ...),
MAIL_ADRS VARCHAR(150) ,
MAIL_CAT VARCHAR(100) ,
MAIL_CAT_I VARCHAR(100) ,
B_EXP_CST DECIMAL(22,7) COMPRESS 0.0000000 ,
B_PRT_CST DECIMAL(22,7) COMPRESS 0.0000000 ,
B_TS_CST DECIMAL(22,7) COMPRESS 0.0000000 ,
CURCY_CD VARCHAR(30) ,
CURCY_EXCH_DT TIMESTAMP(0),
EXP_B_PRC DECIMAL(22,7) COMPRESS 0.0000000 ,
EXP_NB_PRC DECIMAL(22,7) COMPRESS 0.0000000 ,
NB_EXP_CST DECIMAL(22,7) COMPRESS 0.0000000 ,
NB_PRT_CST DECIMAL(22,7) COMPRESS 0.0000000 ,
NB_TS_CST DECIMAL(22,7) COMPRESS 0.0000000 ,
PRT_B_PRC DECIMAL(22,7) COMPRESS 0.0000000 ,
PRT_NB_PRC DECIMAL(22,7) COMPRESS 0.0000000 ,
TS_B_PRC DECIMAL(22,7) COMPRESS 0.0000000 ,
TS_B_TIME DECIMAL(22,7) COMPRESS 0.0000000 ,
TS_NB_PRC DECIMAL(22,7) COMPRESS 0.0000000 ,
TS_NB_TIME DECIMAL(22,7) COMPRESS 0.0000000 ,
TS_TOT_TIME DECIMAL(22,7) COMPRESS 0.0000000 ,
OPTY_ESCLLG CHAR(1) COMPRESS ( 0,123, ...), RSPNS_TIME DECIMAL(10,2) COMPRESS ,
SR_ESCLLG CHAR(1) COMPRESS ( 0,123, ...),
U_CST DECIMAL(22,7) COMPRESS 0.0000000 ,
U_CURCY_CD VARCHAR(20) ,
U_EXCH_DT TIMESTAMP(0),
US_B_EXP_CST DECIMAL(22,7) COMPRESS 0.0000000 ,
US_B_PRT_CST DECIMAL(22,7) COMPRESS 0.0000000 ,
US_B_TS_CST DECIMAL(22,7) COMPRESS 0.0000000 ,
US_EXP_B_PRC DECIMAL(22,7) COMPRESS 0.0000000 ,
US_EXP_NB_PRC DECIMAL(22,7) COMPRESS 0.0000000 ,
US_NB_EXP_CST DECIMAL(22,7) COMPRESS 0.0000000 ,
US_NB_PRT_CST DECIMAL(22,7) COMPRESS 0.0000000 ,
US_NB_TS_CST DECIMAL(22,7) COMPRESS 0.0000000 ,
US_PRT_B_PRC DECIMAL(22,7) COMPRESS 0.0000000 ,
US_PRT_NB_PRC DECIMAL(22,7) COMPRESS 0.0000000 ,
US_TS_B_PRC DECIMAL(22,7) COMPRESS 0.0000000 ,
US_TS_NB_PRC DECIMAL(22,7) COMPRESS 0.0000000 ,
VIS_PR_OWN_ID VARCHAR(15) ,
AGRMNT INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
ENTMNT INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
POSN INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
MAILTYPE VARCHAR(20) ,
CAMP_END_DT INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
CAMP_LNCH_DT INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
CAMP_ST_DT INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
CASLEAD INTEGER DEFAULT 0 COMPRESS 0 ,
CASSTG INTEGER DEFAULT 0 COMPRESS 0 ,
CASE INTEGER DEFAULT 0 COMPRESS 0 ,
INCDNT INTEGER DEFAULT 0 COMPRESS 0 ,
NAME VARCHAR(150) ,
OWN_ORG_PRTNRLG CHAR(1) COMPRESS 'N',
SOURCE INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
VIS_PR_POS_ID VARCHAR(15) ,
APPT_REPT_REPL_CD VARCHAR(30) ,
TEMPLATELG CHAR(1) COMPRESS 'N',
REC_TYPE VARCHAR(30) ,
OWNER_LOGIN VARCHAR(50) ,
ACTVTY_UID VARCHAR(30) ,
STATEILTER VARCHAR(30) ,
REPORMSLG CHAR(1) COMPRESS ( 0,123, ...) ,REP_REGLG CHAR(1) COMPRESS ( 0,123, ...), ROLTYPCD VARCHAR(30) ,
ATTR_01 VARCHAR(100) ,
ATTR_02 VARCHAR(100) ,
ATTR_03 VARCHAR(30) ,
ATTR_04 VARCHAR(30) ,
ATTR_06 VARCHAR(30) ,
ATTR_07 VARCHAR(30) ,
ATTR_08 CHAR(1) COMPRESS ' ',
ATTR_10 CHAR(1) COMPRESS ( 0,123, ...),
ATTR_11 CHAR(1) COMPRESS 'N',
ATTR_34 VARCHAR(50) ,
ATTR_35 VARCHAR(50) ,
ATTR_36 VARCHAR(50) ,
ATTR_37 VARCHAR(50) ,
ATTR_44 VARCHAR(100) ,
ATTR_45 VARCHAR(100) ,
ATTR_26 TIMESTAMP(0),
PRIVLG CHAR(1) COMPRESS ( 0,123, ...),
ATTR_24 DECIMAL(22,7) COMPRESS ( 0,123, ...), 0,123, ...), 0,123, ...),0.0000000 ,0.0200000 ,1.0000000 ,0.0100000 ),
ATTR_25 DECIMAL(22,7) COMPRESS ( 0,123, ...), 0,123, ...), 0,123, ...),0.0000000 ,2.0000000 ,1.0000000 ),
PRD_LINE VARCHAR(30) ,
SER_TYPE VARCHAR(100) ,
COMMENTS_LONG VARCHAR(1500) ,
CRTR_LOGIN VARCHAR(50) ,
EML_SNDR_ADDR VARCHAR(250) ,
EML_STATE VARCHAR(10) ,
PAR_EVT_ID VARCHAR(15) ,
SER_TYPE INTEGER COMPRESS ( 0,123, ...), 0,123, ...),
PRD_LINE INTEGER COMPRESS ( 0,123, ...),
DELETELG CHAR(1) COMPRESS 'D',
OBJECTIVCD VARCHAR(30) ,
PRTICIPANT_NUM DECIMAL(10,0) COMPRESS 1. ,
RELEASELG CHAR(1) COMPRESS ( 0,123, ...), SUBTYPCD VARCHAR(30) ,
APPT_REPTLG CHAR(1) COMPRESS ,
TOPIC VARCHAR(50) ,
END_TIME DECIMAL(10,0) COMPRESS ,
AGING_DAYS DECIMAL(5,0) COMPRESS 0. ,
AGING_DTLG VARCHAR(1) ,
DT_RCVD DECIMAL(10,0) COMPRESS ,
EFF_DT DECIMAL(10,0) COMPRESS ,
AUDIT INTEGER COMPRESS ,
WAKDT DECIMAL(10,0) COMPRESS 0. ,
ACT INTEGER COMPRESS 0 ,
COMP_TIME DECIMAL(10,0) COMPRESS 0. ,
WAKTIME DECIMAL(10,0) COMPRESS 0. ,
TSKID VARCHAR(11) ,
TIMRCVD DECIMAL(10,0) COMPRESS ,
STRT_TIME DECIMAL(10,0) COMPRESS ,
SUBPLAN INTEGER COMPRESS 0 ,
TRADDT DECIMAL(10,0) COMPRESS 0. ,
XAIL_END_DT DECIMAL(10,0) COMPRESS 0. ,
PKTID VARCHAR(11) ,
DPT INTEGER COMPRESS ,
CREATTIME DECIMAL(10,0) COMPRESS ,
PI_DT DECIMAL(10,0) COMPRESS ,
WRKBSKT VARCHAR(8) ,
TURNARND_DAYS DECIMAL(5,0) COMPRESS ( 0,123, ...),
TURNARND_DTLG VARCHAR(1) ,
XAIL INTEGER COMPRESS ,
MAILDESC VARCHAR(30) ,
DEF_ACTCODE DECIMAL(10,0) COMPRESS ( 0,123, ...),
DEF_PRTY DECIMAL(10,0) COMPRESS ( 0,123, ...),
DEF_WRKBSKT VARCHAR(8) NOT CASESPECIFIC,
LAG1_ACT INTEGER COMPRESS ,
LAG1_END_TIME DECIMAL(10,0) COMPRESS 0. ,
LAG1_OWNER_LOGIN VARCHAR(8) NOT CASESPECIFIC,
LAG1_PI_DT DECIMAL(10,0) COMPRESS ,
LAG1_STAT INTEGER COMPRESS ,
LAG2_END_TIME DECIMAL(10,0) COMPRESS 0. ,
LAG2_OWNER_LOGIN VARCHAR(8) NOT CASESPECIFIC,
LAG2_PI_DT DECIMAL(10,0) COMPRESS ,
LEAD1_DPT INTEGER COMPRESS ,
LEAD1_OWNER_LOGIN VARCHAR(8) NOT CASESPECIFIC,
LEAD1_TSK_TYPE INTEGER COMPRESS ,
LEAD2_DPT INTEGER COMPRESS ,
LEAD2_TSK_TYPE INTEGER COMPRESS ,
PI_POSN INTEGER DEFAULT 0 COMPRESS 0 ,
RONUM DECIMAL(10,0) COMPRESS ( 0,123, ...),
TOUCH_DAYS DECIMAL(10,0) COMPRESS ( 0,123, ...),
TSK_TYPE INTEGER DEFAULT 0 COMPRESS 0 ,
CON_ID VARCHAR(50) NOT CASESPECIFIC,
DNIS DECIMAL(10,0) COMPRESS ( 0,123, ...),
TSK_CLOS_METHD VARCHAR(5) NOT CASESPECIFIC,
STATUS_RPTLG CHAR(1) NOT CASESPECIFIC COMPRESS 'N',
LAST_UPD INTEGER COMPRESS 4695634 ,
LAST_UPD_EMP INTEGER COMPRESS 4695634 ,
PERFRM_BY_POS INTEGER COMPRESS ( 0,123, ...),
PHASDURATION_DAYS INTEGER COMPRESS ( 0,123, ...),
ACT_CRTD_DT INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
PERFRM_BY_ORG INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
OWNER_ORG INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
PAPER_SIGNLG CHAR(1) COMPRESS ( 0,123, ...),
RECREATRCPTLG CHAR(1) COMPRESS ( 0,123, ...),
RECPT_REQLG CHAR(1) COMPRESS 'N',
ESCL_CD VARCHAR(30) ,
TMPL_CLNT INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
TMPL_PRD_LN INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
STG INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
DURATION_TM DECIMAL(10,0),
LEAD_TM DECIMAL(22,7) COMPRESS ( 0,123, ...),
LEAD_TM_UOM_CD VARCHAR(30) NOT CASESPECIFIC,
APPT_STRT_DT INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
PERFRM_PER INTEGER NOT NULL DEFAULT 0 COMPRESS ( 0,123, ...),
CHANNEL VARCHAR(30) ,
RSPNS_TYPE VARCHAR(30) ,
APPT_END_DT INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
SESSION INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
PR_PROD INTEGER DEFAULT 0 COMPRESS ( 0,123, ...),
XUNCTION_ROLE VARCHAR(30) ,
PLAN_END_DT TIMESTAMP(0),
XUL_MAILED_DT INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
CTGRY INTEGER NOT NULL DEFAULT 0 COMPRESS ( 0,123, ...),
PR_CNTCT INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
APPT_ALARM_TM_MIN DECIMAL(22,7),
CALLER_NAME VARCHAR(50) NOT CASESPECIFIC,
ATTR_39 VARCHAR(50) NOT CASESPECIFIC,
CAL_TYPCD VARCHAR(30) NOT CASESPECIFIC,
COMM_SESSION_HNDL VARCHAR(250) NOT CASESPECIFIC,
LETTER_CODLONG VARCHAR(100) NOT CASESPECIFIC,
APPT_DURATION_MIN DECIMAL(22,7),
ALARMLAG CHAR(1) NOT CASESPECIFIC COMPRESS ( 0,123, ...), ATTCHMTLG CHAR(1) NOT CASESPECIFIC COMPRESS ( 0,123, ...), DONELG VARCHAR(1) NOT CASESPECIFIC,
PCT_COMPLETE DECIMAL(22,7),
MEETING_CALL_DT INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
LOC_DESC VARCHAR(100) NOT CASESPECIFIC,
NOTIFICATIONLG CHAR(1) NOT CASESPECIFIC COMPRESS 'N',
PROJECT VARCHAR(50) ,
ENROLL_METHOD VARCHAR(30) ,
CASEILLINK VARCHAR(255) ,
REPLACDT INTEGER DEFAULT 0 COMPRESS 0 ,
REPLACEULLPRTIAL VARCHAR(15) NOT CASESPECIFIC,
REPLACINTEXT VARCHAR(15) NOT CASESPECIFIC,
POST_DT INTEGER DEFAULT 0 COMPRESS 0 ,
AGENT_LOCATION VARCHAR(50) NOT CASESPECIFIC,
AGENT_SUPERVISOR_CRD VARCHAR(30) NOT CASESPECIFIC,
REL_AST INTEGER DEFAULT 0 COMPRESS ( 0,123, ...),
UNIQUE PRIMARY INDEX (DSRC_ID ,ROW )
PRTITION BYDSRC_ID ;

CREATE SET TABLE .PRD_D ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
DSRC_ID BYTEINT NOT NULL,
ETL_PROC INTEGER NOT NULL,
INTGRN_ID VARCHAR(30) NOT NULL,
PROD_NAME VARCHAR(100) ,
ROW INTEGER NOT NULL,
APPLICATIONLG CHAR(1) ,
CUSTOM_PRODLG CHAR(1) ,
EFF_END_DT TIMESTAMP(0),
EFF_STRT_DT TIMESTAMP(0),
FRULG CHAR(1) ,
ITEM_SIZE DECIMAL(22,7),
MTBF DECIMAL(22,7),
MTTR DECIMAL(22,7),
PACKAGED_PRODLG CHAR(1) ,
REFERRALLG CHAR(1) ,
RTRN_DEFECTIVELG CHAR(1) ,
SERIALIZEDLG CHAR(1) ,
DETAIL_TYPE VARCHAR(30) ,
DETAIL_TYPI VARCHAR(50) ,
LEAD_TIME VARCHAR(30) ,
PRT_NUM VARCHAR(50) ,
PAR_INTGRN_ID VARCHAR(30) ,
PROD_TYPE VARCHAR(30) ,
PROD_TYPI VARCHAR(50) ,
PROFIT_RANK VARCHAR(30) ,
PROFIT_RANK_I VARCHAR(50) ,
PR_EQUIV_PROD_NAME VARCHAR(100) ,
PR_PROD_LN VARCHAR(100) ,
STATUS VARCHAR(30) ,
STATUS_I VARCHAR(50) ,
SUB_TYPE VARCHAR(30) ,
SUB_TYPI VARCHAR(50) ,
TGT_CUST_TYPE VARCHAR(30) ,
TGT_CUST_TYPI VARCHAR(50) ,
UOM VARCHAR(30) ,
UOM_I VARCHAR(50) ,
VENDOR_LOC VARCHAR(50) ,
VENDOR_NAME VARCHAR(100) ,
VENDR_PRT_NUM VARCHAR(50) ,
BODY_STYLCD VARCHAR(30) ,
BODY_STYLCD_I VARCHAR(50) ,
CASPACK DECIMAL(22,7),
CTLG_CAT_ID VARCHAR(30) ,
DEALER_INV_PRC DECIMAL(22,7),
DOORS_TYPCD VARCHAR(30) ,
DOORS_TYPCD_I VARCHAR(50) ,
DRIVTRAIN_CD VARCHAR(30) ,
DRIVTRAIN_CD_I VARCHAR(50) ,
ENGINTYPCD VARCHAR(30) ,
ENGINTYPCD_I VARCHAR(50) ,
FUEL_TYPCD VARCHAR(30) ,
FUEL_TYPCD_I VARCHAR(50) ,
GROSS_MRGN DECIMAL(22,7),
INVENTORYLG CHAR(1) ,
MAKCD VARCHAR(30) ,
MAKCD_I VARCHAR(50) ,
MODEL_CD VARCHAR(30) ,
MODEL_CD_I VARCHAR(50) ,
MODEL_YR DECIMAL(22,7),
MSRP DECIMAL(22,7),
ORDERABLELG CHAR(1) ,
PRC_TYPCD VARCHAR(30) ,
PRC_TYPCD_I VARCHAR(50) ,
PROD_NDC_ID VARCHAR(30) ,
PROD_TYPCD VARCHAR(30) ,
PROD_TYPCD_I VARCHAR(50) ,
RAVG_PRC DECIMAL(22,7),
SALES_PRODLG CHAR(1) ,
SALES_SRVCLG CHAR(1) ,
SER_TYPE VARCHAR(30) ,
SER_TYPI VARCHAR(50) ,
SUB_TYPCD VARCHAR(30) ,
SUB_TYPCD_I VARCHAR(50) ,
TRANSMISSION_CD VARCHAR(30) ,
TRANSMISSION_CD_I VARCHAR(50) ,
TRIM_CD VARCHAR(30) ,
TRIM_CD_I VARCHAR(50) ,
"TYPE" VARCHAR(30) ,
TYPI VARCHAR(50) ,
U_DEALER_INV_PRC DECIMAL(22,7),
U_DELPRI_CURCY_CD VARCHAR(20) ,
U_DELPRI_EXCH_DT TIMESTAMP(0),
U_MSRP DECIMAL(22,7),
U_MSRP_CURCY_CD VARCHAR(20) ,
U_MSRP_EXCH_DT TIMESTAMP(0),
U_RAVG_PRC DECIMAL(22,7),
U_RXAVPR_CURCY_CD VARCHAR(20) ,
U_RXAVPR_EXCH_DT TIMESTAMP(0),
UNIT_CONVACTOR DECIMAL(22,7),
VENDOR_LOC1 VARCHAR(50) ,
VENDOR_LOC2 VARCHAR(50) ,
VENDOR_LOC3 VARCHAR(50) ,
VER_DT TIMESTAMP(0),
VER_DT1 TIMESTAMP(0),
VER_DT2 TIMESTAMP(0),
VER_DT3 TIMESTAMP(0),
NRCLG CHAR(1) ,
ACNTROM VARCHAR(30) ,
ACNT_TO VARCHAR(30) ,
PR_PROD DECIMAL(10,0),
SOURCCD VARCHAR(30) ,
XTYPE VARCHAR(30) )
UNIQUE PRIMARY INDEX PRD_D_UPI ( ROW );

CREATE SET TABLE .PERSON_D ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
DSRC_ID BYTEINT NOT NULL,
ETL_PROC INTEGER NOT NULL,
FST_NAME VARCHAR(50) ,
INTGRN_ID VARCHAR(30) NOT NULL,
LAST_NAME VARCHAR(50) ,
PR_HOUSEHOLD INTEGER NOT NULL DEFAULT 0 ,
ROW INTEGER NOT NULL,
AGENTLG CHAR(1) ,
ANNL_INCOME DECIMAL(22,7),
ANNL_REVENUE DECIMAL(22,7),
APPR_AUTH DECIMAL(22,7),
BIRTH_DT TIMESTAMP(0),
CALLLG CHAR(1) ,
COMP_OWNERLG CHAR(1) ,
CONSUMERLG CHAR(1) ,
CON_ACTIVELG CHAR(1) ,
CONORMED_DT TIMESTAMP(0),
CREDIT_SCORE DECIMAL(22,7),
DECEASELG CHAR(1) ,
EML_SR_UPDLG CHAR(1) ,
EMP_ACTIVELG CHAR(1) ,
EMPLG CHAR(1) ,
EMPORMED_DT TIMESTAMP(0),
ENTREPRISELG CHAR(1) ,
MEMBERLG CHAR(1) ,
NUM_PROD DECIMAL(10,0),
PROVIDERLG CHAR(1) ,
RESDNCVAL DECIMAL(22,7),
SELF_EMPLLG CHAR(1) ,
SEMINAR_INVITLG CHAR(1) ,
SEMF CHAR(1) ,
SUPPRESS_CALLLG CHAR(1) ,
SUPPRESS_EMLLG CHAR(1) ,
SUPPRESSAXLG CHAR(1) ,
SUPPRESS_MAILLG CHAR(1) ,
TABRACKET DECIMAL(22,7),
USER_ACTIVELG CHAR(1) ,
USERLG CHAR(1) ,
USERORMED_DT TIMESTAMP(0),
YEARS_AT_ACNT DECIMAL(22,7),
YRS_AT_RESIDENCE DECIMAL(22,7),
ACCESS_LVL VARCHAR(30) ,
ACCESS_LVL_I VARCHAR(50) ,
APPR_AUTH_CAT VARCHAR(30) ,
APPR_AUTH_CAT_I VARCHAR(50) ,
APPR_CURCY_CD VARCHAR(30) ,
CALL_LST_NAME VARCHAR(50) ,
CITY VARCHAR(50) ,
COM_PREFERENCE VARCHAR(30) ,
CNTCT_TYPE VARCHAR(30) ,
CNTCT_TYPI VARCHAR(50) ,
CON_BU_NAME VARCHAR(100) ,
COUNTRY VARCHAR(30) ,
CUST_VALUE VARCHAR(30) ,
CUST_VALUI VARCHAR(50) ,
DEPT_TYPE VARCHAR(30) ,
DEPT_TYPI VARCHAR(50) ,
EDCTN_BCKGND VARCHAR(30) ,
EDCTN_BCKGND_I VARCHAR(50) ,
EML_ADDR VARCHAR(100) ,
EMP_ACNT_BU_NAME VARCHAR(100) ,
EMP_ACNT_LOC VARCHAR(50) ,
EMP_ACNT_NAME VARCHAR(100) ,
FAPH_NUM VARCHAR(40) ,
FREQUENCY_CAT VARCHAR(30) ,
FREQUENCY_CAT_I VARCHAR(50) ,
GOALS VARCHAR(250) ,
HELD_POSTN VARCHAR(50) ,
HOBBY VARCHAR(30) ,
INCOMRANGE VARCHAR(30) ,
INCOMRANGI VARCHAR(50) ,
INS_OCCUPATION VARCHAR(50) ,
INVST_EXPERIENCE VARCHAR(30) ,
INVST_EXPERIENCI VARCHAR(50) ,
INVST_HORIZON VARCHAR(30) ,
INVST_HORIZON_I VARCHAR(50) ,
INVST_KNWLDGE VARCHAR(30) ,
INVST_KNWLDGI VARCHAR(50) ,
INVST_OBJECTIVE VARCHAR(30) ,
INVST_OBJECTIVI VARCHAR(50) ,
INVST_PROFILE VARCHAR(30) ,
INVST_PROFILI VARCHAR(50) ,
INVST_RISK VARCHAR(30) ,
INVST_RISK_I VARCHAR(50) ,
JOB_CTGRY VARCHAR(30) ,
JOB_CTGRY_I VARCHAR(50) ,
JOB_TITLE VARCHAR(75) ,
LOGIN VARCHAR(50) ,
MARITAL_STAT_CD VARCHAR(30) ,
MEMBER_NUM VARCHAR(50) ,
MID_NAME VARCHAR(50) ,
MONETARY_CAT VARCHAR(30) ,
MONETARY_CAT_I VARCHAR(50) ,
MRKT_CAP_PREF VARCHAR(30) ,
MRKT_CAP_PREF_I VARCHAR(50) ,
NATIONALITY VARCHAR(30) ,
NET_WORTH VARCHAR(30) ,
NET_WORTH_I VARCHAR(50) ,
OU_MAIL_STOP VARCHAR(30) ,
PAR_HELD_POSTN VARCHAR(50) ,
PREF_COMM_MEDIA VARCHAR(30) ,
PREF_COMM_MEDII VARCHAR(50) ,
PREF_LANG_ID VARCHAR(15) ,
PRSP_CON_UID VARCHAR(100) ,
PR_MKT_SEGMENT VARCHAR(50) ,
PR_POSTN VARCHAR(50) ,
RECENCY_CAT VARCHAR(30) ,
RECENCY_CAT_I VARCHAR(50) ,
REGION VARCHAR(40) ,
RESDNCCTGRY VARCHAR(30) ,
RESDNCCTGRY_I VARCHAR(50) ,
RESDNCTYPE VARCHAR(30) ,
RESDNCTYPI VARCHAR(50) ,
RESIDENCY_INSTN VARCHAR(50) ,
RSRCH_CHNL_PREF VARCHAR(30) ,
RSRCH_CHNL_PREF_I VARCHAR(50) ,
SPOUSNAME VARCHAR(30) ,
STATE VARCHAR(50) ,
ST_ADRS VARCHAR(200) ,
TERR_NAME VARCHAR(75) ,
WRK_PHONE VARCHAR(40) ,
ZIPCODE VARCHAR(30) ,
ACNT_ID VARCHAR(30) ,
ADDR_EFF_DATE TIMESTAMP(0),
CON_ACTIVELG1 CHAR(1) ,
CON_ACTIVELG2 CHAR(1) ,
CON_ACTIVELG3 CHAR(1) ,
CNTCT_ID VARCHAR(30) ,
CSN VARCHAR(150) ,
CUST_VAL_EFF_DT TIMESTAMP(0),
CST_END_DT TIMESTAMP(0),
CST_SINCDT TIMESTAMP(0),
EMP_HIRDT TIMESTAMP(0),
EMPLMNT_STAT_CD VARCHAR(30) ,
EXT_CON_STORE VARCHAR(30) ,
FULL_NAME VARCHAR(102) ,
GRAD_YR DECIMAL(10,0),
HARD_TO_REACH CHAR(1) ,
INCOMRNG_EFF_DT TIMESTAMP(0),
MKT_POTENTIAL VARCHAR(30) ,
NAMEFF_DATE TIMESTAMP(0),
NET_WORTH_EFF_DT TIMESTAMP(0),
NUM_CMPGNS DECIMAL(10,0),
NUM_OFRS_PRSNTD DECIMAL(10,0),
NUM_PROD_EFF_DT TIMESTAMP(0),
PR_SPEC_NAME VARCHAR(50) ,
PR_SPECIALTY VARCHAR(100) ,
PRLANG VARCHAR(50) ,
PRESCRIBERLG CHAR(1) ,
PROF_CALLREQ VARCHAR(30) ,
PROF_TITLE VARCHAR(30) ,
PROSPECT_ID VARCHAR(30) ,
SEMF_CD VARCHAR(30) ,
SEMF_CD_I VARCHAR(50) ,
SPEAKERLG CHAR(1) ,
SPECIALTY_BRICK VARCHAR(40) ,
STAFF VARCHAR(255) ,
STAT_REASON_CD VARCHAR(30) ,
STAT_REASON_CD_I VARCHAR(50) ,
STATUS_CD VARCHAR(30) ,
STATUS_CD_I VARCHAR(50) ,
VER_DT TIMESTAMP(0),
VER_DT1 TIMESTAMP(0),
VER_DT2 TIMESTAMP(0),
VER_DT3 TIMESTAMP(0),
VIS_PR_BU_ID VARCHAR(15) ,
VIS_PR_POS_ID VARCHAR(15) ,
WPH_EFF_DATE TIMESTAMP(0),
CITY VARCHAR(50) ,
COUNTRY VARCHAR(30) ,
CUST_VALUE VARCHAR(30) ,
CUST_VALUI VARCHAR(50) ,
XST_NAME VARCHAR(50) ,
INCOMRANGE VARCHAR(30) ,
INCOMRANGI VARCHAR(50) ,
LAST_NAME VARCHAR(50) ,
NET_WORTH VARCHAR(30) ,
NET_WORTH_I VARCHAR(50) ,
NUM_PROD DECIMAL(10,0),
ST_ADRS VARCHAR(200) ,
STATE VARCHAR(50) ,
WRK_PHONE VARCHAR(40) ,
ZIPCODE VARCHAR(30) ,
AGRANGE VARCHAR(30) ,
BIRTH_PLACE VARCHAR(100) ,
CTZNSHP_CD VARCHAR(30) ,
CTZNSHP_CD_I VARCHAR(50) ,
CMPLXN_CD VARCHAR(30) ,
CMPLXN_CD_I VARCHAR(50) ,
DEATH_DT TIMESTAMP(0),
DISABILITY VARCHAR(50) ,
EDCTN_YEARS DECIMAL(10,0),
ETH1_CD VARCHAR(30) ,
ETH2_CD VARCHAR(30) ,
EYECLR_LEFT VARCHAR(30) ,
EYECLR_LEFT_I VARCHAR(50) ,
EYECLR_RIGHT VARCHAR(30) ,
EYECLR_RIGHT_I VARCHAR(50) ,
FST_PROMO_DT TIMESTAMP(0),
GEO INTEGER NOT NULL DEFAULT 0 ,
HAIR_COLOR VARCHAR(30) ,
HEIGHT VARCHAR(30) ,
LST_PROMO_DT TIMESTAMP(0),
RACE VARCHAR(30) ,
WT VARCHAR(30) )
UNIQUE PRIMARY INDEX PERSON_D_UPI ( ROW );

CREATE SET TABLE .PERSON_DX ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
ETL_PROC INTEGER NOT NULL,
ROW INTEGER NOT NULL,
ATTR_11 DECIMAL(10,0),
ATTR_12 DECIMAL(10,0),
ATTR_13 DECIMAL(10,0),
ATTR_14 DECIMAL(10,0),
ATTR_15 DECIMAL(10,0),
ATTR_16 DECIMAL(22,7),
ATTR_17 DECIMAL(22,7),
ATTR_18 DECIMAL(22,7),
ATTR_19 DECIMAL(22,7),
ATTR_20 DECIMAL(22,7),
ATTR_21 TIMESTAMP(0),
ATTR_22 TIMESTAMP(0),
ATTR_23 TIMESTAMP(0),
ATTR_24 TIMESTAMP(0),
ATTR_25 TIMESTAMP(0),
ATTR_01 VARCHAR(30) ,
ATTR_02 VARCHAR(30) ,
ATTR_03 VARCHAR(30) ,
ATTR_04 VARCHAR(30) ,
ATTR_05 VARCHAR(30) ,
ATTR_06 VARCHAR(30) ,
ATTR_07 VARCHAR(30) ,
ATTR_08 VARCHAR(30) ,
ATTR_09 VARCHAR(30) ,
ATTR_10 VARCHAR(30) ,
HH_ID_CUST VARCHAR(30) ,
INDVID_CUST VARCHAR(30) ,
PERSON_UID VARCHAR(30) NOT NULL,
PIN VARCHAR(30) ,
CLUSTER VARCHAR(222) ,
ADDR_TYPCD CHAR(1) ,
DECEASEDLG CHAR(1) ,
DECEASED_DT TIMESTAMP(0),
PER_TITLE VARCHAR(15) ,
SSN VARCHAR(20) ,
PER_TITLSUF VARCHAR(15) ,
SOURCCD VARCHAR(30) ,
EMPLOYECD VARCHAR(30) ,
GOOD_BAD_CD VARCHAR(30) ,
MILITARYLG CHAR(1) ,
SUP_LGLLG CHAR(1) ,
LGL_MAILLG CHAR(1) ,
SUP_LIFELG CHAR(1) ,
SUP_LTCLG CHAR(1) ,
SUP_MFLG CHAR(1) ,
SUP_MXLG CHAR(1) ,
SUP_IRALG CHAR(1) ,
SUP_INSURELG CHAR(1) ,
SUP_INVESTLG CHAR(1) ,
SUP_KEOGHLG CHAR(1) ,
SUP_PROMOLG CHAR(1) ,
SUP_PUBLG CHAR(1) ,
SUP_RGRALG CHAR(1) ,
SUP_RETIRELG CHAR(1) ,
SUP_ROLLOVERLG CHAR(1) ,
SUP_SRGSRALG CHAR(1) ,
SUP_SURVEYLG CHAR(1) ,
SUP_TRUSTLG CHAR(1) ,
SUP_TUITIONLG CHAR(1) ,
SUP_PALG CHAR(1) ,
COMPLAINTLG CHAR(1) ,
TLC_HIGHLG CHAR(1) ,
CON_TYPCUST VARCHAR(100) ,
ADDR_LN2 VARCHAR(100) ,
ADDR_LN3 VARCHAR(100) ,
ADDR_LN4 VARCHAR(100) ,
ADDR_LN5 VARCHAR(100) ,
MAIL_WALK_RTE VARCHAR(100) ,
HEAD_OF_INST CHAR(1) ,
TLC_SSRA CHAR(1) ,
TLC_TRUSTEE CHAR(1) ,
TLC_VIP CHAR(1) ,
RELATCD VARCHAR(30) ,
XREF_PIN VARCHAR(30) ,
VIPLG CHAR(1) ,
RETIRDT_CUST TIMESTAMP(0),
XAC_GRD_CUST VARCHAR(15) ,
SMOKIND_CUST CHAR(1) ,
EST_AMT_CUST DECIMAL(15,2),
EST_AMT_DT TIMESTAMP(0),
INSTITUITION_CUST VARCHAR(30) ,
NO_MAIL_EDU_IRA CHAR(1) ,
NO_MAIL_MF_IRA CHAR(1) ,
NO_MAIL_UL CHAR(1) ,
NO_MAIL_INS CHAR(1) ,
NO_MAIL_LTC CHAR(1) ,
NO_MAIL_MF CHAR(1) ,
NO_MAIL_PA CHAR(1) ,
EMP_TYPCUST VARCHAR(15) ,
MODCDE VARCHAR(3) ,
CARRIER_CDE VARCHAR(6) ,
MULTI_MAIL_IND CHAR(1) ,
YTD_DIV DECIMAL(14,2),
YTD_IRCONTRIBUTIONS DECIMAL(14,2),
TLC_CODES VARCHAR(100) ,
SUPPRESS_CODES VARCHAR(100) ,
PCLG CHAR(1) ,
RMLG CHAR(1) ,
MKT_SEG_CD CHAR(1) ,
MKT_SEG_MONTHLY_CD CHAR(1) ,
CLUSTER_160 VARCHAR(20) ,
CPM_PREF_1 CHAR(1) ,
CPM_PREF_2 CHAR(1) ,
CPM_PREF_3 CHAR(1) ,
CPM_PREF_4 CHAR(1) ,
CPM_PREF_5 CHAR(1) ,
CPM_PREF_6 CHAR(1) ,
CPM_PREF_7 CHAR(1) ,
CPM_PREF_8 CHAR(1) ,
CPM_PREF_9 CHAR(1) ,
CPM_PREF_10 CHAR(1) ,
CPM_PREF_11 CHAR(1) ,
CPM_PREF_12 CHAR(1) ,
CPM_EML_STATUS_CD CHAR(1) ,
AR_RISK_CAT_PREV CHAR(1) ,
AR_RISK_CAT CHAR(1) ,
VALURISK_CAT_PREV CHAR(1) ,
VALURISK_CAT CHAR(1) ,
AFFLUENTZIP_SCORE VARCHAR(6) ,
CUST_LIFVAL_AMT DECIMAL(10,0),
CUST_LIFVAL_RNK VARCHAR(2) ,
CUST_LIFMODEL_CD CHAR(1) ,
CUST_LIFMODEL_RNK VARCHAR(2) ,
WALLET_SHARPCT VARCHAR(3) ,
WALLET_SHARRNK VARCHAR(2) ,
WMG_SUPPRESSION CHAR(1) ,
WMG_PRIORITY CHAR(1) ,
WMG_STATUS CHAR(1) ,
CUST_LIFSTG_CD CHAR(1) ,
SAS_GEN_CD_1 VARCHAR(5) ,
SAS_GEN_CD_2 VARCHAR(5) ,
SAS_GEN_CD_3 VARCHAR(5) ,
SAS_GEN_CD_4 VARCHAR(5) ,
SAS_GEN_CD_5 VARCHAR(5) ,
SAS_GEN_CD_6 VARCHAR(5) ,
SAS_GEN_CD_7 VARCHAR(5) ,
SAS_GEN_CD_8 VARCHAR(5) ,
SAS_GEN_AMT_1 DECIMAL(10,2),
SAS_GEN_AMT_2 DECIMAL(10,2),
SAS_GEN_AMT_3 DECIMAL(10,2),
SAS_GEN_AMT_4 DECIMAL(10,2),
SAS_GEN_DT_1 TIMESTAMP(0),
ADDR_SRC_SYS_CD VARCHAR(30) ,
DONOTCALL_NATIONAL VARCHAR(2) ,
DONOTCALL_EXEMPT_DESC VARCHAR(50) ,
QUAL_ASTS DECIMAL(20,3),
NON_QUAL_ASTS DECIMAL(20,3),
QUAL_ASTS_HELD_AWAY DECIMAL(20,3),
NON_QUAL_TAAST_HELD_AWAY DECIMAL(20,3),
PLAN_ID DECIMAL(18,0),
CLIENT_ID DECIMAL(18,0),
XIN_PLAN_REP_DATE TIMESTAMP(0),
PLAN_LEVEL DECIMAL(2,0),
GLOBAL_OPTOUTLG VARCHAR(1) ,
CAMP_ANN_OPTOUTLG VARCHAR(1) ,
CAMP_BROK_OPTOUTLG VARCHAR(1) ,
CAMP_EDU_OPTOUTLG VARCHAR(1) ,
CAMP_EMP_RET_OPTOUTLG VARCHAR(1) ,
CAMP_IRKEOGH_OPTOUT VARCHAR(1) ,
CAMP_LIFOPTOUTLG VARCHAR(1) ,
CAMP_RETND_OPTOUTLG VARCHAR(1) ,
CAMP_TRUST_OPTOUTLG VARCHAR(1) ,
NEWSLETTER_OPTOUTLG VARCHAR(1) ,
POALG VARCHAR(7) ,
TOT_AST DECIMAL(10,0),
IM_TOT_AST DECIMAL(22,7),
DEM_DONNELLY_HH_CUST_ID VARCHAR(14) ,
REMOTELG CHAR(1) ,
REMARKSLG CHAR(1) ,
NO_OUTBOUND_CALLS CHAR(1) ,
PROF_DESG_CODE VARCHAR(15) ,
ROLCD VARCHAR(15) ,
PROF_ORG_CD VARCHAR(15) ,
APIN VARCHAR(15) ,
TOP3_PROD_TYPE1 VARCHAR(15) ,
TOP3_PROD_TYPE2 VARCHAR(15) ,
XECOMMISSION_CD VARCHAR(15) ,
CST_TYPCD VARCHAR(15) ,
TOP3_SCORE1 VARCHAR(6) ,
CHANNEL VARCHAR(30) ,
SEGMENT VARCHAR(30) ,
RACF_ID VARCHAR(30) ,
EMP_STAT_CD VARCHAR(30) ,
JOB_TITLE VARCHAR(50) ,
ATTR_03 VARCHAR(30) ,
ATTR_04 VARCHAR(30) ,
ATTR_48 VARCHAR(30) ,
ATTR_50 VARCHAR(30) ,
ATTR_51 VARCHAR(30) ,
ATTR_52 VARCHAR(30) ,
ATTR_53 VARCHAR(30) ,
ATTR_54 VARCHAR(30) ,
ATTR_55 VARCHAR(30) ,
ATTR_39 VARCHAR(50) ,
BRLOC_ATTR03 DECIMAL(22,7),
HOMPH_NUM VARCHAR(50) ,
EMP_STATUS VARCHAR(30) ,
DEPENDS_NUM DECIMAL(10,0),
COUNTRY_OF_RES_CD VARCHAR(50) ,
REP_INCOMRANGE VARCHAR(30) ,
EMP_NAME VARCHAR(100) ,
EMP_ADDR VARCHAR(200) ,
EMP_ADDR_2 VARCHAR(100) ,
EMP_ADDR_3 VARCHAR(100) ,
EMP_CITY VARCHAR(50) ,
EMP_STATE VARCHAR(10) ,
EMP_COUNTRY VARCHAR(30) ,
EMP_ZIPCODE VARCHAR(30) ,
SEC_AFFILIATION CHAR(1) ,
ASSOCIATEDIRM VARCHAR(100) ,
CST_TYPCD VARCHAR(30) ,
AUTH_NAM1 VARCHAR(100) ,
AUTH_NAM2 VARCHAR(100) ,
AUTH_NAM3 VARCHAR(100) ,
DISCR_AUTHORITYLG CHAR(1) ,
DISCR_AUTH_NAME VARCHAR(100) ,
DISCR_AUTH_ADRS VARCHAR(200) ,
DISCR_AUTH_CITY VARCHAR(50) ,
DISCR_AUTH_STATE VARCHAR(30) ,
DISCR_AUTH_ZIP VARCHAR(30) ,
ADEQUATNET_WORTHLG CHAR(1) ,
RETIRSAVINGS CHAR(1) ,
COLLEGSAVINGSLG CHAR(1) ,
HOMPURCHASELG CHAR(1) ,
GROWTH_ASTSLG CHAR(1) ,
CAPITAL_PRESERVLG CHAR(1) ,
OTHR_OBJLG CHAR(1) ,
WHEN_NEEDED_CD VARCHAR(30) ,
INV_EXPERIENCCD CHAR(100) ,
PORTF_STOCKSLG CHAR(1) ,
PORTF_BONDSLG CHAR(1) ,
PORTFUNDSLG CHAR(1) ,
PORTF_ESTATELG CHAR(1) ,
PORTF_VALUE DECIMAL(22,7),
INV_STRATEGY VARCHAR(100) ,
GROSS_WORTH_CD VARCHAR(30) ,
NET_WORTH_CD VARCHAR(30) ,
SUITILING_STATUS_CD VARCHAR(30) ,
ADJUSTED_GROSS_CD VARCHAR(30) ,
SUITEDERAL_TARATE VARCHAR(50) ,
STATTARATE DECIMAL(22,7),
LOCAL_TARATE DECIMAL(22,7),
INVEST_OBJECT VARCHAR(50) ,
PERSONAL_SAVINGSLG CHAR(1) ,
PENSION_PLANLG CHAR(1) ,
IRROLLOVERLG CHAR(1) ,
GIFT_INHERETANCELG CHAR(1) ,
LIFINSURANCELG CHAR(1) ,
HOMMORTGAGELG CHAR(1) ,
PLAN_LOANLG CHAR(1) ,
HARDSHIP_WITHDRAWALLG CHAR(1) ,
OTHRUND_SOURCESLG CHAR(1) ,
OTHR_SOURCEUNDS VARCHAR(50) ,
DATORDER TIMESTAMP(0),
OCCUPATION VARCHAR(50) ,
EML VARCHAR(100) ,
WEB_ACCESS_CREATION_DT TIMESTAMP(0),
WEB_ACCESS_LAST_UPD_DT TIMESTAMP(0),
WEB_ACCESS_LAST_UPD_BY VARCHAR(30) ,
WEB_ACCESS_STAT_IND DECIMAL(1,0),
ATTR_17 DECIMAL(22,7),
ATTR_36 VARCHAR(50) ,
ATTR_33 TIMESTAMP(0),
ATTR_54 VARCHAR(100) ,
XALG VARCHAR(10) NOT CASESPECIFIC,
BENIND CHAR(1) NOT CASESPECIFIC,
BUDGET_CODE VARCHAR(5) NOT CASESPECIFIC,
EMP_AREA VARCHAR(5) NOT CASESPECIFIC,
END_DT TIMESTAMP(0) COMPRESS ,
MAIL_STOP VARCHAR(50) NOT CASESPECIFIC,
NICK_NAME VARCHAR(50) NOT CASESPECIFIC,
PAR_INTGRN_ID VARCHAR(30) NOT CASESPECIFIC,
PROFILE INTEGER DEFAULT 0 COMPRESS ,
CST_STATUS VARCHAR(15) ,
PRM_PAYING DECIMAL(10,0) COMPRESS ,
PRM_PAIDUP DECIMAL(10,0) COMPRESS ,
SER_MODE VARCHAR(30) NOT CASESPECIFIC,
REGION VARCHAR(30) NOT CASESPECIFIC,
OFFICE VARCHAR(50) NOT CASESPECIFIC,
CUST_SINCDT TIMESTAMP(0),
OUTREACH_DATE TIMESTAMP(0),
LAST_SURVEY_DT TIMESTAMP(0),
SURVEYLG CHAR(1) NOT CASESPECIFIC,
ATTR_08 CHAR(1) NOT CASESPECIFIC,
XM_ATTR_34 VARCHAR(50) NOT CASESPECIFIC,
PR_CLIENT_OU_ID VARCHAR(15) NOT CASESPECIFIC,
MKT_SAV_INV_RTRMT CHAR(1) NOT CASESPECIFIC,
MKT_LIV_WELL_RTRMT CHAR(1) NOT CASESPECIFIC,
MKT_SAV_EDU CHAR(1) NOT CASESPECIFIC,
MKT_PROT_MYSLFMLY CHAR(1) NOT CASESPECIFIC,
MKT_PERNCENEWS_LTR CHAR(1) NOT CASESPECIFIC,
MKT_WKLY_MRKT_MNTR CHAR(1) NOT CASESPECIFIC,
DEL_EML_ADDR VARCHAR(100) NOT CASESPECIFIC,
MKT_EML_ADDR VARCHAR(100) NOT CASESPECIFIC,
MKT_EML_STS CHAR(1) NOT CASESPECIFIC,
OUTREACH_DATE INTEGER DEFAULT 0 COMPRESS ,
REASSIGNMENT_DATE INTEGER DEFAULT 0 COMPRESS ,
ACTIVELAG CHAR(1) DEFAULT 'N',
WEB_ACCESS_LOGIN VARCHAR(20) ,
PRIM_PREM_PAID_ACCOUNT INTEGER COMPRESS ,
PRIM_PREM_DT INTEGER COMPRESS ,
ALIAS_NAME VARCHAR(50) ,
PREF_COMM_METH_CD VARCHAR(30) ,
LAST_UPD_DT TIMESTAMP(0),
AFFILIATION_CODE VARCHAR(30) ,
PROVINCE VARCHAR(50) ,
COM_PREFERENCE VARCHAR(30) ,
CON_CD VARCHAR(30) ,
COMPLEXITY VARCHAR(30) ,
ENGAGED CHAR(1) ,
SUITABILITY_DT TIMESTAMP(0),
ATTR_05 VARCHAR(30) ,
PREV_MNTH_CUST_STATUS VARCHAR(15) ,
MNTH_REMITTERLG CHAR(1) ,
ENG_PROPENSITY DECIMAL(10,8),
ATTR_35 VARCHAR(50) ,
LINKED_CON_ID VARCHAR(225) ,
WEB_USER_ID VARCHAR(30) ,
CONVERTEDLG CHAR(1) NOT CASESPECIFIC,
LOC INTEGER NOT NULL DEFAULT 0 ,
XIRM_NAME VARCHAR(100) NOT CASESPECIFIC,
ATTR_35 VARCHAR(50) NOT CASESPECIFIC,
ATTR_43 VARCHAR(50) NOT CASESPECIFIC,
ATTR_37 VARCHAR(50) NOT CASESPECIFIC,
ATTR_51 VARCHAR(100) NOT CASESPECIFIC,
ATTR_52 VARCHAR(100) NOT CASESPECIFIC,
ATTR_19 DECIMAL(22,7),
ATTR_40 VARCHAR(50) NOT CASESPECIFIC,
ATTR_42 VARCHAR(50) NOT CASESPECIFIC,
CELL_PH_NUM VARCHAR(40) NOT CASESPECIFIC,
ATTR_31 TIMESTAMP(0),
ATTR_46 VARCHAR(100) NOT CASESPECIFIC,
ATTR_09 CHAR(1) NOT CASESPECIFIC,
OFFICE INTEGER,
GEO INTEGER,
OFFICDSTNC DECIMAL(20,2),
CLIENT_TIER VARCHAR(2) NOT CASESPECIFIC,
COI_REL_QTY VARCHAR(2) NOT CASESPECIFIC,
HELD_AWAY_AST VARCHAR(2) NOT CASESPECIFIC,
MANAGED_ACC_AST VARCHAR(2) NOT CASESPECIFIC,
CRD_NUM VARCHAR(30) ,
SUP_CRD_NUM VARCHAR(30) ,
ASSIGN_UPD_BY_ID VARCHAR(15) )
UNIQUE PRIMARY INDEX PERSON_DUPI ( ROW );

Junior Contributor

Re: Err0r 5781- cannot collect stats on more than 32 groups of non-indexed columns teradata

According to explain this query should run fast, did you check the actual vs. estimated row count?

ACTVTY.CRTD_DT is an integer with yyyymmdd?

Then
CAST( CAST( F.crt_dt AS CHAR ( 8 ) ) AS DATE FORMAT 'YYYYMMDD' )

can be rewritten as
CAST(crt_dt - 19000000 AS DATE)

and
F.crt_dt = CAST( CAST( (DATE (FORMAT 'YYYY-MM-DD',
TITLE 'Current_Date()')) - 1 AS DATE FORMAT 'yyyymmdd' ) AS CHAR ( 8 ) )

with
F.crt_dt = CAST(DATE - 1 AS INT) + 19000000

The optimizer should resolve this to a hard-coded value (like 20110608) and get a better estimation for this condition.

Dieter