User-Defined-Funktion mit XA-Treiber

[Atomik]

Guten Tag,

ich hoffe mir kann jemand hier weiterhelfen. Ich stehe vor dem Problem, dass ich einem Java-Programm aus einer SQL-Anweisung per User-Defined-Function (RPGLE) zugreifen möchte.

String sql = "SELECT feld1, feld2, pgm.fu132t(feld1, feld2) AS teststring 
FROM dateia1 WHERE feld1 = '100' AND feld2 = 550";

Das ganze läuft auf einem (WebSphere) Application-Server mit dem XA-Treiber (com.ibm.as400.access.AS400JDBCXADataSource) aus der JT400 Toolbox.

Beim Ersten Aufruf funktioniert alles ganz wunderbar. Ich erhalte z.B. die Ausgabe: "100:550:Test100"

Wenn ich allerdings den Test wiederhole bekomme ich die Ausgabe "null:null:null" und folgenden Fehler auf die Java-Konsole:

SystemOut     O java.sql.SQLException: [SQL0443] *N Ursache  . . . . :  
Von einem Auslöserprogramm, einer externen Prozedur oder einer 
externen Funktion wurde ein Fehler erkannt und an SQL zurückgegeben. 
Ist der Fehler in einem Auslöserprogramm aufgetreten, war der Auslöser 
in Tabelle FU132T in Schema PGM. Ist der Fehler in einer externen 
Prozedur oder Funktion aufgetreten, lautet der externe Name FU132T in 
Schema PGM. Der zugeordnete Text ist *N. Trat der Fehler in einem 
Auslöserprogramm auf, ist der zugeordnete Text die Art des 
Auslöserprogramms. Trat der Fehler in einer externen Prozedur oder 
Funktion auf, ist der zugeordnete Text der Text der Fehlernachricht, die 
von der externen Funktion zurückgegeben wurde. Fehlerbeseitigung:  
Das Jobprotokoll enthält weitere Informationen über den erkannten 
Fehler. Die Fehler berichtigen und die Anforderung wiederholen.
	at com.ibm.as400.access.JDError.throwSQLException

--- gekürzt ---

Das RPGLE Routine sieht wie folgt aus:

* ************************************
* * SQL - User defined Procedure
* *
* ************************************
* 1) Module erstellen
* -------------------
* CRTRPGMOD MODULE(PGM/FU132T) SRCFILE(PGMSRC/QRPGLESRC)
* SRCMBR(FU132T) REPLACE(*YES) ALWNULL(*YES)
*
* 2) Service-Programm erstellen
* -----------------------------
* CRTSRVPGM SRVPGM(PGM/FU132T) EXPORT(*ALL)
* TEXT('Serviceprogramm fuer FU132T')
*
* 3) SQL Funktion erstellen
* -------------------------
* CREATE FUNCTION PGM/FU132T
*    ( PARAM1 char(3),
*      PARAM2 dec(4)
*    ) RETURNS char(150)
*     LANGUAGE RPGLE
*     EXTERNAL NAME 'PGM/FU132T(SQL_FUNC)'
*     DETERMINISTIC
*     RETURNS NULL ON NULL INPUT
*     NO SQL
*     NO EXTERNAL ACTION
*     PARAMETER STYLE SQL
*     ALLOW PARALLEL
*     NOT FENCED
* *****************************************
* * PROTOTYP
* * --------
* *****************************************
H* //Dieses Modul beinhaltet nur Subprozeduren
H Option(*noDebugIO : *noShowCpy)
H DECEDIT('0,') DATEDIT(*DMY.)
H NOMAIN
F* ----------------------------------------------------------
F* Dateien
F* ----------------------------------------------------------
FDATEIA1  IF   E           K DISK
D* ----------------------------------------------------------
D* Definition der Prototyp Felder für das Aufrufende Programm
D* ----------------------------------------------------------
DSQL_FUNC         PR           150A
D #PARAM1                        3
D #PARAM2                        4  0
C* **********************************************************
C* * BEGINN der Prozedur
C* * -------------------
C* ***********************************************
C* //Prozedur aus dem Modul exportieren
PSQL_FUNC         B                   EXPORT
C* -----------------------------------------------
C* Definitionen der Funktion
C* -----------------------------------------------
C* //Prozedur Schnittstelle definieren
DSQL_FUNC         PI           150A
D #PARAM1                        3
D #PARAM2                        4  0
D*
C* //Initialisierung Work-Felder
D WRKSTR          S            150    INZ(*BLANKS)
C* -----------------------------------------------
C* Key Felder
C* -----------------------------------------------
C     KEY01         KLIST
C                   KFLD                    #PARAM1
C                   KFLD                    #PARAM2
C
C* -----------------------------------------------------
C* Verarbeitung Datensätze
C* -----------------------------------------------------
C     KEY01         SETLL     DATEIA1
C                   EVAL      WRKSTR = 'TEST' + #PARAM1
C*                  //String zurückgeben
C                   RETURN    WRKSTR
PSQL_FUNC         E

Im Jobprotokoll finde ich noch folgende Hinweise mit denen ich allerdings nichts anfagen kann:

Job 432275/QUSER/QZDASOINIT im Subsystem QUSRWRK in QSYS am 26.02.11 um
  22:04:05 gestartet. Job im System am 26.02.11 um 22:04:05. angekommen.
Benutzer MYUSER an Client xxx.xxx.xxx.xxx ist mit dem Server verbunden.
Cursor CRSR0001 nicht geöffnet.
Es wurde versucht, auf ein nicht mehr vorhandenes Objekt oder Teile des
  Objekts Bezug zu nehmen.
Interner Fehler im Umwandlungsprogramm oder in einer Unterroutine.
Anwendungsfehler. RNX9998 nicht überwacht durch PCSSQ2 bei Anweisung *N,
  Instruktion X'0000'.
Es wurde versucht, auf ein nicht mehr vorhandenes Objekt oder Teile des
  Objekts Bezug zu nehmen.
Aufrufstapeleintrag nicht gefunden.
Ausnahmerekursion erkannt.
Anwendungsfehler. *N nicht überwacht durch *N bei Anweisung *N,
  Instruktion X'4000'.
Fehler bei benutzerdefinierter Funktion in Teildatei DATEIA1.
Fehler bei benutzerdefinierter Funktion in Teildatei DATEIA1.
Fehler von Auslöserprogramm oder externer Routine erkannt.
Cursor CRSR0001 nicht geöffnet.

Es wäre schön wenn mir jemand Hinweise geben könnte wo sich der Fehler befinden könnte.

Andreas Tomik

[Atomik]

noch etwas zur Ergänzung:

Die oben dargestellte RPGLE-Routine ist natürlich etwas konstruiert und dient mir als Test um den Fehler zu lokalisieren.

Wenn ich die SETLL-Anweisung entferne, lässt sich die SQL-Anweisung mit der UDF beliebig oft aufrufen.Allerding muss ich säter natürlich in der Produktivversion des Programms aus Dateien lesen können. Von daher ist es keine Lösung einfach Dateioperationen wegzulassen.

Wenn ich einen anderen JDBC-Treiber verwende (com.ibm.as400.access.AS400JDBCConnectionPoolDataS ource) kann ich die SQL-Anweisung auch beliebig oft aufrufen. Leider befinde ich mich aber mit dem Projekt aber in einem Integrationsumfeld mit verteilten Systemen und benötige daher die Möglichkeit der vertielten Transaktionen (two-phase-commit). Daher ist die Variante ohne den XA-Treiber auch keine Lösung.

[Fuerchau]

Definiere die Datei
a) innerhalb der Prozedur mit USROPN und mach immer einen Open/Close
b) definiere die Prozedur mit CONTAINS SQL, damit Dateizugriffe im selben Thread laufen
c) verwende am besten gleich SQL in der Prozedur

[Atomik]

Vielen, vielen Dank!!! Das war der entscheidende Hinweis

[Atomik]

Definiere die Datei
a) innerhalb der Prozedur mit USROPN und mach immer einen Open/Close
b) definiere die Prozedur mit CONTAINS SQL, damit Dateizugriffe im selben Thread laufen
c) verwende am besten gleich SQL in der Prozedur

Wir haben hier eine RPGLE-Prozedur in der direkt SQL verwendet wird. Damit die Prozedur läuft mussten wir diese mit READS SQL DATA anstatt, wie von Ihnen empfohlen, mit CONTAINS SQL DATA definieren.

Laufen die Datenzugriffe mit READS SQL DATA ebenfalls im selben Thead wie der Aufrufer??? Denn dieses wollten wir ja mit CONTAINS SQL DATA erreichen. - Was auch funktionierte -

[B.Hauser]

Contains SQL oder Reads SQL hat eigentlich nichts damit zu tun, ob die Anwendung im gleichen oder einem anderen Thread läuft.

Hier ist die Definition aus der aktuellen SQL Referenz:

READS SQL DATA
Specifies that the function can execute statements with a data access classification of READS SQL DATA, CONTAINS SQL, or NO SQL. The function cannot execute SQL statements that modify data.

CONTAINS SQL
Specifies that the function can execute only SQL statements with a data access classification of CONTAINS SQL or NO SQL. The function cannot execute any SQL statements that read or modify data.

Du solltest Dir stattdessen die Option FENCED/NOT FENCED anschauen:
Hier ebenfalls ein Auszug aus der aktuellen SQL Referenz:

FENCED or NOT FENCED
Specifies whether the SQL 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.

Birgitta

[Fuerchau]

MODIFIES SQL DATA

The function can execute any SQL statement except those statements that are not supported in any function.

Scheint wohl dann eher die richtige Definition zu sein.