SQL UDF erstellen - Best practices

[dschroeder]

Hallo zusammen,

ich möchte verstärkt SQL Programmierung betreiben und dabei SQL Funktionen programmieren

Dafür brauche ich mal ein paar Tipps von euch:

Insbesondere das Debugging von SQL Funktionen macht mir Schwierigkeiten.

Wenn ich das richtig sehe, wird für eine SQL UDF automatisch ein C-Serviceprogramm erzeugt. Wenn man die SQL-Funktion debuggen will, muss man im Systemdebugger von ACS doch dieses C-Programm angeben, oder?

Bei einer kleinen Testfunktion klappt das und ich bekomme im Debugger den SQL-Code angezeigt und kann ihn im Einzelschritt durchgehen. Bei einer größeren Funktion dagegen wird der C-Code angezeigt.

Es ist auch etwas mühsam, das C-Serviceprogramm zu identifizieren, da der Programmname vom Betriebssystem generiert wird.

Deshalb hier mal ein paar Fragen:

1. Kann man in einer SQL-Funktion eigentlich festlegen, wie der Name des zugehörigen Programms ist?
2. Die SQL-Funktion kann ja einen längeren Namen als 10 Zeichen haben. Es würde sich vielleicht anbieten, den Namen des C-Programm dann so zu nennen, wie der Sourcemember der SQL-Funktion heißt, damit man wieder 10 Zeichen hat. Das würde dann aber bedeuten, dass man nicht 2 SQL-Funktionen in einem Source haben sollte?
3. Gibt es bessere Möglichkeiten des Debuggings? Z.B. per VSCode oder so?
4. Wie handhabt ihr das?

[Fuerchau]

1: "specific name" gibt den 10-stelligen Namen an.
2: Das passiert leider nicht automatisch, da hätte ja einer mitgedacht. Man kann aber auch IFS-Dateien nehmen bzw. PC-Dateien, und dann?
Der Wartbarkeit wegen: 1 Funktion/Prozedur/Type o.ä. => 1 Datei, denn damit generiert man nur das Programm, dass sich ändert. Du kannst natürlich auch alles in eine Quelle packen. Ich habe da dann nur Probleme immer mit dem Semikolon;-).
Außerdem mache ich das ja auch per Datei/Index/Constraint/Trigger.
3: der Debugger ist schon IBM i-spezifisch, wobei allerdings in meinen Augen der STRDBG der effektivste ist. Auf Grund von SQL-Multthreading funktioniert dies nur sauber per STRSRVJOB von einem Terminal aus. Daher teste ich Funktionen immer per STRSQL und nicht per ODBC, da ich da immer erst den korrekten Job ermitteln muss. Es kann da aber auch ab und an dann ein SQL-Timeout passieren.
Per RDi o.ä. soll es ja auch gehen, aber das ist doch eher fehleranfällig, da ja 2 Systeme beteiligt sind.
Über "set options DBGVIEW = *SOURCE" ist das Debuggen auch einfacher.
4: s.o.

Und da eben auch nicht alles per SQL geht, schreibe ich auch gerne SQL-Procedure/Function mit external RPG im SQL-Stil (Parameter + Nullanzeiger + SQL-Variablen).
Und da manchmal SQL nicht direkt gewünscht ist, auch schon mal Service-Wrapper in ILERPG, die dann trotzdem die SQL-Function aufrufen.

[dschroeder]

3: der Debugger ist schon IBM i-spezifisch, wobei allerdings in meinen Augen der STRDBG der effektivste ist. Auf Grund von SQL-Multthreading funktioniert dies nur sauber per STRSRVJOB von einem Terminal aus. Daher teste ich Funktionen immer per STRSQL und nicht per ODBC, da ich da immer erst den korrekten Job ermitteln muss. Es kann da aber auch ab und an dann ein SQL-Timeout passieren.
Per RDi o.ä. soll es ja auch gehen, aber das ist doch eher fehleranfällig, da ja 2 Systeme beteiligt sind.

Zum Debuggen habe ich doch nochmal eine Frage. Der grafische Debugger aus ACS ist schon recht unhandlich. Deshalb habe ich gedacht, ich befolge deinen Rat und debugge das mit STRDBG.

Ich habe also den Job identifiziert, der das SQL ausführt und habe den entsprechenden STRSRVJOB gestartet.

Dann muss ich ja mit STRDBG das zu debuggende Programm angeben, oder? Das kann ja eigentlich nur das generierte C-Serviceprogramm sein, denke ich. Debuggst du das? Ich würde natürlich gerne meinen SQL-Source step by step durchdebuggen.

Im RDi geht das Debuggen von SQL gar nicht, denke ich. Oder gibt es da doch etwas?

[Andreas_Prouza]

Beim CREATE Statement musst du die debug-View mitgeben:

CREATE OR replace PROCEDURE prouzalib.testproc1 (IN p1 varchar(10))
LANGUAGE SQL
SET OPTION dbgview=*SOURCE
BEGIN
...

Du solltest im STRDBG die Debug-Sicht ändern können.
Im RDi und mitlerweile im vscode kannst du das nach dem gleichen Prinzip debuggen.
Im RDi musst du "Debug für Job" (heißt das glaub ich) auswählen.
Hinterlegt Job + PGM und startest den Debug.
Dann im Greenscreen ins STRSQL und die SQL Funktion oder Prozedur aufrufen.
Du kannst nebenbei auch statt STRSQL ein SQL Tool deiner Wahl benützen, du brauchst nur die Job-Infos der Session

[dschroeder]

Vielen Dank, Andreas.

Ich weiß nicht genau, was ich eben falsch gemacht habe. Aber jetzt wird ohne weiteres Zutun der SQL Code beim STRDBG angezeigt.

Das mit RDi probiere ich morgen mal aus.

[BenderD]

... best practis ist: nur Zugriffsfunktionen in die Datenbank, keine Business logic!!!
Im übrigen wäre ich mit dem SQL Compiler sehr vorsichtig!!! Viele features sind in der SQL reference nicht eindeutig dokumentiert und die beiden redbooks waren vor einiger Zeit grauen- fehlerhaft. Ob sich das gebessert hat, habe ich jetzt länger nicht verifiziert; ich vermeide Probleme, die ich nicht haben muss.

D*B

[Fuerchau]

Da Businesslogik, i.d.R., von Transaktionen lebt und Commit/Rollback sich in SQL-Features verbieten, auch wenn es trotzdem viele machen und sich wundern, ist das nahezu selbstverständlich.

[dschroeder]

Vielen Dank an euch beide. Das sind wertvolle Tipps.

Die Sache mit der Businesslogik ist etwas problematisch. Seit 30 Jahren handhaben wir das so, wie Dieter Bender es geschrieben hat: Keine Business Logik in SQL.

Aber seit einigen Jahren stellen wir verstärkt Webservices auf der IBM i zur Verfügung. Da bekommen wir bei rein RPG basierten Services schnell Größenprobleme bei der Rückgabe des Json. Deshalb haben wir uns entschlossen zukünftig mehr SQL based Services zu bauen und dann clobs zurückzugeben. SQL kann da ja 2GB verkraften.
Das führt dann dazu, dass wir vermehrt SQL Funktionen benötigen, um die Daten zu ermitteln. Meistens kapseln die UDFs nur bestehende RPG-Programme. Aber manchmal holen wir damit inzwischen auch selbständig Daten, weil es aus Performance-Gründen notwendig ist.

[Fuerchau]

Also auch in RPG kann ich ja mit CLOB's für JSON umgehen um diese bereitzustellen.
Allerdings sollte man sich hier tatsächlich überlegen, ob für Webservices hier mehr als 100KB, max. ggf. 2MB an Daten zurückgegeben werden müssen.
Da wird vom Client auch gerne ein Paging verwendet, dass ungeführ dem Umfang einer Browser-Seite entspricht (20-50 Zeilen), Page 1 - N.

Die Crux hier ist tatsächlich, dass für die Kommunikation zwischen RPG und Client eine Schicht eingezogen werden muss (Node.js, Java, Python, PHP, ...), um die Businesslogik nicht auch noch neu schreiben zu müssen. Und was bietet sich da einfacher an als SQL?
Ggf. kann man Services in RPG ja auch via "dcl-f .... HANDLER(XXX)" bauen. Im Handler kann der Web-Service laufen und per RPG-READ/WRITE die Kommunikation.
Da würde zwar je Client ggf. ein eigener Server-Job benötigt, aber das ist performanceseitig das geringste Problem.
Da SQL intern multithreaded arbeiten kann, ILERPG aber nicht, verlangsamen externe UDF's das System maßgeblich.

[Andreas_Prouza]

Ich habe da eher den pragmatischen Ansatz dazu.
Wenn man SQL hört, denken alle gleich an DB-Zugriffe und dementsprechend darf da keine Business-Logik hinein.
Ich trenne hier viel mehr zwischen Daten-Zugriffe & Verarbeitung.
Beides kann in RPG, in SQL, Python, Java & Co geschrieben werden können.

Damals was die Diskussion: Wie viel Char darf eine Spalte/Variable haben.
Dieser Gedanke kommt aus dem Zeitalter als die Systeme noch KB oder MB RAM hatten.
Die gleiche Diskussion sehe ich heute immer wieder mit DB Zugriffe.
Wenn ich es schaffe diese Zugriffe aus der Business-Logik raus zu nehmen, ist es egal was in welche Sprache dies gemacht wird

[Fuerchau]

"Wenn ich es schaffe diese Zugriffe aus der Business-Logik raus zu nehmen" meinte Dieter ja auch;-).

[dschroeder]

Anfangs haben wir bei Webservices auch angenommen, dass es reicht, wenige MB (mit RPG) zurückzugeben. Für Listen mit mehr Daten haben wir dann Paging eingebaut. Wir haben aber das Problem, dass wir einige Business-Objekte haben (z.B. Versicherungsverträge), die so komplex sind, dass das 2 oder 3 MB nicht reichen, um ein Objekt zu beschreiben. Wird müssten dann unsere Business-Objekte in kleinere logische Objekte aufspalten oder wird müssten unsere großen Objekte als Byteketten betrachten und dass dann im Paging herausgeben oder wir müssten die Speichergrenze mittels SQL umgehen. Und das ist im Moment der Ansatz, den wir verfolgen.