Stored Procedure & Functions unter Transaktionskontrolle setzen

[Martin82]

Hi,

ich hab eine größere Stored Procedure mit viele einzelnen SQL-Updates geschrieben. Jetzt möchte ich den Aufruf dieser Stored Procedure unter Transaktionskontrolle setzen. D.h. ich will, dass wenn irgendwas schief geht, alle Aktionen welche in der STP durchgeführt wurden, wieder zurückgesetzt werden(rollback).

Wie macht man das? Gibt es dazu eine Dokumentation?

lg

[Fuerchau]

Das Thema nennt sich Journalisierung.
Die Dateien müssen im Journal aufgezeichent werden.
Vor Aufruf der Prozedur muss ggf. STRCMTCTL durchgeführt werden.

Bei Fehlern in der Prozedur muss diese den SQLCODE bzw. SQLSTATE mit einem Fehlerwert besetzten.
Dadurch bekommt der Aufrufer einen entsprechenden SQLCOD zurück um zwischen COMMIT und ROLLBACK zu entscheiden.

Eine Stored Procedure / Function kann selber leider keinen Commit/Rollback auslösen, da sie im Kontext mit anderen Funktionen ja verwendet werden, die ggf. nichts von einander wissen.

Entscheider ist also ausschließlich der Aufrufer der Funktionen.
Wenn hier z.B. SQLCOD nicht ausgewertet und immer ein COMMIT durchgeführt wird hast du konzeptionell schlechte Karten.

[Martin82]

Alle Tabellen welche in der STP verwendet werden, werden journalisiert. Den Aufruf der STP mach ich in einem Java-Programm über JDBC(jtOpen).

D.h. das rollback muss ich im Java-Programm machen?

[BenderD]

... stellt sich zuerst die Frage ob die Procedure Commit Master oder Slave sein soll. Als slave ist da eigentlich nichts zu tun, da zieht im default das Commit Level der Connection und die aufrufende Funktion comitted in Abhängigkeit von der Rückmeldung der Procedure (über SQLCODE via signal).
StoPro als Commit Master ist eigentlich Waffenscheinpflichtig, es sei denn man arbeitet im Server Mode und stellt sicher, dass man eine eigene Connection hat. Letztlich wird man bei dieser Variante bei einem RPG Wrapper für die Procedure landen (den man auch wieder als externe stoPro vernageln könnte, da man bei den SQL Procedures weniger Stellschrauben hat.

D*B

Hi,

ich hab eine größere Stored Procedure mit viele einzelnen SQL-Updates geschrieben. Jetzt möchte ich den Aufruf dieser Stored Procedure unter Transaktionskontrolle setzen. D.h. ich will, dass wenn irgendwas schief geht, alle Aktionen welche in der STP durchgeführt wurden, wieder zurückgesetzt werden(rollback).

Wie macht man das? Gibt es dazu eine Dokumentation?

lg

[BenderD]

... das wäre die Slave Variante und das stabilste.

D*B

Alle Tabellen welche in der STP verwendet werden, werden journalisiert. Den Aufruf der STP mach ich in einem Java-Programm über JDBC(jtOpen).

D.h. das rollback muss ich im Java-Programm machen?

[andreaspr@aon.at]

wenn es dich interessiert, es gibt von der ibm ein sehr schönes redbook bzgl. SPs, Triggers und UDFs
IBM Redbooks | Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries
im kapitel Transaction management in stored procedures und commitment control, findest du sehr gut beschrieben was noch alles möglich ist.

[Martin82]

Ok.
Muss ich dazu auch irgendwelche Options in der STP setzen?

Hab nämlich nach dem Aufruf der STP ein rollback im Java-Programm versucht, die Updates der STP wurden aber nicht zurückgesetzt.

[andreaspr@aon.at]

Ok.
Muss ich dazu auch irgendwelche Options in der STP setzen?

Hab nämlich nach dem Aufruf der STP ein rollback im Java-Programm versucht, die Updates der STP wurden aber nicht zurückgesetzt.

du musst beim erstellen der SP einen eintrag machen:

CREATE PROCEDURE MYLIB.TEST1 ( 
	IN VACTNR CHAR(10) ) 
	DYNAMIC RESULT SETS 1 
	LANGUAGE SQL 
	SPECIFIC MYLIB.TEST1 
	NOT DETERMINISTIC 
	MODIFIES SQL DATA 
	CALLED ON NULL INPUT 
	SET OPTION COMMIT = *ALL ....

per default wird commit = *NONE gesetzt.

[BenderD]

... eigentlich müssten die defaults passen, hast du die Connection richtig eingerichtet?

D*B

Ok.
Muss ich dazu auch irgendwelche Options in der STP setzen?

Hab nämlich nach dem Aufruf der STP ein rollback im Java-Programm versucht, die Updates der STP wurden aber nicht zurückgesetzt.

[Martin82]

Habs jetzt auch mit einer Test-STP probiert. Folgender Code:

create procedure test_cc()
language sql
begin

declare exit handler for sqlexception rollback;

insert into test values(1,'');
/* Dies löst einen SQL0803 aus (Doppelter Schlüsselwert)*/
insert into test values(1,'');

commit;

end;

Den Aufruf hab ich über RunSQLScripts vom System i Navigator gemacht. JDBC-Settings: Cursor stability(*CS)

Theoretisch müsste dann ja das erste INSERT zurückgesetzt werden oder hab ich da einen Denkfehler? Es klappt nicht. Auch nicht wenn ich nach dem Aufruf der STP explizit ein rollback ausführe...

[andreaspr@aon.at]

schau mit dem befehl DSPPGM test_cc was in den beschreibungen unter COMMIT-Steuerung drinnen steht.
wenn *NONE angegeben ist, würde ich beim erstellen der SP (wie von mir vorhin beschrieben) den eintrag angeben.

[BenderD]

... erst mal würde ich das nicht mit Oops Nerv testen, da sieht man zu wenig und außerdem ist das Teil buggy.
Was hast du jetzt eigentlich vor? Master oder Slave? Das, was du da machst, sieht nach slave aus, da gibt es mittlerweile eine Einstellung COMMIT ON RETURN YES, die muss nach der Language Clause platziert werden, die sollte das eigentlich schon tun.

D*B

Habs jetzt auch mit einer Test-STP probiert. Folgender Code:

create procedure test_cc()
language sql
begin

declare exit handler for sqlexception rollback;

insert into test values(1,'');
/* Dies löst einen SQL0803 aus (Doppelter Schlüsselwert)*/
insert into test values(1,'');

commit;

end;

Den Aufruf hab ich über RunSQLScripts vom System i Navigator gemacht. JDBC-Settings: Cursor stability(*CS)

Theoretisch müsste dann ja das erste INSERT zurückgesetzt werden oder hab ich da einen Denkfehler? Es klappt nicht. Auch nicht wenn ich nach dem Aufruf der STP explizit ein rollback ausführe...