SQL Package

[lorenzen]

Wenn im ODBC-Treiber (unter "Pakete" und "Einstellung pro Anwendung anpassen") ein Name vergeben wird, erzeugt das System einen Anhang wie ..VBA (Visual Basic?) oder VJA und andere. Nun hätte ich erwartet im Package die Accesspläne des Optimizers vorzufinden (PRTSQLINF). Dem ist aber leider nicht so.
Es geht im Ganzen um die Beseitigung von z. T. großen Performanceproblemen im Client-Serverbetrieb. Die Optimierung der SQL's hat dabei bereits alle Stufen der SQL-Leistungsanalysen durchlaufen (Visual Explain, Statistikmanager, Indexerzeugung etc).
IBM hat nun Packages empfohlen, aber ich kann nicht erkennen, dass das Wirkung zeigt.
Wie muss ich mit den Packages umgehen? Das SQL Referenzhandbuch hat mich leider nicht weitergebracht.
Der Client ist mit ML4 programmiert. Treiber sind Easycom und ODBC von IBM.
Version: 5.2

[Fuerchau]

Das Problem sind nicht die Packages. Ich habe mir zwar angewöhnt, die Packages in die QTEMP zu legen damit es keine Probleme beim Zugriff von mehreren Usern gibt, Performance-Unterschiede habe ich aber nicht bemerkt.

Wichtig für SQL ist das Bilden der SQL's mit Parametermarkern, d.h., die SQL's dürfen nicht immer neu aufgebaut werden wenn sich der Wert einer Variablen ändert.

Z.B.:

select f1, f2, f3, ...
from myfile
where k1=? and k2=? ...

anstelle jedes mal die Abfrage mit dem Wert für "K1, ..." neu aufzubereiten.

Nur dann kann SQL die Datenpfade geöffnet halten (ODP).

Z.B.:
insert into myfile fields(f1, f2, f3, ...)
values (?, ?, ?, ...)

Auch hier wird der ODP dann für den nächsten Insert aufgelassen.


Vielleicht sollte man auch mal prüfen, ob die ODBC-Verbindung während einer Sitzung geöffnet bleibt, so dass ODP's auch bestehen bleiben.


Anmerkung:

SQLPKG's über ODBC werden beim Aufbauen der Verbindung erstellt. Jeder SQL-Befehl wird per "Prepare" an die AS/400 gesendet. Wenn sich der Befehl ändert, wird auch ein neuer Prepare erforderlich !!!

Frage:

Sind die SQL's auch wirklich mit Parametermarkern, so dass SQLPKG's überhaupt Wirkung haben können ?

[lorenzen]

Danke für die schnelle Reaktion.
Für Embedded SQL z. B. in RPG verstehe ich das. So machen wir es auch.
Aber im Client haben wir keine Hostvariablen und das Select wird schlicht über den Treiber auf die Datenbank geschickt. Das kann dann z. B. so aussehen:
Select
ALWERTA
,VKKOVART
,ADNAME1
,ADNAME2
,STName
,ADHSNV
,ADHSZV
,ADHSNB
,ADHSZB
,ADGBTL
,STPLZ
,STORT
,STBEZ
,ADPOFA
,STSTNR
,admand, adadnr,alwerta
from ( TCD830MD.ADRSTMPF JOIN TCD830MD.VKERGPF on ALSTAT = ' '
and ADMAND = ALMAND and ADADNR = ALKDNR )
LEFT OUTER JOIN TCDAD.STRSTMPF on ADSTNR = STSTNR
Join TCD830MD.VKKOPF on vkkostat = ' ' and vkkomand = admand and vkkovknr = alwerta
Where adstat = ' ' and admand = 30
and ALart = 'VKKOPF' and ALKEY = 'KUNDENNUMMER'
and ADANRE <> 1 and ADANRE <> 4
and ADName2 like 'Meier%'
and STOrt like 'Laatz%'
order by admand, adname2

Der ODBC-Job bleibt übrigens offen.

Gruß

Sven Lorenzen

[Fuerchau]

Das ist ja genau das was ich meine.
Die SQL's können nur wiederverwendet werden, wenn sie IDENTISCH sind. Somit gilt jeder Befehl als neuer SQL und ODP's gibt es nicht.

Wenn ich mir den Befehl so betrachte, wird ja genau eine bestimmte Sicht der Daten verlangt, also ähnlich einem CREATE VIEW die ich dann mittels SELECT * FROM MYVIEW abfrage. Es wird also immer erst eine neue View (eben temporär) verlangt.

Daher erklärt sich auch die Performance, da der SQL generell neu analysiert werden muss, ein SQLPKG also nichts bringt. (Wenn man SQLPKG nicht definiert, wird automatisch eins in QGPL generiert.)

Aber auch auf der Clientseite muss ich mit Parametern arbeiten können !!!!

Wenn ich mit ADO arbeite, so erstelle ich ein Connection-Object an das ich dann Command-Objekte hänge.
Den SQL definiere ich an den richtigen Stellen mit "?" und kann dann jederzeit mit "OpenRecodset" und den Parametern im Array (oder in einer Schleife gefüllt) das Command ausführen.

[lorenzen]

Jawohl, das ist das Problem. Wir haben am Client nämlich keine ADO-Komponente oder ähnliches, mit der wir Hostvariablen (:?) verwenden können. Wir legen jetzt die Select's in Stored Procedures mit SPL ab. Das hatten wir ohnehin vor, aber leider hat unser Treiber damit noch ein Cursorhandlingproblem, welches aber sicher lösbar ist.

Wird denn für SP's auch automatisch ein Package angelegt, oder muss das manuell gemacht werden?

ODP's: die bleiben nach wiederholten Aufrufen schon offen und dann wirds auch erheblich schneller, aber die Erstaufrufe bleiben halt langsam.

Package in QTEMP: Bei IBM stand zu lesen, dass es damit auch Probleme geben kann:

http://www-912.ibm.com/a_dir/as4ptf....=0,SQL,Package

Gruß
Sven Lorenzen

[Fuerchau]

Aufruf von "Stored Procedures" sind ja auch SQL-Befehle, die dann im SQLPKG abgelegt werden. Diese sind aber absolut unkritisch.

Sind denn diese Prozeduren dann mit "?" oder wird für jede Abfrage ein Prozedur erstellt ? Die Performance dürfte sich dann nicht verändern !

Der ODP bleibt schon offen (ODBC-Konfig -> Verzögertes schliessen), kann aber ggf. nicht wiederverwendet werden.

Ab V5R2 hat sich der Optimizer geändert. Für alle Zugriffe wird ein Systemcache verwaltet, in dem gleichartige Zugriffe schneller wiedergefunden werden. Es wird auch kein temporärer Index mehr erstellt.
Aber: nach dem IPL ist der Cache wieder leer !!

Über die QAQQINI (siehe http://publib.boulder.ibm.com/iserie...mstqryoptf.htm "MESSAGES_DEBUG") können Nachrichten ins Joblog geschrieben werden.
Ab CA-Express V5 kann der ODBC-Treiber den Serverjob auch in DEBUG-Modus setzen, auch hier werden dann Nachrichten ins Joblog geschrieben.

Auffinden des ServerJobs: WRKOBJLCK MYUSER *USRPRF

Ich würde diese Nachrichten mal analysieren und dann ggf. zusätzliche Indexe (LF's) anlegen.

[BenderD]

Hallo,

ich muss mich doch mal bei euch einmischen.
Also, die packages haben mit den ODPs erstmal eher nix zu tun, sondern mit den Zugriffsplänen. Im ODBC und auch JDBC Bereich hat man es zunächst mal mit fully dynamic SQL zu tun, das heisst, das DBMS (Datenbank Management System) muss erst mal einen Zugriffsplan dynamisch ermitteln - und das kostet Zeit - bevor es die Abfrage ausführen kann.
Am anderen Ende hat man dann static SQL, wie es bei embedded SQL häufig vorkommt; hierbei liegt zur Compile Time bereits fest, welcher Zugriffsplan erforderlich ist und der wird in einem Package gespeichert (im lokalen Fall Teil des Programm Objektes, im verteilten Fall ein extra Package auf dem Server).
Dazwischen gibt es dann noch zwei Fälle, nämlich prepared Statements und dynamic package Support.
Prepared Statements erlauben es mit Hilfe von Parameter Markers einen Zugriffsplan Programm gesteuert wieder zu verwenden: sprich einmal preparen (= Ermittlung des Zugriffsplans) und mehrfach auszuführen.
Dynamic Package Support gibt es nur auf der AS400 und dort nur für ODBC und JDBC. Diese Packages versucht ds System dann als eine Art cache zu verwenden, in dem es sich Zugriffspläne merkt; dabei versucht das DBMS die Anforderungen zu generalisieren, sprich prepared Statements aus der Anfrage abzuleiten. Der Effekt des Ganzen hängt davon ab, welche Trefferquote solch ein cache hat und wie komplex die Ermittlung des Zugriffspfades ist.
Völlig unabhängig hiervon werden alle ODPs im Grundsatz offen gehalten, selbst wenn sie aus der Anwendung geschlossen werden (lazy close).
Im Problemfall beginne ich immer mit zwei Dingen:

- Untersuchung der Packages (PRTSQLINF)
- Untersuchung mit DataBase Monitor, was denn nun eigentlich solange dauert

Performance Optimierung ist keine Kunst, sondern Handwerk und erfordert eine solide Messtechnische Grundlage!!!

Zu den stored Procedures ist noch zu sagen: Diese haben als (aus Sicht der Datenbank) lokale Programme ein integriertes Package am Programm Objekt kleben und können sowohl statisches SQL, als auch dynamisches SQL mit Prepared Statements nutzen und sollten beim Erstaufruf (bezogen auf den Serverjob!!!) im Vorteil sein.

Zu den zeitlichen Grössenordnungen: die Ermittlung eines Zugriffspfades dauert normalerweise wenige Zehntelsekunden, das heisst: alles was über eine Sekunde liegt, hat damit nichts zu tun, sondern da fehlt in aller Regel ein Zugriffspfad, oder der Server ist überlastet und dreht Kreischen.

Dieter Bender

[lorenzen]

Also: das die Zugriffspläne im Package abgespeichert werden habe ich auch so verstanden und das ist auch so gewünscht.
Auch wenn diese Ermittlung vielleicht wenig Zeit in Anspruch nimmt, so kumuliert sich das dennoch, wenn man, wie bei uns, für eine Anzeige am Client mehrere Select's in einem Ereignis absetzen muss.

ODP's: Es entspricht auch unserer Beobachtung, das diese ausschließlich vom DBMS verwaltet werden und somit keine Möglichkeit besteht sie zu öffnen oder zu schließen. Hierbei wäre noch zu überlegen ob man Share = *YES bei den Tabellen zuläßt.

Performance Optimierung: Wir haben alle SQL's mehrfach über z. B. Visual Explain analysiert (z. Teil war dieses Programm leider nicht in der Lage alle SQL' s überhaupt zu verarbeiten). Wir haben SQL-Leistungsanalysen laufen lassen und die langsamen (> 1,5 sec) rauf- und runteranalysiert (where-Bedingung und order by synchronisiert, "von links nach rechts auflösen" usw.), dabei alle vom Optimizer geforderten Indices angelegt.
Außerdem haben wir nach bestimmten Reihenfolgen (Komplexität) die Indices angelegt.
Alle Tabellen haben einen Primary Key.
Für bestimmte Tabellen haben wir EVI's angelegt.
Die Parameter der QAQQINI haben wir alle durchgetestet.
Wir haben unser Netzwerk mehrfach von Messtechnikern analysieren lassen.
Wir haben von IBM unsere 830 (4 Prozessoren) prüfen lassen.
Wir haben je nach Betriebssystemversion und PTF-Stand unsere SQL's mal so oder so wieder geändert, weil z. B. scalare Subselects unterschiedliche Performance hatten!
Darüber hinaus kontrollieren wir regelmäßig die Statistikdaten.
Wir haben die aktuellsten PTF's geladen, nachdem es ganz üble SQL-Systemfehler gab.

Soviel zum Handwerk der Performance Optimierung.

Nicht unerwähnenswert ist auch, das z. Zt. zwei SQL-Engines parallel gefahren werden (C(lassic)QE un S(ql)CE). Da weiß ich nämlich auch nicht, welche Auswirkungen das nun hat. Da wir eine reine SQL-Datenbank haben, gehe ich im Moment davon aus, dass unsere Statements nur über die neue SQE laufen (bis auf die, die sie noch nicht kann).

Jedenfalls haben wir jetzt mit den Stored Procedures bereits getestet und schon eine deutliche Verbesserung festgestellt.
Wenn jetzt noch unser Treiber vollständig damit umgehen kann, dann werden wir diesen Weg konsequent weiter verfolgen.
Vielen Dank.

Gruß
Sven Lorenzen

[BenderD]

Hallo,

ich habe da selber eine zusätzliche Frage, da sich meine Erfahrungen eher auf klassisches SQL und JDBC beziehen und weniger auf ODBC: hat eigentlich mal jemand mit unterschiedlichen Treibern gearbeitet, gibt es da Unterschiede in der Leistungscharakteristik?

ansonsten bleibt nur wenig anzumerken:

share *yes beszieht sich auf explizit geöffnete Dateien und bringt bei SQL nix (bei RLA nur bei katastrophalem Anwendungsdesign), ODPs werden eh' gemeinsam genutzt (was nicht immer gut sein mus).

Bei den EVIs ist vielleicht für andere noch anzumerken, dass diese nur bei WHERE Klauseln ziehen können und eigentlich nur bei unbalancierten Daten Effekt versprechen.

Soweit man die SQL Statements in der Hand hat, kann man die Tätigkeit des Optimizers zuweilen mit einer (ansonsten eventuell nicht benötigten) ORDER BY Klausel beschleunigen, da diese die Bewertung von Zugriffsstrategien beschleunigen kann und die Resultate in eine gewünschte Richtung beeinflussen kann.

mfg

Dieter Bender

[lorenzen]

Wir verwenden einen nativen Treiber mit Namen Easycom von der Firma Aura Equipments aus Frankreich. Damit ist die Performance bei Nativ-Befehlen (Schreiben usw.) schneller als SQL. Dafür wäre auch Share *Yes zu überlegen.
Die Select's werden halt über das CLI auf den Server geschoben und sind im Vergleich zu ODBC von IBM etwa gleich schnell. Die Probleme damit liegen halt in den bereits erwähnten Bereichen Package und Parametermarken.
Probiert habe ich noch von DataDirect Technologies einen ODBC Treiber, der aber schon an der Namenskonvention der AS/400 gescheitert ist. (/ und . als Trennzeichen).
Allerdings waren die Test-SQL's die ich probiert habe auch recht schnell. DataDirect Technologies bietet auch für andere DB's Treiber an:
www.datadirect-technologies.com

Die allermeisten Select's haben ein order by. Wir haben auch z. T. "Dummyabfragen" (Feld > 0) in die Where-Bedingungen eingebaut um den Optimizer zu befriedigen.

Nochmals Danke für die Hilfe.

Gruß

Sven Lorenzen

[BenderD]

Hallo,

danke erst mal für die Treiber Infos, das könnte nochmal wichtig werden für eine Showcase Arie, da habe ich nämlich wenig Einfluss auf die SQL Statements.

Nach der Bemerkung mit dem CLI wird mir auch klar, warum das mit dem Package nichts gebracht hat. Ich denke in der Konstellation sind die stored Procedures eine Idee, wenn es denn funktioniert - da gibt es, meine ich, Einschränkungen für die nicht Java Welt.

mfg

Dieter Bender

[Asti]

Den SQL definiere ich an den richtigen Stellen mit "?" und kann dann jederzeit mit "OpenRecodset" und den Parametern im Array (oder in einer Schleife gefüllt) das Command ausführen.

Kann ich an einen Parameter ein Array hängen? Arbeite bei INSERT nur noch mit ado command und wüsste gerne, ob man das noch beschleunigen kann, indem man nicht jede Zeile einzeln "abschickt" sondern ein array an jeden Parameter anhängt und dann einen ganzen Block überträgt. Habe das mit ODBC BLOCK INSERT probiert, funktioniert auch, möchte das aber lieber über ado realisieren. Gibt es eine Art BLOCK INSERT für ado (VB6)?

Gruß Andreas