Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jan 2009
    Posts
    153

    Unanswered: Performance Tunning Tool

    Hi Experts,

    Is there any tools for tunning queries in DB2 just like the execution plan in SQL Server?

    I am using DB2 9.5C express and i am not able to view the Access plan in my query window

    PLease help
    TIA

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You need to create the explain tables before you can generate an access plan.

    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

    Andy

  3. #3
    Join Date
    Jan 2009
    Posts
    153
    Thanks Andy..

  4. #4
    Join Date
    Jan 2009
    Posts
    153
    I have created explain_instance table but still am not getting access plan

  5. #5
    Join Date
    Jan 2010
    Posts
    335
    For the Explains you need to create a set of tables, not just the explain_instance-table. The DDL for these tables is in the directory sqllib/misc.

    If you have the GUI-Tools (don't know if there's something within the Express-C Version), you can use the Command Editior. If the user has the priviledges it will automatically create the EXPLAIN-tables and can generate a graphical access plan. If you only have the command-line tools, use

    db2 set current explain mode explain
    db2 "<query>"
    db2 set current explain mode no
    db2exfmt -d <DBNAME>

  6. #6
    Join Date
    Jan 2009
    Posts
    153
    Thanks nvk@vhv

  7. #7
    Join Date
    Jan 2009
    Posts
    153
    I have got access plan and it shows many hash joins ,GRPBY,SORT ,TEMP & table scan?

    Are these causes of performance degradation??
    How can i eliminate these and tune my query??Please find my query below

    SELECT table_name, ID, CARD_ID, CARD_NUMBER, DEBIT_CARD_NUMBER,

    CARD_PAN_SEQ, EXPIRY_DATE, PANDATE, COMPANY_ID,

    COMPANY_NAME, CUST_LASTNAME, CUST_FIRSTNAME, SSN,

    LANGUAGE_TEXT_ID, CARDHOLDER_ID, LATEST_ORDER_STATUS,

    PRIMARY_CARD, ORDERING_TIME, COMPANY_ID, PRODUCT_ID,

    BIRTHDATE, PICTURE_ID_CLM, EMBOSSING_NAME,

    CARD_APPLICATION_TYPE_ID

    FROM (

    SELECT 'card' table_name, C.CARD_ID ID, C.CARD_ID CARD_ID,

    C.CARD_NUMBER CARD_NUMBER, 0 DEBIT_CARD_NUMBER,

    C.CARD_PAN_SEQ CARD_PAN_SEQ, C.CARD_VALID_DATE

    EXPIRY_DATE, concat (char(C.CARD_VALID_DATE, iso), trim(

    char(C.CARD_PAN_SEQ)))PANDATE, COMP.COMPANY_ID

    COMPANY_ID, COMP.FULLNAME COMPANY_NAME, CUST.LASTNAME

    CUST_LASTNAME, CUST.FIRSTNAME CUST_FIRSTNAME,

    CH.EMBOSSING_NAME EMBOSSING_NAME, CUST.SSN SSN,

    PROD.LANGUAGE_TEXT_ID LANGUAGE_TEXT_ID, C.CARDHOLDER_ID

    CARDHOLDER_ID, C.ORDERING_STATUS_CODE

    LATEST_ORDER_STATUS, C.PRIMARY_CARD PRIMARY_CARD,

    C.ORDERING_TIME ORDERING_TIME, PROD.PRODUCT_ID

    PRODUCT_ID, CUST.BIRTHDATE BIRTHDATE, C.PICTURE_ID_CLM

    PICTURE_ID_CLM, 99 CARD_APPLICATION_TYPE_ID

    FROM CARDT C, CARDTYPT CARDTYPE, COMPANYT COMP, CUSTOMERT CUST,

    PRODUCTT PROD, CARDHLDT CH

    WHERE C.CARDHOLDER_ID =CH.CARDHOLDER_ID AND C.CUSTOMER_ID =

    CUST.CUSTOMER_ID AND C.COMPANY_ID =COMP.COMPANY_ID AND

    C.CARD_TYPE_ID =CARDTYPE.CARD_TYPE_ID AND

    CARDTYPE.PRODUCT_ID =PROD.PRODUCT_ID

    UNION

    SELECT 'cardchg' table_name, CHG.CARD_CHANGE_ID ID, CHG.CARD_ID

    CARD_ID, CHG.CARD_NUMBER CARD_NUMBER,

    CHG.DEBIT_CARD_NUMBER DEBIT_CARD_NUMBER,

    CHG.CARD_PAN_SEQ CARD_PAN_SEQ, CHG.CARD_VALID_DATE

    EXPIRY_DATE, concat (char(CHG.CARD_VALID_DATE, iso),

    trim(char(CHG.CARD_PAN_SEQ)))PANDATE, COMP.COMPANY_ID

    COMPANY_ID, COMP.FULLNAME COMPANY_NAME, CUST.LASTNAME

    CUST_LASTNAME, CUST.FIRSTNAME CUST_FIRSTNAME,

    CHG.EMBOSSING_NAME, CUST.SSN SSN, PROD.LANGUAGE_TEXT_ID

    LANGUAGE_TEXT_ID, C.CARDHOLDER_ID CARDHOLDER_ID,

    CHG.ORDERING_STATUS_CODE LATEST_ORDER_STATUS,

    C.PRIMARY_CARD PRIMARY_CARD, C.ORDERING_TIME

    ORDERING_TIME, PROD.PRODUCT_ID PRODUCT_ID,

    CUST.BIRTHDATE BIRTHDATE, C.PICTURE_ID_CLM

    PICTURE_ID_CLM, CHG.CARD_APPLICATION_TYPE_ID

    CARD_APPLICATION_TYPE_ID

    FROM CARDT C, CARDCHGT CHG, CARDTYPT CARDTYPE, COMPANYT COMP,

    CUSTOMERT CUST, PRODUCTT PROD

    WHERE CHG.CARD_ID =C.CARD_ID AND C.CUSTOMER_ID =CUST.CUSTOMER_ID

    AND C.COMPANY_ID =COMP.COMPANY_ID AND C.CARD_TYPE_ID =

    CARDTYPE.CARD_TYPE_ID AND CARDTYPE.PRODUCT_ID =

    PROD.PRODUCT_ID AND CHG.ORDERING_STATUS_CODE NOT IN (10,

    11, 13, 14)

    UNION

    SELECT 'cardhist' table_name, C.CARD_HISTORY_ID ID, C.CARD_ID

    CARD_ID, C.CARD_NUMBER CARD_NUMBER, 0 DEBIT_CARD_NUMBER,

    C.CARD_PAN_SEQ CARD_PAN_SEQ, C.CARD_VALID_DATE

    EXPIRY_DATE, concat (char(C.CARD_VALID_DATE, iso), trim(

    char(C.CARD_PAN_SEQ)))PANDATE, COMP.COMPANY_ID

    COMPANY_ID, COMP.FULLNAME COMPANY_NAME, CUST.LASTNAME

    CUST_LASTNAME, CUST.FIRSTNAME CUST_FIRSTNAME,

    CH.EMBOSSING_NAME EMBOSSING_NAME, CUST.SSN SSN,

    PROD.LANGUAGE_TEXT_ID LANGUAGE_TEXT_ID, C.CARDHOLDER_ID

    CARDHOLDER_ID, C.ORDERING_STATUS_CODE

    LATEST_ORDER_STATUS, C.PRIMARY_CARD PRIMARY_CARD,

    C.ORDERING_TIME ORDERING_TIME, PROD.PRODUCT_ID

    PRODUCT_ID, CUST.BIRTHDATE BIRTHDATE, C.PICTURE_ID_CLM

    PICTURE_ID_CLM, 99 CARD_APPLICATION_TYPE_ID

    FROM CARDHIST C, CARDTYPT CARDTYPE, COMPANYT COMP, CUSTOMERT

    CUST, PRODUCTT PROD, CARDHLDT CH

    WHERE C.CARDHOLDER_ID =CH.CARDHOLDER_ID AND C.CUSTOMER_ID =

    CUST.CUSTOMER_ID AND C.COMPANY_ID =COMP.COMPANY_ID AND

    C.CARD_TYPE_ID =CARDTYPE.CARD_TYPE_ID AND

    CARDTYPE.PRODUCT_ID =PROD.PRODUCT_ID )TAB1

    WHERE concat(char(TAB1.CARD_NUMBER), TAB1.PANDATE)IN (

    SELECT concat(char(CARD_NUMBER), MAX_CARD_PANDATE)

    CARD_MAXPANDATE

    FROM (

    SELECT CARD_NUMBER, max(PANDATE)MAX_CARD_PANDATE

    FROM (

    SELECT C.CARD_NUMBER, concat (char(C.CARD_VALID_DATE, iso),

    trim(char(C.CARD_PAN_SEQ)))PANDATE

    FROM CARDT C

    UNION

    SELECT C.CARD_NUMBER, concat (char(C.CARD_VALID_DATE, iso),

    trim(char(C.CARD_PAN_SEQ)))PANDATE

    FROM CARDCHGT C

    WHERE C.ORDERING_STATUS_CODE NOT IN (10, 11, 13, 14)

    UNION

    SELECT C.CARD_NUMBER, concat (char(C.CARD_VALID_DATE, iso),

    trim(char(C.CARD_PAN_SEQ)))PANDATE

    FROM CARDHIST C )

    FETCH

    GROUP BY CARD_NUMBER )AS TAB2 )

  8. #8
    Join Date
    Jan 2010
    Posts
    335
    Hi TIA,

    we can't tune the query by just looking at the query. We would need the Table-DDL, the table-statistics etc.

    There's an Advisor within DB2, it could analyse the Query and suggest indexes for the Query.

    db2advis -db <DB-Name> -i <Inputfile_with_Queries> -o <Output_File>
    db2advis -h for help

    The Input_file can contain a single Query or a complete Workload. If you create new indexes on the database, it might have impact on other Queries, too.

  9. #9
    Join Date
    Jan 2009
    Posts
    153
    Hi ,

    I tried

    C:\Documents and Settings\rknair>db2advis -db csys -n AABSYS -i C:\query.in -o query_out.txt

    but i am getting error

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is a temporary revised query(still investigating):
    1) Used UNION ALL instead of UNION.
    2) Used JOIN(with LATERAL keyword) instead of IN predicate.
    3) Removed PANDATE from final result.
    Code:
    SELECT table_name
         , ID , CARD_ID , tab2.CARD_NUMBER , DEBIT_CARD_NUMBER
         , CARD_PAN_SEQ , EXPIRY_DATE
    --     , PANDATE
         , COMPANY_ID , COMPANY_NAME , CUST_LASTNAME , CUST_FIRSTNAME , SSN
         , LANGUAGE_TEXT_ID , CARDHOLDER_ID , LATEST_ORDER_STATUS
         , PRIMARY_CARD , ORDERING_TIME , COMPANY_ID , PRODUCT_ID
         , BIRTHDATE , PICTURE_ID_CLM , EMBOSSING_NAME
         , CARD_APPLICATION_TYPE_ID
      FROM (SELECT CARD_NUMBER
                 , max(PANDATE) MAX_CARD_PANDATE
              FROM (SELECT C.CARD_NUMBER
                         , char(C.CARD_VALID_DATE, iso) || trim(char(C.CARD_PAN_SEQ)) PANDATE 
                      FROM CARDT C 
                    UNION ALL
                    SELECT C.CARD_NUMBER
                         , char(C.CARD_VALID_DATE, iso) || trim(char(C.CARD_PAN_SEQ)) PANDATE 
                      FROM CARDCHGT C 
                     WHERE C.ORDERING_STATUS_CODE NOT IN (10, 11, 13, 14)
                    UNION ALL
                    SELECT C.CARD_NUMBER
                         , char(C.CARD_VALID_DATE, iso) || trim(char(C.CARD_PAN_SEQ)) PANDATE 
                      FROM CARDHIST C
                   ) FETCH
             GROUP BY CARD_NUMBER
           ) AS TAB2
         , LATERAL
           ( 
            SELECT 'card' table_name
                 , C.CARD_ID ID , C.CARD_ID CARD_ID , C.CARD_NUMBER CARD_NUMBER
                 , 0 DEBIT_CARD_NUMBER
                 , C.CARD_PAN_SEQ CARD_PAN_SEQ , C.CARD_VALID_DATE EXPIRY_DATE
    --             , concat (char(C.CARD_VALID_DATE, iso), trim(char(C.CARD_PAN_SEQ))) PANDATE
                 , COMP.COMPANY_ID COMPANY_ID , COMP.FULLNAME COMPANY_NAME
                 , CUST.LASTNAME CUST_LASTNAME , CUST.FIRSTNAME CUST_FIRSTNAME
                 , CH.EMBOSSING_NAME EMBOSSING_NAME
                 , CUST.SSN SSN
                 , PROD.LANGUAGE_TEXT_ID LANGUAGE_TEXT_ID
                 , C.CARDHOLDER_ID CARDHOLDER_ID , C.ORDERING_STATUS_CODE LATEST_ORDER_STATUS
                 , C.PRIMARY_CARD PRIMARY_CARD , C.ORDERING_TIME ORDERING_TIME
                 , PROD.PRODUCT_ID PRODUCT_ID
                 , CUST.BIRTHDATE BIRTHDATE
                 , C.PICTURE_ID_CLM PICTURE_ID_CLM
                 , 99 CARD_APPLICATION_TYPE_ID 
              FROM CARDT     C
                 , CARDTYPT  CARDTYPE
                 , COMPANYT  COMP
                 , CUSTOMERT CUST
                 , PRODUCTT  PROD
                 , CARDHLDT  CH 
             WHERE
                   C.CARD_NUMBER = tab2.CARD_NUMBER
               AND char(C.CARD_VALID_DATE, iso) || trim(char(C.CARD_PAN_SEQ))
                   = tab2.MAX_CARD_PANDATE
               AND C.CARDHOLDER_ID     = CH.CARDHOLDER_ID
               AND C.CUSTOMER_ID       = CUST.CUSTOMER_ID
               AND C.COMPANY_ID        = COMP.COMPANY_ID
               AND C.CARD_TYPE_ID      = CARDTYPE.CARD_TYPE_ID
               AND CARDTYPE.PRODUCT_ID = PROD.PRODUCT_ID 
            UNION ALL
            SELECT 'cardchg' table_name
                 , CHG.CARD_CHANGE_ID ID
                 , CHG.CARD_ID CARD_ID
                 , CHG.CARD_NUMBER CARD_NUMBER
                 , CHG.DEBIT_CARD_NUMBER DEBIT_CARD_NUMBER
                 , CHG.CARD_PAN_SEQ CARD_PAN_SEQ
                 , CHG.CARD_VALID_DATE EXPIRY_DATE
    --             , concat (char(CHG.CARD_VALID_DATE, iso), trim(char(CHG.CARD_PAN_SEQ))) PANDATE
                 , COMP.COMPANY_ID COMPANY_ID
                 , COMP.FULLNAME COMPANY_NAME
                 , CUST.LASTNAME CUST_LASTNAME
                 , CUST.FIRSTNAME CUST_FIRSTNAME
                 , CHG.EMBOSSING_NAME
                 , CUST.SSN SSN
                 , PROD.LANGUAGE_TEXT_ID LANGUAGE_TEXT_ID
                 , C.CARDHOLDER_ID CARDHOLDER_ID
                 , CHG.ORDERING_STATUS_CODE LATEST_ORDER_STATUS
                 , C.PRIMARY_CARD PRIMARY_CARD
                 , C.ORDERING_TIME ORDERING_TIME
                 , PROD.PRODUCT_ID PRODUCT_ID
                 , CUST.BIRTHDATE BIRTHDATE
                 , C.PICTURE_ID_CLM PICTURE_ID_CLM
                 , CHG.CARD_APPLICATION_TYPE_ID CARD_APPLICATION_TYPE_ID
              FROM CARDT     C
                 , CARDCHGT  CHG
                 , CARDTYPT  CARDTYPE
                 , COMPANYT  COMP
                 , CUSTOMERT CUST
                 , PRODUCTT  PROD 
             WHERE
                   CHG.CARD_NUMBER = tab2.CARD_NUMBER
               AND char(CHG.CARD_VALID_DATE, iso) || trim(char(CHG.CARD_PAN_SEQ))
                   = tab2.MAX_CARD_PANDATE
               AND CHG.CARD_ID         = C.CARD_ID
               AND C.CUSTOMER_ID       = CUST.CUSTOMER_ID 
               AND C.COMPANY_ID        = COMP.COMPANY_ID
               AND C.CARD_TYPE_ID      = CARDTYPE.CARD_TYPE_ID
               AND CARDTYPE.PRODUCT_ID = PROD.PRODUCT_ID
               AND CHG.ORDERING_STATUS_CODE NOT IN (10, 11, 13, 14)
            UNION ALL
            SELECT 'cardhist' table_name
                 , C.CARD_HISTORY_ID ID
                 , C.CARD_ID CARD_ID
                 , C.CARD_NUMBER CARD_NUMBER
                 , 0 DEBIT_CARD_NUMBER
                 , C.CARD_PAN_SEQ CARD_PAN_SEQ
                 , C.CARD_VALID_DATE EXPIRY_DATE
    --             , concat (char(C.CARD_VALID_DATE, iso), trim(char(C.CARD_PAN_SEQ))) PANDATE
                 , COMP.COMPANY_ID COMPANY_ID
                 , COMP.FULLNAME COMPANY_NAME
                 , CUST.LASTNAME CUST_LASTNAME
                 , CUST.FIRSTNAME CUST_FIRSTNAME
                 , CH.EMBOSSING_NAME EMBOSSING_NAME
                 , CUST.SSN SSN
                 , PROD.LANGUAGE_TEXT_ID LANGUAGE_TEXT_ID
                 , C.CARDHOLDER_ID CARDHOLDER_ID
                 , C.ORDERING_STATUS_CODE LATEST_ORDER_STATUS
                 , C.PRIMARY_CARD PRIMARY_CARD
                 , C.ORDERING_TIME ORDERING_TIME
                 , PROD.PRODUCT_ID PRODUCT_ID
                 , CUST.BIRTHDATE BIRTHDATE
                 , C.PICTURE_ID_CLM PICTURE_ID_CLM
                 , 99 CARD_APPLICATION_TYPE_ID
              FROM CARDHIST  C
                 , CARDTYPT  CARDTYPE
                 , COMPANYT  COMP
                 , CUSTOMERT CUST
                 , PRODUCTT  PROD
                 , CARDHLDT  CH 
             WHERE
                   C.CARD_NUMBER = tab2.CARD_NUMBER
               AND char(C.CARD_VALID_DATE, iso) || trim(char(C.CARD_PAN_SEQ))
                   = tab2.MAX_CARD_PANDATE
               AND C.CARDHOLDER_ID     = CH.CARDHOLDER_ID
               AND C.CUSTOMER_ID       = CUST.CUSTOMER_ID
               AND C.COMPANY_ID        = COMP.COMPANY_ID
               AND C.CARD_TYPE_ID      = CARDTYPE.CARD_TYPE_ID
               AND CARDTYPE.PRODUCT_ID = PROD.PRODUCT_ID
           )TAB1
    Last edited by tonkuma; 01-21-10 at 07:37. Reason: Removed rightmost parenthesis of "trim(char(C.CARD_PAN_SEQ)))"

  11. #11
    Join Date
    Jan 2009
    Posts
    153
    Thank you very much Tonkuma,

    I am getting some error when i tried to execute the modified script

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I am getting some error when i tried to execute the modified script
    What error messages did you got?
    I have updated my code.
    Please try it again, then supply error code and full error message text you got.
    Last edited by tonkuma; 01-21-10 at 07:42. Reason: Added "I have updated my code. ... try it again, then".

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is second modified query:
    (Used ROW_NUMBER() to get maximum values.)
    Code:
    SELECT table_name
         , ID , CARD_ID , tab1.CARD_NUMBER , DEBIT_CARD_NUMBER
         , tab1.CARD_PAN_SEQ , EXPIRY_DATE
    --     , PANDATE
         , COMPANY_ID , COMPANY_NAME , CUST_LASTNAME , CUST_FIRSTNAME , SSN
         , LANGUAGE_TEXT_ID , CARDHOLDER_ID , LATEST_ORDER_STATUS
         , PRIMARY_CARD , ORDERING_TIME , COMPANY_ID , PRODUCT_ID
         , BIRTHDATE , PICTURE_ID_CLM , EMBOSSING_NAME
         , CARD_APPLICATION_TYPE_ID
      FROM (SELECT CARD_NUMBER
                 , CARD_VALID_DATE , CARD_PAN_SEQ
              FROM (SELECT CARD_NUMBER
                         , CARD_VALID_DATE , CARD_PAN_SEQ
                         , ROW_NUMBER()
                             OVER(PARTITION BY CARD_NUMBER
                                      ORDER BY CARD_VALID_DATE DESC
                                             , CARD_PAN_SEQ    DESC) rn
                      FROM (SELECT CARD_NUMBER
                                 , CARD_VALID_DATE , CARD_PAN_SEQ
                              FROM CARDT C 
                            UNION ALL
                            SELECT CARD_NUMBER
                                 , CARD_VALID_DATE , CARD_PAN_SEQ
                              FROM CARDCHGT C 
                             WHERE ORDERING_STATUS_CODE NOT IN (10, 11, 13, 14)
                            UNION ALL
                            SELECT CARD_NUMBER
                                 , CARD_VALID_DATE , CARD_PAN_SEQ
                              FROM CARDHIST C
                           ) FETCH
                   ) T
             WHERE rn = 1
           ) AS TAB2
         , LATERAL
           ( 
            SELECT 'card' table_name
                 , C.CARD_ID ID , C.CARD_ID CARD_ID , C.CARD_NUMBER CARD_NUMBER
                 , 0 DEBIT_CARD_NUMBER
                 , C.CARD_PAN_SEQ CARD_PAN_SEQ , C.CARD_VALID_DATE EXPIRY_DATE
                 , COMP.COMPANY_ID COMPANY_ID , COMP.FULLNAME COMPANY_NAME
                 , CUST.LASTNAME CUST_LASTNAME , CUST.FIRSTNAME CUST_FIRSTNAME
                 , CH.EMBOSSING_NAME EMBOSSING_NAME
                 , CUST.SSN SSN
                 , PROD.LANGUAGE_TEXT_ID LANGUAGE_TEXT_ID
                 , C.CARDHOLDER_ID CARDHOLDER_ID , C.ORDERING_STATUS_CODE LATEST_ORDER_STATUS
                 , C.PRIMARY_CARD PRIMARY_CARD , C.ORDERING_TIME ORDERING_TIME
                 , PROD.PRODUCT_ID PRODUCT_ID
                 , CUST.BIRTHDATE BIRTHDATE
                 , C.PICTURE_ID_CLM PICTURE_ID_CLM
                 , 99 CARD_APPLICATION_TYPE_ID 
              FROM CARDT     C
                 , CARDTYPT  CARDTYPE
                 , COMPANYT  COMP
                 , CUSTOMERT CUST
                 , PRODUCTT  PROD
                 , CARDHLDT  CH 
             WHERE
                   C.CARD_NUMBER     = tab2.CARD_NUMBER
               AND C.CARD_VALID_DATE = tab2.CARD_VALID_DATE
               AND C.CARD_PAN_SEQ    = tab2.CARD_PAN_SEQ
               AND C.CARDHOLDER_ID     = CH.CARDHOLDER_ID
               AND C.CUSTOMER_ID       = CUST.CUSTOMER_ID
               AND C.COMPANY_ID        = COMP.COMPANY_ID
               AND C.CARD_TYPE_ID      = CARDTYPE.CARD_TYPE_ID
               AND CARDTYPE.PRODUCT_ID = PROD.PRODUCT_ID 
            UNION ALL
            SELECT 'cardchg' table_name
                 , CHG.CARD_CHANGE_ID ID , CHG.CARD_ID CARD_ID , CHG.CARD_NUMBER CARD_NUMBER
                 , CHG.DEBIT_CARD_NUMBER DEBIT_CARD_NUMBER
                 , CHG.CARD_PAN_SEQ CARD_PAN_SEQ , CHG.CARD_VALID_DATE EXPIRY_DATE
                 , COMP.COMPANY_ID COMPANY_ID , COMP.FULLNAME COMPANY_NAME
                 , CUST.LASTNAME CUST_LASTNAME , CUST.FIRSTNAME CUST_FIRSTNAME
                 , CHG.EMBOSSING_NAME
                 , CUST.SSN SSN
                 , PROD.LANGUAGE_TEXT_ID LANGUAGE_TEXT_ID
                 , C.CARDHOLDER_ID CARDHOLDER_ID , CHG.ORDERING_STATUS_CODE LATEST_ORDER_STATUS
                 , C.PRIMARY_CARD PRIMARY_CARD , C.ORDERING_TIME ORDERING_TIME
                 , PROD.PRODUCT_ID PRODUCT_ID
                 , CUST.BIRTHDATE BIRTHDATE
                 , C.PICTURE_ID_CLM PICTURE_ID_CLM
                 , CHG.CARD_APPLICATION_TYPE_ID CARD_APPLICATION_TYPE_ID
              FROM CARDT     C
                 , CARDCHGT  CHG
                 , CARDTYPT  CARDTYPE
                 , COMPANYT  COMP
                 , CUSTOMERT CUST
                 , PRODUCTT  PROD 
             WHERE
                   CHG.CARD_NUMBER     = tab2.CARD_NUMBER
               AND CHG.CARD_VALID_DATE = tab2.CARD_VALID_DATE
               AND CHG.CARD_PAN_SEQ    = tab2.CARD_PAN_SEQ
               AND CHG.CARD_ID         = C.CARD_ID
               AND C.CUSTOMER_ID       = CUST.CUSTOMER_ID 
               AND C.COMPANY_ID        = COMP.COMPANY_ID
               AND C.CARD_TYPE_ID      = CARDTYPE.CARD_TYPE_ID
               AND CARDTYPE.PRODUCT_ID = PROD.PRODUCT_ID
               AND CHG.ORDERING_STATUS_CODE NOT IN (10, 11, 13, 14)
            UNION ALL
            SELECT 'cardhist' table_name
                 , C.CARD_HISTORY_ID ID , C.CARD_ID CARD_ID , C.CARD_NUMBER CARD_NUMBER
                 , 0 DEBIT_CARD_NUMBER
                 , C.CARD_PAN_SEQ CARD_PAN_SEQ , C.CARD_VALID_DATE EXPIRY_DATE
                 , COMP.COMPANY_ID COMPANY_ID , COMP.FULLNAME COMPANY_NAME
                 , CUST.LASTNAME CUST_LASTNAME , CUST.FIRSTNAME CUST_FIRSTNAME
                 , CH.EMBOSSING_NAME EMBOSSING_NAME
                 , CUST.SSN SSN
                 , PROD.LANGUAGE_TEXT_ID LANGUAGE_TEXT_ID
                 , C.CARDHOLDER_ID CARDHOLDER_ID , C.ORDERING_STATUS_CODE LATEST_ORDER_STATUS
                 , C.PRIMARY_CARD PRIMARY_CARD , C.ORDERING_TIME ORDERING_TIME
                 , PROD.PRODUCT_ID PRODUCT_ID
                 , CUST.BIRTHDATE BIRTHDATE
                 , C.PICTURE_ID_CLM PICTURE_ID_CLM
                 , 99 CARD_APPLICATION_TYPE_ID
              FROM CARDHIST  C
                 , CARDTYPT  CARDTYPE
                 , COMPANYT  COMP
                 , CUSTOMERT CUST
                 , PRODUCTT  PROD
                 , CARDHLDT  CH 
             WHERE
                   C.CARD_NUMBER     = tab2.CARD_NUMBER
               AND C.CARD_VALID_DATE = tab2,CARD_VALID_DATE
               AND C.CARD_PAN_SEQ    = tab2,CARD_PAN_SEQ
               AND C.CARDHOLDER_ID     = CH.CARDHOLDER_ID
               AND C.CUSTOMER_ID       = CUST.CUSTOMER_ID
               AND C.COMPANY_ID        = COMP.COMPANY_ID
               AND C.CARD_TYPE_ID      = CARDTYPE.CARD_TYPE_ID
               AND CARDTYPE.PRODUCT_ID = PROD.PRODUCT_ID
           )TAB1

  14. #14
    Join Date
    Jan 2010
    Posts
    335
    Quote Originally Posted by ratheeshknair View Post
    Hi ,

    I tried

    C:\Documents and Settings\rknair>db2advis -db csys -n AABSYS -i C:\query.in -o query_out.txt

    but i am getting error
    Well,
    posting the error would help.

  15. #15
    Join Date
    Jan 2009
    Posts
    153
    Can you please let me know what changes or performance improvement the below changes makes in the query

    1) Used UNION ALL instead of UNION.
    2) Used JOIN(with LATERAL keyword) instead of IN predicate.
    3) Removed PANDATE from final result.

Posting Permissions

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