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.

[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.

[BenderD]

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.

... das hört sich für mich alles nach Gebastel an. Einen Versicherungsvertrag in ein SQL Statement zu packen oder per SQL Programm zusammen zu klappern halte ich für ein anspruchsvolles Unterfangen. Wenn ich mir das noch in einer Architektur von Services vorstelle, wird mir flau im Magen dabei.
Das mit dem Debug ist doch nur die halbe Wahrheit, wie will ich denn da Fehlersituationen im richtigen Leben beikommen? m. E. geht da nichts an einem ausgeklügelten logging vorbei, das ich für Analysezwecke feinkörnig steuern kann (log4j oder gleichwertiges lässt grüßen). SQL ist da als Programmiersprache völlig ungeeignet.

D*B

[Fuerchau]

Mit dem Einsatz von BLOB/CLOB/DBCLOB kann ein Dokument 2GB groß werden und auch von RPG mit SQL-Substr bearbeitet werden. Sicherlich kein einfaches Unterfangen aber konzeptionell machbar.
Auch die SQL-Treiber unterstützen LOB's um sie mit anderen Sprachen dann abgreifen zu können.
Ich lese via SQL Dateien aus dem IFS auch als CLOB / BLOB ohne Probleme.

[B.Hauser]

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?

@1/2: Sowohl beim CREATE PROCEDURE als auch beim CREATE FUNCTION kann man den (Service-)Programm-Namen (mit Option PROGRAM_NAME) angeben. Den (Service-)Programm-Namen kann man (selber) festlegen und ist auf 10 Stellen beschränkt.
In der Bedienerführung im ACS kann diese Option nicht ausgewählt werden. Wäre eine "IBM idea", die m.W. noch nicht vorgeschlagen wurde.
Der Programm-Name kann unabhängig von dem SPECIFIC NAME festgelegt werden. Der SPECIFIC Name dient in erster Linie dazu, die aufgerufene Funktion/Prozedur (bei Überladungen) eindeutig zu lokalisieren.
Der Source Code ist beim CREATE OR REPLACE Statement hinterlegt, d.h. jedes CREATE Statement generiert ein eigenes Objekt. Wenn man mehrere CREATE Statements in der gleichen Quelle hat, werden eben mehrere Objekte/Programme/Service-Programme mit einer exportierten Funktion generiert.

@3: Debug: Wenn in der Stored Procedure bzw. UDF die OPTION DBGVIEW = *SOURCE angegeben ist, kann man mit dem ACS-Debugger den SQL Code debuggen. Ohne diese Option lediglich den C-Code.
Der ACS-Debugger lässt zwar zu wünschen übrig, ist aber aktuell die einzige Möglichkeit (mit Bordmitteln) SQL-Source-Code zu debuggen.

@4: Ich arbeite mit dem ACS-Debugger (und SET OPTION DBGVIEW = *SOURCE)