Use this technique to reduce memory requirements associated with BLOB data retrievals. This technique reduces the memory required for the client program and for inter-client communication buffers, at the expense of increased disk input/output time and capacity at the server. Therefore, if this technique is to be used, the tradeoff between memory requirements and disk input/output time must be evaluated.

Use of the BLOB data file output facility is specified in a WRITE specification of the SQL. A WRITE specification can be specified in either a cursor specification or a query specification. For details about WRITE specifications, see the HiRDB Version 8 SQL Reference.

The client can obtain as the SQL retrieval results only the IP address of the server, the BLOB data storage location that is set in the SQL, and the file name of the BLOB data. With this information, the client is able to identify the particular BLOB data stored at the server.

• When BLOB data files are no longer needed, they should be deleted by the user. The following point should be noted with regard to deleting a file (a file can be deleted unconditionally once the cursor has been closed or the transaction resolved):
  
  • If the file is deleted immediately after a FETCH and the BLOB data is the same as the result of the FETCH before the same cursor is searched, it may not be possible to re-create the file under the same file name. Because of this, you should make note of the file's original file name so that you will still be able to delete it after its name has been changed.
• The occurrence of an error or of rollback will not result in deletion of a BLOB data file that has been created. Files that are left undeleted occupy OS resources, such as disk space.
• When any of the following facilities is used, you should check that the disk has adequate free space:
  

  FETCH facility with arrays

  One execution of FETCH generates as many files as there are array elements.
  

  Block transfer facility

  The first execution of FETCH creates as many files as the number of rows involved in a block transfer. Subsequently, after the same number of FETCH operations as the number of block transfer rows, as many files as the number of block transfer rows are created each time FETCH is executed, and this process is repeated.

• If the file name is the same as other transactions or cursor searches, there is a potential for files to be overwritten or damaged. In such a case, for each transaction or cursor, you should change the directory name or file name in the file prefix to avoid duplicate names.

Shown as follows are examples of retrievals using the BLOB data file output facility.

(a) Retrieval from BLOB columns

Retrieve columns C1 and C2 from table T1. The system outputs the BLOB data from C1 to a file, and obtains the file name. Figure 6-16 shows this example retrieval using the BLOB data file output facility (retrieval of BLOB columns).

Figure 6-16 Example of a retrieval using the BLOB data file output facility (retrieval of BLOB columns)

(b) Retrieval of an abstract data type with the BLOB attribute

From table T2, retrieve column ADT1 that makes the CONTAINS function TRUE. In this case, the BLOB value resulting from passing the column value to an argument of the EXTRACTS function is output to a file, and the file name is obtained. In this example, all entries are retrieved. Figure 6-17 shows this example retrieval using the BLOB data file output facility (retrieval of an abstract data type with the BLOB attribute).

Figure 6-17 Example of a retrieval using the BLOB data file output facility (retrieval of an abstract data type with the BLOB attribute)