Scroll_sensitive

[Mercury]

Hello there,

We?ve performances issues and highly CPU consuming, when we use ResultSet.TYPE_SCROLL_SENSITIVE for a query which returns many results (>10000 rows).

Why this behavior?

Is there another way to move through the cursor in JDBC?

Here is an example of coding below.

Any insight ?
TIA

PS : we use the jt400.jar V5R4 provided by IBM directly on the machine.

package sql;

import java.sql.*;

public class SlowAndConsumingScrollableStatement {
    public static void main(String[] args) {
        Statement stmt=null;
        ResultSet rs=null;
        Connection con=null;
        try {
            System.out.println("The 1 argument is the machine name");
            System.out.println("The 2 is the user");
            System.out.println("The 3 is the pwd");
            String machine = "s44g5036";
            // default user
            String user = "dgc022";
            String pwd = "d022gc";

            if (args != null && args.length == 3) {
                machine = args[0];
                user = args[1];
                pwd = args[2];
            }
            // load the AS400 driver class in memory
            Class.forName("com.ibm.as400.access.AS400JDBCDriver");
            // set all the properties for the AS400 connexion
            java.util.Properties props = new java.util.Properties();
            props.put("naming", "system");
            props.put("user", user);
            props.put("password", pwd);
            props.put("date format", "iso");
            props.put("date separator", "-");
            // establish a connection with the AS400
            con = DriverManager.getConnection("jdbc:as400://" + machine, props);
            // set the isolation level : check the Connection Doc to use it
            // properly
            con.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
            con.setAutoCommit(false);
            String sqlStatement = "select duedate,techcreationdate from wwwtask order by org, duedate";

            stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery(sqlStatement);
            //simulation of block navigation 1-50-100-...
            rs.absolute(150);
            int i=1;
            while (rs.next() && (i % 50)!=0 ) {
                System.out.println(rs.getDate(1)+" "+rs.getDate(2));
                i++;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                stmt.close();
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
} 

[bryce4president]

Re: Scroll_sensitive

Hey Mercury, its better to use the native driver when running locally as opposed to the toolkit driver (jt400). The toolkit driver is best used when your database is remote to the webserver.

With that said,

When handling that many records its @#%@#%@#%@#% near impossible to get a happy response time. I've also wrestled with this recently and what I've done is figured out ways to break the ResultSet up into smaller ones.

I created a query package that I use to process my sql. I have 2 classes, one is a connection class, the other is a RS class for handling the queries. Here is my connection class, it has an example of both drivers because when I'm testing on my local machine I need to run against a remote DB, and when I'm actually in production I'm local. So instead of creating two classes and having to change it in the code all the time (which I would forget often) I created this puppy...

public class ConnDB 
{		
	
//	--------------------------------------------------------------------------------------------------------------------------//
//	- Open an AS400 database connection
//	--------------------------------------------------------------------------------------------------------------------------//

	public static java.sql.Connection opnAS400Conn() throws Exception 
	{
		
		

		try
		{	
			Properties prop = new Properties();
			prop.put("prompt", "false");
			prop.put("naming", "system");
			prop.put("date format", "iso");
			prop.put("time format", "hms");
			prop.put("libraries", "BPCSFFG, BPCSUSRFFG, BPCSUSRMM");
			java.sql.Connection cn = null;
			//Use with the toolkit for remote system driver
			Class.forName("com.ibm.db2.jdbc.app.DB2Driver");
			cn = DriverManager.getConnection("jdbc:db2:*local",prop);
			  // Return the connection object
			return cn;
		}
		catch(Exception e)
		{
			String url = "remoteDomain";
			String libraryName = "BPCSFFG, BPCSUSRFFG, BPCSUSRMM";
			String userName = "userName";
			String password	= "YOURFIRED";
			//Used toolkit when accessing remote system
			Class.forName("com.ibm.as400.access.AS400JDBCDriver");
			java.sql.Connection cn = null;

			//  Create the database connection
			cn = DriverManager.getConnection(
					"jdbc:as400:" + url + "; translate binary=true; naming=system; " +
					"extended metadata=true;libraries=" + libraryName + "; date format=iso",
					userName,
					password);

			// Return the connection object
			return cn;
		}
	}
}

I hope this helps...if you have any more questions, please let me know...

P.S. Could you give some better clarification as to what you are trying to accomplish? Maybe there is a better way to do what you want to do...

[Mercury]

Re: Scroll_sensitive

Hey Bryce, thanks for this fast reply.

What I'm trying to do is what the green screen SQL does when it displays the last records of a huge file.

STRSQL

SELECT * FROM HUGEFILE

Type B and you get instant the last records.

Actually I am trying to do smthg that will seem instant to my users.

Again any insights ?

[bryce4president]

Re: Scroll_sensitive

So when they get the records they will be in lets say, asc order. And when they type B they will go to the bottom. You could, when B, make an ajax call and load the last n records. n being the number to display at one time. I'm taking it that this is a web application? In the web app I've been working on I'm using jQuery and jqGrid. jQuery is a javascript library that makes ajax and dom manipulation a snap. jqGrid is a plugin javascript library for jQuery. It does tables and subTables.

Using jqGrid would allow you to easily do paging. And, you can give them a button that will send them to the end, fetching the last n records.

Are you trying to do more of a green screen emulation in a browser?

[Mercury]

Re: Scroll_sensitive

Forget it. I gave up. Thanks for your time anyway.