Spool Space/SQL Tune Improvement [From QUINN, JAY: Mon, 15 Jul 2002 @ 17:26 GMT]

Message Posted: Mon, 15 Jul 2002 @ 17:26:38 GMT

Subj:

Spool Space/SQL Tune Improvement

From:

QUINN, JAY

Hi all, I've got a piece of SQL that will run, and it uses 170GB, I'm looking for some advise to revise it in order to reduce the amount of spool space it's using. Please find the SQL, the Explain and DDL's of the tables being used. Please let me know what else anyone may need to help understand anything in particular. Thanks in advance!

Jay

SELECT trim(A.BAN) (char(9)), LD_ACCESS_VIEWS.VSLC027_CUST_PROD_ASSIGNMENT.INTRALATA_CIC, A.CYCLE_RUN_YEAR, A.TOTAL_DUE_AMT, count(A.BAN), sum(A.BF_DATA_MON_SRV_CHG), sum(A.BF_MON_SRV_CHG), sum(A.CURR_CREDIT_AMT), sum(A.TOTAL_BILLED_ADJUST), sum(A.CURR_CHARGE_AMT), sum(A.CURR_OC_CHRG_AMT), sum(A.CURR_RC_CHRG_AMT), sum(A.CURR_UC_CHRG_AMT), sum(A.PRODUCTS_NUM_CALLS), sum(A.PRODUCTS_NUM_MINS), A.CYCLE_CODE, A.CYCLE_RUN_MONTH, LD_ACCESS_VIEWS.VSLR017_PRICE_PLAN.PICC_STATE FROM ( SELECT BAN, CYCLE_RUN_YEAR, TOTAL_DUE_AMT, BF_DATA_MON_SRV_CHG, BF_MON_SRV_CHG, CURR_CREDIT_AMT, TOTAL_BILLED_ADJUST, CURR_CHARGE_AMT, CURR_OC_CHRG_AMT, CURR_RC_CHRG_AMT, CURR_UC_CHRG_AMT, PRODUCTS_NUM_CALLS, PRODUCTS_NUM_MINS, CYCLE_CODE, CYCLE_RUN_MONTH FROM LD_ACCESS_VIEWS.VSLB001_BILL WHERE CYCLE_RUN_YEAR = 2001 AND CYCLE_RUN_MONTH = 2 AND CYCLE_CODE = 2 ) A, LD_ACCESS_VIEWS.VSLC027_CUST_PROD_ASSIGNMENT, LD_ACCESS_VIEWS.VSLR017_PRICE_PLAN, LD_ACCESS_VIEWS.VSLC007_BILLING_ACCOUNT, LD_ACCESS_VIEWS.VSLC033_CUST_SERVICE_AGRMT WHERE ( LD_ACCESS_VIEWS.VSLC027_CUST_PROD_ASSIGNMENT.CUSTOMER_BAN=LD_ACCESS_VIEWS.VSLC007_BILLING_ACCOUNT.BAN ) AND ( A.BAN=LD_ACCESS_VIEWS.VSLC007_BILLING_ACCOUNT.BAN ) AND (LD_ACCESS_VIEWS.VSLC007_BILLING_ACCOUNT.BAN=LD_ACCESS_VIEWS.VSLC033_CUST_SERVICE_AGRMT.BAN ) AND (LD_ACCESS_VIEWS.VSLC033_CUST_SERVICE_AGRMT.PRICE_PLAN=LD_ACCESS_VIEWS.VSLR017_PRICE_PLAN.PRICE_PLAN ) AND (LD_ACCESS_VIEWS.VSLR017_PRICE_PLAN.PICC_STATE = 'TX' ) GROUP BY 1, 2, 3, 4, 16, 17, 18

Explanation --------------------------------------------------

1)

First, we lock CUSTOMER.TSLC033_CUST_SERVICE_AGRMT for access, we lock CUSTOMER.TSLC027_CUST_PROD_ASSIGNMENT for access, we lock CUSTOMER.TSLC007_BILLING_ACCOUNT for access, we lock REFERENCE.TSLR017_PRICE_PLAN for access, and we lock BILLING.TSLB001_BILL for access.

2)

Next, we do an all-AMPs RETRIEVE step from BILLING.TSLB001_BILL by way of an all-rows scan with a condition of ("(BILLING.TSLB001_BILL.CYCLE_CODE = 2.) AND ((BILLING.TSLB001_BILL.CYCLE_RUN_MONTH = 2.) AND (BILLING.TSLB001_BILL.CYCLE_RUN_YEAR = 2001. ))") into Spool 1, which is redistributed by hash code to all AMPs. The size of Spool 1 is estimated with no confidence to be 3,927,305 rows. The estimated time for this step is 34.08 seconds.

3)

We execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of an all-rows scan into Spool 4, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 4 by row hash. The size of Spool 4 is estimated with no confidence to be 3,927,305 rows. The estimated time for this step is 4.81 seconds.

2) We do an all-AMPs RETRIEVE step from CUSTOMER.TSLC007_BILLING_ACCOUNT by way of an all-rows scan with no residual conditions into Spool 5, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 5 by row hash. The size of Spool 5 is estimated with high confidence to be 5,710,187 rows. The estimated time for this step is 7.94 seconds.

3) We do an all-AMPs RETRIEVE step from REFERENCE.TSLR017_PRICE_PLAN by way of an all-rows scan with a condition of ("REFERENCE.TSLR017_PRICE_PLAN.PICC_STATE = 'TX'") into Spool 6, which is duplicated on all AMPs. Then we do a SORT to order Spool 6 by row hash. The size of Spool 6 is estimated with no confidence to be 742,560 rows. The estimated time for this step is 4.44 seconds.

4) We do an all-AMPs RETRIEVE step from CUSTOMER.TSLC033_CUST_SERVICE_AGRMT by way of an all-rows scan with no residual conditions into Spool 7, which is built locally on the AMPs. Then we do a SORT to order Spool 7 by row hash. The size of Spool 7 is estimated with high confidence to be 35,240,615 rows. The estimated time for this step is 10.78 seconds.

5) We do an all-AMPs RETRIEVE step from CUSTOMER.TSLC027_CUST_PROD_ASSIGNMENT by way of an all-rows scan with no residual conditions into Spool 8, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 8 by row hash. The size of Spool 8 is estimated with high confidence to be 14,132,658 rows. The estimated time for this step is 20.33 seconds.

4)

We execute the following steps in parallel.

1) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of a RowHash match scan., which is joined to Spool 5 (Last Use). Spool 4 and Spool 5 are joined using a merge join, with a join condition of ("BAN = (BAN (CHAR(10), CHARACTER SET LATIN, NOT CASESPECIFIC, FORMAT '---------9.')(CHAR(10), CHARACTER SET LATIN, NOT CASESPECIFIC, NAMED BAN ))"). The result goes into Spool 9, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 9 by row hash. The size of Spool 9 is estimated with no confidence to be 205,894,886 to 22,425,645,956,035 rows. The estimated time for this step is 2 minutes and 17 seconds to 10,903 hours and 2 minutes.

2) We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of a RowHash match scan., which is joined to Spool 7 (Last Use). Spool 6 and Spool 7 are joined using a merge join, with a join condition of ("PRICE_PLAN = PRICE_PLAN"). The result goes into Spool 10, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 10 by row hash. The size of Spool 10 is estimated with no confidence to be 35,240,615 to 12,580,899,555 rows. The estimated time for this step is 8.17 seconds to 48 minutes and 1 second.

5)

We do an all-AMPs JOIN step from Spool 8 (Last Use) by way of a RowHash match scan., which is joined to Spool 9 (Last Use). Spool 8 and Spool 9 are joined using a merge join, with a join condition of ("(CUSTOMER_BAN (CHAR(10), CHARACTER SET LATIN, NOT CASESPECIFIC, FORMAT '---------9.')(CHAR(10), CHARACTER SET LATIN, NOT CASESPECIFIC, NAMED CUSTOMER_BAN ))= (BAN (CHAR(10), CHARACTER SET LATIN, NOT CASESPECIFIC, FORMAT '---------9.')(CHAR(10), CHARACTER SET LATIN, NOT CASESPECIFIC, NAMED BAN ))"). The result goes into Spool 11, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 11 by row hash. The result spool file will not be cached in memory. The size of Spool 11 is estimated with no confidence to be 4,446,762,230 to *** rows. The estimated time for this step is 1 hour and 14 minutes to 304,593,680,611 hours and 38 minutes.

6)

We do an all-AMPs JOIN step from Spool 10 (Last Use) by way of a RowHash match scan., which is joined to Spool 11 (Last Use). Spool 10 and Spool 11 are joined using a merge join, with a join condition of ("(BAN (CHAR(10), CHARACTER SET LATIN, NOT CASESPECIFIC, FORMAT '--- ------9.')(CHAR(10), CHARACTER SET LATIN, NOT CASESPECIFIC, NAMED BAN ))= (BAN (CHAR(10), CHARACTER SET LATIN, NOT CASESPECIFIC, FORMAT '--- ------9.')(CHAR(10), CHARACTER SET LATIN, NOTCASESPECIFIC, NAMED BAN ))"). The result goes into Spool 3, which is built locally on the AMPs. The result spool file will not be cached in memory. The size of Spool 3 is estimated with no confidence to be 51,528,076,680 to *** rows. The estimated time for this step is 2 hours and 53 minutes to *** hours.

7)

We do a SUM step to aggregate from Spool 3 (Last Use) by way of an all-rows scan, and the grouping identifier in field 1. Aggregate Intermediate Results are computed globally, then placed in Spool 12. The aggregate spool file will not be cached in memory. The size of Spool 12 is estimated with no confidence to be 46,375,269,012 to *** rows.

8)

We do an all-AMPs RETRIEVE step from Spool 12 (Last Use) by way of an all-rows scan into Spool 2, which is built locally on the AMPs. The result spool file will not be cached in memory. The size of Spool 2 is estimated with no confidence to be 46,375,269,012 to *** rows. The estimated time for this step is 4 hours and 27 minutes to *** hours.

9)

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.

Request Text ------------------------------------------------------------------------- CREATE SET TABLE customer.TSLC027_CUST_PROD_ASSIGNMENT ,FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL ( PRODUCT_ID CHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, PRODUCT_TYPE CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, CUSTOMER_ID DECIMAL(9,0) NOT NULL, SYS_CREATION_DATE DATE FORMAT 'YYYYMMDD' NOT NULL DEFAULT DATE , SYS_UPDATE_DATE DATE FORMAT 'YYYYMMDD', OPERATOR_ID DECIMAL(9,0) NOT NULL DEFAULT 0. , APPLICATION_ID CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', DL_SERVICE_CODE CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', DL_UPDATE_STAMP DECIMAL(4,0) NOT NULL DEFAULT 0. , EFFECTIVE_DATE DATE FORMAT 'YYYYMMDD', INIT_ACTIVATION_DATE DATE FORMAT 'YYYYMMDD', PROD_STATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', PROD_STATUS_DATE DATE FORMAT 'YYYYMMDD', PROD_STATUS_LAST_ACT CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', PROD_STATUS_RSN_CODE CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CUSTOMER_BAN DECIMAL(9,0) NOT NULL DEFAULT 0. , SUB_MARKET CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', TAX_CTY_EXMP_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', TAX_CNT_EXMP_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', TAX_STT_EXMP_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', TAX_FED_EXMP_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', REQ_ST_GRACE_PERIOD DATE FORMAT 'YYYYMMDD', REQ_END_GRACE_PERIOD DATE FORMAT 'YYYYMMDD', COMMIT_START_DATE DATE FORMAT 'YYYYMMDD', COMMIT_END_DATE DATE FORMAT 'YYYYMMDD', COMMIT_REASON_CODE CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', COMMIT_ORIG_NO_MONTH DECIMAL(3,0) NOT NULL DEFAULT 0. , SUSP_RC_RATE_TYPE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', PROD_GROUP_NO DECIMAL(3,0) NOT NULL DEFAULT 0. , RAO CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', REP_CODE CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', REQ_DEPOSIT_AMT DECIMAL(11,2) NOT NULL DEFAULT 0.00 , NEXT_PROD CHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', NEXT_PROD_CHG_DATE DATE FORMAT 'YYYYMMDD', PRV_PROD CHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', PRV_PROD_CHG_DATE DATE FORMAT 'YYYYMMDD', NEXT_BAN DECIMAL(9,0) NOT NULL DEFAULT 0. , NEXT_BAN_MOVE_DATE DATE FORMAT 'YYYYMMDD', PRV_BAN DECIMAL(9,0) NOT NULL DEFAULT 0. , PRV_BAN_MOVE_DATE DATE FORMAT 'YYYYMMDD', PROD_STS_ISSUE_DATE DATE FORMAT 'YYYYMMDD', ACTIVATE_WAIVE_RSN CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', EARLIEST_ACTV_DATE DATE FORMAT 'YYYYMMDD', PROD_CREATION_SRC CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', PROD_USAGE_THRESHOLD DECIMAL(9,2) NOT NULL DEFAULT 0.00 , TPV_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', TN_VOICE_DATA_TP CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CARRIER_ID CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CARRIER_ACCOUNT_ID VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CARR_DEPOSIT_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CARR_STATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', DUAL_PIC_CD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', LOA_SENT_DATE DATE FORMAT 'YYYYMMDD', LOA_RECEIVED_DATE DATE FORMAT 'YYYYMMDD', PIC_CNG_CHARGE_TP CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', TERMINAL_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', POTS CHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT '', PERSONAL_800 CHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', TYPE_800 CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', VANITY_TRANSLATION VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT '', PREV_LDC VARCHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT '', CALLING_CARD_QTY DECIMAL(4,0) NOT NULL DEFAULT 0. , CALLING_CARD_TYPE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BATCH_ID VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', INTERLATA_CRE_SOURCE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', INTRALATA_CRE_SOURCE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', LOGO_CODE CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', COMPANY_CODE CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', ORIG_PROD_SEQ_NO DECIMAL(9,0) NOT NULL DEFAULT 0. , DEALER_CODE CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', SPEAKING_LANGUAGE CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', FULFILLMENT_LANGUAGE CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', INTERLATA_CIC CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', INTRALATA_CIC CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', ISDN_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', DIR_ASSIST_NAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', DIR_ASSIST_DATE DATE FORMAT 'YYYYMMDD', PERS_RING_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', ORDER_NO DECIMAL(9,0) NOT NULL DEFAULT 0. , CIS_ID DECIMAL(10,0) NOT NULL DEFAULT 0. , TKN DATE FORMAT 'YYYYMMDD', CUSTOMER_REQ_DATE DATE FORMAT 'YYYYMMDD', ACTIVATION_STATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CUST_CONCESSION_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', PIC_RESTRICTION CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', TOLL_BLOCK_CD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', PROD_ACTV_SRC CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', TM_CALL_ORIGINATOR CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', AOS_INCLUDES_CANADA CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', SBC_CIRCUIT_ID VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT '', LEC_SALE_CODE VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT '', P_POTS_STATUS_DATE DATE FORMAT 'YYYYMMDD', O_POTS_STATUS_DATE DATE FORMAT 'YYYYMMDD', TFS_STATUS_DATE DATE FORMAT 'YYYYMMDD', PIC_LINE_TYPE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', SC_WAIVE_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', SC_WAIVE_CHANGE_DT DATE FORMAT 'YYYYMMDD', SUB_PROD_TP CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', NT_SOL_SEQ_NO DECIMAL(8,0) NOT NULL DEFAULT 0. , NT_SOL_TP CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', INTER_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', LD_INTL_CIRCID CHAR(44) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', PR_VENDOR_CIRCID CHAR(44) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', ECCKT_ID_A CHAR(44) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', ECCKT_ID_Z CHAR(44) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', LAP_LOC_A CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', LAP_LOC_Z CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', FRN_CAR_ID CHAR(44) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', FRN_PRVDR CHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', LD_POP VARCHAR(29) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', LD_ORDER_NO CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', PON CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', TECH_INFO_NM VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', TECH_INFO_TN CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', VPN_ORDR_ID DECIMAL(9,0) NOT NULL DEFAULT 0. , TERM_START_DT DATE FORMAT 'YYYYMMDD', TERM_END_DT DATE FORMAT 'YYYYMMDD', CMP_ASSN_CD CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', TEST_EXP_DATE DATE FORMAT 'YYYYMMDD', TEST_EFF_DATE DATE FORMAT 'YYYYMMDD', TEST_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', TEST_IND_CHANGE_DATE DATE FORMAT 'YYYYMMDD', STFS_ORDER_NO DECIMAL(9,0) NOT NULL DEFAULT 0. , TEMP_STFS_NO DECIMAL(7,0) NOT NULL DEFAULT 0. , MIGRATED_TFS_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', VPN_PROV_STATUS_DATE DATE FORMAT 'YYYYMMDD', VPN_PROV_CNF_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', PROVIDER_CODE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', AUDIO_FEATURES_DATE DATE FORMAT 'YYYYMMDD', LSP_ID CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', PRODUCT_LABEL CHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', TERM DECIMAL(3,0) NOT NULL DEFAULT 0. , TERM_UOM CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', AFFILIATE_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', NEW_INTERLATA_CIC CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', NEW_INTRALATA_CIC CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', INTERLATA_CIC_CHANGE_STATUS_CD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', INTRALATA_CIC_CHANGE_STATUS_CD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', ORIGINAL_ACTIVE_DT DATE FORMAT 'YYYYMMDD', VPN_DIALING_PLAN_STATUS_CD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', DIAL_PLAN_CD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', SET_ASSOCIATION_VALUE_CD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', DW_MODIFY_DT DATE FORMAT 'YYYYMMDD', DW_LOAD_DT DATE FORMAT 'YYYYMMDD', COMPENSATION_TRACKING_ORD_NBR VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ') UNIQUE PRIMARY INDEX ( PRODUCT_ID ,PRODUCT_TYPE ,CUSTOMER_ID ) INDEX ( CUSTOMER_ID ) INDEX ( ORDER_NO ) INDEX ( CUSTOMER_BAN ) INDEX ( PROD_STATUS ); Request Text ------------------------------------------------------------------------- CREATE SET TABLE customer.TSLC007_BILLING_ACCOUNT ,FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL ( BAN DECIMAL(9,0) NOT NULL, SYS_CREATION_DATE DATE FORMAT 'YYYYMMDD', SYS_UPDATE_DATE DATE FORMAT 'YYYYMMDD', OPERATOR_ID DECIMAL(9,0) NOT NULL DEFAULT 0. , APPLICATION_ID CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', DL_SERVICE_CODE CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', DL_UPDATE_STAMP DECIMAL(4,0) NOT NULL DEFAULT 0. , CUSTOMER_ID DECIMAL(9,0) NOT NULL DEFAULT 0. , CUSTOMER_TYPE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', AR_EXCPT_ACCT_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BAN_STATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', STATUS_LAST_DATE DATE FORMAT 'YYYYMMDD', STATUS_ACTV_CODE CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', STATUS_ACTV_RSN_CODE CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BAN_CREATION_SRC CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', START_SERVICE_DATE DATE FORMAT 'YYYYMMDD', DEFAULT_SUB_MARKET CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', ACCOUNT_TYPE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BL_LANGUAGE_CD CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', AFFINITY_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CBN_ID VARCHAR(14) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', PAYING_BAN DECIMAL(9,0) NOT NULL DEFAULT 0. , BC_CARRIER_ID CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BC_CARRIER_ACCT_ID VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', AR_BALANCE DECIMAL(9,2) NOT NULL DEFAULT 0.00 , AR_WO_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', AR_USAGE_THRESHOLD DECIMAL(9,2) NOT NULL DEFAULT 0.00 , COL_DELINQ_STATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', COL_DELINQ_STS_DATE DATE FORMAT 'YYYYMMDD', COL_WAIVER_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', COL_WAIVER_DATE DATE FORMAT 'YYYYMMDD', COL_WAIVER_UID CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', COL_CATEGORY_NO DECIMAL(4,0) NOT NULL DEFAULT 0. , COL_PATH_CODE CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', COL_NEXT_STEP_NO DECIMAL(2,0) NOT NULL DEFAULT 0. , COL_NEXT_STEP_DATE DATE FORMAT 'YYYYMMDD', COL_NEXT_STP_APR_COD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', COL_NEXT_STP_APR_COL CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', COL_ASSIGNED_COLL CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', COL_COLCT_ASD_DATE DATE FORMAT 'YYYYMMDD', COL_FIRST_CNTCT_DATE DATE FORMAT 'YYYYMMDD', COL_AGNCY_CODE CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', COL_AGNCY_ASD_DATE DATE FORMAT 'YYYYMMDD', COL_AGNCY_ASD_AMT DECIMAL(9,2) NOT NULL DEFAULT 0.00 , BILL_CYCLE DECIMAL(2,0) NOT NULL DEFAULT 0. , BL_CUR_BILL_SEQ_NO DECIMAL(3,0) NOT NULL DEFAULT 0. , HIST_LAST_DATE DATE FORMAT 'YYYYMMDD', BL_BILLING_METHOD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BL_BANK_CODE CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BL_BANK_ACCT_NO CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BL_SPECL_CYCL_TYPE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BL_SPECIAL_CYCLE DECIMAL(2,0) NOT NULL DEFAULT 0. , BL_MEDIA_CATEGORY CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BL_LAST_PROD_DATE DATE FORMAT 'YYYYMMDD', BL_LAST_RG_CYC_DATE DATE FORMAT 'YYYYMMDD', BL_ZERO_BALANC_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BL_MAN_HNDL_REQ_OPID DECIMAL(9,0) NOT NULL DEFAULT 0. , BL_MAN_HNDL_BY_OPID DECIMAL(9,0) NOT NULL DEFAULT 0. , BL_MAN_HNDL_RSN CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BL_MAN_HNDL_EFF_DATE DATE FORMAT 'YYYYMMDD', BL_COMPLT_STATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BL_COMPLT_DATE DATE FORMAT 'YYYYMMDD', BL_PRT_CATEGORY CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BL_LAST_CYC_RUN_YEAR DECIMAL(4,0) NOT NULL DEFAULT 0. , BL_LAST_CYC_RUN_MNTH DECIMAL(2,0) NOT NULL DEFAULT 0. , BL_REQ_ST_GRACE_PRD DATE FORMAT 'YYYYMMDD', BL_REQ_END_GRACE_PRD DATE FORMAT 'YYYYMMDD', BL_NEXT_CYCLE DECIMAL(2,0) NOT NULL DEFAULT 0. , BL_NEXT_CYC_EFF_DATE DATE FORMAT 'YYYYMMDD', BL_SUSP_RC_RATE_TYPE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BL_MAJOR_ACCOUNT_NO CHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BL_LAST_BC_CARRIER CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BL_NEXT_BC_CARRIER CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BL_NEXT_CARR_EFF_DT DATE FORMAT 'YYYYMMDD', BL_PRT_BREAK VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', RAO_CODE CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BC_CARR_BILL_PERIOD DECIMAL(2,0) NOT NULL DEFAULT 0. , AGREGATE_BILL_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CS_COM_START_DATE DATE FORMAT 'YYYYMMDD', CS_COM_END_DATE DATE FORMAT 'YYYYMMDD', CS_COM_REASON_CODE CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CS_COM_ORIG_NO_MONTH DECIMAL(3,0) NOT NULL DEFAULT 0. , CS_SPEC_MEMO_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', STATUS_ISSUE_DATE DATE FORMAT 'YYYYMMDD', CS_VIP_CLASS CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CREDIT_CLASS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BL_TAX_EXMP_REN_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BL_LAST_CYC_CODE DECIMAL(2,0) NOT NULL DEFAULT 0. , BL_LAST_RG_BL_SEQ_NO DECIMAL(3,0) NOT NULL DEFAULT 0. , BL_PROD_FREQ DECIMAL(2,0) NOT NULL DEFAULT 0. , BL_SPCL_BAN_CODE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BL_MAN_HNDL_EXP_DATE DATE FORMAT 'YYYYMMDD', CS_MIN_REQ_PRODS DECIMAL(7,0) NOT NULL DEFAULT 0. , CS_PEN_PER_MISS_PROD DECIMAL(9,2) NOT NULL DEFAULT 0.00 , CS_FOLLOW_UPS_STATE CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CS_STUDY_SEC CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BL_DEF_MAILING_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BL_BILL_PROD_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BL_BAL_HANDLE_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CREDIT_CARD_NO CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CREDIT_CARD_TYPE CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CR_CARD_REF_NO DECIMAL(10,0) NOT NULL DEFAULT 0. , CR_CARD_EXPIR_DATE DATE FORMAT 'YYYYMMDD', CR_CARD_AUTH_CODE CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BL_BILL_METH_DATE DATE FORMAT 'YYYYMMDD', BL_BILL_METH_STATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BL_BILL_METH_STS_DATE DATE FORMAT 'YYYYMMDD', BL_UD_LST_PROD_DATE DATE FORMAT 'YYYYMMDD', BL_UD_LST_CYC_RUN_YR DECIMAL(4,0) NOT NULL DEFAULT 0. , BL_UD_LST_CYC_RUN_MN DECIMAL(2,0) NOT NULL DEFAULT 0. , BL_UD_LST_RG_BL_SEQNO DECIMAL(3,0) NOT NULL DEFAULT 0. , BL_UD_LST_RG_CYC_DATE DATE FORMAT 'YYYYMMDD', BL_UD_LST_CYC_CD DECIMAL(2,0) NOT NULL DEFAULT 0. , BL_UD_SPCL_CYC DECIMAL(2,0) NOT NULL DEFAULT 0. , BL_UD_SPCL_CYC_TYPE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BL_CORP_BL_EST_DATE DATE FORMAT 'YYYYMMDD', NUM_OF_PRODUCT DECIMAL(7,0) NOT NULL DEFAULT 0. , DEPOSIT_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BLOCK_TRANS_NUM DECIMAL(7,0) NOT NULL DEFAULT 0. , BLOCK_TRANS_DATE DATE FORMAT 'YYYYMMDD', AR_CUST_THRESHOLD DECIMAL(9,2) NOT NULL DEFAULT 0.00 , CLM_SKIP_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', ORDER_NO DECIMAL(9,0) NOT NULL DEFAULT 0. , CS_CPNI_CODE CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', SEND_RAO_CODE CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', DEAF_DISABLED CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', DISABILITY_CODE DECIMAL(2,0) NOT NULL DEFAULT 0. , MAC_AMT DECIMAL(9,2) NOT NULL DEFAULT 0.00 , CID CHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CH_BAN_SEQ_NO DECIMAL(10,0) NOT NULL DEFAULT 0. , LST_MLB_MAS_CHG_DATE DATE FORMAT 'YYYYMMDD', MLB_ACC_LAST_DATE DATE FORMAT 'YYYYMMDD', BILLING_TYPE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CUST_TP_CHG_DATE DATE FORMAT 'YYYYMMDD', SPCL_BAN_CD_CHG_DT DATE FORMAT 'YYYYMMDD', SC_WAIVE_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', SC_WAIVE_CHANGE_DT DATE FORMAT 'YYYYMMDD', NEXT_BAN DECIMAL(9,0) NOT NULL DEFAULT 0. , PREV_BAN DECIMAL(9,0) NOT NULL DEFAULT 0. , ORIG_BC_CARR_ACCT_ID VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', NUM_CHILDREN_REJECTS DECIMAL(9,0) NOT NULL DEFAULT 0. , BAN_CREATION_DATE DATE FORMAT 'YYYYMMDD', ORIG_BAN_CRE_DATE DATE FORMAT 'YYYYMMDD', BAN_ACTV_STATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CORP_BAN_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', REVENUE_REGION_CHANNEL_CD CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC, BILL_LANGUAGE_SYS_UPDATE_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', DW_MODIFY_DT DATE FORMAT 'YYYYMMDD', DW_LOAD_DT DATE FORMAT 'YYYYMMDD', CREDIT_SCORE_NBR DECIMAL(3,0) NOT NULL DEFAULT 0. , LEC_BTN VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ') UNIQUE PRIMARY INDEX ( BAN ) INDEX ( CUSTOMER_ID ) INDEX ( CUSTOMER_TYPE ); Request Text ------------------------------------------------------------------------- CREATE SET TABLE customer.TSLC033_CUST_SERVICE_AGRMT ,FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL ( BAN DECIMAL(9,0) NOT NULL, PRODUCT_ID CHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, PRODUCT_TYPE CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, PRICE_PLAN CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, PP_SEQ_NO DECIMAL(9,0) NOT NULL, SYS_CREATION_DATE DATE FORMAT 'YYYYMMDD', SYS_UPDATE_DATE DATE FORMAT 'YYYYMMDD', OPERATOR_ID DECIMAL(9,0) NOT NULL DEFAULT 0. , APPLICATION_ID CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', DL_SERVICE_CODE CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', DL_UPDATE_STAMP DECIMAL(4,0) NOT NULL DEFAULT 0. , PP_VER_NO DECIMAL(9,0) NOT NULL DEFAULT 0. , PP_EFFECTIVE_DATE DATE FORMAT 'YYYYMMDD', CUSTOMER_ID DECIMAL(9,0) NOT NULL DEFAULT 0. , EFFECTIVE_DATE DATE FORMAT 'YYYYMMDD', SERVICE_TYPE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', EXPIRATION_DATE DATE FORMAT 'YYYYMMDD', PP_LEVEL_CODE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', DEALER_CODE CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CONV_RUN_NO DECIMAL(3,0) NOT NULL DEFAULT 0. , EFFECTIVE_ISSUE_DATE DATE FORMAT 'YYYYMMDD', EXPIRATION_ISSUE_DATE DATE FORMAT 'YYYYMMDD', TRX_ID DECIMAL(9,0) NOT NULL DEFAULT 0. , ORDER_NO DECIMAL(9,0) NOT NULL DEFAULT 0. , FTR_SPREAD_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', SA_UNIQUE_ID DECIMAL(9,0) NOT NULL DEFAULT 0. , ORIG_FULL_PRICE_PLAN CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', ORIG_FULL_PP_EFF_DATE DATE FORMAT 'YYYYMMDD', REVERSE_HANDLE_STS CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', PP_MAC_MMC_INT CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', PP_SPECIAL_INFO CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BILL_PENALTY_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', RENEWAL_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CREDIT_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', DW_MODIFY_DT DATE FORMAT 'YYYYMMDD', DW_LOAD_DT DATE FORMAT 'YYYYMMDD') UNIQUE PRIMARY INDEX ( BAN ,PRODUCT_ID ,PRODUCT_TYPE ,PRICE_PLAN ,PP_SEQ_NO ) INDEX ( BAN ); Request Text ------------------------------------------------------------------------- CREATE SET TABLE reference.TSLR017_PRICE_PLAN ,FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL ( PRICE_PLAN CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, EFFECTIVE_DATE DATE FORMAT 'YYYYMMDD' NOT NULL, SYS_CREATION_DATE DATE FORMAT 'YYYYMMDD', SYS_UPDATE_DATE DATE FORMAT 'YYYYMMDD', OPERATOR_ID DECIMAL(9,0) NOT NULL DEFAULT 0. , APPLICATION_ID CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', DL_SERVICE_CODE CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', DL_UPDATE_STAMP DECIMAL(4,0) NOT NULL DEFAULT 0. , PRICE_PLAN_STATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', SALE_EFF_DATE DATE FORMAT 'YYYYMMDD', SALE_EXP_DATE DATE FORMAT 'YYYYMMDD', PRICE_PLAN_DESCR CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', SERVICE_TYPE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', PRICE_PLAN_LEVEL_CD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', FOR_SALE_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', MIN_REQ_PRODUCTS DECIMAL(3,0) NOT NULL DEFAULT 0. , MINIMUM_NO_MONTHS DECIMAL(3,0) NOT NULL DEFAULT 0. , MIN_REQ_USG_AMOUNT DECIMAL(9,2) NOT NULL DEFAULT 0.00 , MIN_REQ_UOM CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', MIN_REQ_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', ET_MAX_PEN_RATE DECIMAL(9,2) NOT NULL DEFAULT 0.00 , ET_MIN_PEN_RATE DECIMAL(9,2) NOT NULL DEFAULT 0.00 , MIN_REQ_USG_LEVEL CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', INTRA_LATA_IND CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', SAVE_AMT_PRST_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', JM_PP_AVLB_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BF_ENHANCED_PP_NUM CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', PP_JURISDICTION CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', LEVEL_1 DECIMAL(3,0) NOT NULL DEFAULT 0. , LEVEL_2 DECIMAL(3,0) NOT NULL DEFAULT 0. , LEVEL_3 DECIMAL(3,0) NOT NULL DEFAULT 0. , LEVEL_4 DECIMAL(3,0) NOT NULL DEFAULT 0. , LEVEL_5 DECIMAL(3,0) NOT NULL DEFAULT 0. , PICC_STATE CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', PP_MAC_MMC_INT CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', MAC_MMC DECIMAL(9,2) NOT NULL DEFAULT 0.00 , TERM DECIMAL(2,0) NOT NULL DEFAULT 0. , INT_PENALTY DECIMAL(2,0) NOT NULL DEFAULT 0. , PP_DURATION_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', PP_DURATION DECIMAL(3,0) NOT NULL DEFAULT 0. , DURATION_UOM CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', RC_MAC_MMC CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', PP_SPECIAL_INFO CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CREDIT_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', ENVIRONMENT_TYPE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', NOPP_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CONFLICT_GROUP_ID DECIMAL(5,0) NOT NULL DEFAULT 0. , EXPIRATION_DATE DATE FORMAT 'YYYYMMDD', VPN_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ') UNIQUE PRIMARY INDEX ( PRICE_PLAN ,EFFECTIVE_DATE ); Request Text ------------------------------------------------------------------------- CREATE SET TABLE billing.TSLB001_BILL ,FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL ( BAN DECIMAL(9,0) NOT NULL, BILL_SEQ_NO DECIMAL(3,0) NOT NULL, SYS_CREATION_DATE DATE FORMAT 'YYYYMMDD', SYS_UPDATE_DATE DATE FORMAT 'YYYYMMDD', OPERATOR_ID DECIMAL(9,0) NOT NULL DEFAULT 0. , APPLICATION_ID CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', DL_SERVICE_CODE CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', DL_UPDATE_STAMP DECIMAL(4,0) NOT NULL DEFAULT 0. , CYCLE_CODE DECIMAL(2,0) NOT NULL DEFAULT 0. , CYCLE_RUN_YEAR DECIMAL(4,0) NOT NULL DEFAULT 0. , CYCLE_RUN_MONTH DECIMAL(2,0) NOT NULL DEFAULT 0. , CYCLE_CLOSE_DATE DATE FORMAT 'YYYYMMDD', BILL_DUE_DATE DATE FORMAT 'YYYYMMDD', BILL_DATE DATE FORMAT 'YYYYMMDD', PRD_CVRG_STRT_DATE DATE FORMAT 'YYYYMMDD', PRD_CVRG_END_DATE DATE FORMAT 'YYYYMMDD', BAN_STATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BAN_STATUS_DATE DATE FORMAT 'YYYYMMDD', BILL_CONF_STATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BILL_STATUS_DATE DATE FORMAT 'YYYYMMDD', BILLING_METHOD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BANK_ACCOUNT_NO CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BANK_CODE CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CREDIT_CARD_NO CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CREDIT_CARD_TYPE CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CR_CARD_AUTH_CODE CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CR_CARD_EXP_DATE DATE FORMAT 'YYYYMMDD', PRODUCTION_DATE DATE FORMAT 'YYYYMMDD', NM_ADR_LINK_SEQ_NO DECIMAL(9,0) NOT NULL DEFAULT 0. , AFTER_CYCLE_CHG_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', BL_BAL_HANDLE_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', PRODUCTION_TYPE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', PRODUCTION_REQUEST CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', CARRY_OVER_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', NUM_OF_PRODUCTS DECIMAL(7,0) NOT NULL DEFAULT 0. , PRODUCTS_NUM_CALLS DECIMAL(8,0) FORMAT '------9.' NOT NULL DEFAULT 0., PRODUCTS_NUM_MINS DECIMAL(11,2) NOT NULL DEFAULT 0.00 , DEPOSIT_PAID_AMT DECIMAL(9,2) NOT NULL DEFAULT 0.00 , DEPOSIT_REQ_AMT DECIMAL(9,2) NOT NULL DEFAULT 0.00 , PREV_BALANCE_AMT DECIMAL(9,2) NOT NULL DEFAULT 0.00 , PYM_RECEIVED_AMT DECIMAL(9,2) NOT NULL DEFAULT 0.00 , TOTAL_BILLED_CHARGE DECIMAL(9,2) NOT NULL DEFAULT 0.00 , TOTAL_BILLED_ADJUST DECIMAL(9,2) NOT NULL DEFAULT 0.00 , PAST_DUE_AMT DECIMAL(9,2) NOT NULL DEFAULT 0.00 , LATE_PYM_BASE_AMT DECIMAL(9,2) NOT NULL DEFAULT 0.00 , CURR_CHARGE_AMT DECIMAL(9,2) NOT NULL DEFAULT 0.00 , CURR_CREDIT_AMT DECIMAL(9,2) NOT NULL DEFAULT 0.00 , TOTAL_DUE_AMT DECIMAL(9,2) NOT NULL DEFAULT 0.00 , ACTUAL_BALANCE_AMT DECIMAL(9,2) NOT NULL DEFAULT 0.00 , CURR_RC_CHRG_AMT DECIMAL(9,2) NOT NULL DEFAULT 0.00 , CURR_OC_CHRG_AMT DECIMAL(9,2) NOT NULL DEFAULT 0.00 , CURR_UC_CHRG_AMT DECIMAL(9,2) NOT NULL DEFAULT 0.00 , HIERARCHY_ID DECIMAL(10,0) NOT NULL DEFAULT 0. , INVOICE_BAN DECIMAL(9,0) NOT NULL DEFAULT 0. , INV_BILL_SEQ_NO DECIMAL(3,0) NOT NULL DEFAULT 0. , CH_BAL_CHGS DECIMAL(9,2) NOT NULL DEFAULT 0.00 , BF_MON_SRV_CHG DECIMAL(9,2) NOT NULL DEFAULT 0.00 , BF_DATA_MON_SRV_CHG DECIMAL(9,2) NOT NULL DEFAULT 0.00 , BF_ONE_TIME_CRG DECIMAL(9,2) NOT NULL DEFAULT 0.00 , BF_DATA_ONE_TIME_CRG DECIMAL(9,2) NOT NULL DEFAULT 0.00 , BF_CALL_CHG DECIMAL(9,2) NOT NULL DEFAULT 0.00 , BF_ADJ DECIMAL(9,2) NOT NULL DEFAULT 0.00 , BF_CHG_TO_ACCOUNT DECIMAL(9,2) NOT NULL DEFAULT 0.00 , BF_IMM_ADJ DECIMAL(9,2) NOT NULL DEFAULT 0.00 , BF_TAXS DECIMAL(9,2) NOT NULL DEFAULT 0.00 , BF_BTN VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', TAX_FED_AMT DECIMAL(9,2) NOT NULL DEFAULT 0.00 , TAX_STT_AMT DECIMAL(9,2) NOT NULL DEFAULT 0.00 , TAX_LOC_AMT DECIMAL(9,2) NOT NULL DEFAULT 0.00 , BF_LEVEL DECIMAL(5,0) NOT NULL DEFAULT 0. , BAN_ORDER DECIMAL(9,0) NOT NULL DEFAULT 0. , LABEL VARCHAR(80) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ', DW_MODIFY_DT DATE FORMAT 'YYYYMMDD', DW_LOAD_DT DATE FORMAT 'YYYYMMDD') UNIQUE PRIMARY INDEX ( BAN ,BILL_SEQ_NO ); /* Thats all folks */

Attachments

Library

Quick Reference

Archives of the TeradataForum

Message Posted: Mon, 15 Jul 2002 @ 17:26:38 GMT