Performance considerations for LOB processing
When developing federated applications that fetch and process LOB data, application designers and database administrators need to understand how LOB processing affects performance.
When an application fetches data from a federated data source, the federated server must fetch the data into its own application buffers before sending the data to the application. Because LOBs are not processed in a buffer pool, the LOB data must first pass through a temporary table space defined for the federated server. To help improve performance and reduce resource consumption, application designers should only materialize LOB data when necessary.
Similarly, when the federated server updates remote LOB data, the data must pass through a temporary table space assigned to the federated server before it is passed to the data source.
Transient LOBs use the temporary table space assigned to the federated server. Therefore, database administrators might need to increase the size of this temporary table space to ensure that the working area is sufficient for processing the LOBs.
Recommendation: To maximize performance when working with LOBs, define the temporary table space as System Managed (SMS) and ensure that the temporary table space is located on disks with a high I/O bandwidth.
Using the Call Level Interface to access federated LOBs
- The SQLFetch API fetches the LOB from the federated server or data source into the application buffers in a single operation.
- The SQLGetData API fetches the LOB a chunk at a time and can require repeated calls to the API to fetch the entire LOB into the application buffers.
Recommendation: For optimal performance, use the SQLGetData API when fetching LOBs through a federated server.
The federated server supports the SQLExecute and SQLPutData APIs for updating LOB data. The SQLExecute API updates the LOB data in a single operation, whereas the SQLPutData API can require repeated calls to send all of the LOB data from the application buffers to the server. Each API performs at the same level in a federated environment.
Trusted and fenced wrappers
Nicknames created for wrappers defined as trusted or fenced perform equally when fetching or updating LOBs.