DB2 Version 9.7 for Linux, UNIX, and Windows

UTL_FILE module

The UTL_FILE module provides a set of routines for reading from and writing to files on the database server's file system.

The schema for this module is SYSIBMADM.

The UTL_FILE module includes the following system-defined routines and types.

Table 1. System-defined routines available in the UTL_FILE module
Routine name	Description
FCLOSE procedure	Closes a specified file.
FCLOSE_ALL procedure	Closes all open files.
FCOPY procedure	Copies text from one file to another.
FFLUSH procedure	Flushes unwritten data to a file
FOPEN function	Opens a file.
FREMOVE procedure	Removes a file.
FRENAME procedure	Renames a file.
GET_LINE procedure	Gets a line from a file.
IS_OPEN function	Determines whether a specified file is open.
NEW_LINE procedure	Writes an end-of-line character sequence to a file.
PUT procedure	Writes a string to a file.
PUT_LINE procedure	Writes a single line to a file.
PUTF procedure	Writes a formatted string to a file.
UTL_FILE.FILE_TYPE	Stores a file handle.

The following list describes all of the named conditions (these are called "exceptions" by Oracle) that an application can receive.

Table 2. Named conditions for an application
Condition Name	Description
access_denied	Access to the file is denied by the operating system.
charsetmismatch	A file was opened using FOPEN_NCHAR, but later I/O operations used non-CHAR functions such as PUTF or GET_LINE.
delete_failed	Unable to delete file.
file_open	File is already open.
internal_error	Unhandled internal error in the UTL_FILE module.
invalid_filehandle	File handle does not exist.
invalid_filename	A file with the specified name does not exist in the path.
invalid_maxlinesize	The MAX_LINESIZE value for FOPEN is invalid. It must be between 1 and 32672.
invalid_mode	The open_mode argument in FOPEN is invalid.
invalid_offset	The ABSOLUTE_OFFSET argument for FSEEK is invalid. It must be greater than 0 and less than the total number of bytes in the file.
invalid_operation	File could not be opened or operated on as requested.
invalid_path	The specified path does not exist or is not visible to the database
read_error	Unable to read the file.
rename_failed	Unable to rename the file.
write_error	Unable to write to the file.

Usage notes

To reference directories on the file system, use a directory alias. You can create a directory alias by calling the UTL_DIR.CREATE_DIRECTORY or UTL_DIR.CREATE_OR_REPLACE_DIRECTORY procedures. For example, CALL UTL_DIR.CREATE_DIRECTORY('mydir', 'home/user/temp/mydir')@.

The UTL_FILE module executes file operations by using the DB2® instance ID. Therefore, if you are opening a file, verify that the DB2 instance ID has the appropriate operating system permissions.

The UTL_FILE module is supported only in a non-partitioned database environment.