Nonstop Database, HiRDB Version 9 UAP Development Guide
![[Contents]](FIGURE/CONTENT.GIF)
![[Index]](FIGURE/INDEX.GIF)
![[Back]](FIGURE/FRONT.GIF)
(1) Overview of the block transfer facility
Block transfer means that the HiRDB system sends data to a HiRDB client in units of a specified number of rows. The block transfer facility is useful when a HiRDB client accesses the HiRDB system to retrieve a large amount of data.
The following figure provides an overview of the block transfer facility.
Figure 4-58 Overview of block transfer facility
![[Figure]](FIGURE/ZU040401.GIF)
(2) Usage method
The block transfer facility is executed when both of the following conditions are satisfied:
- When at least two values are specified in client environment definition PDBLKF or when at least one value is specified in PDBLKBUFFSIZE
- When the FETCH statement is specified (except when one of the following conditions applies)
- Update using a cursor
- Retrieval involving a BLOB-type selection expression
- Retrieval in which the value of client environment definition PDBINARYBLKF is NO and involving a BINARY-type selection expression with a definition length of 32,001 bytes or more
- Retrieval that uses a BLOB locator type or BINARY locator type variable to accept results and that uses a holdable cursor
(3) Specification of communication buffer size between server and client
You can use client environment definition PDBLKBUFFSIZE to specify the communication buffer size between the server and the client.
For retrievals in which the number of rows to be extracted (PDBLKF specification value) is large, specifying PDBLKBUFFSIZE suppresses the allocation of a communication buffer memory larger than the value specified in the server. However, a communication buffer memory for transferring one row is allocated.
For details about the calculation equation for the communication buffer size between the server and the client, see Formula for size of memory required during block transfer or array FETCH in the HiRDB Version 9 Installation and Design Guide.
(4) Number of rows transferred in one transmission
The table below shows the number of rows transferred in one transmission when the block transfer facility is used.
PDBLKF specification value |
PDBLKBUFFSIZE specification value |
0 |
1 or higher |
1 |
Block transfer facility does not apply. |
Number of rows = MIN(X, 4096)#
- X:
- The number of rows becomes the maximum value (number of rows that can be stored in the specified buffer size) of n that satisfies the following condition expression. However, if (a - b) < ci, then the number of rows becomes 1 (i is 1).
n
(a - b) ci (unit: bytes)
i=1
ci: Data length of the i-th row in the search results received with the FETCH statement
a: Specified buffer size (PDBLKBUFFSIZE value x 1024)
b: Header information and other information (864 + 22 x d + 2 x e)
The d and e variables in the calculation expression for b are described below.
d: Number of retrieval items specified in the SELECT clause
e: Number of BINARY-type selection items in retrieval items specified in the SELECT clause |
2 or higher |
Number of rows = PDBLKF value |
Number of rows = MIN(X, Y)*
X: Number of rows that can be stored in specified buffer size (same as X shown above)
Y: PDBLKF value |
- #: Certain SQL statements may be able to transfer more than the calculated number of rows.
- If one of the following events occurs, HiRDB interrupts retrieval processing and returns the data that was retrieved to that point:
- A warning error occurs during retrieval processing. (HiRDB returns the warning information and the data that was retrieved to that point.)#
- During a search via a list, a row that was present when the list was created is deleted or an attribute value is deleted or updated. (HiRDB returns return code information (SQLCODE=+110) that indicates the event and the data that was retrieved to that point.)
#: HiRDB may not interrupt retrieval processing even if a warning error occurs. If HiRDB does not interrupt processing, it continues retrieval processing until the specified number of rows and returns all warning error information that occurred during the retrieval, and the retrieved data.
- The block transfer facility can shorten the retrieval time because it decreases the communication overhead by transferring a large number of rows at a time. However, the facility must be used with caution because it increases the amount of required memory. When client environment definition PDBLKBUFFSIZE is specified, the memory size used for the communication buffer is held below a fixed value. However, if the value is too small, the block transfer facility becomes ineffective because the number of communications cannot be reduced.
- When the block transfer facility is being used and the search results of one cursor are received with multiple FETCH statements, specify the same embedded variable or embedded variables with the same attribute in each of those FETCH statements. If you try to receive the search results with embedded variables having different attributes, an error occurs.
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.