I want to confirm the best way to maintain performance, I have a SQL cursor which is called in using Java connection, The results are displayed on front end page by page, say if the query returned 100 records, each on front end displays 25 records. Now there is a feature given where in the results can be sorted for one particular page like say teh fields on page are custId, Place, Points, Date etc. Now the page can be sorted on each field.


I have to take a call where teh best performance will be write a stored procedure on DB2 send teh result sets to teh application as an open session porcedure, the result set at application server should be stored in temporary tables and the sorting should be done in the local cache at teh app server side.

The other way is to write the scroll insensitive DB2 cursor directly in java code and save the results in local cache.

Please see this query needs to be frequently run and on tables which gets updated very frequently and are large tables. at a time there are many diff users having sdiff session using the query on same table since it is read only query.

So my decision was to use the Java code withDB2 scroll insensitive cursor, please suggest , if there wud be any other feature of DB2 I can use for better query performance and database management

Thanks