We are using JDBC Adaptor 220.127.116.11 to query Oracle DB 10G. The problem we are facing is, the query returns 2500 rows. And for each record we iterate and invoke two services and finally aggregate the results. It works fine for 300 to max 500 records, but at 2500 the container timesout, which is set at 14 minutes. And the need is to return all the records to the UI. Can we do pagination with the adaptor, like, it sends 200 during the first request, next 200 in second request. I know its coupling ui elements within the service, but is it possible within adaptor, can it be done any other way?
This topic has been locked.
Pinned topic Pagination - JDBC Adaptor
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
SystemAdmin 110000D4XK67 Posts
Re: Pagination - JDBC Adaptor2010-06-24T17:24:39ZThis is the accepted answer. This is the accepted answer.Hello,
Generally the WebSphere Adapters are geared to sit within the middleware space within a stack. This means that we gear the operations for returning the whole message from the EIS/Database. Practically, there is no simple way to have the adapter 'page' the returned data. You will need to devise some mechanism of your own to perform this.
Due to the number of rows your fetching you might run into memory issues if you try to retrieve all 2500 rows at once. So you should figure out some mechanism that you can use to further limit either the BO size or the number of BOs.
One way could be to have one query that ONLY returns the primary keys and maybe one additional field for a description.
Then you could retrieve all 2500 rows / BOs with little concern about memory size.
Note: You will need to set the adapter to a higher threshold on max number of return objects or else you might face an exception. I believe the default for the max number of return objects is 100.
Then for each record you could then request the full row / BO to get the full details needed.
You could use the JDBC Adapter's option for "Query Business Objects" and devise 2 sql queries with parameters that would meet these needs. The sql queries should be judicial about the particular fields that your selecting and only gather the least data needed for processing.
For example the first sql query to retrieve all primary keys:
select primary_key, description from SCHEMA.TABLE where criteria = ? ...
Then the second sql query to retrieve only the detail BO:
select field_1, field_4, field_8 from SCHEMA.TABLE where primary_key = ?
Prasz 270002Y8G42 Posts
Re: Pagination - JDBC Adaptor2010-06-28T04:13:43ZThis is the accepted answer. This is the accepted answer.