Troubleshooting Guide

DB2 Data Links Manager Considerations

Before using DB2 Data Links Manager, you should confirm that your environment supports DLFM. The following is a summary of the conditions in which the DB2 Data Links Manager can function.

• There is a maximum of 256 unique combinations of DATALINK columns and DLFMs. For example, the table can have a maximum of 128 DATALINK columns if the number of DLFMs configured is two.
• Data Links Manager cannot be exchanged with a DRDA server.
• Data Links Manager can be installed on systems with:
  • DB2 UDB EE (AIX)
  • DB2 PE, WE, EE (NT).
• The Data Links Manager cannot be used with:
  • DB2 Enterprise Extended Edition (EEE) for AIX.
  • DB2 Enterprise Extended Edition (EEE) for Windows NT.
• DATALINK values cannot point to a DB2 EEE system.
• The DATALINK data type cannot be used on DB2 EEE systems to reference files on a DB2 Data Links Manager.
• Tables with DATALINK columns can be created on any DB2 UDB server.
• DB2 Server and Data Links Manager have to be on the same release level.
  • DB2 V6 -- Data Links Manager V6 support only.
  • DB2 V5.2 -- Data Links Manager V5.2 support only.
• DATALINK columns cannot be part of a unique index, primary key, or foreign key.

DB2 Data Links Manager

The DB2 Data Links Manager consists of the following two components:

Data Links File Manager

Data Links File Manager registers all files on a particular Data Links Manager Server that are referred by DATALINK columns in DB2 databases. This component tracks the meta data (the administration information) for the link files. It also tracks previously linked files for point-in-time roll-forward recovery.

Data Links Filesystem Filter

Data Links Filesystem Filter filters the file system commands to ensure that registered files are not deleted or renamed. Optionally, it also filters commands to ensure that proper access authority exists. It enhances the file system of the operating system so that no file API can corrupt the integrity between the data on the DB2 Server and the external files managed by Data Links Manager.

Note:

The DLFF must be applied to all file systems that are referenced by DATALINK columns.

There are some important considerations when using the DB2 Data Links Manager:

• The DB2 Database Server must have exactly the same fix pack level as the Data Links File Manager (DLFM) components.
• If the DB2 Server and the DLM are installed on the same machine, the database that is used by the Data Links File Manager (DLFM) must reside in a different instance than the DB2 Database Server Databases (the databases that contain the DATALINK columns used by applications). For example, the instance name filling the role of the DB2 Server must not be "dlfm" because it is used by Data Links File Manager.

Data Access Token

File access control is provided by a combination of DB2 and the Data Links Manager. DB2 assigns an access token dynamically, using a SELECT statement on the DATALINK column and embeds the result in the file path name.

There are several provisions for file access control to operate correctly:

• File access control is provided only if the READ PERMISSION DB option is specified on the DATALINK column when the table is created.
• If a user can query over certain meta data (such as an SQL view), they can access corresponding files.
• Any file system API or command can be used to read the file.
• The rule can be any SQL expression.
• Generation of the access token is shared secretly between DB2 and Data Links Filesystem Filter (DLFF).
• To be valid, an access token must be generated and used within a specified time interval as defined in the Data Links Access Token Expiry Interval (dl_expint) database configuration parameter.
• For each (select) access, a new token is generated and remains valid for the time specified by dl_expint.
• On most file access control mechanisms of an operating system, it is not possible to grant access based on rules for each file.
• Web addresses with embedded access tokens must be used by the application to access the files. Any attempt to open, read, or otherwise manipulate a file using Web addresses with the access token results in an access violation.

Recovery Data Links Manager Environment

This section describes Data Links Manager recovery situations.

DB2 database recovery:

• Restore database backup and roll forward end of logs
• Execute the RECONCILE command on all tables which have DATALINK columns.

DLFS file systems recovery:

• Restore the DLFS files systems
• Execute the db2_reconcile_aid utility for the databases which see the file system
• The db2_reconcile_aid command provides a mechanism for checking and running RECONCILE.

The following table shows possible situations that require recovery operations.

Environment	Conditions	Action and Resolution
The DB2 databases on the DB2 Database Server are lost.	DB2 backup and log files are available.	On the DB2 Database Server: Restore the database(s) and roll forward to end of logs. Reconcile all table that have DATALINK values in that database(s).
DLFM_DB on the Data Links Server is lost.	Backup and log files are available.	On the Data Links Server: Restore the database(s) and roll forward to end of logs. Restore database DLFM_DB and roll forward to end of log.
Recover from a disk crash on the DLFM Server.	DB2 applications interacting with the DLFM can hang.	Use the FORCE APPLICATION command to force applications off the system.
Recover from a machine crash on the DLFM Server.	DB2 applications interacting with the DLFM can hang.	The affected DB2 Data Links Managers should be shut down by using the dlfm shutdown command. These can then be brought up again.
Recover from a disk or machine crash on the DLFM Server.	Applications can hang.	On the Data Links Server: Restore the DLFS file system. On the DB2 Database Server: Perform a reconcile on all tables, containing the damaged disk, by invoking the command, db2_reconcile_aid. This will check and run reconcile on tables that are potentially inconsistent with the linked file on the File Manager Server after a disk failure.
Recover the DLFM backup directory.		Restore the DLFM backup directory.

Files in a linked state, having the RECOVERY NO option set:
Permissions	File Status on File System	Action	Result
	Not Found		DATALINK value will be set to NULL.
READ and WRITE PERMISSION FS	Found	No additional checks will be conducted to validate the correctness of the file.
WRITE PERMISSION BLOCKED	Found	Its modification time and file size will be checked.	If there is a mismatch in the values, the DATALINK value will be set to NULL.

Files in a linked state, having the RECOVERY YES option set
Permissions	File Status on File System	Action	Result
	Found	The file modification time is less than the file modification time at link time.	The correct version of the file will be restored from the archive server.
		The file modification time is greater than the file modification time at link time.	The DATALINK value will be set to NULL.

Backup Data Links Manager Environment

To perform a backup of the entire Data Links File Manager environment, consider the following:

• Ensure all Data Links Servers are up and running (unless you have not specified the NO LINK CONTROL option in the DATALINK definition).
• Back up the databases on the DB2 Database Server(s).
• Back up the file systems used by the Data Links Manager.
• File system(s) need to be unmounted, backed up (via the operating system), and then mounted again.
• Back up the DLFM Backup directory, which holds:
  • Images of the DLFM_DB database
  • Copies of the linked files
  • All updates if the RECOVERY OPTION is set to YES in the DATALINK column. This provides point-in-time roll-forward recovery (if you set DLFM_BACKUP_DIR_NAME=directoryname, not ADSM)

Export Operation

This operation, using DLFM_EXPORT is used on the Data Links server to export two sets of data:

• DATALINK data
• Linked files.

A successful export on a table with DATALINK columns produces:

• An export file for the parametric data
• Control files (one for each involved server) that list the referenced files from the exported rows.

Notes:

1. For a successful DLFM_EXPORT operation to occur, root authority is required. There may be files that need to be archived that cannot be accessed by the DLFM administrator.

2. The DB2 Data Links Manager does not have to be running in order for DLFM_EXPORT to run.

Import Operation

Steps to import data, using the utility DLFM_IMPORT, are included in DB2 Data Links Manager Quick Beginnings. However, there are particular conditions in which data is imported successfully and it is helpful to review these conditions during troubleshooting:

• The import utility must be used whether the archived files are being restored on the same or a different file server.
• Root authority is required as a 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 files exported will be owned by root in case some of the user IDs do not exist on the new machine.
• The administrator must ensure that all files have the right permissions.
• If files are imported to different Data Links Manager server or files system names or directories, the appropriate steps on the Data Link servers must be performed to register file systems and DB2 Database Servers.

On the DB2 Database Servers the appropriate steps for configuring the DB2 Database Servers must be performed if the tables are imported on another DB2 Database Server.

• If the server name or file path has changed, you can use the DL_URL_REPLACE_PREFIX prefix phrase in the import command which changes all paths (in the URL) in the DATALINK value.
• When the import utility executes on the target database, data related to DATALINK 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.

Load Considerations

Before invoking the LOAD utility, it should be noted that some options are not supported:

• The COPY option is not supported for tables with DATALINK columns
• The REPLACE option is not supported for tables with DATALINK columns
• The NONRECOVERABLE clause should not be used when DATALINK columns with the FILE LINK CONTROL attribute are present in, or being added to, the table.

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:

• Ensure that the DB2 Data Links 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 DLFM.
• Copy to the appropriate file servers all files that will inserted as DATALINK values.
• Define the prefix name (or names) to the DLFM on the file servers. For more information, see the description on registering databases with the DLFM in the DB2 Data Links Manager Quick Beginnings.
• If intending to see a new file server in an input file, add the file server name (or names) to the DB2 file server configuration file.

DB2 Data Links Manager for Windows NT

If you install DB2 Data Links Manager on a clean machine, the installation program will also install a compact version of DB2 Universal Database Personal Edition for the DLFM.

With this DB2 Universal Database, there is no way to additionally install the DB2 Server on the same machine. However, if before installing DB2 Data Links Manager on the system there is already a DB2 Universal Database Personal Edition, Workgroup Edition, or Enterprise Edition, DLFM will use the existing DB2 Universal Database product.