SQL-Optimizer ist einfach blöd

[Fuerchau]

Nein, das kann es nicht geben.
Neue Funktion müssen ja irgendwie per SQL verwendet werden.
Aber wie Birgitta ja sagt, man kann ggf. per CREATE INDEX irgendwas erstellen, was der Optimizer aber noch nicht nutzen kann.
Zwar kann man Indices auch per Native-IO (als Datei in ILERPG/COBOL) verwenden, da sie i.W. wie normale LF's funktionieren.
Was das dann allerdings mit SQL zu tun hat, weiß ich auch nicht.
Wahrscheinlich ist den Entwicklern nun DDS nicht mehr weitreichend genug, so dass man eher auf Freeform-Syntax des SQL zurückgreift.

Und irgendwan soll der Optimizer diese Indices ja auch nutzen können (wie oben erwähnte Firebird).

[B.Hauser]

@Baldur,
welches Optimierungsziel per Default verwendet wird, hängt nicht davon ab, ob ein SQL Statement interaktiv oder im Batch ausgeführt wird, sondern ob das SQL-Statement statisch oder dynamisch ist.

Statisch heißt es ist in einem (Service-)Programm hinterlegt und wird zur Laufzeit nur durch verschiedene Variablenwerte verändert. Dynamsich heißt, das SQL-Statement muss zur Laufzeit zunächst aus einem String in ein lauffähiges SQL-Statement konvertiert werden.

Wenn Dir das nicht gefällt kannst Du immer noch die Option OPTIMIZATION_GOAL in der QAQQINI entweder auf *ALLIO oder *FIRSTIO setzen, dann werden alle Abfragen unabhängig davon ob statisch oder dynamisch per Default mit dem angegebenen Optimierungsziel ausgeführt.

@Pickachu
Abfragen, die an die CQE geroutet werden müssen, jedoch neue Funktionen (z.B. OLAP-Ranking-Funktionen) beinhalten werden nicht ausgeführt. Fehlermeldung wird SQL0255 sein:
Funktion nicht für die Abfrage unterstützt.

[Fuerchau]

Warum MUSS etwas an die CQE geroutet werden, wenn nur die SQE dieses ausführen Kann ?
D.h., ich muss wissen, wer was kann, eine entsprechende QAQQINI wählen, damit ich kein SQL0255 bekomme ?

Also, wenn ich in einem Programm diverse SQL's habe, sollten alle vom selben Optimizer ausgeführt werden.
Sonst müsste ich mich ja disconnecten, die QAQQINI ändern und reconnecten.

Unter Commit/Control einfach unmöglich.

Hier sollte es mal generelle Richtlinien geben, dass ICH mich für eine BESTIMMTE Methode entscheiden kann und kein Automatismen, die keiner versteht, Anwendung finden.

SQL0255 gehört in die Tonne und dürfte nie auftreten, das ist meine Meinung.

[BenderD]

du musst wissen wer was kann und darfst dann gewisse Kombinationen nicht in einem Statement verwenden, sonst kriegst du eine SQL0255 um die Ohren, mit QAQQINI kannst du da nix ausrichten.

Ich würde da noch einen Schritt weiter gehen als du:
- SQL0255 ist ein Armutszeugnis für DB2/400
- SQL0101 (statement too long or too complex) ist der Offenbarungseid von DB2/400
- wieviele Query engines es gibt will mich nicht interessieren müssen, wenn ich mir Gedanken über Zugriffswege machen wollte, dann würde ich RLA programmieren!!!

D*B

Warum MUSS etwas an die CQE geroutet werden, wenn nur die SQE dieses ausführen Kann ?
D.h., ich muss wissen, wer was kann, eine entsprechende QAQQINI wählen, damit ich kein SQL0255 bekomme ?

Also, wenn ich in einem Programm diverse SQL's habe, sollten alle vom selben Optimizer ausgeführt werden.
Sonst müsste ich mich ja disconnecten, die QAQQINI ändern und reconnecten.

Unter Commit/Control einfach unmöglich.

Hier sollte es mal generelle Richtlinien geben, dass ICH mich für eine BESTIMMTE Methode entscheiden kann und kein Automatismen, die keiner versteht, Anwendung finden.

SQL0255 gehört in die Tonne und dürfte nie auftreten, das ist meine Meinung.

[Fuerchau]

Eigentlich kann es nur noch schlimmer kommen.
Beide Optimizer unterstützen ja keine derived Indices.
Der ein sagt, da sind welche vorhanden, also nimm den anderen (es sei denn QAQQINI definiert was anders), und der andere ignoriert diese sowieso.
Warum denn dann nicht gleich.

Ausserdem kann es dann passieren (wie mir eben), dass laufende Programme plötzlich kein Ergebnis liefern.

Desweiteren stelle ich mir vor, dass es da ein Tableset (PF+LF's) ohne derivied Indices gibt, alles läuft wie geschmiert.

Plötzlich überlegt sich jemand, da könnte man doch eben eine LF bzw. derived Index anlegen und laufende Programme werden plötzlich umgeroutet, liefern SQL0255 oder ganz anderes.

Inzwischen sind Anwendungen so komplex geworden, dass man sich ja gerade deswegen für SQL entscheidet.
Anwendungen weisen heute einen Mix aus OPM/ILE/RLA/SQL auf, was ja i.W. funktioiert.

Es darf aber einfach nicht passieren, dass sich bei existierenden SQL's mal so mal so entschieden wird.

Hier wäre ggf. ein Set Option hilfreich, um diesen Problemen aus dem Weg zu gehen. Schließlich enthält ein Programm ja meist doch viele SQL's, Actgrp's nicht zu vergessen, Commitzyklen über mehrere Programmaufrufebenen u.v.m.

Da darf man sich nicht von einer QAQQINI abhängig machen, die man auch noch je Job spezifizieren muss.
Ich müsste dann auch noch bei jedem SBMJOB, Dialogjob, ggf. vorgeschaltete CLP's einen CHGQRYA einbauen.

Ich habe auch Anwendungen mit Schnittstellen zu anderen Anwendungen, die per CALL aufgerufen werden.
Ändere ich nun für meine Anwendung die QAQQINI kann diese schon für das Fremdprogramm vollkommen falsch eingestellt sein.

So langsam sollte sich die IBM da was einfallen lassen.
- Wegfall der CQE (ich sehe da keinen Grund zur Erhaltung, spätestens ab V6R2)
- Wegfall der QAQQINI !!!

[BenderD]

die Indexe mit der where Klausel könnten sich wieder mal als zweitbeste Idee entpuppen, ich empfehle hier die Finger davon zu lassen.
set option ist auch kein Allheilmittel, immerhin können in derselben ACTGRP mehrere Module mit kollidierenden settings innerhalb einer Transaktion laufen.
ich vermeide generell globale settings (nicht nur hier!!!) und verwende Einstellungen der qaqqini nur zu Testzwecken.
den CHGQRYA könnte man eventuell noch in einer connect routine per stored procedure machen, die Idee reißt mich aber auch nicht vom Hocker.
Perspektivisch muss man von dem Mix runter und alles per SQL machen und sich konsequent an ANSI SQL halten, ich habe in den letzten Jahren genau das versucht und viele Empfehlungen in das OPNCLO versenkt und bin damit ganz gut gefahren.

D*B

Eigentlich kann es nur noch schlimmer kommen.
Beide Optimizer unterstützen ja keine derived Indices.
Der ein sagt, da sind welche vorhanden, also nimm den anderen (es sei denn QAQQINI definiert was anders), und der andere ignoriert diese sowieso.
Warum denn dann nicht gleich.

Ausserdem kann es dann passieren (wie mir eben), dass laufende Programme plötzlich kein Ergebnis liefern.

Desweiteren stelle ich mir vor, dass es da ein Tableset (PF+LF's) ohne derivied Indices gibt, alles läuft wie geschmiert.

Plötzlich überlegt sich jemand, da könnte man doch eben eine LF bzw. derived Index anlegen und laufende Programme werden plötzlich umgeroutet, liefern SQL0255 oder ganz anderes.

Inzwischen sind Anwendungen so komplex geworden, dass man sich ja gerade deswegen für SQL entscheidet.
Anwendungen weisen heute einen Mix aus OPM/ILE/RLA/SQL auf, was ja i.W. funktioiert.

Es darf aber einfach nicht passieren, dass sich bei existierenden SQL's mal so mal so entschieden wird.

Hier wäre ggf. ein Set Option hilfreich, um diesen Problemen aus dem Weg zu gehen. Schließlich enthält ein Programm ja meist doch viele SQL's, Actgrp's nicht zu vergessen, Commitzyklen über mehrere Programmaufrufebenen u.v.m.

Da darf man sich nicht von einer QAQQINI abhängig machen, die man auch noch je Job spezifizieren muss.
Ich müsste dann auch noch bei jedem SBMJOB, Dialogjob, ggf. vorgeschaltete CLP's einen CHGQRYA einbauen.

Ich habe auch Anwendungen mit Schnittstellen zu anderen Anwendungen, die per CALL aufgerufen werden.
Ändere ich nun für meine Anwendung die QAQQINI kann diese schon für das Fremdprogramm vollkommen falsch eingestellt sein.

So langsam sollte sich die IBM da was einfallen lassen.
- Wegfall der CQE (ich sehe da keinen Grund zur Erhaltung, spätestens ab V6R2)
- Wegfall der QAQQINI !!!

[B.Hauser]

@Baldur,

ich verstehe irgendwie die ganze Aufregung nicht.
1. Was zuvor mit der CQE ausgeführt werden konnte, kann auch weiterhin mit der CQE ausgeführt werden.

2. Was an jetzt an die SQE gegeben wird und abbricht, muss der IBM gemeldet werden.

3. Was jetzt an die SQE geroutet wird und keinen passenden Index findet, weil durch das Setzen der Option IGNORE_DERIVED_INDEX Zugriffswege in logischen Datein mit SELECT/OMIT Anweisungen nicht mehr zugänglich sind sollte folgendes machen:
- Die bestehenden logischen Dateien mit select/omit-Anweisungen löschen
- Für jeden in den logischen Dateien mit select/omit-Anweisung einen SQL Index anlegen
- Die logischen Dateien mit select/omit-Anweisungen wieder erstellen.
Die Anzahl der Zugriffswege wird nicht erhöht, da DDS beschriebene Dateien den Zugriffsweg mit SQL-Indices sharen können (umgekehrt geht das nicht, da DDS beschriebene logische Dateien per Default eine Page Size von 8K haben, währen SQL Indices eine Page Size von 64K haben. Der SQE Query-Optimizer kann jetzt die SQL Indices verwenden, währen native I/O weiterhin die DDS beschriebenen logischen Dateien mit select/omit-Anweisungen verwenden kann.

3. Ansonsten kann man einfach durch Hinzufügen einer Dummy-Anweisung z.B. Where Upper('x') = 'X' die Abfrage an die CQE zurückgeben. Funktionen wie Upper, Lower und Translate werden unter V5R4 noch nicht von der SQE unterstützt.
Ein Workaround, das mit dem man durchaus arbeiten kann bis das PTF ausgeliefert wird.

4. Wenn Optionen in der QAQQINI verändert werden, sollte man schon genau wissen, was man tut und bevor man das Ganze scharf mach auf Herz und Nieren prüfen. Die Optionen, die in der Abfrage-Options-Datei als Default-Werte angegeben sind, sind die von IBM gesetzten und die als beste Variante für das Release angesehenen Werte.
Wenn man schon mit geänderter QAQQINI arbeitet, sollte man diese einmalig bei Job-Start setzten und dann nie wieder ändern.

5. Derived Indices werden vom SQE-Optimizer genauso verwendet wie Zugriffswege in DDS beschriebenen logischen Dateien mit Select/Omit-Anweisungen vom CQE-Optimizer, d.h. nur der Zugriffweg nicht aber die Selektion bzw. Where-Anweisungen werden berücksichtigt.

Unter Release 6.1 sind Derived Index in erster Linie für native I/O gemacht, damit DDS beschriebene Dateien durch SQL abgelöst werden können. Unter Release 6.1 kann der Optimizer die Derived Indices (mit Where-Bedingungen) noch nicht voll ausnutzen, das heißt aber nicht, dass das nicht geplant ist.

6. STRDBG ist nicht mehr strategisches Produkt für die Query und Performance-Analyse, d.h. es werden auch nicht alle Meldungen im Joblog ausgegeben.
Strategisches Produkt ist der iNavigator der gerade unter V5R4 beträchtlich erweitert wurden. Ansonsten muss man halt mit den Database Monitoren arbeitet, was allerdings ohne iNavigator ziemlich schwierig ist und die Beschreibung dazu auch reichlich dürftig.

7. Die CQE wird nicht abgeschafft werden, da z.B. Query/400 auf dem gegenwärtigen Stand stabilisiert wird. Das Strategische Produkt ist jetzt DB2 WebQuery, was bis dato noch die wenigsten installiert haben und noch weniger ausprobiert haben.

Was glaubst Du wer wohl die hundertausende von Query/400s oder OPNQRYF, die immer noch draußen rumschwirren und von denen auch heute noch fleißig neue erstellt werden ausführt?

Birgitta

[BenderD]

ad3: wie soll man denn Hibernate (Object relational mapping Framework in Java) oder Microstrategy (Frontend für DataWarehouse) dazu bringen in bestimmte SQL Statements, die man nicht einmal findet, solche Workarounds einzubauen???

ad4: heißt dann auch: Hände weg von V6R1!!!

ad5: wenn derived Indexe so verarbeitet werden wie andere auch, dann bräuchte man keine!!!!

ad6 und 7: das sind doch alles platte Marketing Aussagen, denen ich nicht einmal bis morgen über den Weg traue. (vergleiche auch: DB2/400 und DB2 UDB, WebQuery, WebSphere und AS400, 64 Bit Java, ADM, APD, Office)

mit ganz lieben Grüßen

Dieter Bender

@Baldur,

ich verstehe irgendwie die ganze Aufregung nicht.

3. Ansonsten kann man einfach durch Hinzufügen einer Dummy-Anweisung z.B. Where Upper('x') = 'X' die Abfrage an die CQE zurückgeben. Funktionen wie Upper, Lower und Translate werden unter V5R4 noch nicht von der SQE unterstützt.
Ein Workaround, das mit dem man durchaus arbeiten kann bis das PTF ausgeliefert wird.

4. Wenn Optionen in der QAQQINI verändert werden, sollte man schon genau wissen, was man tut und bevor man das Ganze scharf mach auf Herz und Nieren prüfen. Die Optionen, die in der Abfrage-Options-Datei als Default-Werte angegeben sind, sind die von IBM gesetzten und die als beste Variante für das Release angesehenen Werte.
Wenn man schon mit geänderter QAQQINI arbeitet, sollte man diese einmalig bei Job-Start setzten und dann nie wieder ändern.

5. Derived Indices werden vom SQE-Optimizer genauso verwendet wie Zugriffswege in DDS beschriebenen logischen Dateien mit Select/Omit-Anweisungen vom CQE-Optimizer, d.h. nur der Zugriffweg nicht aber die Selektion bzw. Where-Anweisungen werden berücksichtigt.

Unter Release 6.1 sind Derived Index in erster Linie für native I/O gemacht, damit DDS beschriebene Dateien durch SQL abgelöst werden können. Unter Release 6.1 kann der Optimizer die Derived Indices (mit Where-Bedingungen) noch nicht voll ausnutzen, das heißt aber nicht, dass das nicht geplant ist.

6. STRDBG ist nicht mehr strategisches Produkt für die Query und Performance-Analyse, d.h. es werden auch nicht alle Meldungen im Joblog ausgegeben.
Strategisches Produkt ist der iNavigator der gerade unter V5R4 beträchtlich erweitert wurden. Ansonsten muss man halt mit den Database Monitoren arbeitet, was allerdings ohne iNavigator ziemlich schwierig ist und die Beschreibung dazu auch reichlich dürftig.

7. Die CQE wird nicht abgeschafft werden, da z.B. Query/400 auf dem gegenwärtigen Stand stabilisiert wird. Das Strategische Produkt ist jetzt DB2 WebQuery, was bis dato noch die wenigsten installiert haben und noch weniger ausprobiert haben.

Was glaubst Du wer wohl die hundertausende von Query/400s oder OPNQRYF, die immer noch draußen rumschwirren und von denen auch heute noch fleißig neue erstellt werden ausführt?

Birgitta

[Fuerchau]

Zu 3 muss ich Dieter zustimmen.
Man macht auch schon mal Queries mit Excel, hat im SQL-Server oder MS-Access verbundene Tabellen. Die kann ich nicht dazu bewegen einen Workaround einzubauen.

Zu 4 kann ich auch nur sagen, dass eine geänderte QAQQINI für ODBC-Zugriffe einfach erforderlich war (durch die plötzlichen Zeitschätzungen der CQE von > 40.000 Sekunden, die es mit V5R3 nicht gab).
Bei ODBC-Verbindungen (ebenso DRDA) kann ich nicht von jedem verlangen, dass er in seinen Verbindungsangaben eine für ihn passende QAQQINI auswählt, es muss also hier eine generellere Lösung geben.
Auch in der AS/400-Anwendung (s.o.) kann ich das nicht machen.

zu 5 kann ich eigentlich nur lachen (Entschuldigung).
Wie soll denn der Optimizer diesen Index verwenden, wenn dieser doch nur eine Teilmenge der ggf. gewünschten Daten enthält ?
Dazu müsste er so intelligent sein, dass er die Select/Omit's mit der Where-Klausel abgleicht.
Dies tun beide Optimizer ja nachweislich nicht, da man diese LF's in den Diagnosenachrichten grundsätzlich abgelehnt findet.
Insbesonders wenn man mit Hostvariablen arbeitet, deren Inhalt nun mal so variable ist, dass der Optimizer bei jedem Open die Optimierung erneut vornehmen müsste.

Zu 6:
Kannst du mir verraten, wie ich bei einer laufenden Anwendung oder ODBC-Zugriffen den OpsNav anwenden soll ?
Die automatischen SQL-Zugriffe kann ich ja nur über QAQQIN mit Diagnosenachrichten prüfen.
Allerdings habe ich auch schon festgestellt, dass SQL-Diagnosen z.T. erst nach DSPJOBLOG ... OUTPUT(*PRINT) erscheinen und nicht in der Dialoganzeige auftauchen (seltsam, sehr seltsam).
Ob das der Verbesserung dienlich ist ?

[Fuerchau]

Ach ja, auch den Unterschied zwischen statischem und dynamischem SQL und den Auswirkungen auf den Optimizer kann ich nicht nachvollziehen.
Irgendwas muss der STRSQL auf jeden Fall anders machen.

Dynamische SQL's sind nach dem Prepare genauso wieder statische SQL's wie embedded SQL's auch.
Wieso sollte es hier ein anderes Verfahren geben ?
ODBC-Zugriffe sind ja grundsätzlich dynamische SQL's die aber genause wie embedded SQL's ausgeführt werden.

Mache ich eine Abfrage über STRSQL wird sie schneller ausgeführt bis zum 1. Satz als die selbe Abfrage über z.B. MS-Access oder MS-Query.
Wo soll da der Unterschied herkommen ?

[BenderD]

ich bin nicht der große ODBC Experte und will keineswegs das Problem der idiotischen Time esttimates wegdiskutieren (die ein Skandal sind), aber ich habe im Hinterkopf, dass man den Timeout auch in Treiber properties deaktivieren kann (siehe auch: http://www.itjungle.com/fhg/fhg071305-story03.html).

ergänzend zu den Time estimates: ich habe angeregt durch Birgitta mal auf einer V5R4 Maschine mit heftigem SQL Traffic reingeschaut was der Query Optimizer so für Indexe (auch EVIs) empfiehlt. Da gibt es auch eine Rubrik estimate ohne und eine estimate mit. In den Empfehlungen tauchen dann Indexe auf, die nach eigener Einschätzung des Optimizers nix bringen (ja, ich weiß, diesen Bug sollte man an IBM reporten), ich habe auch EVI Empfehlungen gefunden, von denen sich der Optimizer was verspricht, wenn auch eher wenig, die aber in früheren Tests als extrem unverträglich mit Schreiboperationen erkannt wurden und deren Aufbau extreme Langläufer zur Folge hat.

D*B

Zu 4 kann ich auch nur sagen, dass eine geänderte QAQQINI für ODBC-Zugriffe einfach erforderlich war (durch die plötzlichen Zeitschätzungen der CQE von > 40.000 Sekunden, die es mit V5R3 nicht gab).

[Fuerchau]

Hallo Dieter, der Link klappt leider nicht.

Klar kann man die Timeouts éinstellen, aus historischen Gründen ist der bei ODBC aber vom Typ SHORT also max. 32.767. Der Optimizer kommt aber leider auf 40.000!!!
Man kann per Programm auch -1 für Nomax einstellen, aber nicht z.B. in MS-Access oder MS-Query.
Bei SQL-Server weiß ich das auch nicht.
Allerdings ist -1 bei ODBC ggf. tödlich, wenn es denn dann aus irgendwelchen Gründen mal tatsächlich so lange dauert.
Dann lassen sich solche Verbindungen fast nicht mehr abbrechen (ausser ggf. Taskmanager). Im Zweifel müsste sogar der SQL-Server runtergefahren werden.

Schön wäre es, wenn man die SQL0666 einfach unterdrücken könnte.