asked on

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

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

Sharath S

your query is looking good.

Shalom Carmel

on the iseries do the following:
run STRDBG
run STRSQL, paste your query and execute it
go to the joblog and read carefully the optimization messages. It looks like you could use a few indexes.
if you do need indexing, insist that they are created with the CREATE INDEX sql commands and NOT with the traditional logical files.
ShalomC

Theo Kouwenhoven

Hi jdwan,

Try to add an "Order by" clause, most of the time SQL can find the most opitmal access patth, but somtime it doesn't work, adding the Order by will help often to determine the right access-path.

Regards,
Murph

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Gary Patterson, CISSP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jdwan

ASKER

Hi Guys,

Thanks for your reponses - i have made progress on the SQL Statement (see new SQL code below), i am going to have to split the points as i tried a few things and have now got to the command (which yesterady was taking nearly 2 hours to run - for running bulk reports) down to approx 3 mins which i am very very happy with. Thanks for you help on this one.

After reviewing the SQL i realised i could take a few things out which were able to be replaced by values passed by the vb program executing the script.
Cheers

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(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.TABLE2, LIB.TABLE1,
                          (SELECT     BLEBUN, BLECLI, BLECCD, BLENBR, BLEULW, BLEALW, BLESSN, BLECOI
                            FROM          LIB.TABLE3 TABLE3_1
                            WHERE      (BLECOI = 1) AND (BLESSN = 108) AND (BLENBR = 210015635)) TABLE3, LIB.TABLE5, LIB.TABLE6, 
                      LIB.TABLE7
WHERE     LIB.TABLE2.BLGSSN = LIB.TABLE1.BLESSN AND LIB.TABLE2.BLGBIM = LIB.TABLE1.BLEBIM AND 
                      LIB.TABLE2.BLGBGI = LIB.TABLE1.BLEBGI AND LIB.TABLE2.BLGBLE = LIB.TABLE1.BLEBLE AND 
                      LIB.TABLE1.BLECOI = TABLE3.BLECOI AND LIB.TABLE1.BLESSN = TABLE3.BLESSN AND 
                      LIB.TABLE1.BLEBUN = TABLE3.BLEBUN 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.TABLE1.BLEMCR = LIB.TABLE6.MITMCR AND 
                      LIB.TABLE1.BLESTR = LIB.TABLE7.STTSTR AND (LIB.TABLE5.PDTPAD = 'CODE') AND 
                      (LIB.TABLE6.MITPAD = 'CODE') AND (LIB.TABLE7.STTPAD = 'CODE') AND (TABLE3.BLECOI = 1) AND 
                      (TABLE3.BLESSN = 108) AND (TABLE3.BLENBR = 210015635)
GROUP BY LIB.TABLE1.BLEGDT, LIB.TABLE1.BLEMCR, LIB.TABLE1.BLESPL, LIB.TABLE7.STTSTG, 
                      LIB.TABLE2.BLGLEF, LIB.TABLE2.BLGCOL, LIB.TABLE2.BLGBRK

Open in new window

Gary Patterson, CISSP

Impressive results. Glad we could help.

- Gary Patterson