Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2011
    Posts
    10

    Unanswered: fetch time issue on java application

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Dec 2011
    Posts
    10
    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

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    That is not what I asked for.

    Andy

  5. #5
    Join Date
    Nov 2011
    Posts
    334
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •