We who program on the iSeries tend to be isolationists when it comes to our data. We store what we need for our applications, and nothing more. If there is desired data in the outside world, we usually attempt to replicate it locally, instead of figuring out how to access it remotely. Well, with DB2 UDB on the iSeries, we now have the capability to pull our heads out of the sand and check out the data the rest of the world has to offer.

Local data is not all that DB2 UDB can handle. It can also access external data using a feature called DataLinks. DataLinks are pointers, or logical views, to data that resides on another server or on the Integrated File System (IFS) of the same server.

DataLinks extend the capabilities of DB2 by allowing you to store any type of stream file that exists in the IFS, as well as files that would normally be too large to fit into large object (LOB) data types, such as video and audio files. A DB2 table can contain information about an object without containing the object itself. This not only extends DB2's capabilities but also allows you to organize your data in such a manner that queries and everyday access to local data can still run efficiently, while the applications that require the external data can do so knowing the consequences; that is, that they might be slower to get the data from another server, especially in the case of large image, audio, or video files.

The pointer to external data is contained within a column of type DataLink and is stored in the form of a URL. The rest of the row is used to store information about the external data. A practical use of datalinks might be a video store database or any database that includes images, such as an item file or employee master file. In either case, the descriptive information can be stored locally, in DB2, where queries, reports, and maintenance can be easily and quickly accomplished, while the video or image data would be stored on a remote server and only retrieved when necessary. The following SQL code fragment demonstrates how you would define a DataLink column in a DB2 table:

Those of you who are aware of DB2's excellent built-in data integrity and security capabilities may be wondering how DataLinks are handled, if at all. The good news is that you can apply the same integrity and security rules that you would any other data type that is local to your database. You can define constraints to your DataLink column to stop someone from deleting or updating the linked file. In addition, you can ensure that the URL is valid before attempting to link to an external file. To show you what you can do, I'm going to add some constraints to our original Create command:

Take a look at the File Link Control parameter. In the first example, I did not specify this parameter, so the URL is checked for its syntax only. There is no guarantee that the URL is valid, because is not checked. In my second example, the File Link Control parameter tells the system to verify that the DataLink value is a valid URL, with a valid server name and file name. The URL link must exist at the time that row is inserted into the table. When the object is found, it is then marked as linked. The linked object cannot be moved, deleted, or renamed during the time that it is linked. If you decide to use the File Link Control parameter for a link to a remote system, that system must be running at least OS/400 V4R4 or AIX with DB2 UDB.

The Integrity All parameter stops the link from being deleted. To delete the link, first delete the row in the DB2 table that refers to the link.

The Write Permission Blocked parameter blocks all direct updates to the linked object. If users want to make changes, they must first copy the linked object, make the changes, and then decide if they want the DB2 table to link to the new object.

There are three ways in which a DataLink can be linked to, or can "control," external data: no control, file link control with IFS permissions, and file link control with DB2 permissions.

A DataLink column defined with No Link Control means that no linking takes place when rows are added. The URL is verified for syntax, but there is no check to ensure that that server is accessible or that the file exists. This is the default.

A DataLink column defined with File Link Control with IFS permissions says that DB2 will verify that the DataLink is a valid URL, with a valid server and file name. The server must be accessible when the row is added to the table. The file must also exist at the time the row is added into the table; it will then be marked as linked. When an object is linked, it cannot be deleted, moved, or renamed while it is linked. If the row is deleted or the URL is updated to a different value, the old object is unlinked. In the case of an update, the new object is then linked. File link control also means that the IFS is responsible for managing the permissions.

When a DataLink column is defined with File Link Control with DB2 permissions, the URL is verified, including the server and file name. If all checks out, the existing permissions are then removed and the object's ownership is changed to a system-supplied user profile. The only way to access an object linked in this manner is to use the DataLink in the DB2 table. This way, access to an IFS object is controlled by the DB2 permissions to the table containing the DataLink to that object. Get it?

The data link file manager, or DLFM, keeps track of the link statuses for files on a server. It handles the necessary linking, unlinking, and commitment control tasks. In order for the DLFM or DataLinks in general to work, you must have TCP/IP configured on both the server where the DB2 tables containing the datalink exist and the server that contains the linked objects. Each server must have the appropriate local database entry in its directory as well as any remote databases it plans to connect to. You can accomplish this task by using the Work with Relational Database Directory Entry (WRKRDBDIRE) command. Once TCP/IP is configured and the correct entries have been made in the directory, the DLFM server must be started. This is accomplished using the Start TCP Server (STRTCPSVR) command.

The DataLink filter is what polices what happens to objects that have been linked by DB2 tables. The filter determines if a file is linked and, if so, if the user has the proper authority to access the object. The filter also verifies access tokens for those objects that are controlled by DB2 permissions (remember the file link control with DB2 permissions option). Because this is a labor-intensive activity, the concept of a DataLink prefix was created. This allows the filter to zero in on only the directories that are a part of a DataLink prefix. A prefix is a path that will contain objects to be linked. Part of the configuration of the DLFM is to provide its prefixes, or paths, to linked objects. This can be done in one of two ways. You can use a script in the Qshell environment to add a prefix, or you can use the CL command Add Physical File Index to Data Link File Manager (ADDPFXDLFM). To further help the DataLink filter do its job more efficiently, the server must also organize the tables that will perform the linking. This is done using the Add Host Database to DataLink File Manager (ADDHDBDLFM) command.

I have provided you with a very high-level look at DataLinks. It is a very powerful feature of DB2 UDB and it can help you organize your enterprise data, even if that data resides on disparate machines. Look for more features on DB2 UDB extensibility in upcoming issues of this newsletter. For now, find an external file and link to it.