Home   |   Register   |   Rules   |   Calendar   |   Get Daily   |     |  
 


If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Performance Tunning Tool

Reply
Page 1 of 2 1 2
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 01-19-10, 02:47
ratheeshknair ratheeshknair is offline
Registered User
  
Join Date: Jan 2009
Posts: 153
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
Reply With Quote
  #2 (permalink)  
Old 01-19-10, 07:56
ARWinner ARWinner is offline
Registered User
  
Join Date: Jan 2003
Posts: 3,575
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
Reply With Quote
  #3 (permalink)  
Old 01-20-10, 01:09
ratheeshknair ratheeshknair is offline
Registered User
  
Join Date: Jan 2009
Posts: 153
Thanks Andy..
Reply With Quote
  #4 (permalink)  
Old 01-20-10, 01:14
ratheeshknair ratheeshknair is offline
Registered User
  
Join Date: Jan 2009
Posts: 153
I have created explain_instance table but still am not getting access plan
Reply With Quote
  #5 (permalink)  
Old 01-20-10, 03:41
nvk@vhv nvk@vhv is offline
Registered User
  
Join Date: Jan 2010
Posts: 294
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>
Reply With Quote
  #6 (permalink)  
Old 01-21-10, 00:07
ratheeshknair ratheeshknair is offline
Registered User
  
Join Date: Jan 2009
Posts: 153
Thanks nvk@vhv
Reply With Quote
  #7 (permalink)  
Old 01-21-10, 00:09
ratheeshknair ratheeshknair is offline
Registered User
  
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 )
Reply With Quote
  #8 (permalink)  
Old 01-21-10, 03:29
nvk@vhv nvk@vhv is offline
Registered User
  
Join Date: Jan 2010
Posts: 294
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.
Reply With Quote
  #9 (permalink)  
Old 01-21-10, 04:55
ratheeshknair ratheeshknair is offline
Registered User
  
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
Reply With Quote
  #10 (permalink)  
Old 01-21-10, 05:26
tonkuma tonkuma is offline
Registered User
  
Join Date: Feb 2008
Location: Japan
Posts: 2,193
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 06:37. Reason: Removed rightmost parenthesis of "trim(char(C.CARD_PAN_SEQ)))"
Reply With Quote
  #11 (permalink)  
Old 01-21-10, 06:17
ratheeshknair ratheeshknair is offline
Registered User
  
Join Date: Jan 2009
Posts: 153
Thank you very much Tonkuma,

I am getting some error when i tried to execute the modified script
Reply With Quote
  #12 (permalink)  
Old 01-21-10, 06:32
tonkuma tonkuma is offline
Registered User
  
Join Date: Feb 2008
Location: Japan
Posts: 2,193
Quote:
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 06:42. Reason: Added "I have updated my code. ... try it again, then".
Reply With Quote
  #13 (permalink)  
Old 01-21-10, 06:46
tonkuma tonkuma is offline
Registered User
  
Join Date: Feb 2008
Location: Japan
Posts: 2,193
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
Reply With Quote
  #14 (permalink)  
Old 01-21-10, 07:25
nvk@vhv nvk@vhv is offline
Registered User
  
Join Date: Jan 2010
Posts: 294
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.
Reply With Quote
  #15 (permalink)  
Old 01-21-10, 07:41
ratheeshknair ratheeshknair is offline
Registered User
  
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.
Reply With Quote
Reply
Page 1 of 2 1 2

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


 
 
 
Home  |   Register  |   Get New  |   FAQ's  |   Sitemap

Powered by vBulletin
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.