StoredProcedure

[Bratmaxxe]

Moin,

um bestehende RPG-Logiken auch im JAVA-Umfeld benutzen zu können, habe ich eine StoredProcedure um ein SQLRPG Programm gebaut, welches seine Ergebnismenge in einem Array zurückgibt, damit diese Später über ein SQLStatement ausgelesen werden kann:

CREATE PROCEDURE PGM/MYPGM(IN TYPE CHAR ( 3), IN SNUM DEC    
(5), IN JAHR DEC (4), ..... ) RESULT SETS 1 LANGUAGE RPGLE NOT   
DETERMINISTIC READS SQL DATA EXTERNAL NAME PGM/MYPGM PARAMETER 
STYLE GENERAL 


callableStmt =  myAS400Connection.prepareCall(
                            "CALL PGM.MYPGM(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)") 
ResultSet rSet = callableStmt.executeQuery();

Mittlerweile ist das Array bzw. Datenstruktur mit 15000 Elementen definiert und müsste noch um einiges vergrößert werden. Jedoch bekomme ich das Programm nicht mehr umgewandelt:

*RNF0376 30      1 Das Datenelement überschreitet die maximal zulässige von
                   von 16.773.104 Byte.

Weiß jemand vielleicht Rat, wie ich das Problem gelöst bekomme?

Viele Grüße
Bratmaxxe

[Fuerchau]

Umstellen auf Terabyte, dann sollte es möglicherweise gehen.
Die Alternative:
PF-Table erstellen und einen Cursor auf diese zurückgeben.
Das klappt ggf. auch in QTEMP.

[Bratmaxxe]

Blöde Frage: Wie / was oder wo muss ich etwas umstellen?

[Fuerchau]

Bei den Erstellbefehlen CRTxxxMOD/CRTxxxPGM im Parameter STGMDL(*TERASPACE).
*INHERIT bzw. *SNGLVL (<= max. 16MB je Variable) ist der Default.
Ich weiß aber nicht, ob SQL Teraspace unterstützt.

Besser ist auf jeden Fall, an Stelle einer Endlosstruktur eine echte Datei (PF, TABLE) und einen offenen Cursor auf den "Select * from MyTmpTable" zurückzugeben.

Wenn das ganze sitzungssicher sein soll kannst du auch eine "CREATE GLOBAL TEMPORARAY TABLE (...)" erstellen. Diese steht dann je SQL-Sitzung (Job, ggf. ACTGRP) exklusiv zur Verfügung.

[Thomas65]

Sowas lässt sich auch schön mit einer UDTF lösen.

create function mylib/myUDTF(
P#SCode varchar(3),
P#Selektion varchar(32000) )
returns table(tb_Feld1 char(60),
tb_Zahl1 dec(4, 0))

External Name 'MYLIB/MYSRVPGM(MYPROCEDURE)'
Language RPGLE
Disallow Parallel
modifies sql data
Parameter Style DB2SQL
Not Deterministic
Scratchpad 32;


mit einem Serviceprogramm diesen Aufbaus dahinter:
H NOMAIN DATEDIT(*DMY.) DATFMT(*EUR.) DECEDIT('0,') TIMFMT(*HMS)
H OPTION(*SRCSTMT:*NODEBUGIO:*NOUNREF)
*
*================================================= ===========================================*
* Konstanten:
*================================================= ===========================================*
D Ja C CONST('J')
D Nein C CONST('N')
*
*================================================= ===========================================*
* Variablen:
*================================================= ===========================================*
D M#Feld S 60A
D M#Zahl S 4P 0
*
* UDTF call parameter constants
D UDTF_FirstCall S 10I 0 Inz(-2)
D UDTF_Open S 10I 0 Inz(-1)
D UDTF_Fetch S 10I 0 Inz(0)
D UDTF_Close S 10I 0 Inz(1)
D UDTF_LastCall S 10I 0 Inz(2)
* SQL States
D SQLSTATEOK C '00000'
D ENDOFTABLE C '02000'
D UDTF_ERROR C 'US001'
* NULL Indicators
D ISNULL C -1
D NOTNULL C 0
*
*================================================= ===========================================*
* Funktionsimplementierung:
*================================================= ===========================================*
P MyProcedure...
P B EXPORT
D PI

* Table Function Eingabeparameter
D in_SCode 3A varying
D in_Selektion 32000A varying

* Table Function Ausgabeparameter (Spalten)
D tb_Feld1 60A
D tb_Zahl1 15A

* Null Indicator Input Parameters
D ni_SCode 5I 0
D ni_Selektion 5I 0

* Null Indicator Output Parameters
D ni_Feld1 5I 0
D ni_Zahl1 5I 0

* DB2SQL Style Parameters
D P#SQLState 5
D P#FunctionName 517
D P#SpecificName 128
D P#SQLMsgText 70 Varying
D P#ScratchPad 32 Varying
*
* UDTF Call Type
D P#TFCallType 10I 0
*
*================================================= ===========================================*
* Datenstruktur zur Zerlegung der Übergabeparameter zwischen SQL und RPG (sogen. "ScratchPad")
*================================================= ===========================================*
D ScratchDS DS 32 based(ptrScratch)
D Dummy 1A
*
*================================================= ===========================================*
* Code:
*================================================= ===========================================*
C EVAL ptrScratch = %addr(P#ScratchPad)
*
C SELECT
* Erster Aufruf:
C WHEN p#TFCallType = UDTF_Open
C EXSR SubOpen
*
* Satz-Anforderung:
C WHEN p#TFCallType = UDTF_Fetch
C EXSR SubFetch
*
* Beenden:
C WHEN p#TFCallType = UDTF_Close
C EXSR SubClose
C ENDSL
*
C RETURN
*
*================================================= ===========================================*
* Öffnen
*================================================= ===========================================*
C SubOpen BEGSR
*
* Aufrufen eines Hilfsprogramms, das eine Temporärdatei mit den gewünschten
* Daten erstellt:
<>
C CALL 'IRGENDWAS'
C PARM in_Selektion
*
/FREE
EXEC SQL declare csr cursor for
select EinText,
EineZahl
from MyLib/MyFile;

EXEC SQL open csr;

/END-FREE
*
C ENDSR
*================================================= ===========================================*
* Satz holen:
*================================================= ===========================================*
C SubFetch BEGSR
*
/FREE
EXEC SQL fetch next from csr into
:M#Feld,
:M#Zahl
/END-FREE
*
C IF SQLCOD = 0
C EVAL tb_Feld1 = M#Feld
C EVAL tb_Zahl1 = M#Zahl
*
C ELSE
C EVAL p#SQLState = ENDOFTABLE
C ENDIF
*
C ENDSR
*================================================= ===========================================*
* Letzter Aufruf:
*================================================= ===========================================*
C SubClose BEGSR
*
C/EXEC SQL
C+ close csr
C/END-EXEC
C EVAL *INLR = *ON
*
C ENDSR
P E

Im SQL kann man das dann mittels "table" wie eine Tabelle verwenden:
select * from table(myUDTF(Steuercode, Selektionsparameter));

[Fuerchau]

Das Problem wird hierfür sein, dass ich die Tabelle nicht 2 Mal gleichzeitig öffnen oder mit sich selber verjoinen kann.
Ich muss die Daten nacheinander lesen.
Für solche Fälle nehme ich dann doch lieber temporäre Tabellen.

[Bratmaxxe]

Moin,

vielen Dank für die Lösungsansätze.

Ich habe da noch einige Verständnisfragen:

1. Beim CRTSQLRPGI kann ich bei den Compilersettings kein *TERASPACE angeben / beim CRTBDNRPG, etc. geht das aber. Jetzt war mein Ansatz, dass ich das in die H-Bestimmung eintrage - wie lautet dafür denn die Korrekte Syntax?
--> H DFTACTGRP(*NO) STGMDL(*TERASPACE) wird als falsch angemeckert

2. Ich hatte die DS in ein RPG-Testprogramm reinkopiert und dann mit CRTBNDRPG und den Terraspace-Einstellungen compiliert - bricht aber trotzdem ab: Das Datenelement überschreitet die maximal zulässige Größe von 16.773.104 Byte. Warum??? Ich steh im Moment auf dem Schlauch...

@Fuerchau: Gibt es bei der Variante PF / Table noch irgendetwas bei der PROC zu beachten?
Sitzungssicher soll es sein, da mehrere User gleichzeitig Daten abrufen können... PF oder Table sollte sicherlich nach QTEMP dupliziert werden? Wenn ich dann den Cursor zurückgebe, muss sicherlich auch die PROC anders erstellt werden (siehe unten: CLOSQLCSR(*ENDMOD))?

CRTSQLRPGI OBJ(PGM/MYPGM) SRCFILE(PGMSRC/QRPGLESRC) COMMIT(     
*NONE)                                                            
OUTPUT(*PRINT) OPTION(*XREF *SEQSRC) CLOSQLCSR(*ENDMOD) DBGVIEW(  
*SOURCE)

Mit der Global Temporary Table habe ich keine Erfahrung. Verhält sich die temporäre Tabelle genauso wie das Füllen einer DS mit Mehrfachvorkommen (Stichwort "Performance")?

Viele Grüße
BM

[Fuerchau]

GLOBAL TEMPORARY ist eine Tabelle, die automatisch in QTEMP zur Verfügung gestellt wird.
Diese füllst du ganz normal per Insert und gibst einen Cursor per Select zurück.
Auch hier gilt, dass der Cursor vom Client erst geschlossen werden muss bevor deine Prozedur erneut aufgerufen wird.

Ggf. kann SQL halt kein Teraspace. Hier sind ja Variablen nur bis 16MB erlaubt, alles andere muss dann über LOB's abgewickelt werden.
Also bleibt dir nur eine echte Tabelle übrig.

[BenderD]

... das storage model ist eine Eigenschaft eines *PGM/*SRVPGM, kann also nicht beim erstellen eines Moduls angegeben werden. Die RNF0376 kommt bei der Erstellung des modules, lässt sich also nicht mit stgmdl *TERASPACE toppen.
Was ist denn an der RPG Logik so wertvoll, dass man das nicht gleich im Java macht? Da gibt es so schicke Sachen wie Hibernate und Co., die den data access in Jave dem ganzen RPG Gerödel haushoch überlegen machen.

D*B

[Fuerchau]

Warum baut man Wrapper um bestehende RPG-Schnittstellen, die bekannte Parameter haben man sich aber um die enthaltene Logik nicht mehr kümmern muss oder mangels Quellen nicht verfügbar ist?

[Bratmaxxe]

Hallo,

ich habe mir einen kleinen Test zusammengeschustert:

Nachfolgend das SQLRPGLE - in den Compilersettings wird der Cursor mit *ENDMOD geschlossen:

      * CREATE PROCEDURE PGM/SRL_TSTX6(IN MANDANT CHAR ( 3))                                        
      * RESULT SETS 1 NOT                                                                           
      * DETERMINISTIC READS SQL DATA EXTERNAL NAME PGM/SRL_TSTX6                                   
      * PARAMETER STYLE GENERAL    
                                                                 
     H DFTACTGRP(*NO) ACTGRP(*CALLER)                                                               
     d aDate           s               d   datFmt(*ISO) inz(D'2010-12-07')                          
     d dayNumber       s             10i 0                                                          
     d dayName         s             10a                                                            
      /free                                                                                         
       exec SQL Set Option Commit=*NONE, Naming=*SYS;                                               
       exec SQL declare global temporary table temp_daynames                                        
               (day_number integer, day_Name varchar (9)) ;                                         
                                                                                                    
       exec SQL insert into temp_daynames                                                           
                    values(1, 'Monday'), (2, 'Tuesday'), (3, 'Wednesday'),                          
                          (4, 'Thursday'), (5, 'Friday'), ('6', 'Saturday'),                        
                          (7, 'Sunday');                                                            
       exec SQL values dayOfWeek_ISO(:aDate) into : dayNumber ;                                     
                                                                                                    
       EXEC SQL Declare CustCsr cursor for                                                          
               SELECT * FROM temp_daynames;                                                         
       EXEC SQL Open CustCsr;                                                                       
       EXEC SQL SET RESULT SETS for Return to Client                                                
                Cursor CustCsr;                                                                     
       return;                                                                                      
     C                   EVAL      *INLR = *ON

Und das Java-Programm:

@Test
    public void temporaryTable()
    {
        try
        {
            CallableStatement callableStmt = AS400_Anmeldung.getConnection().prepareCall("CALL PGM.SRL_TSTX6(?)");
            callableStmt.setString(1, "100");
            ResultSet rSet = callableStmt.executeQuery();
            while (rSet.next())
            {
                String dayName = rSet.getString(rSet.findColumn("day_Name"));
                System.out.println(dayName);
            }
        } catch (final Exception ex)
        {
            System.out.println(ex);
        }
    }

Nur leider kommt es zu einer Exception (java.sql.SQLException: Cursor state not valid.).

Wo ist der Haken? Habe ich etwas übersehen?

Gruß und schönes Wochenende
BM

[andreaspr@aon.at]

Vielleicht darfst du nicht den *INLR auf *ON setzten, sondern muss *OFF bleiben.

lg Andreas