Insert SQL

[mk]

Hallo zusammen,

ein Javaprogramm schreibt Daten in eine SQL Tabelle
insert.executeUpdate ();

Nach ca. 10.000 Inserts kommt folgende Fehlermeldung:
java.sql.SQLException: Limit on number of statements exceeded.
at com.ibm.as400.access.JDError.throwSQLException(JDE rror.java:382)
at com.ibm.as400.access.AS400JDBCConnection.getUnused Id(AS400JDBCConnection.java(Compiled Code))

Hat jemand eine Idee wie man die Kiste dazu bringt
alles zu verarbeiten ?

Gruss
Michael

[KM]

Hallo Michael,

ich habe mal gelesen, dass nur 9999 Statements gleichzeitig offen gehalten werden können. Danach erscheint die besagte Meldung. Wenn das System selbst es nicht schafft die Statements rechtzeitig zu schließen, sollte man das zu Fuß tun. Versuch's doch mal zwischendurch mit Statement.close().

Gruß,
KM

[Fuerchau]

Wenn man mit Parameter-Markern und Prepared-Statements umgeht, dürfte das nicht passieren, da immer das selbe Statement verwendet wird (auch Perfomancesteigernd).
Die 2. Möglichkeit ist, dass ein Batch-Modus gewählt wurde und nur 9999 Inserts gecached werden können, irgendwann muss dann auch mal ein BatchUpdate durchgeführt werden, der die Inserts dann auch wirksam macht.

[mk]

Hallo zusammen,

nach einigen Versuchen klappt es jetzt.

Wenn nach dem insert direkt ein close folgt dann
arbeitet das pgm alles ab.


insert.executeUpdate ();
insert.close();





Ein close z.b. bei 9000 Sätzen brachte nichts.


PS: Das Sql Statement ist ein PreparedStatement insert

[Ottersberg]

stehe gerade vor dem selben Problem. Lösung ist hier dieselbe. Nach dem execute ein close.

Widerspricht das dann aber nicht eigentlich dem Sinn eines PreparedStatements? Ein Grund ist doch, dass das Statement nicht jedes Mal neu geladen werden soll um Ressourcen zu sparen und die Performance zu verbessern.

[Fuerchau]

Da ist (zumindest auf der AS/400) der Optimizer davor.
Was immer Java da auch treibt, die AS/400 bügelt da einiges aus.
Wenn man nichts explizit bei der Verbindung angibt, verwendet die AS/400 SQL-Packages, die automatisch in der QGPL angelegt werden (schau da mal mit PDM nach der Objektart *SQLPKG, wahrscheinlich findest du da eins mit passendem Namen zu deiner Anwendung).

Bei jedem Statement extrahiert die AS/400 Feldwerte und ersetzt diese automatisch mit Parametermarkern.
Anschließend prüft sie, ob es dieses Statement im SQLPKG bereits gibt und verwendet dieses, ein interner Prepare, Analyse der Zugriffswege und Speichern im SQLPKG entfällt.

Danach wird das Statement ausgeführt wobei die Parametermarker jetzt mit den Felddaten verbunden werden.

Zusätzlich wird jetzt geprüft, ob das Statement bereits einmal ausgeführt wurde und schließt daher die Datei nun nicht mehr (wiederverwenden des ODP's).

Der einzig nennenswerte Unterschied zwischen SQL's mit eingebetteten Feldwerten oder Verwendung von Parametermarkern ist die Verwendung von UNICODE.

SQL's werden normalerweise NICHT in UNICODE an die AS/400 übergeben, so dass Feldwerte in UNICODE nicht mehr sprachneutral sind. Es gibt also ggf. Ersatzwerte, was insbesonders bei Mischung verschiedener Sprachräume nicht so angenehm ist (west-/osteuropäisch).
Bei der direkten Verwendung von Parametermarkern passiert das eben nicht, da der Feldwert "as is" übergeben wird.

[RobertPic]

Die von Fuerchau beschriebene Optimierung beschreibt wie normale SQL-Statement von der AS/400 als preparedStatement erkannt werden - bzw. zumindest wird das von der AS/400 versucht.

In deinem Fall hast du ja bereits prepared Statement.

Da gibt es 2 Vorgangsweisen:

1.) Man erstellt ein prepared Statement (z.B. vor der Schleife) und verwendet dann immer das gleiche Statement (man beschickt nur die Parameter neu).

Der Fehler (*) von den Postings 2005 bestand darin, dass das Statement innerhalb der Verarbeitungsschleife neu erstellt wurde. Mit statement.close() innerhalb der Verarbeitungsschleife funktioniert es zwar - bringt aber nicht so viel.

2.) Man verwendet einen Connectionpool

Damit kann man jedes mal ein neues prepared Statement erzeugen und sofort wieder zumachen. Auch die Verbindung wird nach dem Arbeitschritt geclosed. Beim Connectionpool wird das connection.close() aber als "Connection zurück in den Pool legen" verarbeitet.

Auch wenn man die (prepared) Statements zumacht - bleiben diese auch im Connectionpool.

Damit das so funktioniert muss beim JT400/JTOPEN Pool das lazyclose eingeschalten werden. Bei anderen Connectionspools kann man per Config das offen halten von prepared Statements steuern bzw. die max. offenen Statements angeben.

Man kann das Ganze noch mit Batchupdate ergänzen. Also PreparedStatement.addBatch() sammeln mit mit ..Statement.executeBatch() loslassen.

Bei Massenupdates sollte Autocommit ausgeschalten sein und man sollte z.B. alle 1000 Updates ein COMMIT bzw. executeBatch (bei Batchupdate) machen.

Noch ein Detail am Rande: Wenn man StoredProcedure als prepared Statement offen hält und das 3GL-Programm (CL, Cobol, RPG) mit CALLLVL *CALLER gewandelt ist, bleibt das 3GL offen. Ich bediene z.B. einen Webshop mit kundenspezifischen Preisen. Für einen Preis im GH sind manchmal bis zu 50 Datenbankzugriffen im Bewertungsmodul notwendig. Durch das offen halten des Programmes (Cobol) und dessen Verbindungen geht die Preisermittlung innerhalb von wenigen Millisekunden (zw. 4 - 19 ms).

Durch das switchen der Connections (vom Pool) reicht eine offene Instanz des Bewertunsmoduls aus um bis zu 120 Webkunden damit zu bedienen.

Mit der richtigen Mischung aus RecordLevelAccess und SQL aus Java kann die i5 durchaus mit x86 Systemen mithalten bzw. überflügeln.

/Robert

(*) Den Fehler muss ich ohne Source raten. Aber entweder das oder der JDBC-Treiber hat einen Bug.

[Fuerchau]

@Robert
Das Verhalten betrifft alle SQL-Zugriffe, also auch die per ODBC/DRDA.
Wenn man eine Verbindung mit Debugmode öffnet, kann man entsprechende Hinweise auch im Joblog finden.

Ausserddem übertrage ich mit Dieter Benders Java-Transfer-Beispielen auch jede Menge Daten zwischen AS/400 und Oracle in beide Richtungen.
Ich verwende auch für den Insert prepared Statements.
Selbst nach 100.000 Inserts habe ich obigen Fehler noch nicht erhalten.

[RobertPic]

@Robert
Das Verhalten betrifft alle SQL-Zugriffe, also auch die per ODBC/DRDA.
Wenn man eine Verbindung mit Debugmode öffnet, kann man entsprechende Hinweise auch im Joblog finden.

Jein. Nur beim ersten Aufruf! Der Witz soll ja sein, dass das prepared Statement wiederverwendet wird. Das heißt das Statement bleibt offen (man sieht im ODBC-Job auch die offene Datei) und sollte (laut Java Theorie) auch keinen Syntaxcheck mehr durchlaufen.

Ausserddem übertrage ich mit Dieter Benders Java-Transfer-Beispielen auch jede Menge Daten zwischen AS/400 und Oracle in beide Richtungen.
Ich verwende auch für den Insert prepared Statements.
Selbst nach 100.000 Inserts habe ich obigen Fehler noch nicht erhalten.

Dieter Bender's Programm fragt auch brav ab, ob es das prepared Statement bereits gibt - es wird nur 1x erzeugt, offen gelassen und mehrmals verwendet.

if(out == null) {
   prepare();
}
out.execute();
...
void prepare() {
try {
   insert = props.getString("insert");
   out = con.prepareStatement(insert);
..

Genau diese Abfrage (out == null) fehlt(e) offensichtlich bei den Threadstellern. Damit wird jedesmal ein neues Statement erzeugt. Das Close innerhalb der Verarbeitungschleife ist da der falsche Lösungsweg. Das hat Ottersberg schon richtig erkannt.

/Robert

[BenderD]

- manchmal ist der Unterschied zwischen Loesung und zweitbester gering (if (ps == null))
- am besten ist hier natuerlich immer ein Connectionpool, der was taugt (eben nicht aus der Dollschachtel!)
- extended Package Support bringt meist fast nix und ist bei einigen Treiberversionen katastrophal (Bug !?)
- wenn die Hardware stimmt, dann brauchts den (oft abenteuerlichen) Mix aus Java und 1,86 GL nicht

D*B

[RobertPic]

- extended Package Support bringt meist fast nix und ist bei einigen Treiberversionen katastrophal (Bug !?)

Yep. I zitiere noch von der IBM Websphere FAQ:

In our opinion, the extendedDynamic/packageCache provides you no additional advantage when used with Application Server connection pooling and statement cache.

Sollte man wohl abschalten.

- wenn die Hardware stimmt, dann brauchts den (oft abenteuerlichen) Mix aus Java und 1,86 GL nicht

1.) Die Hardware ist nicht immer das Problem. Der 3GL/Javamix ist nicht immer freiwillig. Nur weil es mit Java schöner geht, kann ich ja trotzdem nicht über Nacht Code von zig Mannjahren in Java neuschreiben. Ich finde die stored Procedures noch die am wenigsten abenteuerliche Methode 3GL-Funktionen in Javaapplikationen zu nutzen.

2.) Wollte ich einmal die Performance der DB2 auf der AS/400 loben. Wenn man noch mit andere Datenbanken (spez. Oracle) arbeitet, kommt dieser Wunsch nicht oft vor..

/Robert

[BenderD]

die Datenbanken haben wohl unterschiedliche Charakteristik, DB2/400 ist kein Rennpferd, aber relativ gutmütig, wie Ackergäule halt sind. Was die Performance angeht ist DB2/400 nicht so schlecht, wenn man genug Hardware und parallel Database Feature hat (und die von manchen heiß geliebte SQE nict dazwischen bugt) und die Satzanzahlen unter 500 Millionen liegt, aber da wird die Luft acuh für andere dünn...

D*B

2.) Wollte ich einmal die Performance der DB2 auf der AS/400 loben. Wenn man noch mit andere Datenbanken (spez. Oracle) arbeitet, kommt dieser Wunsch nicht oft vor..

/Robert