Topic
  • 2 replies
  • Latest Post - ‏2010-06-28T04:13:43Z by Prasz
Prasz
Prasz
2 Posts

Pinned topic Pagination - JDBC Adaptor

‏2010-06-24T08:05:51Z |
Hi,

We are using JDBC Adaptor 6.2.0.0 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?
Updated on 2010-06-28T04:13:43Z at 2010-06-28T04:13:43Z by Prasz
  • SystemAdmin
    SystemAdmin
    67 Posts

    Re: Pagination - JDBC Adaptor

    ‏2010-06-24T17:24:39Z  
    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 = ?
    Regards,
    -David
  • Prasz
    Prasz
    2 Posts

    Re: Pagination - JDBC Adaptor

    ‏2010-06-28T04:13:43Z  
    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 = ?
    Regards,
    -David
    Thanks David for your inputs.

    The UI Designs is totally out of our control, and we are close to completion of the project hence to change UI is not possible. We have decided to narrow down the search with more 'where' parameters.

    -Prashant