NT - HOST
[ ruban.de ] [ Antworten ] [ Forum ]
Geschrieben von mickey2 am März 25, 2004 um 09:26:
Haben Sie eine Idee, warum das folgende SQL-Statement unter
NT (DB2-Vers. 7) in angemessener Zeit ein Ergebnis liefert und
auf dem IBM-HOST unter DB2-Vers. 7 zum Langläufer wird, den
man nur noch abbrechen kann ?
Auf NT- und HOST-Tabellen sind die gleichen Indizes.
Der Datenumfang beläuft sich auf 15.03.04 HOST NT Ges-Speicher-Vol IPBT1 68.916 S. 64.197 S. ca. 25 MB IPBE1 115.014 S. 104.180 S. ca. 40 MB
IPVG 9.920 S. 9.005 S. ca. 70 k QVTKZ ca. 3 Mio S. ca. 3 Mio ca. 225 MB
SELECT DISTINCT VALUE(SUBSTR(ipbe1.katfnr, 1, 6), '') AS katfnr
, VALUE(SUBSTR(ipbe1.katfnr, 7, 2), '') AS bz
, VALUE(SUBSTR(ipbe1.katfnr, 10, 3), '') AS oz
, VALUE(ipbe1.ozfnr, '') AS ozfnr, VALUE(ipbe1.kasksp, '') AS kasksp
, VALUE(ipbe1.blvhk, '') AS blvhk, VALUE(ipbe1.hstk, '') AS hstk
, VALUE(ipbe1.tkz, '') AS tkz, VALUE(ipbe1.versnran, '') AS versnran
, VALUE(ipbt1.bschteil, '') AS bschteil, VALUE(ipbe1.materhk, '') AS materhk
, VALUE(ipbe1.mpb, '') As mpb, VALUE(ipbe1.gmebo, '') AS gmebo
, VALUE(ipbe1.auswhksp, '') AS auswhksp
, VALUE(ipbe1.anwkbsp, '') AS anwkbsp
, VALUE(ipbe1.anwkgsp, '') AS anwkgsp, VALUE(ipbe1.awrke, '') AS awrke
, VALUE(ipbe1.awr, '') AS awr, VALUE(ipbe1.qv, '') AS qv
, VALUE(ipbe1.kznabb, '') AS kznabb, VALUE(ipbe2.ssnr, '') AS ssnr
, VALUE(ipbt1.nbarbhk, '') AS nbarbhk, VALUE(ipbe5.aenrsp, '') AS aenrsp
, VALUE(ipvg.gk, '') AS gk, VALUE(ipbe3.bproj, '') AS bproj, VALUE(ipvg.etusp, '') AS etusp, VALUE(ipbe3.ilsn, '') AS ilsn
, VALUE(ipvg.cokl, '') AS cokl
, VALUE(ipbe1.katfnr, '') AS parameterdummy
, CASE WHEN qvtkz.anzahl > 0
THEN 'J' ELSE 'N'
END AS inmgi
FROM ( (SELECT VALUE(gk, '') AS gk, VALUE(etusp, '') AS etusp, VALUE(cokl, '') AS cokl
FROM T3126.IPVG WHERE '1' = '1' ) ipvg
INNER JOIN (SELECT VALUE(versnran, '') AS versnran
, VALUE(tkz, '') AS tkz , VALUE(hstk, '') AS hstk
, VALUE(kasksp, '') AS kasksp , VALUE(katfnr, '') AS katfnr
, VALUE(ozfnr, '') AS ozfnr , VALUE(auswhksp, '') AS auswhksp , VALUE(anwkbsp, '') AS anwkbsp , VALUE(anwkgsp, '') AS anwkgsp , VALUE(materhk, '') AS materhk , VALUE(blvhk, '') AS blvhk
, VALUE(qv, '') AS qv , VALUE(mpb, '') AS mpb
, VALUE(gmebo, '') AS gmebo , VALUE(awrke, '') AS awrke
, VALUE(awr, '') AS awr , VALUE(kznabb, '') AS kznabb
, VALUE(etusp, '') AS etusp
FROM T3126.IPBE1
WHERE '1' = '1' AND etusp = 'F0210H004'
AND etusp IN (SELECT DISTINCT etusp
FROM T3126.IPVG WHERE '1' = '1' )
) ipbe1
ON (ipvg.etusp = ipbe1.etusp)
LEFT OUTER JOIN (SELECT VALUE(bproj, '') AS bproj
, VALUE(ilsn, '') AS ilsn, VALUE(etusp, '') AS etusp
, VALUE(katfnr, '') AS katfnr, VALUE(ozfnr, '') AS ozfnr
FROM T3126.IPBE3
WHERE '1' = '1' AND etusp = 'F0210H004'
AND etusp
IN (SELECT DISTINCT etusp
FROM T3126.IPVG WHERE '1' = '1' ) ) ipbe3
ON (ipbe1.etusp = ipbe3.etusp
AND ipbe1.katfnr = ipbe3.katfnr
AND ipbe1.ozfnr = ipbe3.ozfnr)
LEFT OUTER JOIN (SELECT VALUE(aenrsp, '') AS aenrsp
, VALUE(etusp, '') AS etusp, VALUE(katfnr, '') AS katfnr
, VALUE(ozfnr, '') AS ozfnr
FROM T3126.IPBE5
WHERE '1' = '1'
AND etusp = 'F0210H004'
AND etusp IN (SELECT DISTINCT etusp
FROM T3126.IPVG WHERE '1' = '1' )
) ipbe5
ON (ipbe1.etusp = ipbe5.etusp
AND ipbe1.katfnr = ipbe5.katfnr
AND ipbe1.ozfnr = ipbe5.ozfnr)
LEFT OUTER JOIN (SELECT VALUE(ssnr, '') AS ssnr
, VALUE(etusp, '') AS etusp, VALUE(katfnr, '') AS katfnr
, VALUE(ozfnr, '') AS ozfnr
FROM T3126.IPBE2 WHERE '1' = '1'
AND etusp = 'F0210H004' AND etusp
IN (SELECT DISTINCT etusp
FROM T3126.IPVG WHERE '1' = '1' ) ) ipbe2
ON (ipbe1.etusp = ipbe2.etusp
AND ipbe1.katfnr = ipbe2.katfnr
AND ipbe1.ozfnr = ipbe2.ozfnr)
LEFT OUTER JOIN (SELECT VALUE(bschteil, '') AS bschteil, VALUE(nbarbhk, '') AS nbarbhk, VALUE(cokl, '') AS cokl
, VALUE(tkz, '') AS tkz, VALUE(hstk, '') AS hstk
FROM T3126.IPBT1 ) ipbt1
ON (ipbt1.cokl = ipvg.cokl AND ipbt1.tkz = ipbe1.tkz
AND ipbt1.hstk = ipbe1.hstk)
LEFT OUTER JOIN (SELECT hstk, tkz, count(*) as anzahl
FROM G4180000.QVTKZ GROUP BY hstk, tkz ) qvtkz
ON (ipbe1.hstk = qvtkz.hstk AND ipbe1.tkz = qvtkz.tkz) )
WHERE '1' = '1' AND ipbe1.etusp = 'F0210H004'
ORDER BY 1
Schreibe eine Antwort
|