Package-based continuous block fetch

Package-based continuous block fetch can significantly improve application elapsed time and processor costs on the requester and server for queries that retrieve large amounts of data from remote sites.

Introductory concepts

Package-based continuous block fetch can provide a performance advantage for an application with the following characteristics:

  • Queries in the application that access remote sites are read-only queries.
  • The package contains no remote INSERT, UPDATE, DELETE or MERGE statements.

Package-based continuous block fetch is preferable to SQL-based continuous block fetch because:

  • Package-based continuous block fetch can be enabled on an application-by-application basis.
  • Package-based continuous block fetch is easier to enable. You do not need to set the subsystem parameters that control the maximum number of query blocks that are expected and allowed. If the remote SQL statements in the application are all read-only queries, you do not need to update the application. Enabling an application or stored procedure for package-based continuous block fetch involves only binding the package with the DBPROTOCOL(DRDACBF) option on the requester and servers.

If you also enable SQL-based continuous block fetch, applications that are enabled for package-based continuous block fetch use only package-based continuous block fetch.

When package-based continuous block fetch is enabled, and a remote SQL statement is initiated, the requester and server perform the following actions:

  1. The requester checks the type of SQL statement and the server for which the SQL statement is intended.
    • If the SQL statement is a dynamic UPDATE, INSERT, DELETE, or MERGE statement, the requester returns an SQL error.
    • If the SQL statement is not a dynamic UPDATE, INSERT, DELETE, or MERGE statement, and the server for which the SQL statement is intended is not Db2 for z/OS® Version 11 or later (for example, Db2 for z/OS Version 10 or earlier, or Db2 for Linux®, UNIX, and Windows), the statement is processed as if package-based continuous block fetch is not set.
    • If the SQL statement is not a dynamic UPDATE, INSERT, DELETE, or MERGE statement, and the server for which the SQL statement is intended is Db2 for z/OS Version 11 or later, processing continues with the next step.
  2. The requester opens a separate connection for each statement.
  3. The requester sends the SQL statement on the connection, and indicates to the server that the statement is enabled for package-based continuous block fetch.
  4. The server accepts each connection, associates a thread with each connection, and processes the SQL statement independently of the application.
  5. If the statement is an SQL CALL statement or a query, the server processes the statement until all data is returned to the requester. It continues to generate and send query blocks on the network, without further prompting, until there is no more data to return.
  6. During execution of the SQL statement on the server, if the statement causes a unit of recovery to be created (that is, the statement performs some type of update operation), the server returns an SQL error.
  7. The requester receives a query block. As the application fetches data, when all contents of the query block have been processed by the requester, the requester receives the next query block from the network without prompting the server.
  8. When all data has been returned to the requester, the connection is closed. If subsystem parameter CMTSTAT is set to INACTIVE, the thread is immediately pooled. Otherwise, the thread is deallocated. The application does not need to issue a RELEASE CONNECTION statement to close the connection, or issue a COMMIT or ROLLBACK statement to allow the thread to be pooled.

The process is similar if the application connects to a remote server, and then requests data from a second server. The first server indicates to the second server that the requester can handle package-based continuous block fetch. The second server returns data to the first server using package-based continuous block fetch, and the first server uses package-based continuous block fetch to forward the data to the requester.

If an application calls an external stored procedure that is enabled for package-based continuous block fetch and returns result sets, the stored procedure returns the result set data to the requester without using package-based continuous block fetch. However, if the stored procedure opens cursors to return data only to itself, the data is returned using package-based continuous block fetch.