troubleshooting Question

Optimsing an SQL Statement for IBM.Data.DB2.Iseries (asp.net)

Avatar of jdwan
jdwanFlag for Australia asked on
Operating SystemsASP.NETDB2
7 Comments2 Solutions906 ViewsLast Modified:
Hi,

I currently have a program which is collates a large amount of data (residing on an iseries) to create a crystal report - basically my crystal report is using a dataset built by a backend process (vb code) which uses an sql statement connecting to an iseries using the db2.iseries .Net driver. My issue is that because of the large amount of data my sql query is taking some considerable time to run - i have included my sql statement below and was hoping someone could provide any assistance on optimizing this query (if possible). I have also inlcude the size of the tables these table sizes can vary from year to year as each yeear some of them are archived

TABLE1 -577107
TABLE2 -443379
TABLE3-1890095
TABLE4-8957
TABLE5-392816
TABLE6-2474
TABLE7-10179


SELECT     LIB.TABLE1.BLEGDT, LIB.TABLE2.BLGCOL, LIB.TABLE2.BLGLEF, 
                      LIB.TABLE2.BLGBRK, LIB.TABLE1.BLESPL, LIB.TABLE1.BLEMCR, MIN(LIB.TABLE7.STTSTG) as STTSTG, 
                      SUM(LIB.TABLE3.BLEALW) AS TOTALW, MIN(LIB.TABLE5.PDTUSP) AS PDTUSP, MIN(LIB.TABLE6.MITUSP) AS MITUSP, 
                      MIN(LIB.TABLE6.MITWPD) AS MITWPD, MIN(LIB.TABLE7.STTUSP) AS STTUSP
FROM         LIB.TABLE1, LIB.TABLE2, LIB.TABLE3, LIB.TABLE4, LIB.TABLE5, LIB.TABLE6, 
                      LIB.TABLE7
WHERE     LIB.TABLE1.BLESSN = LIB.TABLE2.BLGSSN AND LIB.TABLE1.BLEBIM = LIB.TABLE2.BLGBIM AND 
                      LIB.TABLE1.BLEBGI = LIB.TABLE2.BLGBGI AND LIB.TABLE1.BLEBLE = LIB.TABLE2.BLGBLE AND 
                      LIB.TABLE1.BLECOI = LIB.TABLE3.BLECOI AND LIB.TABLE1.BLESSN = LIB.TABLE3.BLESSN AND 
                      LIB.TABLE1.BLEBUN = LIB.TABLE3.BLEBUN AND LIB.TABLE3.BLECOI = LIB.TABLE4.HDRCOI AND 
                      LIB.TABLE3.BLENBR = LIB.TABLE4.HDRNBR AND LIB.TABLE3.BLECCD = LIB.TABLE4.HDRCCD AND 
                      LIB.TABLE3.BLESSN = LIB.TABLE4.HDRSSN AND LIB.TABLE4.HDRPDT = LIB.TABLE5.PDTPAD AND 
                      LIB.TABLE2.BLGCOL = LIB.TABLE5.PDTCOL AND LIB.TABLE2.BLGLEF = LIB.TABLE5.PDTLEF AND 
                      LIB.TABLE2.BLGBRK = LIB.TABLE5.PDTRED AND LIB.TABLE1.BLESPL = LIB.TABLE5.PDTSPL AND 
                      LIB.TABLE4.HDRPDT = LIB.TABLE6.MITPAD AND LIB.TABLE1.BLEMCR = LIB.TABLE6.MITMCR AND 
                      LIB.TABLE4.HDRPDT = LIB.TABLE7.STTPAD AND LIB.TABLE1.BLESTR = LIB.TABLE7.STTSTR AND 
                      (LIB.TABLE4.HDRCOI = 1) AND (LIB.TABLE4.HDRSSN = 108) AND (LIB.TABLE4.HDRNBR = 210015635)
GROUP BY LIB.TABLE1.BLEGDT, LIB.TABLE1.BLEMCR, LIB.TABLE1.BLESPL, LIB.TABLE1.BLESTR, 
                      LIB.TABLE2.BLGLEF, LIB.TABLE2.BLGCOL, LIB.TABLE2.BLGBRK, LIB.TABLE4.HDREXH, STTSTG

Open in new window

ASKER CERTIFIED SOLUTION
Kent Olsen
Data Warehouse / Database Architect

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 2 Answers and 7 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 2 Answers and 7 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004