SQL-Optimizer ist einfach blöd

[BenderD]

wenn du wieder mal so ein Erlebnis hast, wäre ich dir sehr verbunden, wenn du dieses mit uns teilst...

Ich habe jedenfalls versucht mich an die Empfehlungen in Performance and Query Optimization zu halten und habe für eine Tabelle mit Mandanten Key mit einigen hundert Mio Sätzen, bei 6 Mandantenkey Ausprägungen erstellt mit SQL, kein RLA, alle SQL Statements auf Views, alles wie es sich gehört, alle selects ausgeführt mit SQE, EVIs angelegt wie ein Weltmeister, unter V5R3 und V5R4, unter mehreren PTF Ständen; das einzige was passiert ist, ist dass die Schreibperformance messbar gelitten hat. Löschen des EVIs vor schreiben und Neuaufbau danach, wie in der Reference empfohlen, lag dann beim Aufbau eines einzigen Indexes deutlich im Stundenbereich, bei einer Maschine mit parallel Database Feature, die in einer Stunde 30 Millionen commited Transactions wegpackt, das kann man nichtmal im Winter laufen lassen, wenn die Nächte länger sind.

Ach ja, und der Softwaresupport, das ist noch so ein Thema für sich, wenn man denen dann keine reproduzierbare Konstellation liefern kann, dann ist eh' Ende der Fahnenstange und den Aufwand, den man treibt steckt man dann oftmals gleich ins finden von Workarounds.

Deine Palette an Stichworten am Ende klingt erst mal beeindruckend, aber über Starschema Support und OLAP Funktionen scheint es auch sehr unterschiedliche Erfahrungswerte zu geben; nachdem ich einen halben Tag mit RANK rumlaboriert habe, um den 10. niedrigsten Preis zu finden, habe ich dann eine SQL Function geschrieben, die das in einem Bruchteil der Zeit erledigt.

D*B

Ich hab's schon erlebt, wenn EVIs über einzelne Felder gelegt wrrden, können z.T. sogar mehrere EVIs zum Erstellen von Bitmaps verwendet werden.
(Stichwort: Index anding and oring, Star Join Schema und Look ahead Predicat Generation LPG)

Birgitta

[Fuerchau]

Ich sags ja, kaum dreht man an der einen Schraube läuft was anderes nicht.
Ich hatte ja in der QAQQINI den Wert 'IGNORE_DERIVED_INDEX' auf *YES gesetzt.
Dies führt nun dazu, dass so gut wie alle SQL's von der SQE durchgeführt werden.

Tja, nun stirbt ein Programm, dass schon lange (Februar 2005) läuft mit einem MCH3203-Fehler:

Nachricht . . . : Funktionsfehler X'1720' in Maschineninstruktion. Interne
Speicherauszugs-ID .
Ursache . . . . : Die Ausführung der Maschineninstruktion ist
fehlgeschlagen. Zeitmarke = 22.10.08 11:58:23, Fehlercode = X'1720',
Fehlerklasse = 0, Einheitennummer = X'0000'. Die Fehlerklasse zeigt an, wie
der Fehler entdeckt wurde:
0000 = nicht spezifizierte abnormale Bedingung
0002 = logisch ungültiger Einheitensektor
0003 = Einheitenfehler
0004 = ungültige Operation ausgeführt
Bei Fehlerklasse 0003 kennzeichnet die Einheitennummer die fehlerhafte
Einheit oder sie enthält Null, falls der Fehler im Hauptspeicher aufgetreten
ist.
Bei Fehlerklasse 0004 wurde ein nicht unterstützter Operationscode einer
MI-Instruktion verwendet.
Fehlerbeseitigung: Bei Fehlerklasse 0004 den nicht unterstützten
Operationscode der MI-Instruktion aus dem Programm entfernen. Bei allen
anderen Fehlerklassen die Problemanalyse starten (Befehl ANZPRB).

Nachrichten-ID . . . . : MCH3203 Bewertung . . . . . . : 60
Sendedatum . . . . . . : 22.10.08 Sendezeit . . . . . . : 11:58:28
Nachrichtenart . . . . : Abbruch
Von . . . . . . . . . : REMHOL_800 CCSID . . . . . . . . : 65535

Von Programm . . . . . . . . . : assert
Instruktion . . . . . . . . : 000004

An Programm . . . . . . . . . : QQQOOODBOP
An Bibliothek . . . . . . . : QSYS
An Modul . . . . . . . . . . : QQQOOOINV
An Prozedur . . . . . . . . : CALLDBMAINTFOROPENOROPTIMIZE
An Anweisung . . . . . . . . : 4139

Zusätzlich gibts einen 1419 Seiten Dump des Optimizers.

Ich habe nun in einem CLP per CHGQRYA auf eine andere, nicht modifizierte QAQQINI ungeschaltet und siehe da, der Befehl läuft wieder, da er nun von der CQE ausgeführt wird.

Der SQL ist eigentlich recht simpel und auch schnell:

c/exec sql 
c+ with 
c+ x1IVCL as -- alle offenen Inventuren 
c+ (select distinct ivivnr from ivcl 
c+ where ivfirm=:DAFIRM 
c+ and ivwknr=:DAWKNR 
c+ and ivlanr=:DASTLA 
c+ and ivsart='KO' 
c+ and ivstat<'80' -- erledigt/storniert 
c+ ) 
c+ , 
c+ x1LGLM as -- alle Bestände aus LGST 
c+ -- die nicht gesperrt sind 
c+ (select cmfirm, cmwknr, cmlanr, cmlonr, cmtenr 
c+ ,dec(sum(cmbest), 11, 2) as cmbest 
c+ ,dec(sum(case tevpbm*tevpap -- auf palette 
c+ when 0 then 1 
c+ else cmbest / (tevpbm*tevpap) -- Anz.Paletten 
c+ end) 
c+ , 11, 5) as cmpale 
c+ from lglm 
c+ inner join teil 
c+ on cmfirm=tefirm 
c+ and cmwknr=tewknr 
c+ and cmtenr=tetenr 
c+ inner join LGST 
c+ on cmfirm=clfirm 
c+ and cmwknr=clwknr 
c+ and cmlanr=cllanr 
c+ and cmlonr=cllonr 
c+ where cmfirm=:DAFIRM 
c+ and cmwknr=:DAWKNR 
c+ and cmlanr=:DASTLA 
c+ and clspgr <> '09' -- Auslagerung anstehend 
c+ and clakiv not in (select ivivnr from x1IVCL) 
c+ and clivda < :GZDAVON -- Letzte Inventur < Grenze 
c+ group by cmfirm, cmwknr, cmlanr, cmlonr, cmtenr 
c+ ) 
c+ 
c+ -- Sammeln der Daten 
c+ 
c+ select min(cllonr) -- erster Platz 
c+ ,max(cllonr) -- letzter Platz 
c+ ,count(cllonr) -- Anzahl Plätze 
c+ 
c+ ,(select count(*) from LGST -- Anzahl gezählt 
c+ where clfirm=:DAFIRM 
c+ and clwknr=:DAWKNR 
c+ and cllanr=:DASTLA 
c+ and clivda >= :GZDAVON -- Letzte Inventur 
c+ and abs(clfr04) <= :DAAGNR -- Mengendiff % 
c+ and abs(clfr05) <= :DAKONR -- Wertdiff % 
c+ ) 
c+ 
c+ ,(select count(*) from LGST -- Anzahl Differenz 
c+ where clfirm=:DAFIRM 
c+ and clwknr=:DAWKNR 
c+ and cllanr=:DASTLA 
c+ and clivda >= :GZDAVON -- Letzte Inventur 
c+ and clakiv not in (select ivivnr from x1IVCL) 
c+ and (abs(clfr04) > :DAAGNR -- Mengendiff % 
c+ or abs(clfr05) > :DAKONR) -- Wertdiff % 
c+ ) 
c+ 
c+ ,(select count(*) from LGST -- leere Plätze 
c+ exception join LGLM 
c+ on clfirm=cmfirm 
c+ and clwknr=cmwknr 
c+ and cllanr=cmlanr 
c+ and cllonr=cmlonr 
c+ where clfirm=:DAFIRM 
c+ and clwknr=:DAWKNR 
c+ and cllanr=:DASTLA 
c+ and clakiv not in (select ivivnr from x1IVCL) 
c+ and clivda < :GZDAVON -- Letzte Inventur 
c+ ) 
c+ 
c+ ,(select count(distinct cllonr) -- Anzahl ohne Res. 
c+ from LGLM 
c+ inner join LGST 
c+ on cmfirm=clfirm 
c+ and cmwknr=clwknr 
c+ and cmlanr=cllanr 
c+ and cmlonr=cllonr 
c+ inner join teil 
c+ on cmfirm=tefirm 
c+ and cmwknr=tewknr 
c+ and cmtenr=tetenr 
c+ where clfirm=:DAFIRM 
c+ and clwknr=:DAWKNR 
c+ and cllanr=:DASTLA 
c+ and clakiv not in (select ivivnr from x1IVCL) 
c+ and clivda < :GZDAVON -- Letzte Inventur 
c+ and teresb = 0 -- keine Reservierung 
c+ ) 
c+ 
c+ ,(select count(distinct cmlonr) -- Anzahl volle Palette 
c+ from x1LGLM 
c+ where floor(cmpale) = cmpale -- Ganzzahl 
c+ ) 
c+ 
c+ ,(select count(distinct cmlonr) -- Anzahl Anbruchpalette 
c+ from x1LGLM 
c+ where floor(cmpale) <> cmpale -- Ganzzahl 
c+ ) 
c+ 
c+ ,(select count(*) from LGST -- Anzahl für Inventur 
c+ where clfirm=:DAFIRM 
c+ and clwknr=:DAWKNR 
c+ and cllanr=:DASTLA 
c+ and clakiv not in (select ivivnr from x1IVCL) 
c+ and clivda < :GZDAVON -- Letzte Inventur 
c+ and clspgr <> '09' -- Auslagerung 
c+ ) 
c+ 
c+ into :WKLGMI 
c+ ,:WKLGMX 
c+ ,:WKLGAN 
c+ ,:WKLGGZ 
c+ ,:WKANPD 
c+ ,:WKANLE 
c+ ,:WKANRS 
c+ ,:WKANVP 
c+ ,:WKANAP 
c+ ,:WKANSU 
c+ from lgst 
c+ where clfirm=:DAFIRM 
c+ and clwknr=:DAWKNR 
c+ and cllanr=:DASTLA

Und nur wegen der SQE nun diesen SQL umzuschreiben finde ich nicht sehr amüsant.

[BenderD]

das ist wohl ein einfacher Fall für eine Fehlermeldung.

BTW: ich habe diesen Fall mal zum Anlass genommen mir die QAQQINI defaults genauer anzuschauen => da scheinen die Entwickler selber den Fähigkeiten der SQE unterschiedlich zu trauen.
FORCE_JOIN_ORDER default *NO favorisiert SQE
IGNORE_DERIVED_INDEX default *NO favorisiert CQE
... ließe sich fortsetzen

D*B

[Fuerchau]

Fehlermeldung hin oder her, das Problem ist doch, dass die IBM dann immer relativ lange benötigt ein PTF zu erstellen /Tage bis Wochen).
Wenn Anwendungen aber massiv beeinträchtigt werden, ist die Wartezeit ziemlich heftig.

Ich hege die Befürchtung, dass uns die CQE irgendwann verläßt und Betriebe dann stillstehen.

Wer kann es sich schon leisten, die gesamte Anwendung auf einem neuen Release (teilweise durch neue Hardware bedingt) mit tausenden von Funktionen und Programmen zu testen und dann ggf. auf den Einsatz zu verzichten.
Insbesonders dann, wenn die alte Hardware aus z.B. Leasing und Wartung ausläuft und man die neue nehmen muss.

[malzusrex]

Muss mich jetzt hier mal aus DAU outen.
Was ist er Unterschied zwischen SQE und CQE.

Gruß Ronald

[BenderD]

CQE := classic query engine (hatte vor V5R1 keinen Namen)
SQE := SQL query engine; wird seit V5R1 parallel zur alten entwickelt und mit jedem PTF und Release ein wenig mehr installiert.
Die beiden existieren parallel, weil die alte (CQE) die neuen Features nicht kann und die neue die alten (noch) nicht alle kann und je nach SQL Statement muss die Arbeit von der neuen, oder der alten gemacht werden, oder der Optimizer darf würfeln welche er nimmt.
Mit anderen Worten: seit V5R1 ist die Datenbank permanent im Beta Status und der arme User (aka DAU) darfs ausbaden.

Dieter Bender

Muss mich jetzt hier mal aus DAU outen.
Was ist er Unterschied zwischen SQE und CQE.

Gruß Ronald

[Fuerchau]

Wobei ich jetzt halt gemerkt habe:

IGNORE_DERIVED_INDEX = *YES zwingt zu 99,99% in die SQE.

IGNORE_DERIVED_INDEX = *NO (*DEFAULT) und LF mit Select/Omit zwingt zu 99,99% in die CQE.

Ansonsten gilt halt, bestimmte neue SQL-Funktionen sind in der CQE nicht mehr implementiert, so dass die SQE zwangsläufig verwendet werden muss.
Andersherum gilt auch, dass es in der SQE noch nicht implementierte Funktionen gibt, so dass eben doch die CQE genommen wird.


PS:
optimize for n rows:
Alle meine Versuche, die selben Antwortzeiten bzgl. des 1. Satzes wie mit STRSQL zu erreichen, sind kläglich gescheitert.
Egal ob ich ODBC oder embedded SQL verwende, den Weg, den STRSQL geht kann ich leider nicht nachvollziehen.

Ein SQL in STRSQL ist häufig sehr schnell, die Debug-Nachrichten werden untersucht, ggf. angewendet.
Spätestens im embedded SQL läuft da irgendwas anders, so dass diese Antwortzeiten einfach nicht erreichbar sind.

[BenderD]

- das war bisher einer der elegantesten Workarounds im Problemfall an der SQE vorbei zu kommen.
- STRSQL: hast du optimize for 1 rows direkt beim select angegeben?
ansonsten wird interaktiv meist CPU schonend gearbeitet (betrifft auch embedded SQL im 5250) und oft ist da auch noch caching im Spiel und interaktiv ist immer read only und blocken ist erlaubt. Wenn du dann immer noch Laufzeitdifferenzen hast, dann wären die Diagnostics des Optimizers bezüglich der Ausführung noch interessant, am Besten per DBMON ermittelt.

D*B

PS: wobei ich immer noch glaube, dass du am leuchten in den Augen noch arbeiten musst, wenn du "s la vache qui rit engine" vor dich hin murmelst während der Optimizer den optimalen Zugriffsweg zusammenbraut.

IGNORE_DERIVED_INDEX = *NO (*DEFAULT) und LF mit Select/Omit zwingt zu 99,99% in die CQE.

PS:
optimize for n rows:
Alle meine Versuche, die selben Antwortzeiten bzgl. des 1. Satzes wie mit STRSQL zu erreichen, sind kläglich gescheitert.
Egal ob ich ODBC oder embedded SQL verwende, den Weg, den STRSQL geht kann ich leider nicht nachvollziehen.

Ein SQL in STRSQL ist häufig sehr schnell, die Debug-Nachrichten werden untersucht, ggf. angewendet.
Spätestens im embedded SQL läuft da irgendwas anders, so dass diese Antwortzeiten einfach nicht erreichbar sind.

[B.Hauser]

optimize for n rows:
Alle meine Versuche, die selben Antwortzeiten bzgl. des 1. Satzes wie mit STRSQL zu erreichen, sind kläglich gescheitert.
Egal ob ich ODBC oder embedded SQL verwende, den Weg, den STRSQL geht kann ich leider nicht nachvollziehen.

Ein SQL in STRSQL ist häufig sehr schnell, die Debug-Nachrichten werden untersucht, ggf. angewendet.
Spätestens im embedded SQL läuft da irgendwas anders, so dass diese Antwortzeiten einfach nicht erreichbar sind.

Alle dynamischen SQL-Interfaces oder Abfragen (dazu gehören natürlich auch interaktives SQL oder embedded dynamic SQL) werden per Default mit dem Optimierungsziel *FIRSTIO ausgeführt. Es wird also so optimiert, dass die erste Seite so schnell wie möglich ausgegeben wird.

Statisches Embedded SQL wird dagegen mit dem Optimiuerungsziel *ALLIO ausgeführt. Es wird also so optimiert, dass die komplette Abfrage so schnell wie möglich ausgeführt wird.

Diese Unterscheidung ist vor allem dann wichtig, wenn es darum geht einen Index-Zugriff oder einen Table Scan zu verwenden. *FIRSTIO tendiert dazu doch lieber einen Index zu nehmen, während *ALLIO zum Table Scan tendiert.

Um beim intraktiven SQL mit dem gleichen Optimierungsziel wie beim Statischen embedded SQL zu arbeiten, sollte im interaktiven SQL am Ende des Select-Statements OPTIMIZE FOR *ALL ROWS angegeben werden.

Noch besser ist es, Du gibts im embedded SQL das Optimierungsziel immer am Ende eines SELECT-Statements an und übernimmst dann dieses Ziel, wenn Du die Abfrage interaktiv ausführst.


@Dieter

FORCE_JOIN_ORDER default *NO favorisiert SQE
IGNORE_DERIVED_INDEX default *NO favorisiert CQE

IGNORE_DERIVED_INDEX war deshalb auf *NO geblieben, um (voraussehbare) Probleme mit der SQE zu minimieren, bzw. um zu verhindern dass einem wichtige Zugriffswege unter den Füßen weggezogen wurden. Da die meisten alten Anwendungen logische Dateien mit SELECT/OMIT-Anweisungen haben, wurden relativ wenige Abfragen mit der SQE ausgeführt.

Nur wer eine einigermaßen saubere SQL-Datenbank mit Referentiellen Integritäten und sauberen SQL-Indices hat, hat das Vergnügen sich mit der SQE herumzuschlagen. (Deshalb auch mein Kommentar von gestern, dass in den meisten Fällen, in denen auf die SQE geschimpf wurde und wird, die CQE die Abfragen ausführt.)

Das böse Erwachen wird für viele mit Release 6.1 kommen. Mit Einführung der Derived Indices unter Release 6.1 hat IBM beschlossen den Default-Wert für IGNORE_DERIVED_INDEX in der QAQQINI in der Bibliothek QSYS von *YES auf *NO zu ändern. Wurden also die DDS beschriebenen logischen Dateien nicht durch Derived Indices ersetzt werden bei vielen Abfragen wichtige Zugriffwege nicht mehr gezogen.

Übrigens die SQE ist mit Release 6.1 abgeschlossen, d.h. was jetzt nicht von der SQE verarbeitet werden kann, wird es auch nie werden können.

@Baldur
Die CQE wird genauso verschwinden wie die /36-Umgebung und der RPGIII-Compiler ... also uns noch einige Zeit erhalten bleiben.

Birgitta

[BenderD]

@optimize Klausel:
soweit ich das sehe ist das kein SQL Standard! genauso wie irgendwelches rumbasteln an SQL Statements um die Macken von Query engines auszubügeln, für SQL gilt immer: entscheidend ist, was hinten rauskommt.

@sauber:
ich mache seit Jahren nichts anderes und genau daher kommen meine negativen Wertungen zur SQE. Weiterentwicklung der DB2/400 muss am SQL Ende stattfinden und nicht an der Altlastenfront, wenn IBM die Büchse weiter halten will.

@Perspektiven CQE:
ich glaube nicht, dass IBM in dieser Frage Herrn Fürchau, Frau Hauser oder mich konsultieren wird!!!

D*B

Noch besser ist es, Du gibts im embedded SQL das Optimierungsziel immer am Ende eines SELECT-Statements an und übernimmst dann dieses Ziel, wenn Du die Abfrage interaktiv ausführst.

Nur wer eine einigermaßen saubere SQL-Datenbank mit Referentiellen Integritäten und sauberen SQL-Indices hat, hat das Vergnügen sich mit der SQE herumzuschlagen. (Deshalb auch mein Kommentar von gestern, dass in den meisten Fällen, in denen auf die SQE geschimpf wurde und wird, die CQE die Abfragen ausführt.)

Die CQE wird genauso verschwinden wie die /36-Umgebung und der RPGIII-Compiler ... also uns noch einige Zeit erhalten bleiben.

Birgitta

[Fuerchau]

Ich habe mich bei unserer BI-Anwendung damals für die von Dieter angegebenen Firebird-Datenbank entschieden.
Gerade was den Optimizer angeht, könnte IBM doch von dieser OpenSource-DB viel lernen.
Dieses gehuddel mit "Optimize for" kann ich deshalb nicht nachvollziehen.

Wieso verändert STRSQL meinen eingegebenen Select mit einer eigenständigen Optimize-Klausel ?
Das gehört verboten!
Wie soll man denn da vernünftige Entscheidungen bezgl. des SQL's treffen ?
Ich baue viele SQL's erst mal per STRSQL bevor ich mich der Mühsal des Programmtestens unterziehe.
Den OpsNav verwende ich da nicht, da mir das einfach zu langsam geht und tatsächlich nervt.

Der Vorteil von embedded SQL's insbesonders dem Einbinden von Servicemodulen (auch in OPM-Konzepten durchaus vorhanden), die sowohl in Batch als auch Dialog aufgerufen werden ist somit dahin.

Ich muss also in den Programmen abfragen, ob es ein Dialog- oder Batchprogramm ist um den entsprechenden statischen SQL aufzurufen ?
Der Jobstatus alleine reicht da nun mal nicht mehr aus, da es auch Batch-Services gibt, die eigentlich auf Dialog ausgerichtet sind (Web-Services).

Und was passiert, wenn man mal per RDB auf eine AS/400 durchgreift, die den Optimize for noch nicht kennt ?

Also alles nicht so schön, wie IBM immer beschreibt.
Da kann ich nun langsam die Anwender verstehen, dass sie zu SQL-Server oder Oracle wechseln, wenn es um SQL-Anwendungen geht (nicht nur wegen der bunten Oberflächen).

PS:
Und was die Compiler angeht, so ist ja das Lizenzmodell ab V6 erschreckend, dass man nun 2 Compilergruppen hat:
1. preiswerte ILE-Compiler
2. teure OPM-Compiler mit Tendenz zum Verschwinden
Bei tausenden OPM-Anwendungen, die sicherlich auch noch nach V6 gewartet und auch als OPM erweitert werden müssen, ist das kein gutes Zeichen.

[Pikachu]

Ansonsten gilt halt, bestimmte neue SQL-Funktionen sind in der CQE nicht mehr implementiert, so dass die SQE zwangsläufig verwendet werden muss.
Andersherum gilt auch, dass es in der SQE noch nicht implementierte Funktionen gibt, so dass eben doch die CQE genommen wird.

Kann es SQL-Anweisungen geben, die sowohl neue in der CQE nicht mehr implementierte als auch alte in der SQE noch nicht implementierte SQL-Funktionen beinhalten, und wie werden diese dann ausgeführt?