SQL-UDTF mit dynamischem SQL

[dschroeder]

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.

[B.Hauser]

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]

Hallo Birgitta.
Herzlichen Dank. Da ist viel neues für mich dabei. Mal sehen, wie weit ich damit komme.

LG,
Dieter

[Fuerchau]

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]

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.

[B.Hauser]

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]

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.

[BenderD]

... or ist doch ein union all über die Ergebnismengen beider Bedingungen

[dschroeder]

... or ist doch ein union all über die Ergebnismengen beider Bedingungen

Genau. Deshalb sieht mein Workaround ja zur Zeit so aus, dass ich mehrere selects mit union verbinde, um das or zu bekommen. Ich brauche aber zusätzlich auch noch AND-Verknüpfungen. Die bekomme ich, wenn ich mehrere Unions mache und diese mit join verbinde. Dazu benötige ich aber wiederum Common Table Expressions. Die sind aber wieder langsam (siehe meine Ausführungen weiter oben). Um das zu umgeben, benötige ich meine eigene UDTF.
Und damit läuft es jetzt.
Ich hätte mir es aber einfacher gewünscht!