Antwort: [DB2-L] Expalin help

Roy Boxwell

Antwort: [DB2-L] Expalin help
Hi!

Without knowing the Table, Key/Index and Cardinality information it is
impossible to state whether or not the SQL could be improved. Post the
Table, Key/Index and Cardinality information for these Tables and Indexes
and then perhaps we can help!
But apart from that 1 Millisecond and 6 Service units is not bad! However
remember that when explain states 1 millisecond it can still take seconds
to run!

Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Strasse 5
40470 Duesseldorf/Germany
Tel. +49 (0)211 96149-0
Fax +49 (0)211 96149-35
E-mail [login to unmask email]
Homepage www.seg.de





tspdba001 <[login to unmask email]>
Gesendet von: DB2 Data Base Discussion List <[login to unmask email]>
10.01.2006 00:53
Bitte antworten an DB2 Database Discussion list at IDUG


An: [login to unmask email]
Kopie:
Thema: [DB2-L] Expalin help


Hi All,

I need help with the visual explain, which I did. Below are the results of
visual explain. Does this explain after runstats looks good? OR does
this sql needs more tuning? If yes what does it needs? Thanks in advance.

Before runstats explain

Name Value
-------------- -----------------------
Type SELECT
CPU Cost (ms) 670082066
CPU Cost (su) 2147483647
Cost Category B
Reason TABLE CARDINALITY
Timestamp 2006-01-09 15:26:21.08

After Runstats explain

Name Value
-------------- -----------------------
Type SELECT
CPU Cost (ms) 1
CPU Cost (su) 6
Cost Category A
Reason
Timestamp 2006-01-09 15:39:30.63

Below is the explain statement


SELECT A.SEQ_ID COND_SEQ_ID, A.MKTBGSA_ID, A.PRICE_DATA_ID COND_DATA_ID,
A.PRICE_DATA_TYPE, A.CPI_STATUS, A.DESCRIPTION, A.EFF_DT, A.ORIGIN_TYPE,
B.STATUS, B.CURRENT_MKT_OFFER, B.SUBS_FLAG_IND, B.LIMITATION_TYPE,
B.VIS_TMSTAMP, C.SEQ_ID COND_TYPE_ID, C.TARGET_TYPE, C.TARGET_VALUE,
C.TARGET_VALUE_DESC, C.EFF_DT TARGET_EFF_DT, C.END_DT TARGET_END_DT,
D.PLAN_SEQ_ID, D.PRICE_PLAN_ID , D.DESCRIPTION PRICE_DATA_DESC,
D.I_STATUS PRICE_DATA_STATUS
FROM B3I.TPRICE_DATA A,B3I.TVIS_COND B, B3I.TVIS_COND_TYPE C,
B3I.TPRICE_PLANS D
WHERE A.ORIGIN_TYPE != 'O'
AND A.I_STATUS NOT IN ('I CANCELLED', 'I DEPLOYED')
AND D.PLAN_SEQ_ID = B.OWNER_SEQ_ID
AND B.SEQ_ID = C.COND_SEQ_ID
AND B.SEQ_ID = A.SEQ_ID
AND ((TARGET_TYPE = 'AD' AND TARGET_VALUE = 'A')) OR (TARGET_TYPE =
'AR' AND TARGET_VALUE = 'NE')
ORDER BY D.PRICE_PLAN_ID, COND_DATA_ID
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm