Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2010
    Posts
    6

    Red face Unanswered: Query is Taking too much of time around 30 mins... plz help me out folks :-(

    SELECT DISTINCT
    A.BILLING_TELEPHONE_NUMBER,
    A.LINE_OF_BUSINESS_CODE,
    A.ACCOUNT_CUSTOMER_CODE,
    A.CUSTOMER_ACCOUNT_NUMBER,
    A.SERVICE_NAME,
    B.SERVICE_ORDER_ID,
    B.STATUS,
    B.ORIGINAL_ORDER_TYPE,
    B.SUBID,
    B.ORDER_NUMBER,
    B.REGION_CODE,
    B.ORDER_BATCHID,
    B.CREATE_DATE,
    B.ORIGINAL_CREATE_DATE,
    B.COMPLETION_DATE,
    B.SENT_TO_BILL_DATE,
    B.ORIGINAL_SENT_TO_BILL_DATE,
    B.DUE_DATE_SUFFIX,
    B.RELATED_ORIGINAL_ORDER_TYPE,
    B.RELATED_ORDER_NUMBER,
    B.BIR,
    B.CANCEL_ORDER_FLAG,
    C.LINE_ITEM_SEQ_NUM,
    C.COMPENSABLE_PRODUCT_ID,
    C.WORKING_TELEPHONE_NUMBER,
    C.PRODUCT_CODE,
    C.ACTION,
    C.QUANTITY,
    C.RATE,
    C.CONTRACT_EFFECTIVE_DATE,
    C.CONTRACT_USOC_TERM,
    C.CONTRACT_EXPIRATION_DATE,
    C.ORIGINAL_PROCESS_FLAG,
    C.LAST_PROCESS_FLAG,
    C.MCC_CD,
    C.ISSUANCE_SENT_TO_BILL_FLAG,
    C.QTNUM,
    ltrim(D.SALESID),
    D.ISSUANCE_SHARE_PERCENT
    FROM CUPAPP.ACCT A,CUPAPP.SERVICE_ORDER B,CUPAPP.SO_LINE_ITEM C,CUPAPP.SO_SALESID D WHERE
    A.ACCOUNT_ID=B.ACCOUNT_ID AND
    B.SERVICE_ORDER_ID=C.SERVICE_ORDER_ID AND
    C.SERVICE_ORDER_ID=D.SERVICE_ORDER_ID AND
    C.LINE_ITEM_SEQ_NUM=D.LINE_ITEM_SEQ_NUM AND
    B.SERVICE_ORDER_ID=D.SERVICE_ORDER_ID AND
    b.create_date>=date('01/01/2011')

  2. #2
    Join Date
    Dec 2010
    Posts
    6
    Quote Originally Posted by ravuri.rajesh View Post
    SELECT DISTINCT
    A.BILLING_TELEPHONE_NUMBER,
    A.LINE_OF_BUSINESS_CODE,
    A.ACCOUNT_CUSTOMER_CODE,
    A.CUSTOMER_ACCOUNT_NUMBER,
    A.SERVICE_NAME,
    B.SERVICE_ORDER_ID,
    B.STATUS,
    B.ORIGINAL_ORDER_TYPE,
    B.SUBID,
    B.ORDER_NUMBER,
    B.REGION_CODE,
    B.ORDER_BATCHID,
    B.CREATE_DATE,
    B.ORIGINAL_CREATE_DATE,
    B.COMPLETION_DATE,
    B.SENT_TO_BILL_DATE,
    B.ORIGINAL_SENT_TO_BILL_DATE,
    B.DUE_DATE_SUFFIX,
    B.RELATED_ORIGINAL_ORDER_TYPE,
    B.RELATED_ORDER_NUMBER,
    B.BIR,
    B.CANCEL_ORDER_FLAG,
    C.LINE_ITEM_SEQ_NUM,
    C.COMPENSABLE_PRODUCT_ID,
    C.WORKING_TELEPHONE_NUMBER,
    C.PRODUCT_CODE,
    C.ACTION,
    C.QUANTITY,
    C.RATE,
    C.CONTRACT_EFFECTIVE_DATE,
    C.CONTRACT_USOC_TERM,
    C.CONTRACT_EXPIRATION_DATE,
    C.ORIGINAL_PROCESS_FLAG,
    C.LAST_PROCESS_FLAG,
    C.MCC_CD,
    C.ISSUANCE_SENT_TO_BILL_FLAG,
    C.QTNUM,
    ltrim(D.SALESID),
    D.ISSUANCE_SHARE_PERCENT
    FROM CUPAPP.ACCT A,CUPAPP.SERVICE_ORDER B,CUPAPP.SO_LINE_ITEM C,CUPAPP.SO_SALESID D WHERE
    A.ACCOUNT_ID=B.ACCOUNT_ID AND
    B.SERVICE_ORDER_ID=C.SERVICE_ORDER_ID AND
    C.SERVICE_ORDER_ID=D.SERVICE_ORDER_ID AND
    C.LINE_ITEM_SEQ_NUM=D.LINE_ITEM_SEQ_NUM AND
    B.SERVICE_ORDER_ID=D.SERVICE_ORDER_ID AND
    b.create_date>=date('01/01/2011')
    DDL's

    CREATE TABLE "CUPAPP "."ACCT" (
    "ACCOUNT_ID" INTEGER NOT NULL ,
    "ACCOUNT_CUSTOMER_CODE" VARCHAR(3) ,
    "OLD_ACCOUNT_CUSTOMER_CODE" VARCHAR(3) ,
    "SUBSIDIARY_TIE_CODE" VARCHAR(11) ,
    "MASTER_SERVICE_DATE" DATE ,
    "OLD_MASTER_SERVICE_DATE" DATE ,
    "CORPORATE_TIE_CODE" VARCHAR(11) ,
    "BILLING_TELEPHONE_NUMBER" VARCHAR(10) NOT NULL ,
    "CUSTOMER_ACCOUNT_NUMBER" VARCHAR(13) ,
    "SUMMARY_CUSTOMER_ACCOUNT_NUMBE" VARCHAR(13) ,
    "WILDCARD_FLAG" "CUPAPP "."LOGICAL" NOT NULL WITH DEFAULT 'N' ,
    "DUNS_CODE" VARCHAR(9) ,
    "LINE_OF_BUSINESS_CODE" VARCHAR(5) ,
    "PRIMARY_CLASS_OF_SERVICE" CHAR(1) NOT NULL ,
    "OLD_BILLING_TELEPHONE_NUMBER" VARCHAR(10) ,
    "OPERATING_COMPANY_NUMBER" VARCHAR(4) ,
    "ACCESS_CUST_NAME_ABBREVIATION" VARCHAR(3) ,
    "DISCONNECT_FLAG" "CUPAPP "."LOGICAL" NOT NULL WITH DEFAULT 'N' ,
    "BLG_TYPE_CD" CHAR(1) ,
    "BAC_CD" VARCHAR(7) ,
    "SUBORDINATE_CLASS_OF_SERVICE" VARCHAR(3) ,
    "NON_PUB_IND" CHAR(1) ,
    "CREATE_TIME" TIMESTAMP NOT NULL ,
    "AUDIT_TIME" TIMESTAMP NOT NULL ,
    "CREATE_USERID" VARCHAR(20) NOT NULL ,
    "AUDIT_USERID" VARCHAR(20) NOT NULL ,
    "SERVICE_NAME" VARCHAR(30) WITH DEFAULT 'xxxxxxxx' )
    COMPRESS YES
    IN "TSDA01CUPDB" INDEX IN "TSIX01CUPDB" ;


    -- DDL Statements for primary key on Table "CUPAPP "."ACCT"

    ALTER TABLE "CUPAPP "."ACCT"
    ADD PRIMARY KEY
    ("ACCOUNT_ID");



    -- DDL Statements for indexes on Table "CUPAPP "."ACCT"

    CREATE UNIQUE INDEX "CUPAPP "."IDX1_ACCT" ON "CUPAPP "."ACCT"
    ("BILLING_TELEPHONE_NUMBER" ASC,
    "ACCOUNT_CUSTOMER_CODE" ASC,
    "MASTER_SERVICE_DATE" ASC)
    PCTFREE 10 MINPCTUSED 10
    DISALLOW REVERSE SCANS;

  3. #3
    Join Date
    Dec 2010
    Posts
    6
    Quote Originally Posted by ravuri.rajesh View Post
    DDL's

    CREATE TABLE "CUPAPP "."ACCT" (
    "ACCOUNT_ID" INTEGER NOT NULL ,
    "ACCOUNT_CUSTOMER_CODE" VARCHAR(3) ,
    "OLD_ACCOUNT_CUSTOMER_CODE" VARCHAR(3) ,
    "SUBSIDIARY_TIE_CODE" VARCHAR(11) ,
    "MASTER_SERVICE_DATE" DATE ,
    "OLD_MASTER_SERVICE_DATE" DATE ,
    "CORPORATE_TIE_CODE" VARCHAR(11) ,
    "BILLING_TELEPHONE_NUMBER" VARCHAR(10) NOT NULL ,
    "CUSTOMER_ACCOUNT_NUMBER" VARCHAR(13) ,
    "SUMMARY_CUSTOMER_ACCOUNT_NUMBE" VARCHAR(13) ,
    "WILDCARD_FLAG" "CUPAPP "."LOGICAL" NOT NULL WITH DEFAULT 'N' ,
    "DUNS_CODE" VARCHAR(9) ,
    "LINE_OF_BUSINESS_CODE" VARCHAR(5) ,
    "PRIMARY_CLASS_OF_SERVICE" CHAR(1) NOT NULL ,
    "OLD_BILLING_TELEPHONE_NUMBER" VARCHAR(10) ,
    "OPERATING_COMPANY_NUMBER" VARCHAR(4) ,
    "ACCESS_CUST_NAME_ABBREVIATION" VARCHAR(3) ,
    "DISCONNECT_FLAG" "CUPAPP "."LOGICAL" NOT NULL WITH DEFAULT 'N' ,
    "BLG_TYPE_CD" CHAR(1) ,
    "BAC_CD" VARCHAR(7) ,
    "SUBORDINATE_CLASS_OF_SERVICE" VARCHAR(3) ,
    "NON_PUB_IND" CHAR(1) ,
    "CREATE_TIME" TIMESTAMP NOT NULL ,
    "AUDIT_TIME" TIMESTAMP NOT NULL ,
    "CREATE_USERID" VARCHAR(20) NOT NULL ,
    "AUDIT_USERID" VARCHAR(20) NOT NULL ,
    "SERVICE_NAME" VARCHAR(30) WITH DEFAULT 'xxxxxxxx' )
    COMPRESS YES
    IN "TSDA01CUPDB" INDEX IN "TSIX01CUPDB" ;


    -- DDL Statements for primary key on Table "CUPAPP "."ACCT"

    ALTER TABLE "CUPAPP "."ACCT"
    ADD PRIMARY KEY
    ("ACCOUNT_ID");



    -- DDL Statements for indexes on Table "CUPAPP "."ACCT"

    CREATE UNIQUE INDEX "CUPAPP "."IDX1_ACCT" ON "CUPAPP "."ACCT"
    ("BILLING_TELEPHONE_NUMBER" ASC,
    "ACCOUNT_CUSTOMER_CODE" ASC,
    "MASTER_SERVICE_DATE" ASC)
    PCTFREE 10 MINPCTUSED 10
    DISALLOW REVERSE SCANS;


    CREATE TABLE "CUPAPP "."SERVICE_ORDER" (
    "SERVICE_ORDER_ID" INTEGER NOT NULL ,
    "ACCOUNT_ID" INTEGER NOT NULL ,
    "STATUS" CHAR(1) NOT NULL ,
    "REGION_CODE" VARCHAR(2) NOT NULL ,
    "SALES_CHANNEL" VARCHAR(3) ,
    "ORDER_NUMBER" VARCHAR(13) NOT NULL ,
    "ORDER_TYPE" VARCHAR(1) NOT NULL ,
    "ORIGINAL_ORDER_TYPE" VARCHAR(3) NOT NULL ,
    "SUBID" VARCHAR(3) ,
    "ORDER_SALESID" VARCHAR(8) ,
    "ORDER_BATCHID" VARCHAR(8) ,
    "ORDER_SUFFIX" VARCHAR(2) ,
    "PROCESSING_SYSTEM" CHAR(1) ,
    "RC_CODE" VARCHAR(4) ,
    "POSITION_CODE" VARCHAR(32) ,
    "CANCEL_ORDER_FLAG" "CUPAPP "."LOGICAL" ,
    "COMPANY_STATE" VARCHAR(2) ,
    "CREATE_DATE" DATE NOT NULL ,
    "DUE_DATE" DATE ,
    "CANCEL_DATE" DATE ,
    "COMPLETION_DATE" DATE ,
    "SENT_TO_BILL_DATE" DATE ,
    "RELATED_ORDER_REGION_CODE" VARCHAR(2) ,
    "DUE_DATE_SUFFIX" VARCHAR(6) ,
    "RELATED_ORDER_NUMBER" VARCHAR(13) ,
    "RELATED_ORDER_TYPE" CHAR(1) ,
    "RELATED_ORIGINAL_ORDER_TYPE" VARCHAR(3) ,
    "RELATED_ORDER_SUFFIX" VARCHAR(2) ,
    "SUBSCRIBER_ORDER_NUMBER" VARCHAR(16) ,
    "RELATED_ORDER_BATCH_ID" VARCHAR(8) ,
    "RESELL_CODE" CHAR(1) ,
    "RESELL_FID" VARCHAR(4) ,
    "RELATED_ORDER_SUBID" VARCHAR(3) ,
    "RESELL_WIN_BACK_INDICATOR" CHAR(1) ,
    "VENDOR_CODE" VARCHAR(5) ,
    "OWNER_CODE" VARCHAR(3) ,
    "MANUAL_CREATE_FLAG" "CUPAPP "."LOGICAL" ,
    "MANUAL_EDIT_FLAG" "CUPAPP "."LOGICAL" ,
    "CMPY_CD" VARCHAR(4) ,
    "CMPY_RT_ST" VARCHAR(2) ,
    "ORIG_STAT_CD" VARCHAR(3) ,
    "MANUAL_BTN" CHAR(10) ,
    "MANUAL_CUST_NAME" VARCHAR(30) ,
    "LOCK_SO_FLAG" INTEGER ,
    "ZCRW_IND" CHAR(1) ,
    "OCC_CD" VARCHAR(2) ,
    "BILLING_EFFECTIVE_DATE" DATE ,
    "BIR" VARCHAR(11) ,
    "CREATE_TIME" TIMESTAMP ,
    "AUDIT_TIME" TIMESTAMP ,
    "CREATE_USERID" VARCHAR(20) ,
    "AUDIT_USERID" VARCHAR(20) ,
    "PROCESSED_BY_ESG_PORTAL" CHAR(1) WITH DEFAULT NULL ,
    "ORIGINAL_CREATE_DATE" DATE ,
    "ORIGINAL_SENT_TO_BILL_DATE" DATE )
    COMPRESS YES
    IN "TSDA02CUPDB" INDEX IN "TSIX02CUPDB" ;


    -- DDL Statements for primary key on Table "CUPAPP "."SERVICE_ORDER"

    ALTER TABLE "CUPAPP "."SERVICE_ORDER"
    ADD PRIMARY KEY
    ("SERVICE_ORDER_ID");



    -- DDL Statements for indexes on Table "CUPAPP "."SERVICE_ORDER"

    CREATE UNIQUE INDEX "CUPAPP "."IDX1_SERVICE_ORDER" ON "CUPAPP "."SERVICE_ORDER"
    ("ACCOUNT_ID" ASC,
    "SERVICE_ORDER_ID" ASC)
    PCTFREE 10 MINPCTUSED 10
    DISALLOW REVERSE SCANS;

    -- DDL Statements for indexes on Table "CUPAPP "."SERVICE_ORDER"

    CREATE UNIQUE INDEX "CUPAPP "."IDX2_SERVICE_ORDER" ON "CUPAPP "."SERVICE_ORDER"
    ("ORDER_NUMBER" ASC,
    "SERVICE_ORDER_ID" ASC)
    PCTFREE 10 CLUSTER MINPCTUSED 10
    DISALLOW REVERSE SCANS;

    -- DDL Statements for indexes on Table "CUPAPP "."SERVICE_ORDER"

    CREATE UNIQUE INDEX "CUPAPP "."IDX3_SERVICE_ORDER" ON "CUPAPP "."SERVICE_ORDER"
    ("ORDER_NUMBER" ASC,
    "ORIGINAL_ORDER_TYPE" ASC,
    "REGION_CODE" ASC,
    "ORDER_BATCHID" ASC)
    PCTFREE 10 MINPCTUSED 10
    DISALLOW REVERSE SCANS;

    -- DDL Statements for indexes on Table "CUPAPP "."SERVICE_ORDER"

    CREATE INDEX "CUPAPP "."IDX4_SERVICE_ORDER" ON "CUPAPP "."SERVICE_ORDER"
    ("CREATE_DATE" ASC,
    "MANUAL_CREATE_FLAG" ASC)
    PCTFREE 10 MINPCTUSED 10
    DISALLOW REVERSE SCANS;

    -- DDL Statements for indexes on Table "CUPAPP "."SERVICE_ORDER"

    CREATE INDEX "DB2CUP01"."IDX_STG" ON "CUPAPP "."SERVICE_ORDER"
    ("REGION_CODE" ASC,
    "ORDER_NUMBER" ASC,
    "ORIGINAL_ORDER_TYPE" ASC)
    DISALLOW REVERSE SCANS;

  4. #4
    Join Date
    Dec 2010
    Posts
    6
    Quote Originally Posted by ravuri.rajesh View Post
    CREATE TABLE "CUPAPP "."SERVICE_ORDER" (
    "SERVICE_ORDER_ID" INTEGER NOT NULL ,
    "ACCOUNT_ID" INTEGER NOT NULL ,
    "STATUS" CHAR(1) NOT NULL ,
    "REGION_CODE" VARCHAR(2) NOT NULL ,
    "SALES_CHANNEL" VARCHAR(3) ,
    "ORDER_NUMBER" VARCHAR(13) NOT NULL ,
    "ORDER_TYPE" VARCHAR(1) NOT NULL ,
    "ORIGINAL_ORDER_TYPE" VARCHAR(3) NOT NULL ,
    "SUBID" VARCHAR(3) ,
    "ORDER_SALESID" VARCHAR(8) ,
    "ORDER_BATCHID" VARCHAR(8) ,
    "ORDER_SUFFIX" VARCHAR(2) ,
    "PROCESSING_SYSTEM" CHAR(1) ,
    "RC_CODE" VARCHAR(4) ,
    "POSITION_CODE" VARCHAR(32) ,
    "CANCEL_ORDER_FLAG" "CUPAPP "."LOGICAL" ,
    "COMPANY_STATE" VARCHAR(2) ,
    "CREATE_DATE" DATE NOT NULL ,
    "DUE_DATE" DATE ,
    "CANCEL_DATE" DATE ,
    "COMPLETION_DATE" DATE ,
    "SENT_TO_BILL_DATE" DATE ,
    "RELATED_ORDER_REGION_CODE" VARCHAR(2) ,
    "DUE_DATE_SUFFIX" VARCHAR(6) ,
    "RELATED_ORDER_NUMBER" VARCHAR(13) ,
    "RELATED_ORDER_TYPE" CHAR(1) ,
    "RELATED_ORIGINAL_ORDER_TYPE" VARCHAR(3) ,
    "RELATED_ORDER_SUFFIX" VARCHAR(2) ,
    "SUBSCRIBER_ORDER_NUMBER" VARCHAR(16) ,
    "RELATED_ORDER_BATCH_ID" VARCHAR(8) ,
    "RESELL_CODE" CHAR(1) ,
    "RESELL_FID" VARCHAR(4) ,
    "RELATED_ORDER_SUBID" VARCHAR(3) ,
    "RESELL_WIN_BACK_INDICATOR" CHAR(1) ,
    "VENDOR_CODE" VARCHAR(5) ,
    "OWNER_CODE" VARCHAR(3) ,
    "MANUAL_CREATE_FLAG" "CUPAPP "."LOGICAL" ,
    "MANUAL_EDIT_FLAG" "CUPAPP "."LOGICAL" ,
    "CMPY_CD" VARCHAR(4) ,
    "CMPY_RT_ST" VARCHAR(2) ,
    "ORIG_STAT_CD" VARCHAR(3) ,
    "MANUAL_BTN" CHAR(10) ,
    "MANUAL_CUST_NAME" VARCHAR(30) ,
    "LOCK_SO_FLAG" INTEGER ,
    "ZCRW_IND" CHAR(1) ,
    "OCC_CD" VARCHAR(2) ,
    "BILLING_EFFECTIVE_DATE" DATE ,
    "BIR" VARCHAR(11) ,
    "CREATE_TIME" TIMESTAMP ,
    "AUDIT_TIME" TIMESTAMP ,
    "CREATE_USERID" VARCHAR(20) ,
    "AUDIT_USERID" VARCHAR(20) ,
    "PROCESSED_BY_ESG_PORTAL" CHAR(1) WITH DEFAULT NULL ,
    "ORIGINAL_CREATE_DATE" DATE ,
    "ORIGINAL_SENT_TO_BILL_DATE" DATE )
    COMPRESS YES
    IN "TSDA02CUPDB" INDEX IN "TSIX02CUPDB" ;


    -- DDL Statements for primary key on Table "CUPAPP "."SERVICE_ORDER"

    ALTER TABLE "CUPAPP "."SERVICE_ORDER"
    ADD PRIMARY KEY
    ("SERVICE_ORDER_ID");



    -- DDL Statements for indexes on Table "CUPAPP "."SERVICE_ORDER"

    CREATE UNIQUE INDEX "CUPAPP "."IDX1_SERVICE_ORDER" ON "CUPAPP "."SERVICE_ORDER"
    ("ACCOUNT_ID" ASC,
    "SERVICE_ORDER_ID" ASC)
    PCTFREE 10 MINPCTUSED 10
    DISALLOW REVERSE SCANS;

    -- DDL Statements for indexes on Table "CUPAPP "."SERVICE_ORDER"

    CREATE UNIQUE INDEX "CUPAPP "."IDX2_SERVICE_ORDER" ON "CUPAPP "."SERVICE_ORDER"
    ("ORDER_NUMBER" ASC,
    "SERVICE_ORDER_ID" ASC)
    PCTFREE 10 CLUSTER MINPCTUSED 10
    DISALLOW REVERSE SCANS;

    -- DDL Statements for indexes on Table "CUPAPP "."SERVICE_ORDER"

    CREATE UNIQUE INDEX "CUPAPP "."IDX3_SERVICE_ORDER" ON "CUPAPP "."SERVICE_ORDER"
    ("ORDER_NUMBER" ASC,
    "ORIGINAL_ORDER_TYPE" ASC,
    "REGION_CODE" ASC,
    "ORDER_BATCHID" ASC)
    PCTFREE 10 MINPCTUSED 10
    DISALLOW REVERSE SCANS;

    -- DDL Statements for indexes on Table "CUPAPP "."SERVICE_ORDER"

    CREATE INDEX "CUPAPP "."IDX4_SERVICE_ORDER" ON "CUPAPP "."SERVICE_ORDER"
    ("CREATE_DATE" ASC,
    "MANUAL_CREATE_FLAG" ASC)
    PCTFREE 10 MINPCTUSED 10
    DISALLOW REVERSE SCANS;

    -- DDL Statements for indexes on Table "CUPAPP "."SERVICE_ORDER"

    CREATE INDEX "DB2CUP01"."IDX_STG" ON "CUPAPP "."SERVICE_ORDER"
    ("REGION_CODE" ASC,
    "ORDER_NUMBER" ASC,
    "ORIGINAL_ORDER_TYPE" ASC)
    DISALLOW REVERSE SCANS;


    CREATE TABLE "CUPAPP "."SO_LINE_ITEM" (
    "SERVICE_ORDER_ID" INTEGER NOT NULL ,
    "LINE_ITEM_SEQ_NUM" INTEGER NOT NULL ,
    "COMPENSABLE_PRODUCT_ID" INTEGER ,
    "WORKING_TELEPHONE_NUMBER" VARCHAR(55) NOT NULL ,
    "PRODUCT_CODE" VARCHAR(13) NOT NULL ,
    "CHANGE_FLAG" CHAR(1) ,
    "ACTION" CHAR(1) NOT NULL ,
    "ACTION_CODE" VARCHAR(2) NOT NULL ,
    "QUANTITY" DECIMAL(11,3) NOT NULL ,
    "RATE" DECIMAL(14,2) ,
    "RATE_FLAG" CHAR(1) ,
    "RATE_TYPE" CHAR(1) ,
    "RATE_ACTION" CHAR(1) ,
    "INTERLATA_CODE" VARCHAR(5) ,
    "INTERLATA_TYPE" VARCHAR(2) ,
    "INTERLATA_PIC_FREEZE" VARCHAR(2) ,
    "INTERLATA_DATE" DATE ,
    "INTRALATA_CODE" VARCHAR(5) ,
    "INTRALATA_TYPE" VARCHAR(2) ,
    "INTRALATA_PIC_FREEZE" VARCHAR(2) ,
    "INTRALATA_DATE" DATE ,
    "CLASS_OF_SERVICE" VARCHAR(5) ,
    "OLD_CLASS_OF_SERVICE" VARCHAR(5) ,
    "CIRCUIT" VARCHAR(55) ,
    "RETURN_REGION_CODE" VARCHAR(2) ,
    "RETURN_ORDER_TYPE" CHAR(1) ,
    "RETURN_ORDER_NUMBER" VARCHAR(13) ,
    "RETURN_SUBID" VARCHAR(3) ,
    "RETURN_BATCH_ID" VARCHAR(8) ,
    "RETURN_ORIGINAL_TYPE" VARCHAR(3) ,
    "RETURN_DATE" DATE ,
    "RETURN_DAYS" INTEGER ,
    "RETURN_QUANTITY" DECIMAL(11,3) ,
    "AUDIT_DATE" DATE ,
    "SECOND_USOC_INDICATOR" CHAR(1) ,
    "PACKAGE_INDICATOR" CHAR(1) ,
    "UPSELL_INDICATOR" CHAR(1) ,
    "SERVICE_CODE_MODIFIER" VARCHAR(5) ,
    "NUMBER_OF_LINES" INTEGER ,
    "CONTRACT_EFFECTIVE_DATE" DATE ,
    "CONTRACT_EXPIRATION_DATE" DATE ,
    "CONTRACT_USOC_TERM" INTEGER ,
    "CONTRACT_REASON_CODE" VARCHAR(2) ,
    "CONTRACT_ESTIMATED_AMOUNT" DECIMAL(14,2) ,
    "CONTRACT_TOTAL_AMOUNT" DECIMAL(14,2) ,
    "ORIGINAL_PROCESS_FLAG" CHAR(1) NOT NULL ,
    "ORIGINAL_PROCESS_DATE" DATE ,
    "ORIGINAL_QUANTITY" DECIMAL(11,3) NOT NULL ,
    "LAST_PROCESS_FLAG" CHAR(1) ,
    "LAST_PROCESS_DATE" DATE ,
    "LAST_QUANTITY" DECIMAL(11,3) ,
    "BUNDLE_INDICATOR" CHAR(1) ,
    "PLAN_IDENTIFICATION_CODE" VARCHAR(4) ,
    "CALLING_CARD_RESTRICTION_CODE" CHAR(1) ,
    "TRANSLATE_ID" INTEGER ,
    "MANUAL_NEW_OR_RESIGN" CHAR(1) ,
    "MCC_CD" VARCHAR(3) ,
    "FIBL_IND" CHAR(1) ,
    "DSL_IND" CHAR(1) ,
    "RIE_IND" CHAR(1) ,
    "RTE_IND" CHAR(1) ,
    "SBA_IND" CHAR(1) ,
    "MARGIN_PCT_ACTION" CHAR(1) ,
    "MARGIN_PCT" DECIMAL(7,4) ,
    "MARGIN_ACTION" CHAR(1) ,
    "MARGIN_DLLRS" DECIMAL(14,2) ,
    "SPEED_SVC" VARCHAR(20) ,
    "ISSUANCE_SENT_TO_BILL_FLAG" CHAR(1) ,
    "CN" VARCHAR(3) ,
    "MAPPING_CONTRACT_TERM" INTEGER ,
    "CREATED_BY_CUP" "CUPAPP "."LOGICAL" NOT NULL WITH DEFAULT 'N' ,
    "CREATE_TIME" TIMESTAMP ,
    "AUDIT_TIME" TIMESTAMP ,
    "CREATE_USERID" VARCHAR(20) ,
    "AUDIT_USERID" VARCHAR(20) ,
    "PROCESSED_BY_ESG_PORTAL" CHAR(1) WITH DEFAULT NULL ,
    "CNTRT_STATUS_CD" CHAR(1) ,
    "CNTRT_RCPT_DT" DATE ,
    "CNTRT_SIGN_DT" DATE ,
    "QTNUM" VARCHAR(15) ,
    "ORIGINAL_RATE" DECIMAL(14,2) ,
    "ORIGINAL_CONTRACT_USOC_TERM" INTEGER )
    COMPRESS YES
    IN "TSDA02CUPDB" INDEX IN "TSIX02CUPDB" ;


    -- DDL Statements for primary key on Table "CUPAPP "."SO_LINE_ITEM"

    ALTER TABLE "CUPAPP "."SO_LINE_ITEM"
    ADD PRIMARY KEY
    ("SERVICE_ORDER_ID",
    "LINE_ITEM_SEQ_NUM");



    -- DDL Statements for indexes on Table "CUPAPP "."SO_LINE_ITEM"

    CREATE INDEX "CUPAPP "."IND_SOLINE_ITEM" ON "CUPAPP "."SO_LINE_ITEM"
    ("TRANSLATE_ID" ASC)
    ALLOW REVERSE SCANS;

    -- DDL Statements for indexes on Table "CUPAPP "."SO_LINE_ITEM"

    CREATE INDEX "CUPAPP "."IND_WORK_TNO" ON "CUPAPP "."SO_LINE_ITEM"
    ("WORKING_TELEPHONE_NUMBER" ASC)
    DISALLOW REVERSE SCANS;

    -- DDL Statements for indexes on Table "CUPAPP "."SO_LINE_ITEM"

    CREATE INDEX "CUPAPP "."IX1_SLI" ON "CUPAPP "."SO_LINE_ITEM"
    ("COMPENSABLE_PRODUCT_ID" ASC)
    DISALLOW REVERSE SCANS;

    -- DDL Statements for indexes on Table "CUPAPP "."SO_LINE_ITEM"

    CREATE INDEX "DB2CUP01"."INDEX1_SLI" ON "CUPAPP "."SO_LINE_ITEM"
    ("PRODUCT_CODE" ASC)
    DISALLOW REVERSE SCANS;

  5. #5
    Join Date
    Dec 2010
    Posts
    6
    Quote Originally Posted by ravuri.rajesh View Post
    CREATE TABLE "CUPAPP "."SO_LINE_ITEM" (
    "SERVICE_ORDER_ID" INTEGER NOT NULL ,
    "LINE_ITEM_SEQ_NUM" INTEGER NOT NULL ,
    "COMPENSABLE_PRODUCT_ID" INTEGER ,
    "WORKING_TELEPHONE_NUMBER" VARCHAR(55) NOT NULL ,
    "PRODUCT_CODE" VARCHAR(13) NOT NULL ,
    "CHANGE_FLAG" CHAR(1) ,
    "ACTION" CHAR(1) NOT NULL ,
    "ACTION_CODE" VARCHAR(2) NOT NULL ,
    "QUANTITY" DECIMAL(11,3) NOT NULL ,
    "RATE" DECIMAL(14,2) ,
    "RATE_FLAG" CHAR(1) ,
    "RATE_TYPE" CHAR(1) ,
    "RATE_ACTION" CHAR(1) ,
    "INTERLATA_CODE" VARCHAR(5) ,
    "INTERLATA_TYPE" VARCHAR(2) ,
    "INTERLATA_PIC_FREEZE" VARCHAR(2) ,
    "INTERLATA_DATE" DATE ,
    "INTRALATA_CODE" VARCHAR(5) ,
    "INTRALATA_TYPE" VARCHAR(2) ,
    "INTRALATA_PIC_FREEZE" VARCHAR(2) ,
    "INTRALATA_DATE" DATE ,
    "CLASS_OF_SERVICE" VARCHAR(5) ,
    "OLD_CLASS_OF_SERVICE" VARCHAR(5) ,
    "CIRCUIT" VARCHAR(55) ,
    "RETURN_REGION_CODE" VARCHAR(2) ,
    "RETURN_ORDER_TYPE" CHAR(1) ,
    "RETURN_ORDER_NUMBER" VARCHAR(13) ,
    "RETURN_SUBID" VARCHAR(3) ,
    "RETURN_BATCH_ID" VARCHAR(8) ,
    "RETURN_ORIGINAL_TYPE" VARCHAR(3) ,
    "RETURN_DATE" DATE ,
    "RETURN_DAYS" INTEGER ,
    "RETURN_QUANTITY" DECIMAL(11,3) ,
    "AUDIT_DATE" DATE ,
    "SECOND_USOC_INDICATOR" CHAR(1) ,
    "PACKAGE_INDICATOR" CHAR(1) ,
    "UPSELL_INDICATOR" CHAR(1) ,
    "SERVICE_CODE_MODIFIER" VARCHAR(5) ,
    "NUMBER_OF_LINES" INTEGER ,
    "CONTRACT_EFFECTIVE_DATE" DATE ,
    "CONTRACT_EXPIRATION_DATE" DATE ,
    "CONTRACT_USOC_TERM" INTEGER ,
    "CONTRACT_REASON_CODE" VARCHAR(2) ,
    "CONTRACT_ESTIMATED_AMOUNT" DECIMAL(14,2) ,
    "CONTRACT_TOTAL_AMOUNT" DECIMAL(14,2) ,
    "ORIGINAL_PROCESS_FLAG" CHAR(1) NOT NULL ,
    "ORIGINAL_PROCESS_DATE" DATE ,
    "ORIGINAL_QUANTITY" DECIMAL(11,3) NOT NULL ,
    "LAST_PROCESS_FLAG" CHAR(1) ,
    "LAST_PROCESS_DATE" DATE ,
    "LAST_QUANTITY" DECIMAL(11,3) ,
    "BUNDLE_INDICATOR" CHAR(1) ,
    "PLAN_IDENTIFICATION_CODE" VARCHAR(4) ,
    "CALLING_CARD_RESTRICTION_CODE" CHAR(1) ,
    "TRANSLATE_ID" INTEGER ,
    "MANUAL_NEW_OR_RESIGN" CHAR(1) ,
    "MCC_CD" VARCHAR(3) ,
    "FIBL_IND" CHAR(1) ,
    "DSL_IND" CHAR(1) ,
    "RIE_IND" CHAR(1) ,
    "RTE_IND" CHAR(1) ,
    "SBA_IND" CHAR(1) ,
    "MARGIN_PCT_ACTION" CHAR(1) ,
    "MARGIN_PCT" DECIMAL(7,4) ,
    "MARGIN_ACTION" CHAR(1) ,
    "MARGIN_DLLRS" DECIMAL(14,2) ,
    "SPEED_SVC" VARCHAR(20) ,
    "ISSUANCE_SENT_TO_BILL_FLAG" CHAR(1) ,
    "CN" VARCHAR(3) ,
    "MAPPING_CONTRACT_TERM" INTEGER ,
    "CREATED_BY_CUP" "CUPAPP "."LOGICAL" NOT NULL WITH DEFAULT 'N' ,
    "CREATE_TIME" TIMESTAMP ,
    "AUDIT_TIME" TIMESTAMP ,
    "CREATE_USERID" VARCHAR(20) ,
    "AUDIT_USERID" VARCHAR(20) ,
    "PROCESSED_BY_ESG_PORTAL" CHAR(1) WITH DEFAULT NULL ,
    "CNTRT_STATUS_CD" CHAR(1) ,
    "CNTRT_RCPT_DT" DATE ,
    "CNTRT_SIGN_DT" DATE ,
    "QTNUM" VARCHAR(15) ,
    "ORIGINAL_RATE" DECIMAL(14,2) ,
    "ORIGINAL_CONTRACT_USOC_TERM" INTEGER )
    COMPRESS YES
    IN "TSDA02CUPDB" INDEX IN "TSIX02CUPDB" ;


    -- DDL Statements for primary key on Table "CUPAPP "."SO_LINE_ITEM"

    ALTER TABLE "CUPAPP "."SO_LINE_ITEM"
    ADD PRIMARY KEY
    ("SERVICE_ORDER_ID",
    "LINE_ITEM_SEQ_NUM");



    -- DDL Statements for indexes on Table "CUPAPP "."SO_LINE_ITEM"

    CREATE INDEX "CUPAPP "."IND_SOLINE_ITEM" ON "CUPAPP "."SO_LINE_ITEM"
    ("TRANSLATE_ID" ASC)
    ALLOW REVERSE SCANS;

    -- DDL Statements for indexes on Table "CUPAPP "."SO_LINE_ITEM"

    CREATE INDEX "CUPAPP "."IND_WORK_TNO" ON "CUPAPP "."SO_LINE_ITEM"
    ("WORKING_TELEPHONE_NUMBER" ASC)
    DISALLOW REVERSE SCANS;

    -- DDL Statements for indexes on Table "CUPAPP "."SO_LINE_ITEM"

    CREATE INDEX "CUPAPP "."IX1_SLI" ON "CUPAPP "."SO_LINE_ITEM"
    ("COMPENSABLE_PRODUCT_ID" ASC)
    DISALLOW REVERSE SCANS;

    -- DDL Statements for indexes on Table "CUPAPP "."SO_LINE_ITEM"

    CREATE INDEX "DB2CUP01"."INDEX1_SLI" ON "CUPAPP "."SO_LINE_ITEM"
    ("PRODUCT_CODE" ASC)
    DISALLOW REVERSE SCANS;


    CREATE TABLE "CUPAPP "."SO_SALESID" (
    "SERVICE_ORDER_ID" INTEGER NOT NULL ,
    "LINE_ITEM_SEQ_NUM" INTEGER NOT NULL ,
    "SALESID" VARCHAR(8) NOT NULL ,
    "ISSUANCE_SHARE_PERCENT" DECIMAL(7,4) NOT NULL ,
    "USED_FOR_COMP_FLAG" "CUPAPP "."LOGICAL" ,
    "SALES_PERCENT_TYPE" CHAR(2) NOT NULL ,
    "SENT_TO_BILL_SHARE_PERCENT" DECIMAL(7,4) NOT NULL ,
    "CREATE_TIME" TIMESTAMP ,
    "AUDIT_TIME" TIMESTAMP ,
    "CREATE_USERID" VARCHAR(20) ,
    "AUDIT_USERID" VARCHAR(20) ,
    "ONE_WORLD_ID" VARCHAR(20) NOT NULL WITH DEFAULT 'NONE' ,
    "EMPLOYEE_PERCENT" DECIMAL(7,4) NOT NULL WITH DEFAULT )
    COMPRESS YES
    IN "TSDA01CUPDB" INDEX IN "TSIX01CUPDB" ;


    -- DDL Statements for primary key on Table "CUPAPP "."SO_SALESID"

    ALTER TABLE "CUPAPP "."SO_SALESID"
    ADD PRIMARY KEY
    ("SERVICE_ORDER_ID",
    "LINE_ITEM_SEQ_NUM",
    "SALESID",
    "ONE_WORLD_ID");

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Why have you started a new thread ?
    Have you checked your original thread and the suggestions provided?

    Post the information you have posted here in the other thread.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Thanks for letting me know it is a different query(by PM).
    All the answers for the previous thread still hold good for this one too.
    If you have not made use of the previous suggestions, not sure what other suggestions you will get for this one [ I have not read either of your queries in detail]

    I have unlocked the thread

    --
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Aug 2011
    Posts
    6

    Cool steps

    Dear Rajesh,

    use db2adviser ..

    (i)db2adis -d database_name file.sql

    (i)db2expln -d ..... see the cost and cardinality

    (iii)I/O utilisation (iostat 5 10, d), CPU utilisation.....



    try all these

    Cheers
    Ganesh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •