fetch time issue on java application

automona · #1 (**permalink**) 01-26-12, 10:34

Dear All,

I found some queries which return result 100 rows take time more than 10 second
but those queries have execute time just 1 second or less than 1 second.
All queries run on java application and connect database by jdbc.

Database Information: DB2 version 9.7.3 on AIX 6.1
I think no network issue because I tried to ftp file between server its so fast.
Can Anyone help me? Please

Thanks

ARWinner · #2 (**permalink**) 01-26-12, 10:39

Can you post the queries and the java code that is processing the result sets? Also post the DDL for all table/objects in the query.

What happens if you run the query through the CPL on the same machine?

Andy

automona · #3 (**permalink**) 01-27-12, 03:35

example some query on sevice xml file run the query through jdbc on java application

Code:

<?xml version="1.0" encoding="utf-8"?>
<query-collection>
	<sql-service name='list_getid' datasource='telcon' type='simple'>
		<description>List ID</description>
		<result-name>#resultSet</result-name>
		<query><![CDATA[
                                   SELECT id_type ,id_num, id_name, primary_type,primary_id, flag_no, des1,des2, des3, des4, des5
                                   		  FROM LIST_CODE
                                   		 WHERE id_type = '?id_type'
                                   		   AND id_num <> '?id_num'
                                   	ORDER BY lpad(nvl(sort_key, '?id1'),'?id2','?id3'), id_num
		]]></query>
	</sql-service>
</query-collection>

process

Code:

[0001][09:22:16 980][   20][    4] GET-CONNECTION DataSource.getConnection[java:/cmPool]
[0002][09:22:16 982][    2][    4] SQL-PREPARE_STMT{0}
                                   SELECT id_type ,id_num, id_name, primary_type,primary_id, flag_no, des1,des2, des3, des4, des5
                                   		  FROM LIST_CODE
                                   		 WHERE id_type = '$'
                                   		   AND id_num <> '$'
                                   	ORDER BY lpad(nvl(sort_key, '$'),#,'$'), id_num
                                    param1:[MAP_CD,000000,0,0,0,]

[0003][09:22:16 989][    7][    4] SQL-EXECUTE-QUERY{0} [7 ms]
                                   param1:[MAP_CD,000000,0,0,0,] param2:[]

[0004][09:22:28 052][11,063][2,014] FETCH[199/199] [11,063 ms]
[0005][09:22:28 052][    0][2,014] CLOSE-CONNECTION
[0006][09:22:29 162][1,110][    0] FILE-WOPEN requestmon.log
[0007][09:22:29 162][    0][    0] END

result 199 rows
execute time 0.007 sec
fetch time 11.063 sec

I tried to run this query through the CPL on the same machine take time just 0.18 sec

Thanks

ARWinner · #4 (**permalink**) 01-27-12, 09:12

That is not what I asked for.

Andy

fengsun2 · #5 (**permalink**) 01-27-12, 11:53

i dont know which tools you used to get this result
result 199 rows
execute time 0.007 sec
fetch time 11.063 sec

plz run the query through the db2batch command and put the output here
it will tell you the actually execute time and fetch time (also compile time)

put the sql in sqlfile and try this
db2batch -d <dbname> -f sqlfile -i complete -o r 200 p 5 e yes

Internet Services

Web Development

Digital Marketing

Consumer Tech