SQL Stored Procedures u. ThreadSafety

[axl]

Hallo zusammen,

ich benutze eine SQL-Procedure um ein Resultset nach Prüfung der Berechtigung an den Client (in diesem Fall Java) zurückzugeben.

Das Ganze funktioniert auch zu 99% ganz gut, aber eben nur zu 99%. Irgendwie erhält der Client manchmal das falsche Resultset zurück.
Dies passiert, wenn zwei Java-Threads in sehr kurzen Zeitabständen ein Resultset über diese SQL-Procedure anfordern.
Auch wenn ich den Zugriff mittels 'synchronize' in der Java-Methode serialisiere taucht dieses Phänomen immer noch auf.

Unser Versionsstand ist V5R2.

Für eine Antwort wäre ich sehr dankbar. Auch für einen evtl. anderen Lösungsansatz bzgl. Resultsets u. Berechtigungsprüfung.

[Fuerchau]

Ggf. liegt es an dem Attribut "[NOT] DETERMNINISTIC" !

DETERMINISTIC

or NOT DETERMINISTIC Specifies whether the procedure returns the same results each time the procedure is called with the same IN and INOUT arguments.
NOT DETERMINISTIC The procedure may not return the same result each time the procedure is called with the same IN and INOUT arguments, even when the referenced data in the database has not changed.
DETERMINISTIC The procedure always returns the same results each time the procedure is called with the same IN and INOUT arguments, provided the referenced data in the database has not changed.

[B.Hauser]

Hallo,

ich würde eher FENCED bzw. NOT FENCED vorschlagen. Dieses Schlüssel-Wort ist bereits V5R2 sowohl beim CREATE PROCEURE als auch beim CREATE FUNCTION verfügbar:

FENCED or NOT FENCED
Specifies whether the external function runs in an environment that is isolated from the database manager environment. FENCED is the default.

FENCED
The function will run in a separate thread.
FENCED functions cannot keep SQL cursors open across individual calls to the function. However, the cursors in one thread are independent of the cursors in any other threads which reduces the possibility of cursor name conflicts.

NOT FENCED
The function may run in the same thread as the invoking SQL statement.
NOT FENCED functions can keep SQL cursors open across individual calls to the function. Since cursors can be kept open, the cursor position will also be preserved between calls to the function. However, cursor names may conflict since the UDF is now running in the same thread as the invoking SQL statement and other NOT FENCED UDFs.
NOT FENCED functions usually perform better than FENCED functions.

Bei DETERMINISTIC werden die Ausgabe-Werte (Parameter- und Result Sets) zwischen gespeichert. Beim nächsten Aufruf mit den gleichen Parametern, wird die Prozedur nicht ausgeführt, sondern lediglich das zuvor gesicherte Ergebnis ausgegeben.

Birgitta

[Fuerchau]

Genau das DETERMINISTIC scheint ja das falsche Ergebnis zu bringen.

Ausserdem sollten verschiedene Threads nicht die selbe Connection sharen, das führt immer zu Problemen.

[axl]

Vielen Dank für die schnellen Antworten, ich habe mal beide Schlüsselwörter ('NOT DETERMINISTIC, u. 'FENCED') integriert, wobei ich mit 'NOT DETERMINISTIC' schon auch rumprobiert hatte. 'FENCE' hatte ich allerdings noch nicht ausprobiert.

Auf jeden Fall schaut jetzt mein Procedure-Kopf folgendermaßen aus (siehe unten). Jetzt warte ich mal, wann dieser Fehler wieder auftritt...

Evtl. ist bei höheren Versionsständen (wir haben V5R2) dieses Phänomen beseitigt!?

-----------------------------------------
create procedure vogtj/SPTUNNEL(
IN actionname char(20)
)

result sets 1
language sql
NOT DETERMINISTIC
FENCED

[BenderD]

das sieht mir eher nach einer fehlerhaften Implementierung im Java aus, die bei Verwendung einer extra Connection (aus einem ConnectionPool) für jeden SQL Zugriff nicht passieren kann.
Für alternative Lösungsansätze und Berechtigungsprüfungen bräuchte man schon ein wenig mehr Input.

D*B

Auch wenn ich den Zugriff mittels 'synchronize' in der Java-Methode serialisiere taucht dieses Phänomen immer noch auf.

Für eine Antwort wäre ich sehr dankbar. Auch für einen evtl. anderen Lösungsansatz bzgl. Resultsets u. Berechtigungsprüfung.

[axl]

Das ist wahrscheinlich auch das Problem. Jede Abfrage (auch aus verschiedenen Threads) laufen über ein Connection Objekt.

Bisher funktionierte das auch wunderbar. Zumindest als wir noch direkt auf die Tabellen/Views zugegriffen haben.

Aber seit wir aus 'sicherheitsgründen' nur noch über Stored-Procedures zugreifen gibt's hier Probleme.

Bzgl. einer eigenen Connection pro SQL-Abfrage (ConnectionPool) wäre ich wahrscheinlich auf der sicheren Seite. Für mehr Infos diesbezüglich wäre ich Ihnen sehr dankbar.

[BenderD]

Hallo,

normalerweise verwendet man bei JDBC (und nicht nur da) für jede Transaktion (-> sind bei Commit > 1 SQL Operation) grundsätzlich eine eigene Connection, das vereinfacht alles drastisch (auch die Threading Probleme sind damit automatisch ausgeschlossen). Damit nicht ständig Connections erzeugt und geschlossen werden, hält man sich diese in einem Connection Pool (davon gibt es mehrere OpenSource Implementierungen SourceForge.net: c3p0:JDBC DataSources/Resource Pools oder DBCP - Downloads ). Auf der AS/400 ist das zwar nicht so essentiell, weil da Server seitig eine Art Pool gehalten wird, aber bei externen Reviews sieht man ohne Pool eher schlecht aus.
Das ganze hat allerdings auch zur Konsequenz, dass in der Regel alle Datenbankzugriffe unter demselben Applikationsuser gemacht werden und die Zugriffskontrolle in die Applikation wandert, also gerade icht in einer stored Procedure abgefackelt werden. BTW: ich halte dieses Design für fragwürdig! User spezifische Sichten lassen sich in der Datenbank über inner join zu Berechtigungstabellen wesentlich eleganter und performanter lösen.
Wenn man spezifische Connections benötigt, dann sollte man trotzdem ConnectionPool dazwischen schalten (dann pro Benutzer ein Pool), der macht einiges mehr, wie Connections auf Gängigkeit prüfen, selbständig von Zeit zu Zeit neue zu nehmen, bis hin zum cachen von prepared Statements.

mfg

Dieter Bender

Das ist wahrscheinlich auch das Problem. Jede Abfrage (auch aus verschiedenen Threads) laufen über ein Connection Objekt.

Bisher funktionierte das auch wunderbar. Zumindest als wir noch direkt auf die Tabellen/Views zugegriffen haben.

Aber seit wir aus 'sicherheitsgründen' nur noch über Stored-Procedures zugreifen gibt's hier Probleme.

Bzgl. einer eigenen Connection pro SQL-Abfrage (ConnectionPool) wäre ich wahrscheinlich auf der sicheren Seite. Für mehr Infos diesbezüglich wäre ich Ihnen sehr dankbar.

[axl]

Vielen Dank nochmals für die vielen Anregungen. Mittlerweile habe ich auf Connectionpools umgestellt. Wobei ich aber noch nicht nach jedem SQL eine 'neue' Connection verwende. Zumindest wird jetzt eine Connection auch nur von einem Thread verwendet.

Das Phänomen mit manchmal unsinnigen zurückgegeben Resultsets habe ich aber nach wie vor.

Unter: IBM - SE25763 - OSP-DB-MSGCPD4373 OR EMPTY RESULT SET
habe ich aber folgendes gefunden, wovon ich ausgehe, dass dies unser Phänomen genau beschreibt. Leider haben wir keine AS400 mit höherem Releasestand um dies auch bestätigen zu können.

[BenderD]

auch der in deinem link beschriebene Fall ist eine grob fehlerhafte Implementierung in Java und kein Bug in der Datenbank:
private static PreparedStatement ps = null;
dieses Konstrukt ist Java seitig nicht Threadsafe!!!

mit solch einem Konstrukt kann ich soviele Connections verwenden, wie ich will - das preparedStatement wird unterm Hintern weg ausgetauscht, und das nicht nur über Threads, sondern über alle Objekte in der JVM, da schüttelts mich, und wenn ich in den Sandalen Socken anhätte, dann hätten meine sich hochrollenden Fußnägel Löcher hinterlassen.

Dieter Bender

Vielen Dank nochmals für die vielen Anregungen. Mittlerweile habe ich auf Connectionpools umgestellt. Wobei ich aber noch nicht nach jedem SQL eine 'neue' Connection verwende. Zumindest wird jetzt eine Connection auch nur von einem Thread verwendet.

Das Phänomen mit manchmal unsinnigen zurückgegeben Resultsets habe ich aber nach wie vor.

Unter: IBM - SE25763 - OSP-DB-MSGCPD4373 OR EMPTY RESULT SET
habe ich aber folgendes gefunden, wovon ich ausgehe, dass dies unser Phänomen genau beschreibt. Leider haben wir keine AS400 mit höherem Releasestand um dies auch bestätigen zu können.

[axl]

Der Link auf die Fehlerbeschreibung war eigentlich mehr wegen der Ähnlichkeit des Fehlers drin. Die Implementierung bei uns sieht anders aus. Wir verwenden auch keine static PreparedStatement Variablen, wie in dem Link beschrieben.

Mittlerweile kann ich (nach sehr mühsamer Arbeit) den Fehler mit Minimalcode und mit nur einem Thread reproduzieren.

Die Exception ist:
java.sql.SQLException: [SQL0901] SQL-Systemfehler.ErrorCode -901

at com.ibm.as400.access.JDError.throwSQLException(JDE rror.java:650)
at com.ibm.as400.access.JDError.throwSQLException(JDE rror.java:621)
at com.ibm.as400.access.AS400JDBCStatement.commonExec ute(AS400JDBCStatement.java:896)
at com.ibm.as400.access.AS400JDBCPreparedStatement.ex ecuteQuery(AS400JDBCPreparedStatement.java:1108)
at testpackage.TestGleicheAbfrage2.main(TestGleicheAb frage2.java:65)
Message [SQL0901] SQL-Systemfehler.
SQLState 58004

und der Code:

package testpackage;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import com.ibm.as400.access.AS400;


public class TestGleicheAbfrage2 {

private static Connection connection;

public static void main(String args) {
try {

try {
Class.forName("com.ibm.as400.access.AS400JDBCDriver",true, AS400.class.getClassLoader());
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
connection = DriverManager.getConnection("jdbc:as400://zedv.erl.firma.com;libraries=VOGTJ, *LIBL", "XXX", "YYY");

CallableStatement cs1 = callsptunnel("JFDKNR = 18");
ResultSet rs1 = cs1.executeQuery();
rs1.next();

// ----

CallableStatement cs2 = callsptunnel("JFDKNR is null");
ResultSet rs2 = cs2.executeQuery();
rs2.next();

rs1.close();
rs2.close();

// ----

CallableStatement cs3 = callsptunnel("JFDKNR is null");
ResultSet rs3 = cs3.executeQuery();
rs3.next();
rs3.close();

// ---

CallableStatement cs4 = callsptunnel("JFDKNR = 18");
ResultSet rs4 = cs4.executeQuery();
rs4.next();

// ----

CallableStatement cs5 = callsptunnel("JFDKNR is null");
ResultSet rs5 = cs5.executeQuery();
rs5.next();

rs4.close();
rs5.close();

// ----

CallableStatement cs6 = callsptunnel("JFDKNR is null");
ResultSet rs6 = cs6.executeQuery();
rs6.next();
rs6.close();

connection.close();

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("ErrorCode \t" + e.getErrorCode());
System.out.println("Message \t" + e.getMessage());
System.out.println("SQLState \t" + e.getSQLState());
try {
connection.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}

}





private static CallableStatement callsptunnel(String wherestatement) throws SQLException {

String command = "{call SPTUNNEL" +"(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}";
CallableStatement callablestatement = connection.prepareCall(command);

callablestatement.registerOutParameter(7, Types.CHAR);
callablestatement.registerOutParameter(8, Types.CHAR);

callablestatement.setString(1, "JFDK");
callablestatement.setString(2, "TEST");
callablestatement.setString(3, "4711");

callablestatement.setDouble(4, new Double(0));
callablestatement.setString(5, "MY");
callablestatement.setString(6, "0");

callablestatement.setString(7, "");
callablestatement.setString(8, "");

callablestatement.setString(9, wherestatement);
callablestatement.setString(10, "");

return callablestatement;

}

}

[BenderD]

Hallo,

das sieht auf den ersten Blick erstmal unverdächtig aus, ich sehe zwar nicht auf den ersten Blick wo Zeile 65 ist (wo er wohl rausdüst). Aufgefallen ist mir allenfalls, dass die CallableStatements nicht geschlossen werden.
Zusätzliche Info könnte man noch bekommen, wenn man den Database Server Job unter debug nimmt (mit separatem User connecten und nach dem Connect WRKOBJLCK MYUSR *USRPRF Job lokalisieren, STRSRVJOB und STRDBG) da müsste man sogar bis in die stored Procedure reinkommen.
Was sagt denn so euer GRPPTF Stand der Database (und den Treiber könnte man mal tauschen.
Für die wohl fällige Fehlermeldung bei IBM wäre es wohl ganz gut, wenn man den Fehler auch in RPG hinbekommt, dann haben die nur noch eine Ausrede.

mfg

Dieter Bender