2.22 WRITE specification

Organization of this section
(1) Function
(2) Format
(3) Operands
(4) Rules on the results of a WRITE specification
(5) Rules on the file to which BLOB data is to be output
(6) Common rules
(7) Notes
(8) Example

(1) Function

A WRITE specification outputs BLOB data to a file at the single server or front-end server unit and returns the IP address and the filename of the output unit.

(2) Format

WRITE (output-BLOB-value, file-prefix, file-output-option)

(3) Operands

Specifies any of the following (note that the data type of output-BLOB-value must be the BLOB type):

The following rules apply to output-BLOB-value:

  1. Embedded variables and ? parameters can be specified in the argument for the function call or in the second or third argument of the SUBSTR scalar function that is specified in output-BLOB-value.
  2. If a BLOB column is specified in output-BLOB-value, the BLOB column cannot by itself be specified in a selection expression or in the output-BLOB-value for another WRITE specification.
    Examples of invalid specifications are shown below:

    SELECT WRITE(C1,***),C1 FROM ***
    SELECT WRITE(C1,***),WRITE(C1,***) *** FROM ***

  3. If a FOR READ ONLY clause is specified, only a column specification can be specified in output-BLOB-value.
  4. A subquery cannot be specified in an argument of a function call or in an argument of the scalar function SUBSTR specified as an output BLOB value.

Specifies the prefix part of the file name that HiRDB will assemble. The data type of file-prefix must be VARCHAR and the length must not exceed 222 bytes.

The following can be specified in a file-prefix:

The following rules apply to file-prefix:

  1. Specify an absolute path name, including directory, that is valid at the unit for the single-server or front-end server to which HiRDB is connected. Additionally, the HiRDB administrator must grant the file user the privileges (access type: full control) for performing all types of operations on the directory. In the case of the UNIX edition, the HiRDB administrator must grant the file user the privileges to read, write, and search within the directory.
  2. For the file separator to be specified as a file prefix, specify a forward slash (/) if the HiRDB server is UNIX, and specify a backslash (\) if the HiRDB server is Windows. For the characters that can be specified in a file name, the rules for the HiRDB server platform apply. However, if utf-8 or chinese-gb18030 is specified for the character code classification in the pdntenv command (for the UNIX edition, the pdsetup command), specify it using the ASCII code range.
  3. If only an embedded variable or a ? parameter is specified in the file prefix, the embedded variable or the ? parameter must have a simple structure.
  4. Use the default character set for the file prefix.

Specifies the file output mode as a numeric data type (INTEGER type is returned during execution of the DESCRIBE INPUT statement).

The following can be specified in file-output-option:

The following values can be specified:

FunctionValue
Re-create (overwrites any existing file)0
Append (adds to the end of an existing file)1
Overwrite disable (flags an error if an existing file is encountered)2
Asynchronous output (requests asynchronous output from the operating system)4
Note
The asynchronous output option can be specified in combination with re-create, append, or overwrite disable. When asynchronous output is combined with another option, specify the value that is the logical union of the asynchronous output option's value and the value for the other option. When the asynchronous output option is not specified, HiRDB requests synchronous output (immediate WRITE) from the operating system.

If only an embedded variable or a ? parameter is specified in the file prefix, the embedded variable or the ? parameter must have a simple structure.

(4) Rules on the results of a WRITE specification

  1. The result of a WRITE specification uses a VARCHAR type without the NOT NULL constraint (allowing null values), a defined length of 255 bytes, and the default character set.
  2. The result of a WRITE specification is in the following format:
    IP-address : file-prefix - column-number - row-counter
    <- - - - - file-name - - - - - >
    Note: A colon (:) separates the IP address and the file prefix. The file prefix, column number, and row counter are delimited by the hyphen (-).
    Explanation
    IP-address
    Returns the IP address of the unit for the single-server or front-end server to which the client is connected. An IP address is in the format XXX.XXX.XXX.XXX and its length is 7-15 bytes (XXX is numeric characters in the range 0-255).
    file-prefix
    Returns the file prefix that was specified in the second argument of the WRITE specification.
    column-number
    Returns a number indicating the specified position for the derived table. A column number consists of five numeric digits; the number of the first column is 1 (preceded by leading zeros).
    row-counter
    Returns numbers in ascending order, corresponding to the number of rows that are retrieved. The row counter is a 10-digit numeric character string beginning with 1 (preceded by leading zeros). When the count goes past 2,147,483,646 rows, the counter resets itself to 1.
  3. If a retrieval is performed from a client at which the WRITE specification cannot be used, no IP address is set; instead, a character string beginning with a colon is returned.
  4. If any of output-BLOB-value, file-prefix, or file-output-option is the null value, the result is also the null value. If the default value setting facility for null values is used in an embedded variable, only an IP address is returned.
  5. Similar to an ordinary character data retrieval, if the embedded variable receiving the result of a WRITE specification is shorter than the result, the excess part is truncated and the actual length is assigned to an indicator variable. The truncation process treats the length of the embedded variable as (embedded variable length - 15) bytes by excluding the maximum length of the IP address; if the length includes an IP address, truncation can occur even if the result of the WRITE specification can be stored in the embedded variable.
  6. An error may result if the embedded variable receiving the results of the WRITE specification is shorter than 15 bytes.

(5) Rules on the file to which BLOB data is to be output

  1. The BLOB data specified in output-BLOB-value is output to a file on a unit at the single-server or front-end server.
  2. If the result of the WRITE specification is the null value, no file is created.
  3. If the actual length of the BLOB data produced by a WRITE specification is 0 bytes, a file whose size is 0 bytes is created.
  4. The format of the output file contains BLOB data only; it does not include information on the actual length of the data.
  5. In the UNIX edition, the owner of the created file and the mode are as follows:
    Owner: HiRDB administrator
    Group: Group that includes the HiRDB administrator
    Mode: rw-rw-rw-
  6. If fewer embedded variables receiving retrieval results are specified than the number of columns for retrieval results and if there is no embedded variable to receive the results of the WRITE specification, no file is created.

(6) Common rules

  1. A WRITE specification can be specified by itself in a selection expression for the outermost query specification.
  2. A WRITE specification cannot be specified in a sort key field when ORDER BY is specified.
  3. In a derived query expression in a WITH clause, a WRITE specification cannot be specified in a selection expression.
  4. In a set operation, a WRITE specification cannot be specified in a column of the derived table that is subject to the operation.
  5. A WRITE specification cannot be specified in a selection expression for a subquery (including a derived table in a FROM clause).
  6. A WRITE specification cannot be specified in a selection expression for a derived query expression for a view definition.
  7. A WRITE specification cannot be specified in a selection expression with a query specification in an INSERT statement.
  8. A WRITE specification cannot be specified in query specification in an SQL procedure statement in a routine.

(7) Notes

  1. Any files that are created should be deleted by the user. If a file name is returned to the UAP as a retrieval result, HiRDB will not manipulate (read or write) the created file; however, caution should be exercised with respect to the following points:
    • If a file is deleted after a FETCH, and if the preceding FETCH result and the BLOB value for the same cursor retrieval are the same, the same file name may be returned but the file will not be re-created. In such a case, you should note the preceding file name and delete the file when its file name has changed.
    • The file can be deleted unconditionally after the cursor is closed.
    • The file can be deleted unconditionally after the transaction is resolved.
  2. If the event of an error or rollback, HiRDB does not delete the created file.
  3. Normally in the event of an SQL error, any file that is created by the affected SQL statement is deleted. However, if an error occurs after the file output processing within the HiRDB server is completed, files may not be deleted in some case; e.g., in the case of a communication error involving return of results from the HiRDB server to the HiRDB client.
  4. If the FETCH facility is used with an array, each FETCH creates a file equal in size to the number of elements in the array, in which case it is important to monitor the available disk space.
  5. If the block transfer facility is used, the first FETCH creates a file equal in size to the number of rows transferred per block. Subsequently during each FETCH following completion of the FETCH equal to the number of transferred rows per block, files are repeatedly created equal to the number of rows transferred per block, in which case it is important to monitor the available disk space.
  6. Files that are not deleted can compete for OS resources, such as disk space; it is important to be aware of this possibility.
  7. If file names conflict with other transactions or cursor retrievals, files can destroy one another; it is important to be aware of this possibility. Hitachi recommends that duplicate file names be avoided, such as by using a different directory name in the file prefix or a different file name for each transaction and cursor.
  8. If character string truncation occurs as a result of a WRITE specification, a complete file name cannot be acquired but a file is created. It is important to monitor any competition for disk space for such a possibility.
  9. If asynchronous output is specified as the file output option, HiRDB will output BLOB data to a file without specifying synchronous output (immediate WRITE) to the OS. As a result, the file output processing by the operating system can remain incomplete due to a high load on output devices even if the file output processing within HiRDB is complete. Consequently, even if a file name is returned to the client, the file is not actually created or its creation remains pending under certain timing conditions.
    This situation can be avoided by not specifying asynchronous output as the file output option, at the expense of I/O overhead having a significant impact on response time.

(8) Example

Retrieval examples using the file output facility involving BLOB data are shown below:

(a) Retrieving BLOB columns

Retrieve columns C1 and C2 from Table T1, writing BLOB data from C1 to a file and acquiring the file name:

[Figure]

(b) Retrieving abstract data types with the BLOB attribute

From table T1, retrieve column ADT1 such that CONTAINS() is TRUE, writing a BLOB value, which is the result of passing the column value to an argument of EXTRACTS(), to a file, and acquiring the file name (this example illustrates the case in which all columns are retrieved).

[Figure]