SQL Select Statement - Execute dauert sehr lange

**max40** · 06-02-15, 10:28

Moin,

ich führe mehrere Statements hintereinander aus.
- Connectionpool getConnection
- connection create statement
- statement execute
- statement close
- connection create statement
- stament execute
- statement close
- connection close
Ab dem 2. Statement braucht er für das execute sehr lange. Wobei da nichts groß gemacht wird. Hin und wieder gehts es auch mal sehr zügig.
Ich habe mal das Log angeschaltet und folgendes kommt raus:

(Die xxxx = as400: ConnectionPool com.ibm.as400.access.AS400JDBCConnectionPool@1f1d1 f1d (522002205) )

Code:

Toolbox for Java - Open Source Software, JTOpen 8.1, codebase 5770-SS1 V7R2M0.00 built=20131021 @J5
java.home=/QOpenSys/QIBM/ProdData/JavaVM/jdk60/64bit/jre java.vm.version=2.4 java.version=1.6.0 os.name=OS/400 os.version=V7R1M0
...

Thread[main,5,main] 09:15:04:475  Getting version level.
Thread[main,5,main] 09:15:04:475  Version level:  7
Thread[main,5,main] 09:15:04:475  Bidi layout transformation of SQL meta-data: SELECT FELD1, FELD2, FELD3, FELD4 FROM LIB/DATEI WHERE  FELDX = 111111 AND FELDY between 201101 AND 201112 ORDER BY 1
Thread[main,5,main] 09:15:04:475  Bidi layout transformation from 5 to 5 of SQL statement: SELECT FELD1, FELD2, FELD3, FELD4 FROM LIB/DATEI WHERE  FELDX = 111111 AND FELDY between 201101 AND 201112 ORDER BY 1
Thread[main,5,main] 09:15:04:476  as400: PreparedStatement STMT0002 (915355279)  open. Parent: Connection S4405619 (486612225) .
Thread[main,5,main] 09:15:04:477  as400: PreparedStatement STMT0002 (915355279) : Escape processing = "true".
Thread[main,5,main] 09:15:04:477  as400: PreparedStatement STMT0002 (915355279) : Fetch direction = "1000".
Thread[main,5,main] 09:15:04:477  as400: PreparedStatement STMT0002 (915355279) : Fetch size = "0".
Thread[main,5,main] 09:15:04:477  as400: PreparedStatement STMT0002 (915355279) : Max field size = "0".
Thread[main,5,main] 09:15:04:477  as400: PreparedStatement STMT0002 (915355279) : Max rows = "0".
Thread[main,5,main] 09:15:04:477  as400: PreparedStatement STMT0002 (915355279) : Query timeout = "0".
Thread[main,5,main] 09:15:04:477  as400: PreparedStatement STMT0002 (915355279) : Result set concurrency = "1007".
Thread[main,5,main] 09:15:04:477  as400: PreparedStatement STMT0002 (915355279) : Result set holdability = "1".
Thread[main,5,main] 09:15:04:477  as400: PreparedStatement STMT0002 (915355279) : Result set type = "1003".
Thread[main,5,main] 09:15:04:477  as400: PreparedStatement STMT0002 (915355279) : Behavior Override = "0".
Thread[main,5,main] 09:15:04:477  as400: PreparedStatement STMT0002 (915355279) : Data to correlate statement with cursor Cursor CRSR0002 (924333848) .
Thread[main,5,main] 09:15:04:477  as400: PreparedStatement STMT0002 (915355279) : isjvm16Synchronizer=false.
Thread[main,5,main] 09:15:04:477  as400: PreparedStatement STMT0002 (915355279) : Preparing [SELECT FELD1, FELD2, FELD3, FELD4 FROM LIB/DATEI WHERE  FELDX = 111111 AND FELDY between 201101 AND 201112 ORDER BY 1].
Thread[main,5,main] 09:15:04:477  com.ibm.as400.access.BidiConversionProperties@48104810:Setting bidi string type:0
Thread[main,5,main] 09:15:04:477  Converting string to byte array for ccsid: 1141
Thread[main,5,main] 09:15:04:477  Destination byte array for ccsid: 1141
Thread[main,5,main] 09:15:04:477  com.ibm.as400.access.BidiConversionProperties@4ac44ac4:Setting bidi string type:0
Thread[main,5,main] 09:15:04:477  Converting string to byte array for ccsid: 1141
Thread[main,5,main] 09:15:04:477  Destination byte array for ccsid: 1141
Thread[main,5,main] 09:15:04:477  send(): send request...
Thread[main,5,main] 09:15:04:477  Data stream sent (connID=1767926112) ...
Thread[main,5,main] 09:15:04:478  com.ibm.as400.access.BidiConversionProperties@4ebb4ebb:Setting bidi string type:0
Thread[main,5,main] 09:15:04:478  Converting string to byte array for ccsid: 13488
Thread[main,5,main] 09:15:04:479  Destination byte array for ccsid: 13488
Thread[main,5,main] 09:15:04:479  send and receive(): ...
Thread[main,5,main] 09:15:04:479  send(): send request...
Thread[main,5,main] 09:15:04:479  Data stream sent (connID=1767926112) ...
Thread[main,5,main] 09:15:04:480  AS400Server.receive
Thread[main,5,main] 09:15:04:480  receive(): Reply not found. Waiting...
Thread[AS400 Read Daemon [system:localhost;job:173298/QUSER/QZDASOINIT],5,main] 09:15:04:670  Data stream data received (connID=1767926112) ...
Thread[AS400 Read Daemon [system:localhost;job:173298/QUSER/QZDASOINIT],5,main] 09:15:04:670  Data stream data received (connID=1767926112) ...
Thread[AS400 Read Daemon [system:localhost;job:173298/QUSER/QZDASOINIT],5,main] 09:15:04:671  run(): Adding reply:  7
Thread[AS400 Read Daemon [system:localhost;job:173298/QUSER/QZDASOINIT],5,main] 09:15:04:671  run(): Notifying threads.
Thread[AS400 Read Daemon [system:localhost;job:173298/QUSER/QZDASOINIT],5,main] 09:15:04:671  run(): Threads notified.
Thread[AS400 Read Daemon [system:localhost;job:173298/QUSER/QZDASOINIT],5,main] 09:15:04:671  run(): Waiting for reply...
Thread[main,5,main] 09:15:04:672  receive(): Valid reply found:  7
Thread[main,5,main] 09:15:04:674  decompressRLE() sourceLength: 1003
Thread[main,5,main] 09:15:04:674  decompressRLE() destinationLength: 1330
Thread[main,5,main] 09:15:04:675  Received empty parameter marker format.
Thread[main,5,main] 09:15:04:687  as400: PreparedStatement STMT0002 (915355279) : Prepared STMT0002*, SQL Statement -->[SELECT FELD1, FELD2, FELD3, FELD4 FROM LIB/DATEI WHERE  FELDX = 111111 AND FELDY between 201101 AND 201112 ORDER BY 1].
Thread[main,5,main] 09:15:04:690  send and receive(): ...
Thread[main,5,main] 09:15:04:690  send(): send request...
Thread[main,5,main] 09:15:04:690  Data stream sent (connID=1767926112) ...
Thread[main,5,main] 09:15:04:690  AS400Server.receive
Thread[main,5,main] 09:15:04:690  receive(): Reply not found. Waiting...

--- Was passiert hier ? ? ? ? --- 

Thread[ACPM,5,main] 09:20:04:368  as400: xxxx : ConnectionPool cleanup....
Thread[ACPM,5,main] 09:20:04:368  as400: xxxx :    MaxLifeTime: 86400000.
Thread[ACPM,5,main] 09:20:04:368  as400: xxxx :    MaxUseTime: -1.
Thread[ACPM,5,main] 09:20:04:368  as400: xxxx :    MaxInactivity: 3600000.
Thread[ACPM,5,main] 09:20:04:368  as400: xxxx :    PretestConnections: false.
Thread[ACPM,5,main] 09:20:04:369  as400: xxxx : Idle Connections: 0.
Thread[ACPM,5,main] 09:20:04:369  as400: xxxx : Active Connections: 1.
Thread[ACPM,5,main] 09:20:04:369  as400: xxxx : Dead Connections: 0.
Thread[ACPM,5,main] 09:20:04:369  as400: xxxx : com.ibm.as400.access.AS400JDBCPooledConnection@1d011d01.
Thread[ACPM,5,main] 09:20:04:369  as400: xxxx : ConnectionPool cleanup finished..
Thread[ACPM,5,main] 09:20:04:369  as400: xxxx :    Idle Connections: 0.
Thread[ACPM,5,main] 09:20:04:369  as400: xxxx :    Active Connections: 1.
Thread[ACPM,5,main] 09:20:04:369  as400: xxxx :    Dead Connections: 0.
Thread[ACPM,5,main] 09:25:04:403  as400: xxxx : ConnectionPool cleanup....
Thread[ACPM,5,main] 09:25:04:404  as400: xxxx :    MaxLifeTime: 86400000.
Thread[ACPM,5,main] 09:25:04:404  as400: xxxx :    MaxUseTime: -1.
Thread[ACPM,5,main] 09:25:04:404  as400: xxxx :    MaxInactivity: 3600000.
Thread[ACPM,5,main] 09:25:04:404  as400: xxxx :    PretestConnections: false.
Thread[ACPM,5,main] 09:25:04:404  as400: xxxx : Idle Connections: 0.
Thread[ACPM,5,main] 09:25:04:404  as400: xxxx : Active Connections: 1.
Thread[ACPM,5,main] 09:25:04:404  as400: xxxx : Dead Connections: 0.
Thread[ACPM,5,main] 09:25:04:404  as400: xxxx : com.ibm.as400.access.AS400JDBCPooledConnection@1d011d01.
Thread[ACPM,5,main] 09:25:04:404  as400: xxxx : ConnectionPool cleanup finished..
Thread[ACPM,5,main] 09:25:04:404  as400: xxxx :    Idle Connections: 0.
Thread[ACPM,5,main] 09:25:04:404  as400: xxxx :    Active Connections: 1.
Thread[ACPM,5,main] 09:25:04:404  as400: xxxx :    Dead Connections: 0.
Thread[ACPM,5,main] 09:30:04:437  as400: xxxx : ConnectionPool cleanup....
Thread[ACPM,5,main] 09:30:04:439  as400: xxxx :    MaxLifeTime: 86400000.
Thread[ACPM,5,main] 09:30:04:439  as400: xxxx :    MaxUseTime: -1.
Thread[ACPM,5,main] 09:30:04:439  as400: xxxx :    MaxInactivity: 3600000.
Thread[ACPM,5,main] 09:30:04:439  as400: xxxx :    PretestConnections: false.
Thread[ACPM,5,main] 09:30:04:439  as400: xxxx : Idle Connections: 0.
Thread[ACPM,5,main] 09:30:04:439  as400: xxxx: Active Connections: 1.
Thread[ACPM,5,main] 09:30:04:439  as400: xxxx: Dead Connections: 0.
Thread[ACPM,5,main] 09:30:04:439  as400: xxxx: com.ibm.as400.access.AS400JDBCPooledConnection@1d011d01.
Thread[ACPM,5,main] 09:30:04:440  as400: xxxx: ConnectionPool cleanup finished..
Thread[ACPM,5,main] 09:30:04:440  as400: xxxx:    Idle Connections: 0.
Thread[ACPM,5,main] 09:30:04:440  as400: xxxx:    Active Connections: 1.
Thread[ACPM,5,main] 09:30:04:440  as400: xxxx:    Dead Connections: 0.
Thread[ACPM,5,main] 09:35:04:474  as400: xxxx: ConnectionPool cleanup....
Thread[ACPM,5,main] 09:35:04:474  as400: xxxx:    MaxLifeTime: 86400000.
Thread[ACPM,5,main] 09:35:04:474  as400: xxxx:    MaxUseTime: -1.
Thread[ACPM,5,main] 09:35:04:474  as400: xxxx:    MaxInactivity: 3600000.
Thread[ACPM,5,main] 09:35:04:474  as400: xxxx:    PretestConnections: false.
Thread[ACPM,5,main] 09:35:04:475  as400: xxxx: Idle Connections: 0.
Thread[ACPM,5,main] 09:35:04:475  as400: xxxx: Active Connections: 1.
Thread[ACPM,5,main] 09:35:04:475  as400: xxxx: Dead Connections: 0.
Thread[ACPM,5,main] 09:35:04:475  as400: xxxx: com.ibm.as400.access.AS400JDBCPooledConnection@1d011d01.
Thread[ACPM,5,main] 09:35:04:475  as400: xxxx: ConnectionPool cleanup finished..
Thread[ACPM,5,main] 09:35:04:475  as400: xxxx:    Idle Connections: 0.
Thread[ACPM,5,main] 09:35:04:475  as400: xxxx:    Active Connections: 1.
Thread[ACPM,5,main] 09:35:04:475  as400: xxxx:    Dead Connections: 0.
Thread[ACPM,5,main] 09:40:04:508  as400: xxxx: ConnectionPool cleanup....
Thread[ACPM,5,main] 09:40:04:508  as400: xxxx:    MaxLifeTime: 86400000.
Thread[ACPM,5,main] 09:40:04:508  as400: xxxx:    MaxUseTime: -1.
Thread[ACPM,5,main] 09:40:04:508  as400: xxxx:    MaxInactivity: 3600000.
Thread[ACPM,5,main] 09:40:04:508  as400: xxxx:    PretestConnections: false.
Thread[ACPM,5,main] 09:40:04:508  as400: xxxx: Idle Connections: 0.
Thread[ACPM,5,main] 09:40:04:509  as400: xxxx: Active Connections: 1.
Thread[ACPM,5,main] 09:40:04:509  as400: xxxx: Dead Connections: 0.
Thread[ACPM,5,main] 09:40:04:509  as400: xxxx: com.ibm.as400.access.AS400JDBCPooledConnection@1d011d01.
Thread[ACPM,5,main] 09:40:04:509  as400: xxxx: ConnectionPool cleanup finished..
Thread[ACPM,5,main] 09:40:04:509  as400: xxxx:    Idle Connections: 0.
Thread[ACPM,5,main] 09:40:04:509  as400: xxxx:    Active Connections: 1.
Thread[ACPM,5,main] 09:40:04:509  as400: xxxx:    Dead Connections: 0.
Thread[ACPM,5,main] 09:45:04:544  as400: xxxx: ConnectionPool cleanup....
Thread[ACPM,5,main] 09:45:04:545  as400: xxxx:    MaxLifeTime: 86400000.
Thread[ACPM,5,main] 09:45:04:545  as400: xxxx:    MaxUseTime: -1.
Thread[ACPM,5,main] 09:45:04:545  as400: xxxx:    MaxInactivity: 3600000.
Thread[ACPM,5,main] 09:45:04:545  as400: xxxx:    PretestConnections: false.
Thread[ACPM,5,main] 09:45:04:545  as400: xxxx: Idle Connections: 0.
Thread[ACPM,5,main] 09:45:04:545  as400: xxxx: Active Connections: 1.
Thread[ACPM,5,main] 09:45:04:545  as400: xxxx: Dead Connections: 0.
Thread[ACPM,5,main] 09:45:04:545  as400: xxxx: com.ibm.as400.access.AS400JDBCPooledConnection@1d011d01.
Thread[ACPM,5,main] 09:45:04:545  as400: xxxx: ConnectionPool cleanup finished..
Thread[ACPM,5,main] 09:45:04:545  as400: xxxx:    Idle Connections: 0.
Thread[ACPM,5,main] 09:45:04:545  as400: xxxx:    Active Connections: 1.
Thread[ACPM,5,main] 09:45:04:545  as400: xxxx:    Dead Connections: 0.
Thread[AS400 Read Daemon [system:localhos;job:173298/QUSER/QZDASOINIT],5,main] 09:46:25:183 Data stream data received (connID=1767926112) ...
Thread[AS400 Read Daemon [system:localhost;job:173298/QUSER/QZDASOINIT],5,main] 09:46:25:183  Data stream data received (connID=1767926112) ...

Was macht er da? Muss ich noch irgendwelche Einstellungen setzen? PTFs?

Danke+Gruß
Max

Thema: SQL Select Statement - Execute dauert sehr lange

Thread Tools

Bewerten Sie diesen Thema

Display

Threaded View