Application Development Guide

LOB File Reference Variables

File reference variables are similar to host variables except they are used to transfer data to and from client files, and not to and from memory buffers. A file reference variable represents (rather than contains) the file, just as a LOB locator represents (rather than contains) the LOB value. Database queries, updates, and inserts may use file reference variables to store, or to retrieve, single LOB values.

For very large objects, files are natural containers. In fact, it is likely that most LOBs begin as data stored in files on the client before they are moved to the database on the server. The use of file reference variables assists in moving LOB data. Programs use file reference variables to transfer LOB data from the client file directly to the database engine. The client application does not have to write utility routines to read and write files using host variables (which have size restrictions) to carry out the movement of LOB data.

Note:

The file referenced by the file reference variable must be accessible from (but not necessarily resident on) the system on which the program runs. For a stored procedure, this would be the server.

A file reference variable has a data type of BLOB, CLOB, or DBCLOB. It is used either as the source of data (input) or as the target of data (output). The file reference variable may have a relative file name or a complete path name of the file (the latter is advised). The file name length is specified within the application program. The data length portion of the file reference variable is unused during input. During output, the data length is set by the application requester code to the length of the new data written to the file.

When using file reference variables there are different options on both input and output. You must choose an action for the file by setting the file_option field in the file reference variable structure. Choices for assignment to the field covering both input and output values are shown below.

Values (shown for C) and options when using input file reference variables are as follows:

• SQL_FILE_READ (Regular file) -- This is a file that can be open, read, and closed. DB2 determines the length of the data in the file (in bytes) when opening the file. DB2 then returns the length through the data_length field of the file reference variable structure. (The value for COBOL is SQL-FILE-READ, and for FORTRAN is sql_file_read.)

Values and options when using output file reference variables are as follows:

• SQL_FILE_CREATE (New file) -- This option creates a new file. Should the file already exist, an error message is returned. (The value for COBOL is SQL-FILE-CREATE, and for FORTRAN is sql_file_create.)
• SQL_FILE_OVERWRITE (Overwrite file) -- This option creates a new file if none already exists. If the file already exists, the new data overwrites the data in the file. (The value for COBOL is SQL-FILE-OVERWRITE, and for FORTRAN is sql_file_overwrite.)
• SQL_FILE_APPEND (Append file) -- This option has the output appended to the file, if it exists. Otherwise, it creates a new file. (The value for COBOL is SQL-FILE-APPEND, and for FORTRAN is sql_file_append.)

Notes:

1. In an Extended UNIX Code (EUC) environment, the files to which DBCLOB file reference variables point are assumed to contain valid EUC characters appropriate for storage in a graphic column, and to never contain UCS-2 characters. For more information on DBCLOB files in an EUC environment, see Considerations for DBCLOB Files.

2. If a LOB file reference variable is used in an OPEN statement, the file associated with the LOB file reference variable must not be deleted until the cursor is closed.

For more information on file reference variables, refer to the SQL Reference.