Transaktion in einer SQL UDF durchführen

[dschroeder]

Hallo zusammen,
ich habe mich bisher mit dem Thema commit Steuerung und Transaktionen nicht wirklich beschäftigt.

Jetzt habe ich aber einen Fall, wo eine Transaktion sehr hilfreich wäre.

In einer SQL UDF möchte ich einen update auf 2 Tabellen durchführen. Nur wenn beide updates fehlerfrei sind, soll das ganze "wirken".

Wie starte ich denn eine Transaktion und wie beende ich sie?

Ich habe die SQL Befehle dafür noch nicht benutzt.

LG, Dieter

[Fuerchau]

Innerhalb einer UDF ist es nicht möglich, eine Transaktion zu starten.
Dies muss das aufrufende Programm machen.
Via "exec sql set option commit = *chg;" teilst du deinem SQL-Programm mit, dass du Transaktionen machst.
Mit dem 1. Update/Insert/Delete wird dann eine Transaktion gestartet.
Dann kannst du mehrere Datenänderungen durchführen.
Wenn für das Programm alles OK ist, schließt ein "exec sql commit;" die Transaktion ab, hebt alle Speren auf und die Daten sind von anderen Jobs wieder änderbar.
Tritt während der Verarbeitung ein Businesslogik-Fehler auf, macht du einen "exec sql rollback;".
Führt dein Programm zu einem Absturz wird ggf. vom System beim Ende der ACTGRP oder bei Jobende eine Rollback durchgeführt.

Auf Grund der Sperre aller Änderungen ist eine Dialogeingabe durch User verboten. Denn wenn der gerade Pause macht bleiben die Daten gesperrt und andere Jobs fallen ggf. mit Satzsperren-Timeout auf die Nase.

Bei commit *none (Default) wird ein sog. Autocommit gemacht, d.h., jede Änderung wird sofort durchgeführt und sperrt nicht.

[dschroeder]

Vielen Dank für deine Antwort.

Geht das wirklich nur per RPG? Ich habe gar kein RPG-Programm. Ich habe eine SQL-Funktion, die einen Webservice-Aufruf bedient. Die Funktion hängt am IWS.

Die SQL Funktion soll in 2 Tabellen schreiben. Ich dachte, es sei kein Problem, die beiden Schreiboperationen in einer Transaktion zusammen zu fassen.

Aber wenn es so ist, muss ich damit leben.

[dschroeder]

Hallo Baldur,
ich habe gerade mal ChatGPT gefragt. Die KI ist der Meinung, dass es klappt:

Ja, es ist möglich, die Updates in einer Transaktion innerhalb der SQL UDF zusammenzufassen, um sicherzustellen, dass alle Updates erfolgreich sind oder alle Änderungen zurückgerollt werden, wenn ein Fehler auftritt.
Sie können die UDF wie folgt ändern, um die Transaktion hinzuzufügen:
CREATE FUNCTION update_two_tables (IN param1 VARCHAR(10), IN param2 VARCHAR(10))
RETURNS VARCHAR(10)
LANGUAGE SQL
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING ROLLBACK;

START TRANSACTION;

UPDATE table1 SET column1 = 'new value' WHERE column2 = param1;
UPDATE table2 SET column3 = 'new value' WHERE column4 = param2;

COMMIT;

RETURN 'success';
END;
In diesem Beispiel wird eine Transaktion mit "START TRANSACTION" gestartet und mit "COMMIT" abgeschlossen. Wenn während der Ausführung der UDF ein Fehler auftritt, wird die Transaktion mit "ROLLBACK" zurückgerollt.
Durch Hinzufügen der Transaktion stellen Sie sicher, dass beide Updates erfolgreich sind oder beide fehlschlagen, wenn ein Problem auftritt.


Hast du meine Frage vielleicht missverstanden? Es geht mir nicht um embedded SQL.

Vielleicht stimmt es natürlich auch nicht, was die KI sagt.

Ich habe den Code noch nicht ausprobiert.

[Fuerchau]

Genau, probier ihn doch aus.
Es ist eine konzeptionelle Frage, wie damit umgegangen wird.
Da der Client ja keine Abfrage macht, sondern dein Server-Handler, so muss dieser die Transaktion starten und Beenden.
Bei ODBC/JDBC macht man das dann auf dem Connection-Objekt.
MyConnection.BeginTransaction();
MyConnection.CommitTransaction();
MyConnection.RollbackTransaction();
Je nach Treiberimplementation liefert das BeginTransaction ein Transaction-Objekt.
Dieses muss man dann den jeweiligen command-Objekten vor der Ausführung zuweisen und kann dann auf dem Transaction-Objekt ein Commit() oder Rollback() durchführen.
Für die Verbindung muss man bei der DB2 for i den Wunsch für Transaktionen per "...commit=*chg;" in der Verbindungsfolge angeben.

Manche DB's erlauben tatsächlich Transaktionen, was jedoch immer wieder zu Problemen führt, wenn eine UDF, eher einer Procedure, plötzlich einen Commit oder Rollback macht mit dem mein Programm dann Probleme bekommt.

Vielleicht hättest du ChatGPT mal nach "DB2 for i" gefragt, denn den "STart xxx" findest du schon nicht.

Alternativ kannst du auch eine Externe UDF machen, also in RPG. Diese bekommt eine eigene ACTGRP und somit eine neue Verbindung und eigene Transaktionen.

[dschroeder]

Nochmals Danke für deine Nachricht. Ich hatte ChatGpt bereits gesagt, dass es um IBM i geht.
Aber ich habe nochmal nachgefragt. ChatGpt glaubt, es gäbe die Anweisung START TRANSACTION.

Ich muss das nächste Woche mal probieren!

[Fuerchau]

Wie rufst du die Funktion denn auf?
Poste doch einfach mal den Code.

Leider kann man ChatGPT nicht mitteilen, dass der Code falsch ist.

[Andreas_Prouza]

Hast du schon probiert beim Erstellen der SQL Funktion die SET OPTION zu setzen?

update_two_tables (IN param1 VARCHAR(10), IN param2 VARCHAR(10))RETURNS VARCHAR(10)
LANGUAGE SQL	
SET OPTION  COMMIT = *CHG
	BEGIN 
        -- SQL ....
	RETURN ...; 
END  ;

[Fuerchau]

Nun, auch das funktioniert nicht.
Der aktuelle Commit-Level wird bei der Aufnahme einer Verbindung zur Datenbank festgelegt.
In unserer Job-Welt ist das das 1. Programm, dass SQL-Statements enthält und den Commit setzt.
Der Default bei CRTSQLxxx ist *CHG und kann per Set Option überschrieben werden.
Statt auf ein Programm zu warten, kann man ebenso per CL STRCMTCTL starten und nach Commit/Rollback auch wieder per ENDCMTCTL stoppen.

Scope ist hier die Definition *JOB oder *ACTGRP, wobei der Default eben *ACTGRP ist.
Hier bestimmt also auch die aktuelle Umgebung was verwendet wird.
Bei OPM (CLP, RPG, usw.) ist das die "*Default (2)", "*Default (1)" gilt für das System.
Bei ILE (CLLE, ILERPG) ist das QILE.

Im CRTPGM (implizit bei CRTSQLRPG) kann eine ACTGRP explizit festgelegt werden. *NEW immer wieder neu, "NAME" explizit, *CALLER vom Aufrufer.
*NEW wird immer geschlossen, wenn der Initiator endet, benannt wird i.d.R. bei Jobende geschlossen. Per ENDACTGRP kann man diese zwar auch schließen, führt aber häufig in der ILERPG-Welt dann zu Folgefehler weil statische, interne, Pointer häufig nicht initialisiert werden.

Was macht ein Create or Replace MyFunction / MyProcedure?
Genau: Es wird ein C-Modul sowie ein Serviceprogramm erstellt, mit der ACTGRP(*CALLER).

Alle SQL-Routinen brauchen nun mal einen Aufrufer (Caller) für jede Funktion und somit gilt hier grundsätzlich der Commit-Level des Rufers, egal was du da als Option bei der Erstellung angibst.
Was ich noch nicht geprüft habe, ob der Schutzlevel ggf. erhöht werden kann, eine Verminderung auf z.B. *NONE ist nicht möglich.

Was erlaubt ist, ist die Angabe von "with nc" am Ende von Insert/Update/Delete um explizit die Transaktion zu unterlaufen, allerdings ohne Kontrolle, d.h., ein Rollback dreht das nicht zurück.
Manchmal ganz hilfreich nach dem Motto "Wer hat's gelöscht?".

Was ChatGPT nicht wusste ist, dass es im DB2 for i den "Set Transaction" gibt, allerdings mit einem gravierenden Hinweis:

-------------------------------------------
SET TRANSACTION restrictions: The SET TRANSACTION statement can only be executed when it is the
first SQL statement in a unit of work, unless:
• all previous statements executed in the unit of work are SET TRANSACTION statements or statements
that are executed under isolation level NC, or
• it is executed in a trigger.
In a trigger, SET TRANSACTION with READ ONLY is allowed only on a COMMIT boundary. The SET
TRANSACTION statement can be executed in a trigger at any time, but it is recommended that it be
executed as the first statement in the trigger. The SET TRANSACTION statement is useful within triggers
to set the isolation level for SQL statements in the trigger to the same level as the application which
caused the trigger to be activated
--------------------------------------------

Das kannst du ggf. bei dir ausschließen, denn dein Aufruf wird bestimmt nicht der 1. in der "Unit of Work" sein.

Des weiteren gilt bei Commit:
----------------------------------------------------
COMMIT is not allowed in a trigger if the trigger program and the triggering program run under the same
commitment definition. COMMIT is not allowed in a procedure if the procedure is called on a Distributed
Unit of Work connection to a remote application server or if the procedure is defined as ATOMIC. COMMIT
is not allowed in a function.
------------------------------------------------------
Dasselbe gilt i.Ü. auch für Rollback.

Wie oben bereits geschrieben kannst du eine externe Procedure definieren (z.B. ILERPG), die ein externes Programm/Service-Programm aufruft.
Diese erstellst du separat und hier kannst du eine eigene ACTGRP erstellen.
Das sollte sehr dediziert angewendet werden, da du hier sehr schnell auch auf Satzsperren im eigenen Job kommst. *NEW verbietet sich hier, denn damit geht die SQL-Performance drastisch in den Keller.

Also betrachte die Stelle, an der du den Aufruf machst und kommite dann dort die Arbeit.

Es gibt hier auch einen Thread z.T. "Best Practice" und Transaktionen gehören zur Business-Logik.

[BenderD]

... das ist weit weg von best practices. SQL functions sind dafür gedacht einzelne Feldwerte zu liefern, sprich in einem select statement berechnete Felder bereitzustellen. Es wäre fatal, wenn man da ein commit oder rollback machen dürfte.
Ich empfehle dringendst das Design Deiner Anwendung zu überdenken - Du bist dabei von einem Hundehaufen in den nächsten ...

D*B

[Fuerchau]

Ggf. liegts ja daran, dass der Begriff UDF (User Defined Function) gerne auch mit UDP (User Defined Procedure) zusammengefasst bzw. verwechselt wird.
Wenn ich mir vorstelle, dass bei einer UDF tatsächlich ein Commit passiert, dass daraufhin (fast) jeder Leser-Cursor sofort geschlossen wird und somit noch mitten im Fetch der Cursor weg ist....

Das ist bei ODBC tatsächlich anders. Da eine Transaktion einem oder mehreren Command zugeordnet wird, bleibt dieser auch nach Commit/Rollback weiter offen. Einen "Declare Cursor .... with Hold" gibts da gar nicht und scheint somit standard zu sein.

Off Topic:
Gerade beim "Cursor .. with hold" wurde mir letztens klar, dass dies wohl nur für dem Commit, nicht aber für den Rollback gilt. Um den Cursor dann offen zu halten, musste ich den Savepoint bemühen um einen Rollback Savepoint zu machen. Dadurch blieb der Cursor dann offen.