Transaktion in einer SQL UDF durchführen

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