Block fetch

Edit online

Block fetch pre-extracts rows and sends them in blocks to the requesting node. this process improves the performance of most queries by minimizing network traffic and by using data that is already on the node to accommodate subsequent queries.

Data Virtualization Manager server only uses block fetch with read-only queries. This type of query occurs in the following situations:
  • The SELECT statement has a FOR FETCH ONLY clause.
  • The SELECT statement has an ORDER BY clause.
  • The SELECT statement’s first FROM clause contains more than one table (or view).
  • The SELECT statement has the UNION or UNION ALL operator.
  • The SELECT statement has the DISTINCT keyword in the first SELECT clause.
  • The SELECT statement has a column function in the first SELECT clause.
  • The SELECT statement has a HAVING clause in the outside SELECT statement.
  • The SELECT statement has a GROUP BY clause in the outside SELECT statement.
  • The SELECT statement contains a subquery where the base object of the SELECT statement and the subquery is the same table.

By default, blocks hold 256 KB of data. This number is set by the Data Virtualization Manager server NETWORKBUFFERSIZE parameter. The number of blocks that are used is set by the Data Virtualization Manager server PREFETCH parameter. If Data Virtualization Manager server evaluates a query and determines that it is eligible for block fetch, it begins fetching rows into the prefetch buffers; however, no transmission of data takes place until the first (real) FETCH statement reaches the server.

Note: The maximum number of bytes that is sent for each transmission (for each VTAM SEND) is limited to 32 KB, although Data Virtualization Manager server’s internal prefetch buffers can be larger.

Use block fetch to improve the performance of queries that process many rows in a table.

Note: Using block fetch with a query in which no DESCRIBE (or PREPARE INTO) is performed in advance of fetching rows can degrade performance. Data Virtualization Manager server must internally perform a DESCRIBE to determine the types of data that may be returned.
In addition, depending on the type of isolation level that is used, remember the following considerations:
  • If the plan is bound with the Repeatable Read (RR) option and block fetch is used, many more pages can be locked for update than without block fetch, especially if the number of rows that are normally extracted by the query is small.
  • If the plan is bound with the Cursor Stability (CS) option and block fetch is used, data changes can take place between the time the data is extracted and the time that it is used by the application.