LOB or XML file reference variables
File reference variables are used for direct file input and output for LOB and XML values (when the XML value is declared using XML AS variable-type), and can be defined in all host languages.
Because these are not native data types, SQL extensions are used and the Db2 precompiler or coprocessor generates the host language constructs necessary to represent each variable. In the case of REXX, LOB values are mapped to strings. See XML variables for more information about XML host variables.
A file reference variable represents (rather than contains) the file, just as a LOB locator represents, rather than contains, the LOB data. Database queries, updates, and inserts can use file reference variables to store or to retrieve single column values.
As with all other host variables, file reference variables can have an associated indicator variable. Indicator variables for file reference variables behave in the same way as indicator variables for other data types. When a null value is returned from the database manager, the indicator variable is set, and the variable is unchanged. When the indicator variable that is associated with a file reference variable is null, the value of the referenced LOB or XML object is null. This means that a file reference variable can never point to a null value.
The length attribute of a file reference variable is assumed to be the maximum length of a LOB or XML object.
- Data type
- BLOB, CLOB, or DBCLOB. This property is specified when the variable is declared using BLOB_FILE, CLOB_FILE, or DBCLOB_FILE.
- Direction
- This must be specified by the application program at run time (it is implicitly specified as
part of the File options value). The direction can be either of the following:
- Input
- Input is used as a source of data on an EXECUTE statement, an OPEN statement, an update operation, an insert operation, or a delete operation.
- Output
- Output is used as the target of data. For example, on a FETCH statement or a SELECT INTO statement.
- File name
- This must be specified by the application program at run time. It must be the complete path name of the file. Within an application, a file should only be referenced one time in a file reference variable.
- File name length
- This must be specified by the application program at run time. It is the length of the file name in bytes.
- Data length
- Sets the data length to the length of the new data that is written to the file. The length is in bytes. Data length is unused on input.
- File options
- Options are set by an INTEGER value in a field in the file reference
variable structure. One of the following values must be specified
in an application for each file reference variable before that file
reference variable can be used in the application:
- SQL_FILE_READ
- This is a regular file that can be opened, read and closed. (The option is SQL-FILE-READ in COBOL, sql_file_read in FORTRAN, and READ in REXX.) SQL_FILE_READ is an input (from client to server) file option.
- SQL_FILE_CREATE
- Create a new file. If the file already exists, an error is returned. (The option is SQL-FILE-CREATE in COBOL, sql_file_create in FORTRAN, and CREATE in REXX.) SQL_FILE_CREATE is an output (from server to client) file option.
- SQL_FILE_OVERWRITE
- If an existing file with the specified name exists, it is overwritten; otherwise a new file is created. (The option is SQL-FILE-OVERWRITE in COBOL, sql_file_overwrite in FORTRAN, and OVERWRITE in REXX.). SQL_FILE_OVERWRITE is an output (from server to client) file option.
- SQL_FILE_APPEND
- If an existing file with the specified name exists, the output is appended to it; otherwise a new file is created. (The option is SQL-FILE-APPEND in COBOL, sql_file_append in FORTRAN, and APPEND in REXX.) SQL_FILE_APPEND is an output (from server to client) file option.
The encoding scheme CCSID of the file name is based on the encoding scheme of the application. The CCSID of the LOB or XML data (the contents of the file) can be set by the application by using a DECLARE host-variable CCSID statement if the CCSID of the LOB or XML data is different from the CCSID of the application. Db2 performs any character conversion that is required prior to insertion of the LOB or XML data into a table or writing of the LOB or XML data to a file.