JDBC mediator performance considerations and limitations

Use these tips to help determine if a JDBC Data Mediator Service suits the requirements of our application serving environment.

(iSeries) Driver requirements for using SDO to access DB2 UDB for iSeries

Because the SDO JDBC Mediator takes advantage of the ResultSetMetaData interface in JDBC 2.0, it must use JDBC providers that are fully compliant with that specification. Both the IBM Developer Kit for Java JDBC driver (also known as the DB2 UDB for iSeries Native driver) and the IBM Toolbox for Java JDBC driver meet this criteria for JDBC access to DB2 UDB for iSeries. For performance reasons, however, neither of these drivers have default settings to return all the information that the mediator requires. We must set a connection property on the JDBC provider or data source that corresponds to each driver for it to return full ResultSetMetaData data sets.

The property we use varies according to how your driver implementation acquires database connections.

• If our driver gets connections through the DriverManager class, set the JDBC provider URL property extended metadata to true: extended metadata=true. In this scenario, both the IBM Developer Kit for Java and IBM Toolbox for Java JDBC drivers require the same setting on the JDBC provider object.

• If the application acquires connections through a data source, set a different custom property on the data source, depending on the driver that we use:

  • For the IBM Toolbox for Java JDBC driver, set the custom property extendedMetaData to true.

  • For the IBM Developer Kit for Java JDBC driver, set the custom property returnExtendedMetaData to true.

Miscellaneous database limitations

• Sybase before Version 12.5.1 does not support in-line queries in the "from" clause, and therefore does not support multiple table DataGraphs with filters. To use the Service Data Object in WebSphere Application Server use Sybase Version 12.5.1.

• The Informix Dynamic Server does not support sub-selects, which are needed for multiple table graphs. Use Informix Extended Parallel Server.
• Oracle 8i does not support the ANSI join syntax. The mediator in multiple table cases requires Oracle 10g or later.

General performance recommendations

• Evaluate if your target projects are well suited to these technologies. In general, projects that are read-intensive and require disconnected data are good candidates.
• Limit the number of tables in the metadata. One or two is best because relationships, with respect to filters, become ambiguous when graphs have many branches.
• Work with small data sets as often as possible to avoid consuming excessive amounts of memory within the applications. We can limit the amount of data returned to the SDO by specifying filters in the metadata objects or using paging.

• For web applications, if the DataGraph is not too large and is to be reused later, store it in the user session.