Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2010
    Posts
    2

    Unanswered: Binding timestamp variables

    Hello ,
    I'm facing the following problem :
    DB2 Express V9.7.

    I'm using jdbc in order to query a certain table via java.
    In order to do so , I'm using PreparedStatement object , so I can cache queries in the DB level.
    Everything works fine , till the moment I'm binding timestamp variables.
    The funny thing is that binding only one distinct timestamp field works as expected (uses a predefined index) , the problem is when binding the same field twice.
    To be clearer :
    timestamp_field >= ? - Works fine.
    timestamp_filed <= ? - Works fine.

    timestamp_field between ? and ?
    OR
    timestamp_field >= ? and timestamp_field <= ? - Hangs.

    By concatenating the values (like in Statement) , it works fine as well :
    timestamp_field >= '2010-01-07-00.00.00' and timestamp_field <= '2010-01-07-23.59.59'

    Am I missing something here ?
    Any assistance will be appreciated.

    Thanks in advance ,
    Yossi.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    How about posting your code and the actual error you get?

    Andy

  3. #3
    Join Date
    Jan 2010
    Posts
    2
    Here's the code :
    Code:
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.Timestamp;
    import java.text.SimpleDateFormat;
    
    public class Db2Query {
    	public static void main(String[] args) {
    		Connection conn = null;
    		PreparedStatement stmt = null;
    		ResultSet rs = null;
    		
    		String sql = "select * from ( select rownumber() over() as rownumber_, event0.event_id from t_event event0_ left outer join t_event_type eventtype1_ on event0_.evt_name_hash=eventtype1_.ett_name_hash left outer join t_protocol_to_rule protocolto2_ on eventtype1_.ett_protocol_rule_id=protocolto2_.ptr_id left outer join t_application applicatio3_ on protocolto2_.ptr_app_id=applicatio3_.app_id where event0_.evt_parent_id=? " +
    				"and event0_.evt_start_time between ? and ? order by event0_.evt_start_time DESC FETCH FIRST 26 ROWS ONLY ) as temp_ where rownumber_ <= ?";
    		
    		try {
    			Class.forName("com.ibm.db2.jcc.DB2Driver");
    			conn = DriverManager.getConnection("jdbc:db2://localhost:50040/spdb2", "db2sp", "correl");
    			long t = System.currentTimeMillis();
    			System.out.println("1");
    			stmt = conn.prepareStatement(sql);
    			System.out.println("2");
    			stmt.setLong(1, 0);
    			Timestamp t1 = new Timestamp(110,0,7,0,0,0,0);
    			System.out.println("3");
    			SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd-HH.mm.ss");
    
    			String timestamp = simpleDateFormat.format(t1);
    			System.out.println(timestamp);
    
    			System.out.println("4");
    			stmt.setTimestamp(2, t1);
    			System.out.println("5");
    			Timestamp t2 = new Timestamp(110,0,7,23,59,59,0);
    
    
    			//String timestamp2 = simpleDateFormat.format(t2);
    			
    			stmt.setTimestamp(3, t2);
    			System.out.println("6");
    			stmt.setLong(4, 26);
    			System.out.println("7");
    			rs = stmt.executeQuery();
    			System.out.println("8");
    			System.out.println(" " + (System.currentTimeMillis()-t) + "ms");
    			
    			while (rs.next()) {
    				System.out.println(rs.getString(2));
    			}
    		}
    		catch (Exception e)	{
    			e.printStackTrace();
    		}
    		finally	{
    			try {stmt.close();}catch (Exception e){}
    			try {conn.close();}catch (Exception e){}
    		}
    	}
    }
    I'm not getting errors , just low performance.
    By running the same query with the timestamp fields inline , it will be executed fast.

    10x.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Have you generated access plans with the SQL using both constants (java.sql.Statement) and parameter markers (java.sql.PreparedStatement)? If so, what is the difference?

    Andy

Posting Permissions

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