San Jose, CA 95120

The DataLinks technology developed at IBM Almaden Research Center and now available in DB2 UDB 5.2 introduces a new data type called DATALINK for a database to reference/manage files stored external to the database. An external file is put under a database control by "linking" the file to the database. Control to a file can also be removed by "unlinking" it. The technology provides transactional semantics with respect to linking or unlinking the file when DATALINK value is stored or updated. Further more, it provides the following set of properties: (1) managing access control to linked files, (2) enforcing referential integrity, such as referenced file cannot be deleted or renamed as long as it is referenced from the RDBMS, and (3) providing coordinated backup and recovery of RDBMS data with the file data.

Datalink File Manager (DLFM) is a key component of the DataLinks technology. DLFM is a sophisticated SQL application with a set of daemon processes residing at a file server node that work cooperatively with host database server(s) to manage external files. To reduce the number of messages between database server and DLFM, DLFM maintains a set of meta data on the file system and files under database control. One of the major decisions we made was to build DLFM on top of an existing database manager, such as DB2, instead of implementing a proprietary persistent data store. We have mixed feelings about using the RDBMS to build such a resource manager. One of the major challenges is to support transactional semantic for DLFM operations. To do this, we implemented the two-phase commit protocol in DLFM and designed an innovative scheme to enable rolling back transaction update after local database commit. Also a major gotchas is that the RDBMS’ cost based optimizer generates the access plan, which does not take into account the locking costs of a concurrent workload. Using the RDBMS as a black box can cause "havoc" in terms of causing the lock timeouts and deadlocks and reducing the throughput of the concurrent workload. To solve the problem, we came up with a simple but effective way of influencing the optimizer to generate access plans matching the needs of DLFM implementation. And also several precautions had to be taken to ensure that lock escalation did not take place; next key locking was disabled to avoid deadlocks on heavily used indexed and SQL tables; and timeout mechanism was applied to break global deadlocks.

We were able to run 100-client workload for 24 hours without much deadlock/timeout problem in system test. Also, the system achieves insert rate of 300 per minute and 150 updates per minute. This paper describes the motivation for building the DLFM and the lessons that we learned from this experience.

IBM has focused on extensible database research for more than a decade. The results of many of these efforts have already appeared in IBM’s DB2 family of relational database management systems (RDBMSs) [1]. To extend the reach of RDBMS functions even farther, IBM Almaden Research has now developed a new technology called DataLinks [2], which enables DBMS to manage data stored in external operating system files. DataLinks gives DBMS comprehensive control over external data and provides the following properties: referential integrity, access control, coordinated backup and recovery, and transaction consistency.

The DataLinks technology comprises of the following major components: the DLFM, DLFF (Datalinks File System Filter), and an extension to the RDBMS engine (termed datalink engine hereafter). It also introduces a new DATALINK data type [4] to facilitate RDBMS to reference and manage externally stored data. The datalink engine is responsible for processing DDL requests to create datalink column(s) and for processing DML requests against the datalink column(s). On the other hand, the DLFM and DLFF components reside at file servers where the database managed external data are stored. The value of the datalink column in a SQL table is URL, which may reference files on the same or remote file server. A datalink column can be populated by an SQL insert statement or by a database load utility. Similarly, a record with datalink attribute can be deleted or updated through a standard SQL statement. Whenever a datalink value is selected or updated (or inserted or deleted), the datalink engine is invoked by the database engine to process the part of request specific to datalink. As part of the processing, a request to the DLFM residing on the file server as specified by the URL is sent to apply certain constraints in order to start (or stop) managing the file on that file server. DLFF helps to enforce these constraints when file system commands are executed against the linked files.

Figure 1 shows an example of the storage model of the DataLinks technology. In Figure 1, a DB2 database is used as a host RDBMS to store user’s data and references to external objects via a URL in the datalink column. A DLFM residing with each file server is responsible for managing files stored in that server. System generated metadata for managing files and enforcing access control and integrity is stored in each DLFM. As previously mentioned, DLFM uses DB2 as a persistent store for all its data. DLFM treats the DB2 as a black box and all requests to retrieve, insert, or update DLFM data/state are via standard SQL. While this provides great flexibility and portability, it also poses significant challenge in enforcing transactional semantic and providing good system performance.

The rest of the paper is organized as follows. Section 2 gives an overview of the DataLinks technology and DataLinks application flow. Section 3 describes the functions and services provided by the DLFM component. Section 4 presents the experience and lessons we learned in building the DLFM and section 5 summarizes the paper.

DataLinks is software technology that enables DBMS to manage data stored in external operating system files as if the data were stored directly in the database. By extending the reach of the DBMS to operating system files, DataLinks gives users flexibility to store data inside or outside the database as appropriate. To store and reference data outside of DBMS, a database application developer declares a column of DATALINK data type when creating an SQL table. The value stored in the datalink column is then used to represent and reference data in an external file. Figure 2 illustrates the architecture of the DataLinks technology. As shown in the figure, DataLinks comprises two components: datalink engine and Data Link Manager (ref. Figure 2). Datalink engine resides in the host database server and is implemented as a part of the database (DB2) engine code. It is responsible for processing SQL requests involving datalink column(s) such as table creation, select, insert, delete, and update of records with datalink column. Data Link Manager consists of 2 components, DataLinks File Manager (DLFM) and DataLinks File System Filter (DLFF).

At high level, DLFM applies constraints on the files that are referenced by the host database and DLFF enforces the constraints when file system commands or operations affect these files. For example, a file rename or delete would be rejected if that file is referenced by the database. DLFF is a topic on its own and will not described further in this paper. DLFM resides with file server which can be local or remote to the host database server. DLFM provides a set of API’s which the datalink engine uses to make requests for linking a file, unlinking a file, carrying out two-phase commit protocol, etc. Invoking the API’s is through remote procedure call mechanism.

A datalink application is an application that manipulates datalink attribute (column) in an SQL tables and it is no different from a regular database application as far as the database is concerned. Before a datalink application can issue any requests, it must establish a database connection first. As part of database connect request, a DB2 agent (process or thread) is identified to serve the application. After connection has been established, the application can start submitting SQL requests to the database engine. If a SQL request requires manipulating datalink column, the datalink engine is invoked to process part of the request specific to the datalink column. In turn, the datalink engine sends request to the DLFM to manipulate files and metadata stored at file server, if necessary.

Figure 3 illustrates how DataLinks works from an application perspective. In a DataLinks environment, a hosts database (eg., DB2 UDB) provides the metadata repository for external data. Attributes and subsets of the data stored in external files are maintained in the host database tables along with the logical references to the location of the files (e.g., a server name and a file name). The application searches the host database via the SQL API to identify external files of interest. Examples would be finding the following: 50-day-moving-average chart of stocks that are tripled in price during last 12 months; a video clip used in TV commercials within the last year that contains images of Michael Jordan; all of the email attachments received within the last six months that concern customer profiles; or employee who is older than 40 and has blue eyes and red hair. DB2 processes the request and returns the references (URL’s) for selected files to the application. The application then accesses the file data directly using standard file-system API calls (file-open, etc.) Using standard file APIs is very important for supporting existing applications without having to modify either the application or the file system.

Figure 3: Managing external data with Datalinks

The Data Links File Manager (DLFM) component of DB2 Data Links Manager plays a key role in managing external files. It is responsible for executing the link/unlink operations in the same transaction context as the one in the host database. To do this, the DLFM maintains its own repository about files that are linked to (referenced in) the database. When a file is initially linked to the database, the DLFM applies the constraints for referential integrity, access control, and backup and recovery as specified in the DATALINK column definition. If the DBMS controls read access, for example, the DLFM changes the owner of the file to the DBMS and marks the file "read only". All of these changes to the DLFM repository and to the file system are applied as part of the same DBMS transaction as the initiating SQL statement. If the SQL transaction is rolled back, the changes made by the DLFM are undone as well.

In order to support certain SQL operations, such as Drop SQL table, the concept of File Group was introduced. A File Group corresponds to all files that are referenced by a particular datalink column of an SQL table. This is so that it is possible to unlink all files associated with a column of an SQL table when it is dropped.

The DLFM is also responsible for coordinating backup and recovery of external files with the database. When the DBMS transaction that includes a LinkFile operation commits, the DLFM initiates a backup of the newly linked file if DLFM is responsible for recovery of the file. This file backup is done asynchronously and is not part of the database transaction for performance reasons. In addition, by doing it this way, the database backup itself is not slowed down because the referenced file would typically have been backed up. This is particularly important in the case of very large files. Coordinated backup and recovery of external files with DB2 data can be done directly to disk or to an archive server supported by DLFM, such as IBM’s ADSTAR Distributed Storage Manager (ADSM).

The DLFM tracks different versions of a referenced file and maintains the backup status of each in order to support point-in-time recovery. The DBMS also provides the DLFM with a "Recovery id" for a file whenever it is linked or unlinked to help synchronize recovery of files with data. This is important because a file with same name but different content may be linked and unlinked several times. Without a separate "Recovery id" for each link operation, DLFM would not be able to restore the file to match the database state.

When the DBMS does a backup of its data, it communicates with the DLFMs to ensure that all of the necessary asynchronous copy operations for referenced files have completed before declaring that the database backup has been successfully completed. The DBMS backup utility has been extended to handle this level of communication and to keep additional information in the backup image about which file servers and file groups were involved in the backup. Backup copies of unlinked files may be kept for a specific number of database backup cycles, in case the database is restored to a point in the past in which the file was still linked to the database. The DLFM is also responsible for "garbage collection" of backup copies of unlinked files that are no longer required by the DBMS.

The DLFM uses a local database to keep its metadata and state information. This information is stored in the following SQL tables.

1. Dbid Table: This table consists of registered entries for each host database that talks to this DLFM. The dbid field in this table represents the unique combination of the host database name, instance name, and host machine name.
2. Group Table: This table consists of file group entries. Each group entry corresponds to a datalink column in a SQL table on the host database side.
3. File Table: This is the most accessed table that consists of the information of linked and unlinked files on the file server. Whenever a file is linked, a new entry is inserted into the file table. During the unlink operation existing file entry in linked state is marked as unlinked. This table retains the unlinked file entries if files need to be restored in the future via host database restore utility. The columns of interest defined in this table are dbid, filename, transaction_id, Recovery_id, file_status, entry_state. Their usage will be described with functional processing later.
4. Transaction Table: This table keeps track all the DLFM transactions. Transaction state is maintained for each transaction as long as it is active. The transaction state information is first kept in an in-memory table when the transaction starts. The entry is inserted into the SQL table when the transaction begins the first phase of the commit processing. Once the transaction is completed, its entry is removed from the transaction table.
5. Archive Table: This table contains file and group entries that need to be archived to the archive server. When load utility is used to insert large number of files into a datalink column on the host database side, instead of replicating each file entry in the Archive table, only a group entry is inserted into the Archive table. Copy daemon picks up the entries from the Archive table and copies them to archive server. After copy has completed, corresponding entries are removed from the Archive table.

LinkFile and UnlinkFile are two important operations in the DataLinks technology which corresponds to insert and delete of the datalink value respectively from the host database. Whenever an application inserts the file entry into a datalink column the corresponding file on the server is linked by the DLFM. Linking involves applying certain constraints on the file such that subsequent rename and deletion of the referenced file, via normal file system APIs (or commands), are prevented to preserve referential integrity from the host database. Furthermore, the access control mode of the datalink column determines the partial or full take over of the file. In full access control file ownership is changed to "DB" (to the DLFM admin user) and the file is marked read-only. Also an access token assigned by the host database is needed to access such a file. All the files linked to the host database are guarded against unauthorized move / delete / rename operations by DLFM and DLFF. During the LinkFile operation DLFM puts a new entry in the File table. This entry consists of dbid, transaction id, filename, and Recovery id among other things. Recovery id generated at the host database consists of dbid and a timestamp. It is guaranteed to be globally unique and monotonically increasing. For every link file operation the DLFM makes following two checks,

1. If a link entry already exists for the same file in the DLFM metadata table then it rejects the LinkFile operation as file is already in a linked state.
2. If an unlink entry exists for the same file in the DLFM table whose unlink transaction is not committed (i.e. in in-flight or indoubt state) then it rejects the LinkFile operation as the outcome of the unlink transaction is not known.

During Unlinkfile operation, the table entry for the file is marked as unlinked. It also updates the unlink transaction id and unlink timestamp in the entry. At any given time the DLFM table can have at most one linked entry for a given file while there can be multiple unlinked entries for a file because many successive link and unlink operations can take place for the same file. The unlinked entry is used in the coordinated backup-and-restore operation to identify the correct version of the file from the archive server, if needed. In this case, the unlinked file entry is later removed by the Garbage Collector daemon (described in Section 3.5) when it is no longer needed. If file recovery is not needed, the unlinked entry is deleted in the second phase of the commit processing. Note that we could not delete the entry earlier than the second phase of commit since we would not be able to undo the action if the transaction’s outcome is abort after phase 1 (see Section 3.3).

During the link file operation, file entry check and insert must be an atomic operation (otherwise there is a small window where two child agents can both check for and not find the linked entry for a file and then insert the two linked entries for the same file). To close the window for race condition, a unique index on filename and a new check-flag is defined. During link file operation, the check-flag attribute is set to zero and during unlink file operation, the check-flag is set to Recovery id provided by the host database. This unique index prevents two linked entries but allows multiple unlinked entries for the same file.

During the forward progress of the transaction DLFM manipulates the entries in the File table as per link/unlink file operations. If transaction needs to rollback, DLFM uses the recovery mechanism provided by the local database to undo the actions. The file server, on the other hand, does not support transactional semantic in general. Thus, actual takeover or release of the file from file system is done during the second phase of the commit processing and are done by Chown daemon (described in Section 3.5). DLFM also supports the unlink of a file from one datalink column and link of the same file to another datalink column within the same transaction. This is an important customer requirement where current and old versions of the file are maintained in separate SQL tables.

3.2.1 Performance Consideration

The File table has at least one entry for each file under database control. In a production environment, hundreds of thousand or millions of entries in the File table is expected to be common. Since each link or unlink file operation needs to access the File table. Efficiency in finding and retrieving entries from the File table is essential to provide good overall performance. The first thing we did was to avoid table scan by building several indexes, one for each access path. A side benefit of avoiding table scan is that the probability of triggering lock escalation is also reduced. When the table size (cardinality) is small, the optimizer could still pick table scan even when an index is available. To ensure that the optimizer always picks the access plan we want, the statistics in the catalog are manually set before DLFM’s SQL programs are compiled and bound. In multi-user test, a different problem surfaced that was resulted partially from using multiple indexes. When multiple insert and/or delete entry operations are being done concurrently, different index may be used by different DLFM processes to access the File table. This results in frequent deadlocks because of the next key locking feature supported in local database server. Since repeatable read is not really needed by DLFM processes, that feature is turned off. With these enhancements, we were able to run 100-client workload for 24 hours without much deadlock/timeout problem in the system test. Also, the system achieved insert rate of 300 per minute and 150 updates per minute.

When a new transaction is started by the application, the host database assigns a new transaction id. In the case of an XA transaction, the host database also generates a local transaction id that is different from the global XA transaction id. A transaction id is w.r.t. a particular database such that there is no problem with transaction id being the same from different databases. The transaction id generated a specific database is guaranteed to be monotonically increasing, which is absolutely essential. This id is passed to the DLFM in each of the API invocation. The DLFM associates the transaction id with each operation that changes DLFM metadata and state. The reason is that DLFM does not have logging services of it’s own, but uses a local database for persistence and logging. By associating the transaction id along with the operation, and storing them in the database tables, it can relate the actions performed by a particular transaction. This is important because the actions done by a DLFM for a particular sub-transaction may need to be undone if the transaction aborts after the sub-transaction completing the prepare phase (i.e., completed phase 1 of the 2 phase commit protocol) in the DLFM.

It is assumed that the commit transaction processing should not fail on the DLFM side if prepare transaction processing is successful. But that is not always true because there is a major difference between database SQL commit processing and DLFM commit processing [ref. Fig 4]. The SQL commit processing does not acquire any new locks. It in fact releases all the locks acquired by the present transaction. On the other hand the DLFM uses the SQL interface to update the metadata and its state stored in its local database during commit processing. For commit, for example, DLFM retrieves entries from the File table and deletes entry from the Transaction table. This, in turn, requires additional locks to be acquired by the DLFM. Since deadlocks are always possible when new locks are acquired, a retry logic is included in the commit processing and it keeps retrying until it succeeds.

SQL Txn.

Access R1 Access R2 Prepare Txn. Commit/Abort Txn.

Write R1 log Write R2 log Force logs Release locks

(un)link file1 (un)link file2 Prepare Txn. Commit/Abort Txn.

DLFM: SQL insert SQL insert SQL insert/commit SQL delete/update/commit

During prepare transaction processing, DLFM inserts the entry into the transaction table and marks it as prepared. If DLFM fails after prepare transaction then that transaction remains in an indoubt state. It is the host database’s responsibility for resolving the indoubt transactions with the DLFM. Either host database restart processing does it, or if DLFM is unavailable at restart, host database spawns a daemon whose sole purpose is to poll the DLFM periodically and resolve the indoubts when the DLFM is up. Indoubt transactions are resolved based on the outcome of parent transactions in the host database.

The DLFM plays an important role in coordinated backup and recovery of DBMS data along with file data. When the transaction linking a file commits and the file group has recovery option equals yes, DLFM starts archiving that file to the archive server such as ADSM. The DLFM child agent (described in Section 3.5) puts the entry of file into archive table and the Copy daemon picks up the entry from archive table and writes the file to the archive server. The main purpose behind the archive table is to avoid contention in the main metadata table, the File table, and also efficiently restart copying when DLFM fails. Because multiple indexes are defined on the Archive table and size of the Archive table is small (entry gets deleted as soon as it is archived), deadlocks were encountered between child agent and Copy Daemon while accessing the Archive table. Those deadlocks were eliminated by disabling the next key locking feature in DLFM’s local database.

Note that the archiving of files is asynchronous when a transaction commits. DLFM does not hold any database locks while backup copy is being made. The asynchronous backup is only possible because DLFM takes away the "write" permission of the file during commit operation. The Backup utility on the host database side makes sure that all the files since last backup are archived to the archive server before declaring that backup is successful. In case copy of some files is pending then it asks the Copy daemon to archive this set of files with high priority.

Restore utility restores the database from the backup image on the host database side. Whenever the host database is restored, DLFM may need to retrieve files from the archive server to match the database state if the linked files are not present in the file system. The database Recovery id at the time of backup is preserved in the backup image which is sent to the DLFM during restore to reconcile its metadata. Based on this Recovery id, all the files that are linked before the backup and unlinked after the backup are restored to linked state. Similarly, files that are linked after the backup are removed from the unlink state. All these actions (entry manipulation) are done via SQL calls to local database in DLFM side and we did not find it to be an issue.

The Reconcile utility is a new database utility introduced by DataLinks for synchronizing the host database state with the DLFM metadata information. After a database is restored to a point in the past, database state and DLFM state may be out of synchronization. To bring the two sides back to consistent state, the reconcile utility is invoked. When invoked, this utility goes through each datalink column and scans all entries for the column on the host database side and then compares the information with the corresponding file status and metadata information on the DLFM side. It updates the information on either or both sides if necessary to bring the system back to a consistent state. Since the number of entries/records processed could potentially be very large, they are first stored in a temp table in the local database to reduce the number of messages between the host database and DLFM and the number of file scans. The processing on the DLFM side involves complex joins, sub-queries, and EXCEPT (difference) operation between the temp table and the File table, thus picking the right access plans is absolutely essential. To further optimizing the performance, we hand-crafted the table statistics to ensure that the database optimizer generates the best access plans.

The DLFM is a concurrent server, i.e., it has a main daemon which spawns the child agent (or a process) when a connect request from a DB2 agent is received. The child agent then establishes a connection with the requesting DB2 agent. All subsequent requests from the same connection will be served by this child agent. DLFM’s main daemon then waits for another connect request from same or different host DB2. Applications on the host DB2 side need to establish separate connections with DLFM, thus they are served by a separate child agent on the DLFM side. Besides the child agent, DLFM provides several other services implemented as daemons and they are spawned by the main DLFM daemon [ref. Figure 5]. This section describes the functionality and service provided by each of the daemons.

Figure 5: DLFM process model

• Delete Group Daemon

• Upcall Daemon

The Upcall daemon services requests from DLFF to determine if a file is in linked state. If a file is linked to database, user’s request to delete, rename, or move the file will be rejected by the DLFF. Its main purpose is to enforce referential integrity for the files linked to the host database in partial access control. Upcall to DLFM is not needed if a file is linked in full access control.

• Chown Daemon

The Chown daemon is a separate process whose effective user id is root. The Chown daemon needs super user privilege as it manipulates the information (such as ownership, permissions etc) of files belonging to different users. A child agent communicates with the Chown daemon whenever it needs to get the file information such as file system id, inode, last modification time, owner, group etc. During commit processing, the child agent sends a request (with a file name) to the Chown daemon to takeover the file (change owner and access permissions) or to release the file to the file system (restore original owner and access permissions). Since Chown daemon runs as super user, it is important to safeguard unauthorized requests. Thus, Child agent communicates with chown daemon with proper authentication.

• Copy Daemon

Copy daemon is responsible for copying linked files from file system to an archive server or disk. When a file is linked, the file will be copied asynchronously by Copy daemon if DLFM is responsible for restoring file after database restore.

• Retrieve Daemon

Retrieve daemon is responsible for restoring file from archive server or disk. When the host database is restored to a point in the past, the file system state may be out of sync with the new database state. As part of re-synchronization, files are restored by the Retrieve daemon from archive server if necessary.

As mentioned previously, we decided to use a DBMS (DB2) as a persistent store for storing DLFM metadata information. All changes to DLFM metadata are written to DB2 tables. Since standard SQL does not support two-phase commit between application and database, changes to metadata are hardened during the prepare phase of the 2PC protocol. When something go wrong in host DB2 or in other DLFM’s, the transaction will be aborted. In such cases, an abort request will be sent to the DLFM in the second phase of the 2PC protocol and DLFM would have to undo the changes even after they have already been committed in local DB2. While schemes based on compensation application technique have been proposed for achieving this, it is extremely complicated to implement one in production systems. Consequently, our design takes the delayed update approach. With this approach, delete of any metadata information is marked as "deleted" while update creates a new entry in the database table with the old entry marked "deleted". When the transaction commits (commit request in second phase), entries marked "deleted" in the (un)link processing are then deleted from database. If transaction gets aborted we only need to change these records back to normal state from the deleted state. This however, incurs a different problem. During both commit and abort processing, for example, locks will be acquired in the local database since these are normal SQL update/delete calls. This in turn may result in deadlocks or lock timeouts in both commit and abort processing. Since it is a sub-transaction and is not possible to change the outcome of a transaction in phase 2, DLFM will retry the commit/abort operation until it succeeds. Our experience is that this was not a problem.

A set of indices are defined on the DLFM tables to improve search performance. We found that deleting a record from a table having index triggers the next key locking. Since we have multiple indexes on some of frequently accessed tables, the next key locking feature results in deadlocks frequently when multiple datalink applications are running concurrently. To maintain high performance while avoid such deadlocks, we turned off the next key locking in the DLFM database.

Load and reconcile utilities tend to run for a long time and involve large number of link/unlink operations. Like any other long running transactions, there is potential for running out of system resources such as log file or lock table entry. Since very long running transactions are always triggered by database utilities that can be broken into pieces (undo of completed piece is not needed in case of the utility failure), we put intelligence in DLFM to recognize such transactions and to do local commit after finishing processing of each piece. The transaction entry is inserted into transaction table in DLFM database when a local commit is issued for the first time for a given transaction but keep the entry marked as in-flight. The same mechanism is also applied to deleting entries in batch. For example, in the delete group daemon we unlink all the files under deleted group. If large number of files are linked under one group then unlinking them in single local DB2 transaction can cause the DB2 log full error condition. So we issue commits to local DB2 periodically after processing every N records (where N is implementation dependent).

We found that commit transaction API must be synchronous with respect to host database. Desire was to release the database locks on the host DB2 side while DLFM is doing the commit processing. However, this could lead to a distributed deadlock between host database and DLFM as shown in the following scenario.

As in most distributed systems, identifying and breaking distributed deadlock is an important issue. While a distributed deadlock detector can be built in theory, it will add significant complexity and overhead to the system as host DB2 and DLFM do not communicate directly. Instead, we take a simple approach and rely on the timeout mechanism to resolve potential distributed deadlock. The problem with the timeout mechanism is that it is difficult to come up with a perfect timeout period and some transactions may get rollback unnecessarily. In our case, we set the timeout to 60 seconds and it has performed reasonably well. Another problem related to locking is lock escalation. When a DLFM process holds lots of row locks in a metadata table then it may cause the lock escalation to table level lock. The lock escalation for a high traffic table will result in timeouts for other applications. The rollback operations as a result of timeouts in turn add additional workload to the system. We observed that lock escalation in any of the metadata tables usually brings the system to its knees. Within our daemons, we are careful that they commit frequently enough so as to not cause any lock escalation. Also, applications should issue commit frequently to avoid holding large number of locks and lock list size should be set sufficiently large to avoid forced lock escalation.

Cost based optimizer is the most advanced database optimizer and it has been used in most commercial database systems. We observed that Cost based Optimizer does not take locking cost (concurrent accesses) into account when choosing an index for access. In certain cases it also chose an index which was not only sub-optimal but also caused table scan (instead of index scan) to evaluate predicates. To get the desired access plan, we wrote a utility to set the statistics in the database catalog to force optimizer to select the plan we want. While this works in the lab, issuing a runstate operation by user will overwrite the hand-crafted statistics and potentially result in sub-optimal plan being generated again. To prevent this from happening, additional logic is put into DLFM to check for changes in metadata statistics and re-invoke the utility to reset statistics and rebind plans if necessary.

In summary, DataLinks meets a very challenging application requirement that has existed for many years. DataLinks enables organizations to continue storing data (particularly large files of unstructured or semi-structured data such as documents, images, and video clips) in the file system to take advantage of file-system capabilities, while at the same time coordinating the management of these files and their contents with associated data stored in an RDBMS.

DLFM is a key component of DataLinks technology developed at the IBM Almaden Research Center. It plays a key role in enforcing access control and referential integrity as well as supporting coordinated backup and restore. DLFM uses a DBMS as a persistent store for storing its data (metadata) and state change information which takes the advantage of existing database technology and at the same time offers excellent portability. Doing this, however, has its drawbacks too. Because the DBMS used is treated as a block box, one of the major challenges is to support transactional semantic for DLFM operations. To do this, we implemented the two-phase commit protocol in DLFM and designed a innovative scheme to enable rolling back transaction update after local database commit. Also a major gotchas is that the RDBMS’ cost based optimizer generates the access plan, which does not take into account the locking costs of a concurrent workload. Using the RDBMS as a black box can cause "havoc" in terms of causing the lock timeouts and reducing the throughput of the concurrent workload. To solve the problem, we came up with a simple but effective way of influencing the optimizer to generate access plans matching the needs of DLFM implementation. And also several precautions had to be taken to ensure that lock escalation did not take place, that next key locking was disabled to avoid deadlocks on heavily used indexed and SQL tables, and that timeout mechanism was applied to break global deadlocks. In the system test, we were able to run 100-client workload for 24 hours without much deadlock/timeout problem. Also, the system achieves insert rate of 300 per minute and 150 updates per minute.

Many people contributed to building DataLinks technology over the last few years. Major contributors include Suparna Bhattachaga, Karen Brannon, Kiran Mehta, Suhas Gogate, Mahadevan Subramanian, Ajay Sood, Dale McInnis, Lindsay Hemms, Jason Gartner, Nelson Mattos, Robin Willaims, and last but not least Ashok Chandra. The authors would like to thank them for their dedication and contribution that made this paper possible.