View Full Version : SQL-UDTF mit dynamischem SQL
... ohne continue handler düst das Teil direkt beim ersten negativen SQLCODE ab, ohne dass Du noch die Chance hast etwas zu machen.
Die Beispiele für UDTFs sind weiter hinten und Du musst das kombinieren. Ich habe sowas zwar noch nicht gebraucht, aber ich würde einfach mal den Cursor nach dem open mit return zurückgeben.
D*B
Mein letzter Versuch sieht jetzt so aus:
create or replace function SCR/BVS9AD_suchUDTF2(anw nvarchar(10000))
returns table (ad_rec_id decimal(15))
language sql
reads sql data
begin
prepare cmd from anw;
return anw;
end;
Die Funktion soll folgendermaßen aufgerufen werden:
select * from table(BVS9AD_suchUDTF2('select distinct sm_ad_rec from bvsadsuc')) as liste;
Leider hakt es schon beim Erstellen der Funktion wegen:
SQ20120 30 1 SQL-Tabellenfunktion muss ein Tabellenergebnis zurückgeben.
... da fehlt der declare cursor noch, für selbigen machst du dann den prepare und open und gibst den cursor mit return zurück
dschroeder
01-12-17, 11:14
Was heißt "den Cursor zurückgeben"? Also kein Fetch?
Etwa so:
create or replace function SCR/BVS9AD_suchUDTF2(anw nvarchar(10000))
returns table (ad_rec_id decimal(15))
language sql
reads sql data
begin
prepare cmd from anw;
declare c1 cursor for anw;
open c1;
return c1;
close c1;
end;
In der Zeile "declare c1 cursor for anw;" bekomme ich folgenden Fehler:
SQL0104 30 18 Position 9 Token C1 ungültig. Gültige Token: GLOBAL.
Irgendwie mag er das mit dem declare c1 nicht. Außerdem würde der close c1 ja nie ausgeführt, denke ich.
Das funktioniert so nicht! So wie Du das vorhast kannst Du RESULT SETS ausgeben, jedoch keine UDTF. Bei der Ausgabe von RESULT SETS bleiben die Cursor geöffnet. In Folge-Aufrufen musst Du dafür sorgen, dass der CURSOR, sofern er nicht geschlossen ist, vor dem erneuten Öffenen zunächst geschlossen wird.
Für UDTFs kannst Du entweder eine temporäre Datei befüllen und diese dann im SELECT des RETURN-Statements ausgeben oder Du verwendest eine pipielined UDTF.
... etwa so:
Begin
Declare SQLCODE as Integer Default 0;
Declare YOURVAR as ....;
Declare YOURSELECT as VarChar(1024) Default '';
... sonstige Variablen
Declare C1 Cursor for DynSQL;
Set YOURSELECT = 'Select .... ';
Prepare DYNSQL from YOURSELECT;
Open C1;
YOURLOOP: LOOP Fetch Next From C1 into YourVar;
IF SQLCODE = 100 Then Leave YourLoop;
End IF;
.....
PIPE (YourVar);
End Loop;
Close C1;
Return;
End;
Übrigens der Prepare darf nicht vor dem DECLARE CURSOR stehen, da alle DECLARE CURSOR Statements in der Declare-Section nach der Variablen Definition und vor den Handlern angegeben werden müssen.
Der PREPARE wie auch der OPEN, FETCH und CLOSE gehört in den Verarbeitungsteil der Prozedur.
dschroeder
05-12-17, 10:21
Hallo Birgitta.
Herzlichen Dank. Da ist viel neues für mich dabei. Mal sehen, wie weit ich damit komme.
LG,
Dieter
Kommt mal wieder auf das Release (TR) an. Laut Handbuch erst ab V7R3.
Aber noch mal zur Ursprungsfrage:
"Da ich in meinen SQL-Abfrage aber ein OR benötige, möchte ich problematische where-Teile in eine UDTF auslagern. Die kann ich dann ja wieder mit OR verknüpfen. "
Die UDTF muss doch wiederum mit Join verknüpft werden um das Ergebnis wiederum per "or" abzufragen.
Was ändert dies dann an deinem Performance-Problem?
Nun betrachte mal deine Abfrage genau.
I.d.R. wird eine Prüfung gar nicht erst gemacht, wenn sie für das Gesamtergebnis nicht relevant ist.
Je nach Datenbank kann es mal zu umgedrehter Betrachtung kommen oder es werden erst mal alle Teilergebnisse ermittelt und dann der Logische Ausdruck.
Es kann also durchaus performanter werden wenn man die "Or"-Bedingungen an den Anfang stellt.
where datum < %date or contains(name, 'müller' AND 'hans') = 1 ;
Nach korrekter Optimierung sollte Contains nicht mehr ausgeführt werden müssen, wenn Datum bereits erfüllt ist.
Ansonsten:
Statt UDTF könnte ebenso ein ", lateral (select ...) " vielleicht zum selben Ergebns kommen oder ebenso eine "derived" Table:
join (select ...) x on ...
dschroeder
05-12-17, 10:53
Hallo Baldur,
das OR ist in SQL natürlich nicht grundsätzlich ein Problem. Nur ein OR bei Omnifind ist ein Problem. Es handelt sich dabei meiner Meinung nach um einen Bug. Aber so wie es aussieht, will (oder kann) IBM das nicht lösen, sondern sie verweisen auf das Handbuch, wo steht "an OR does not perform well".
Omnifind ist ja eine spezielle Textindizierung, die nicht direkt in der SQL-Datenbank-Engine läuft. IBM hat das über die contains-Funktion in die SQL-Abfragesprache "reingetrickst", denke ich. Deshalb kann man mit herkömmlichen SQL-Logiken da möglicherweise nicht rangehen. Aus meiner Sicht hat Omnifind immer dann ein Problem, wenn Ergebnismengen zwischengespeichert werden müssen. Das ist nicht sauber implementiert. Man kann das per Trick sogar nachweisen:
select * from myTable where contains(name, 'müller')=1;
=> Das performt gut
Jetzt das gleiche als Common Table expression:
with daten as (
select * from myTable where contains(name, 'müller')=1
)
select * from daten;
=> Das performt unterirdisch (kann je nach Datenmenge auch Stunden dauern).
Wenn ich eine eigene UDTF erstelle, erzwinge ich ja das Zwischenspeichern innerhalb der SQL-Engine. Damit klappt es performancemäßig dann einigermaßen.
Kommt mal wieder auf das Release (TR) an. Laut Handbuch erst ab V7R3.
Wenn Du damit auf die PIPE ansprichst:
Pipelined UDTFs wurden bereits 2014 mit Technology Refresh in Release 7.2 eingeführt und via PTF für 7.1 bereitgestellt.
... und im Handbuch ist die PIPE übrigens unter der Überschrift: "In addition, the following enhancements are new since 7.2" aufgeführt.
Birgitta
dschroeder
05-12-17, 11:45
Nochmal zu deinem Vorschlag mit der anderen OR-Reihenfolge. Ich habe es gerade konkret ausgeführt:
select * from bvsadsuc where sm_ad_rec = 1000005410629; => Result in 0,005 Sekunden
select * from bvsadsuc where contains(sm_vna, 'müller AND uwe') = 1; => Result in 0,152 Sekunden
select * from bvsadsuc where sm_ad_rec = 1000005410629 or contains(sm_vna, 'müller AND uwe') = 1; => kein Result in absehbarer Zeit
Das ist ein Bug aus meiner Sicht.
... or ist doch ein union all über die Ergebnismengen beider Bedingungen