Administration Guide

Moving DB2 File Manager Data

To move data, you can use the load, import, and export utilities. The following sections describe considerations for using these utilities with DB2 File Manager data.

For information about the file formats that you can use with these utilities, see the Command Reference.

Use the load utility for the initial load or an append of a table where large amounts of data are moved. The LOAD command is faster than the IMPORT command, because it writes formatted pages directly into the database while IMPORT performs SQL INSERTs.

If you are loading data to a table with a DATALINK column that is defined as FILE LINK CONTROL, perform the following steps before invoking the LOAD utility. (If all the DATALINK columns are defined with NO LINK CONTROL, these steps are not necessary.)

Ensure that DB2 File Manager is installed on the file servers that will be referred to by the DATALINK column values.
Ensure that the database is registered with the File Manager.
Copy to the appropriate file servers all files that will be inserted as DATALINK values.
Define the prefix name (or names) to the File Managers on the file servers. For more information, refer to the description of how to register databases with the File Manager in the Quick Beginnings for DB2 File Manager book.
If you intend to refer to a new file server in an input file, add the file server name (or names) to the DB2 file server configuration file.

Troubleshooting the Load Utility

It is possible that while running the load utility, the connection between DB2 and the file server may fail. This causes the load to fail. If this occurs:

Start the file server and DB2 File Manager.
Issue the LOAD RESTART command. The load utility restarts from the last consistency point. See the Command Reference for additional information on this command.

Any links that fail during the load process are considered to be a type of data integrity violation, and they are handled in much the same way as violations of unique constraints. Consequently an exception is defined that can occur only if the table under load has one or more DATALINK columns. For additional information, refer to the description of exceptions in the SQL Reference.

Export Utility Considerations

All the database data for a table resides in the database, while the files referred to by DATALINK columns reside on file servers. The EXPORT utility has to move both the database data, and the data files on the corresponding file servers. To do this, the EXPORT utility produces one control file per file server. The name of the control file that is created is the same as the name of the file server (for example, the control file for the file server fs1 is "fs1"). The control files are created in a newly created directory that has the name dlfmYYYY, where YYYY is a system-generated pattern. This directory is created under the same directory where the EXPORT datafile is created.

A control file lists the names of the corresponding DB2 File Manager files that are referenced by the DATALINK columns of rows that are exported. DATALINK values that have the "no link" control property are not placed in the control file.

You issue the dlfm_export command at each file server, and specify the control file that was generated for that file server. The dlfm_export command produces an archive of the files listed in the control file.

Running the Export Utility

To ensure that a consistent copy of the table and the corresponding files that are referenced by the DATALINK columns are copied for export, perform the following steps:

Ensure that no update transactions are in progress when EXPORT is run by issuing the following command:
```
   QUIESCE TABLESPACES FOR TABLE tablename SHARE
```
Run the EXPORT utility
Run the dlfm_export command with root authority at each file server. As input to dlfm_export, specify the control file (server_name), that is generated by the EXPORT utility. You need to run dlfm_export as root in order to successfully archive files that the DLFM administrator may not have access to.
Create the table available for updates by issuing the following command:
```
   QUIESCE TABLESPACES FOR TABLE tablename RESET 
```

The EXPORT utility executes as an SQL application. The rows and columns that satisfy the select-statement conditions are extracted from the database. For the DATALINK columns, the select-statement should not specify any scalar function. The EXPORT utility extracts parts of the DATALINK value, such as the link type, file server name, file path name, and comments.

When the EXPORT utility executes successfully, the following files are generated:

The database export data file as specified in the EXPORT command. A DATALINK column value in this file is in the same format as that used by the LOAD or IMPORT utilities. If the DATALINK column value is NULL, it is treated in the same way as for other NULL columns.
Control files for each file server (each control file has the same name as its file server). The control file lists the complete path and file names of all the files that should be exported from that file server.

Use the dlfm_export utility to export files from a file server as follows:

   dlfm_export control_file_name tar_file_name

where control_file_name is the file name generated by running the EXPORT utility on the DB2 client; and, tar_file_name is the name of the archive file that will be generated. The default tar_file_name is "export.tar" in the current working directory.

A corresponding utility called dlfm_import is provided to retrieve and restore files from the archive that dlfm_export generates. This utility must be used whether the archived files are being restored on the same, or a different, file server.

Use the dlfm_import utility to retrieve files from the archive as follows:

   dlfm_import tar_file_name

where tar_file_name is the name of the archive file that will be used to recover the files. The default tar_file_name is "export.tar" in the current working directory.

Notes:

Both dlfm_export and dlfm_import utilities must be run with root authority. For dlfm_export, this is required because there may be some files needing to be archived that the DLFM administrator does not have access to. In dlfm_import's case, root authority is required because the user may decide to restore the archived files on a different file server which may not have the same directory structure and user IDs as the file server on which the dlfm_export utility was run.
The DB2 File Manager does not have to be up and running in order for dlfm_export and dlfm_import utilities to run.
When running the dlfm_import utility on a file server other than the one where dlfm_export was run, the files will be restored in the right paths. However, the files will be owned by root in case some of the user ID's do not exist on the new machine. Before inserting these files into their database, it is your responsibility as the administrator to ensure that all files have the right permissions, belong to the right user IDs, and so on.

Exporting Between Instances

The figure below shows how to export the DB2 data and the files that are referenced by the instance called "SystemA" to the instance called "SystemB". SystemA uses the file servers DLFM1 and DLFM2. SystemB uses the file servers DLFMX and DLFMY. The files on DLFM1 will be exported to DLFMX and the DLFM2 files will be exported to DLFMY.

InstanceA with File Servers DLFM1 and DLFM2 Step
DB2 Data on File File1 with filename for DLFM1 File2 with filename for DLFM2 1) Run the dlfm_export command (as root) on both file servers. This will produce an archive on both file servers.
InstanceB with File Servers DLFMX and DLFMY

On DLFMX, restore from archive On DLFMY, restore from archive 2) Run dlfm_import (as root) on both file servers

3) Run the IMPORT command on InstanceB with the parameter DL_URL_REPLACE_PREFIX to specify the appropriate file server for each exported file.
After you run the import utility on InstanceB, the InstanceA data and all files referenced by DATALINK columns are now imported.

InstanceA with File Servers DLFM1 and DLFM2	Step
DB2 Data on File	File1 with filename for DLFM1	File2 with filename for DLFM2	1) Run the dlfm_export command (as root) on both file servers. This will produce an archive on both file servers.
InstanceB with File Servers DLFMX and DLFMY
	On DLFMX, restore from archive	On DLFMY, restore from archive	2) Run dlfm_import (as root) on both file servers
			3) Run the IMPORT command on InstanceB with the parameter DL_URL_REPLACE_PREFIX to specify the appropriate file server for each exported file.
After you run the import utility on InstanceB, the InstanceA data and all files referenced by DATALINK columns are now imported.

Import Utility Considerations

All the database data for a table resides within the database, while the files referred to in the DATALINK columns reside on the file servers. The import utility has to move both the database data, and the files on the corresponding file servers.

Before running the import utility in the target database, you should perform the following steps:

Copy the files that you want to link to the appropriate file servers.
Define the prefix name (or names) to the File Managers on the file servers. (You may have to perform other administration tasks, such as registering the database.)
Update the file server information of the DATALINK columns to include the URLs from the exported data for the SQL table (if required).
Define the file servers at the target configuration in the DB2 File Manager configuration file.

Then run the import utility in the target database system.

When the import utility executes on the target database, data related to DATALINK columns (and the other columns) is loaded into the underlying tables using SQL INSERT. During the INSERT, the DATALINK column is linked to the file on the appropriate file server.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]