troubleshooting Question

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

Avatar of jdwan
jdwanFlag for Australia asked on
Operating SystemsDB2ASP.NET
7 Comments1 Solution906 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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros