Additional Release Notes for FixPak 6 and Greater IBM(R) DB2(R) Universal Database Additional Release Notes for FixPak 6 and Greater Version 6.1 © Copyright International Business Machines Corporation 2001. All rights reserved. U.S. Government Users Restricted Rights -- Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp. ------------------------------------------------------------------------ Table of Contents Special Notes * 1.1 Drop Data Links Manager * 1.2 Uninstalling DLFM Components Using SMIT May Remove Additional Filesets * 1.3 Baltic Rim Code Page (Windows) Administration Guide * 2.1 DB2 Data Links Manager Considerations/Backup Utility Considerations * 2.2 DB2 Data Links Manager Considerations/Restore and Rollforward Utility Considerations * 2.3 Restoring Databases from an Offline Backup without Rolling Forward * 2.4 Restoring Databases and Table Spaces, and Rolling Forward to the End of the Logs * 2.5 DB2 Data Links Manager and Recovery Interactions * 2.6 Detection of Situations that Require Reconciliation * 2.7 Configuring DB2/DB2 Data Links Manager/Data Links Access Token Expiry Interval (dl_expint) * 2.8 DB2 Registry and Environment Variables/DB2_ANTIJOIN Application Development Guide * 3.1 Language Considerations/Programming in Java/Creating Java Applications and Applets/Applet Support Command Reference * 4.1 ADD DATALINKS MANAGER * 4.2 BACKUP DATABASE * 4.3 DROP DATALINKS MANAGER (new command) o DROP DATALINKS MANAGER * 4.4 RECONCILE * 4.5 RESTORE DATABASE * 4.6 ROLLFORWARD DATABASE DB2 Data Links Manager for AIX Quick Beginnings * 5.1 Before You Begin/Determine hostname * 5.2 Working with the Data Links File Manager: Cleaning up After Dropping a DB2 Data Links Manager from a DB2 Database * 5.3 DLFM1001E (New Error Message) * 5.4 DLFM Setup Configuration File Option DB2 Data Links Manager for Windows NT Quick Beginnings * 6.1 Before You Begin/Determine hostname * 6.2 Working with the Data Links File Manager: Cleaning up After Dropping a DB2 Data Links Manager from a DB2 Database * 6.3 DLFM1001E (New Error Message) * 6.4 DLFM Setup Configuration File Option Message Reference * 7.1 SQL0357N (Changed) * 7.2 SQL0358N (Changed) * 7.3 SQL0368N (Changed) * 7.4 SQL2077W (New) * 7.5 SQL2078N (New) * 7.6 SQL2554N (New Reason Codes 10 and 11) * 7.7 DB210200I (New) * 7.8 DB210201I (New) SQL Reference * 8.1 Language Elements/DATALINK Values * 8.2 Language Elements/DATALINK Assignments ------------------------------------------------------------------------ Special Notes ------------------------------------------------------------------------ 1.1 Drop Data Links Manager The functionality to drop a DB2 Data Links Manager for a specified database is now available. The processing of some Data Links related SQL requests, as well as utilities, such as backup/restore, involve communicating with all DLMs configured to a database. UDB did not have the capability to drop a configured DLM even though it may have not been operational. This obviously resulted in an additional overhead in SQL and utilities processing. Once a DLM was added, the engine communicated with it in the processing of requests which may have resulted in the failure of some SQL requests (e.g. drop table/tablespace/database). ------------------------------------------------------------------------ 1.2 Uninstalling DLFM Components Using SMIT May Remove Additional Filesets Before uninstalling DB2 (Version 5, 6, or 7) from an AIX machine on which the Data Links Manager is installed, follow these steps: 1. As root, make a copy of /etc/vfs using the command: cp -p /etc/vfs /etc/vfs.bak 2. Uninstall DB2. 3. As root, replace /etc/vfs with the backup copy made in step 1: cp -p /etc/vfs.bak /etc/vfs ------------------------------------------------------------------------ 1.3 Baltic Rim Code Page (Windows) DB2 UDB now supports the Baltic Rim code page, MS-1257, on Windows platforms. This code page is used for Latvian, Lithuanian, and Estonian. ------------------------------------------------------------------------ Administration Guide ------------------------------------------------------------------------ 2.1 DB2 Data Links Manager Considerations/Backup Utility Considerations Replace the second paragraph in this section with: When files are linked, the Data Links servers schedule them to be copied asynchronously to an archive server such as ADSM, or to disk. When the backup utility runs, DB2 ensures that all files scheduled for copying have been copied. At the beginning of backup processing, DB2 contacts all Data Links servers that are specified in the DB2 configuration file. If a Data Links server has one or more linked files and is not running, or stops running during the backup operation, the backup will not contain complete DATALINK information. The backup operation will complete successfully. Before the Data Links server can be marked as available to the database again, backup processing for all outstanding backups must complete successfully. If a backup is initiated when there are already twice the value of num_db_backups (see below) outstanding backups waiting to be completed on the Data Links server, the backup operation will fail. That Data Links server must be restarted and the outstanding backups completed before additional backups are allowed. ------------------------------------------------------------------------ 2.2 DB2 Data Links Manager Considerations/Restore and Rollforward Utility Considerations Replace paragraphs beginning with: When you restore a database or table space and do not specify the WITHOUT DATALINK... and When you restore a database or table space and you do specify the WITHOUT DATALINK option... with: When you restore a database or table space, the following conditions must be satisfied for the restore operation to succeed: o If any Data Links Server recorded in the backup file is not running, the restore operation will still complete successfully. Tables with DATALINK column information that are affected by the missing Data Links server will be put into datalink reconcile pending state after the restore operation (or the rollforward operation, if used) completes. Before the Data Links servers can be marked as available to the database again, this restore processing must complete successfully. o If any Data Links Server recorded in the backup file stops running during the restore operation, the restore operation will fail. The restore can be restarted with the Data Links Server down (see above). o If a previous database restore operation is still incomplete on any Data Links server, subsequent database or table space restore operations will fail until those Data Links servers are restarted, and the incomplete restore is completed. o Information about all DATALINK columns that are recorded in the backup file must exist in the appropriate Data Links servers' registration tables. If all the information about the DATALINK columns is not recorded in the registration tables, the table with the missing DATALINK column information is put into datalink reconcile not possible state after the restore operation (or the roll-forward operation, if used) completes. If the backup is not recorded in the registration tables, it may mean that the backup file that is provided is earlier than the value for num_db_backups and has already been "garbage collected". This means that the archived files from this earlier backup have been removed and cannot be restored. All tables that have DATALINK columns are put into datalink reconcile pending state. If the backup is not recorded in the registration tables, it may mean that backup processing has not yet been completed because the Data Links server is not running. All tables that have DATALINK columns are put into datalink reconcile pending state. When the Data Links server is restarted, backup processing will be completed before restore processing. The table remains available to users, but the values in the DATALINK columns may not reference the files accurately (for example, a file may not be found that matches a value for the DATALINK column). If you do not want this behavior, you can put the table into check pending state by issuing the "SET CONSTRAINTS for tablename TO DATALINK RECONCILE PENDING" statement. If, after a restore operation, you have a table in datalink reconcile not possible state, you can fix the DATALINK column data in one of the ways suggested under "Removing a Table from the Datalink_Reconcile_Not_Possible State". The note at the bottom of the first paragraph remains the same. Add the following at the end of this section: It is strongly recommended that the datalink.cfg file be archived to cover certain unusual recovery cases, since the datalink.cfg file in the database backup image only reflects the datalink.cfg as of the backup time. Having the latest datalink.cfg file is required to cover all recovery cases. Therefore, the datalink.cfg file must be backed up after every ADD DATALINKS MANAGER or DROP DATALINKS MANAGER command invocation. This would help to retrieve the latest datalink.cfg file, if the latest datalink.cfg file is not available on disk. If the latest datalink.cfg file is not available on disk, replace the existing datalink.cfg file (restored from a backup image) with the latest datalink.cfg file that was archived before running a rollforward operation. Do this after the database is restored. ------------------------------------------------------------------------ 2.3 Restoring Databases from an Offline Backup without Rolling Forward You can only restore without rolling forward at the database level, not the table space level. To restore a database without rolling forward, you can either restore a nonrecoverable database (that is, a database that uses circular logging), or specify the WITHOUT ROLLING FORWARD parameter on the RESTORE DATABASE command. If you use the restore utility with the WITHOUT DATALINK option, all tables with DATALINK columns are placed in datalink reconcile pending (DRP) state, and no reconciliation is performed with the Data Links servers during the restore operation. If you do not use the WITHOUT DATALINK option, and a Data Links server recorded in the backup file is no longer defined to the database (that is, it has been dropped using the DROP DATALINKS MANAGER command), tables that contain DATALINK data referencing the dropped Data Links server are put in DRP state by the restore utility. If you do not use the WITHOUT DATALINK option, all the Data Links servers are available, and all information about the DATALINK columns is fully recorded in the registration tables, the following occurs for each Data Links server recorded in the backup file: * All files linked after the backup image that was used for the database restore operation are marked as unlinked (because they are not recorded in the backup image as being linked). * All files that were unlinked after the backup image, but that were linked before the backup image was taken, are marked as linked (because they are recorded in the backup image as being linked). If the file was subsequently linked to another table in another database, the restored table is put into the datalink reconcile pending state. Note:The above cannot be done if the backup image that was used for the database restore operation was taken when at least one Data Links server was not running, since the DATALINK information in the backup is incomplete. The above is also not done if the backup image that was used for the database restore operation was taken after a database restore with or without rollforward. In both cases, all tables with DATALINK columns are placed in datalink reconcile pending state, and no reconciliation is performed with the Data Links servers during the restore operation. ------------------------------------------------------------------------ 2.4 Restoring Databases and Table Spaces, and Rolling Forward to the End of the Logs If you restore, then roll forward the database or table space to the end of the logs (meaning that all logs are provided), a reconciliation check is not required unless at least one of the Data Links servers recorded in the backup file is not running during the restore operation. If you are not sure whether all the logs were provided for the roll-forward operation, or think that you may need to reconcile DATALINK values, do the following: 1. Issue the SQL statement for the table (or tables) involved: SET CONSTRAINTS FOR tablename TO DATALINK RECONCILE PENDING This puts the table into datalink reconcile pending state and check pending state. 2. If you do not want a table to be in check pending state, issue the following SQL statement: SET CONSTRAINTS FOR tablename IMMEDIATE CHECKED This takes the table out of check pending state, but leaves it in datalink reconcile pending state. You must use the reconcile utility to take the table out of this state. It may happen that the backup file contains DATALINK data that refers to a DB2 Data Links Manager (that is, a DB2 Data Links Manager was registered to the database when the backup was taken) that has been dropped from the database. For each table space being rolled forward that contains at least one table with DATALINK data referencing the dropped DB2 Data Links Manager, all tables are put in DRP state by the rollforward utility. ------------------------------------------------------------------------ 2.5 DB2 Data Links Manager and Recovery Interactions The following table shows the different types of recovery that you can perform, the DB2 Data Links Manager processing that occurs during restore and roll-forward processing, and whether you need to run the Reconcile utility after the recovery is complete: Type of Recovery DB2 Data Links Manager DB2 Data Links Manager Reconcile Processing during Restore Processing during Rollforward Non-recoverable database (logretain=NO) Database restore Fast reconcile is performed N/A Can be optionally of a complete run if problem with backup, all Data file links is Links Servers up suspected Database restore Tables put in N/A Required using WITHOUT Datalink_Reconcile _Pending DATALINK option state Database restore Fast reconcile is performed NA Required for tables of a complete only on those tables in in table spaces backup, at least table spaces that do not with links to the one Data Links have links to a Data Links Data Links server server down server that is down, other that is down tables put in Datalink_Reconcile_Pending state Database restore Fast reconcile is not NA Required of an incomplete performed, all tables with backup, all Data DATALINK columns put in Links servers up Datalink_Reconcile_Pending state Recoverable database (logretain=YES) Database restore Fast reconcile is performed N/A Optional using WITHOUT ROLLING FORWARD option, using a complete backup, all Data Links servers up Database restore Tables put in N/A Required using WITHOUT Datalink_Reconcile _Pending ROLLING FORWARD state and WITHOUT DATALINK options, using a complete or incomplete backup, Data Links servers up or down Database restore Fast reconcile is performed N/A Required on tables using WITHOUT only on those tables in in table spaces ROLLING FORWARD table spaces that do not with links to the option, using a have links to the Data Data Links servers complete backup, Links servers that are that are down at least one Datadown, other tables put in Links server downDatalink_Reconcile_Pending state Database restore Fast reconcile is not N/A Required using WITHOUT performed, all tables with ROLLING FORWARD DATALINK columns put into option, using an Datalink_Reconcile_Pending incomplete state backup, Data Links servers up or down Database restore No action No action Optional and roll forward to end of logs, using a complete backup, all Data Links servers up Database restore No action No action Optional and roll forward to end of logs, using a complete backup, at least one Data Links server down during roll forward processing Database restore No action All tables with DATALINK Required for all and roll forward columns put into tables with to end of logs, Datalink_Reconcile_Pending DATALINK columns using a complete state or an incomplete backup, any Data Links server down during restore Database restore No action No action Optional and roll forward to end of logs, using an incomplete backup, all Data Links servers up during restore Database restore No action All tables in table spaces Required and roll forward with links to a Data Links to end of logs, server where the backup is using a complete unknown put in or an incomplete Datalink_Reconcile_Pending backup, all Data state Links servers up, backup unknown at any Data Links server Table space No action No action Optional restore and roll forward to end of logs, using a complete backup, all Data Links servers up Table space No action No action Optional restore and roll forward to end of logs, using a complete backup, at least on Data Links server down during roll forward processing Table space No action All tables in table spaces Required for tables restore and roll with links to any Data in table spaces forward to end of Links server that is down with links to any logs, using a put into Data Links server complete or an Datalink_Reconcile_Pending that is down incomplete state backup, any Data Links server down during restore processing Table space No action No action Optional restore and roll forward to end of logs, using an incomplete backup, all Data Links servers up Database restore No action Tables put in Required and roll forward Datalink_Reconcile _Pending to a point in state time, using a complete or an incomplete backup, Data Links servers up or down during restore and/or roll forward processing Table space No action Tables put in Required restore and roll Datalink_Reconcile _Pending forward to a state point in time, using a complete or an incomplete backup, Data Links servers up or down during restore and/or rollfoward processing Database restore Tables put in N/A Optional, but to a different Datalink_Reconcile tables in database name, _Not_Possible state Datalink_Reconcile alias, hostname, _Not_Possible state or instance with must be manually no roll forward fixed (NOTE1) Database restore No action Tables put in Optional, but to a different Datalink_Reconcile tables in database name, _Not_Possible state Datalink_Reconcile alias, hostname _Not_Possible state or instance, and must be manually roll forward fixed Database restore Tables put in No action Required from an unusable Datalink_Reconcile _Pending backup (image hasstate been garbage-collected on the Data Links server) with no roll forward (NOTE1), with or without WITHOUT DATALINK option Database restore No action Tables put in Required from an unusable Datalink_Reconcile _Pending backup (image has state been garbage-collected on the Data Links server), and roll forward, with or without WITHOUT DATALINK option Table space No action Tables put in Required restore from an Datalink_Reconcile _Pending unusable backup state (image has been garbage-collected on the Data Links server), and roll forward Notes: 1. A restore using an offline backup and the WITHOUT ROLLING FORWARD option (logretain is on), or a restore using an offline backup (logretain is off). 2. A complete backup is a backup taken when all required Data Links servers were running. An incomplete backup is a backup taken when at least one required Data Links server was not running. 3. Fast reconcile processing will not be performed if the backup image that was used for the database restore operation was taken after a database restore, with or without rollforward. In this case, all tables with DATALINK columns are put in Datalink_Reconcile_Pending state. ------------------------------------------------------------------------ 2.6 Detection of Situations that Require Reconciliation Following are some situations in which you may need to run the reconcile utility: * The entire database is restored and rolled forward to a point in time. Because the entire database is rolled forward to a committed transaction, no tables will be in check pending state (due to referential constraints or check constraints). All data in the database is brought to a consistent state. The DATALINK columns, however, may not be synchronized with the metadata in the DB2 Data Links Manager, and reconciliation is required. In this situation, tables with DATALINK data will already be in DRP state. You should invoke the reconcile utility for each of these tables. * A particular Data Links server running the DB2 Data Links Manager loses track of its metadata. This can occur for different reasons. For example: o The Data Links server was cold started. o The Data Links server metadata was restored to a back-level state. In some situations, such as during SQL UPDATEs and DELETEs, DB2 may be able to detect a problem with the metadata in a Data Links server. In these situations, the SQL statement would fail. You would put the table in DRP state by using the SET CONSTRAINTS statement, then run the reconcile utility on that table. * A file system is not available (for example, because of a disk crash) and is not restored to the current state. In this situation, files may be missing. * A DB2 Data Links Manager is dropped from a database, and there are DATALINK FILE LINK CONTROL values referencing that DB2 Data Links Manager. You should run the reconcile utility on such tables. ------------------------------------------------------------------------ 2.7 Configuring DB2/DB2 Data Links Manager/Data Links Access Token Expiry Interval (dl_expint) Contrary to the documentation, if dl_expint is set to "-1", the access control token expires. The workaround for this is to set dl_expint to its maximum value, 31536000 (seconds). This corresponds to an expiration time of one year, which should be adequate for all applications. ------------------------------------------------------------------------ 2.8 DB2 Registry and Environment Variables/DB2_ANTIJOIN The default value for this variable is has been changed to NO in a DB2 UDB EEE environment and YES in other environments. The description of this variable has been changed to: In DB2 Universal Database Enterprise - Extended Edition environments, specifying YES causes the optimizer to search for opportunities to transform NOT EXISTS subqueries into anti-joins that can be processed more efficiently by DB2. In other environments, specifying NO causes the optimizer to limit the opportunities for transforming NOT EXISTS subqueries into anti-joins. ------------------------------------------------------------------------ Application Development Guide ------------------------------------------------------------------------ 3.1 Language Considerations/Programming in Java/Creating Java Applications and Applets/Applet Support It is essential that the db2java.zip file used by the Java applet be at the same FixPak level as the JDBC applet server. Under normal circumstances, db2java.zip is loaded from the Web Server where the JDBC applet server is running, as shown in Figure 10 of the book. This ensures a match. If, however, your configuration has the Java applet loading db2java.zip from a different location, a mismatch can occur. Prior to FixPak 6, this could lead to unexpected failures. As of FixPak 6, matching FixPak levels between the two files is strictly enforced at connection time. If a mismatch is detected, the connection is rejected, and the client receives one of the following exceptions: * If db2java.zip is at FixPak 6 or later: COM.ibm.db2.jdbc.DB2Exception: [IBM][JDBC Driver] CLI0621E Unsupported JDBC server configuration. * If db2java.zip is prior to FixPak 6: COM.ibm.db2.jdbc.DB2Exception: [IBM][JDBC Driver] CLI0601E Invalid statement handle or statement is closed. SQLSTATE=S1000 If a mismatch occurs, the JDBC applet server logs one of the following messages in the jdbcerr.log file: * If the JDBC applet server is at FixPak 6 or later: jdbcFSQLConnect: JDBC Applet Server and client (db2java.zip) versions do not match. Unable to proceed with connection., einfo= -111 * If the JDBC applet server is prior to FixPak 6: jdbcServiceConnection(): Invalid Request Received., einfo= 0 ------------------------------------------------------------------------ Command Reference ------------------------------------------------------------------------ 4.1 ADD DATALINKS MANAGER The required authorization level for this command is one of the following: * sysadm * sysctrl * sysmaint The following usage note should be added: This command is effective only after all applications have been disconnected from the database. The DB2 Data Links Manager being added must be completely set up and running for this command to be successful. The database must also be registered on the DB2 Data Links Manager using the dlfm add_db command. The maximum number of DB2 Data Links Managers that can be added to a database is 16. ------------------------------------------------------------------------ 4.2 BACKUP DATABASE DB2 Data Links Manager Considerations: If one or more Data Links servers are configured for the database, the backup operation will succeed, even if a Data Links server is not available. When the Data Links server restarts, backup processing will be completed on that Data Links server before it becomes available to the database again. Note:If there are twice as many backups still waiting for an unavailable Data Links server as are retained in the history file for the database (database configuration parameter num_db_backups), the backup operation will fail. ------------------------------------------------------------------------ 4.3 DROP DATALINKS MANAGER (new command) DROP DATALINKS MANAGER Drops a DB2 Data Links Manager from the list of registered DB2 Data Links Managers for a specified database. Authorization One of the following: * sysadm * sysctrl * sysmaint Command Syntax >>-DROP DATALINKS MANAGER FOR----+-DATABASE-+--dbname---USING---> '-DB-------' >----name------------------------------------------------------>< Command Parameters DATABASE dbname Specifies a database name. USING name Specifies the name of the DB2 Data Links Manager server as shown by the LIST DATALINKS MANAGER command. Examples Example 1 Dropping a DB2 Data Links Manager micky.almaden.ibm.com from database test under instance validate residing on host bramha.almaden.ibm.com when some database tables have links to micky.almaden.ibm.com. It is extremely important that the following steps be taken when dropping a DB2 Data Links Manager. 1. Take a database backup for database test. 2. If there are any links to micky.almaden.ibm.com, unlink them: a. Log on with a user ID belonging to SYSADM_GROUP and obtain an exclusive mode connection the the database test. connect to test in exclusive mode Ensure that this is the only connection to test using that user ID. This will prevent any new links from being created. b. Obtain a list of all FILE LINK CONTROL DATALINK columns and the tables containing them in the database. select tabname, colname from syscat.columns where substr(dl_features, 2, 1) = 'F' c. For each FILE LINK CONTROL DATALINK column in the list, issue SQL SELECT to determine if links to micky.almaden.ibm.com exist. For example, for a DATALINK column c in table t, the SELECT statement would be: select count(*) from t where dlurlserver(t.c) = 'MICKY.ALMADEN.IBM.COM' d. For each FILE LINK CONTROL DATALINK column containing such links, issue SQL UPDATE to unlink values which are links to micky.almaden.ibm.com. For example, for a DATALINK column c in table t, the UPDATE statement would be: update t set t.c = null where dlurlserver(t.c) = 'MICKY.ALMADEN.IBM.COM' If t.c is not nullable, the following can be used instead: update t set t.c = dlvalue('') where dlurlserver(t.c) = 'MICKY.ALMADEN.IBM.COM' 3. Issue the DROP DATALINKS MANAGER command: drop datalinks manager for db test using node micky.almaden.ibm.com 4. Terminate the exclusive mode connection to make the changes effective and to allow other connections to the database: terminate 5. Initiate unlink processing and garbage collection of backup information for test on micky.almaden.ibm.com. As DB2 Data Links Manager Administrator, issue the following command on micky.almaden.ibm.com: dlfm drop_dlm test validate bramha.almaden.ibm.com This will unlink any files that are still linked to database test, just in case the user has missed unlinking them before invoking step 3. If micky.almaden.ibm.com has backup information (for example, archive files, metadata) for files previously linked to database test, this command will initiate garbage collection of that information. The actual unlinking and garbage collection will be performed asynchronously. Example 2 Deleting DATALINK values that are links to files on a DB2 Data Links Manager called micky.almaden.ibm.com, when the Manager has already been dropped from database test. This may be required if steps in Example 1 were not followed while dropping micky.almaden.ibm.com. SQL DELETE, SELECT, and UPDATE statements will not be successful for such DATALINK values (SQL0368). The user must run a reconcile operation for each table that contains such DATALINK values. Each DATALINK value that was a link to micky.almaden.ibm.com will be updated to NULL or a zero-length DATALINK value. Any row containing such a value will be inserted into the exception table (if one was specified). However, the DATALINK value will not include the prefix name. The prefix name in the original DATALINK value is no longer obtainable by the system, because micky.almaden.ibm.com has been dropped. For example, if the original DATALINK value was 'http://host.com/dlfs/x/y/a.b' and '/dlfs' is the prefix name, the DATALINK value in the exception table will contain 'http://host.com/x/y/a.b'. The files referenced by these DATALINK values will continue to remain in linked state on the DB2 Data Links Manager. The dlfm drop_dlm command can be issued on micky.almaden.ibm.com to initiate unlink processing for these files. If micky.almaden.ibm.com has backup information (for example, archive files, metadata) for files previously linked to database test, this command will initiate garbage collection of that information. The actual unlinking and garbage collection will be performed asynchronously. Example 3 Multiple incarnations of a DB2 Data Links Manager micky.almaden.ibm.com for a database test. This scenario demonstrates that a DB2 Data Links Manager can be re-registered after being dropped, and that it is then treated as a completely new DB2 Data Links Manager. The following steps are only illustrative of a scenario that is possible. If, as recommended, the steps in Example 1 are followed for dropping micky.almaden.ibm.com, links to the older incarnation of micky.almaden.ibm.com will not exist; that is, one will not see error SQL0368 in step 7 below. 1. Register micky.almaden.ibm.com to database test: add datalinks manager for db test using node micky.almaden.ibm.com port 14578 2. Create links to files on micky.almaden.ibm.com: connect to test create table t(c1 int, c2 datalink linktype url file link control mode db2options) insert into t values(1, dlvalue('file://micky.almaden.ibm.com/pictures/yosemite.jpg')) terminate 3. Drop micky.almaden.ibm.com from database test: drop datalinks manager for db test using micky.almaden.ibm.com 4. Select DATALINK values: connect to test select * from t terminate The user will see: SQL0368 The DB2 Data Links Manager "MICKY.ALMADEN.IBM.COM" is not registered to the database. SQLSTATE=55022. 5. Register micky.almaden.ibm.com to database test again: add datalinks manager for db test using node micky.almaden.ibm.com port 14578 6. Insert more DATALINK values: connect to test insert into t values(2, dlvalue('file://micky.almaden.ibm.com/pictures/tahoe.jpg')) 7. Select DATALINK values: select c2 from t where c1 = 2 is successful because the value being selected is a link to the currently registered incarnation of micky.almaden.ibm.com. select c2 from t where c1 = 1 returns: SQL0368 The DB2 Data Links Manager "MICKY.ALMADEN.IBM.COM" is not registered to the database. SQLSTATE=55022. because the value being selected is a link to the incarnation of micky.almaden.ibm.com which was dropped in step 3 above. Usage Notes The effects of the DROP DATALINKS MANAGER command cannot be rolled back. It is extremely important to follow the steps outlined in Example 1 when using the DROP DATALINKS MANAGER command. This command is effective only after all applications have been disconnected from the database. Upon successful completion of the command, the user is informed (DB210201I) that no processing has been done on the DB2 Data Links Manager. Before dropping a DB2 Data Links Manager, the user must ensure that the database does not have any links to files on that DB2 Data Links Manager. If links do exist in the database after a DB2 Data Links Manager has been dropped, the user must run the reconcile utility to get rid of them. The reconcile utility will set these links to NULL (if the DATALINK column is nullable), or to a zero-length DATALINK value. Files corresponding to links between a database and a dropped DB2 Data Links Manager remain in linked state. That is, they are inaccessible to operations like read, write, rename, delete, change of permissions, or change of ownership. Archived copies of unlinked files on the DB2 Data Links Manager will not be garbage collected by this command. However, users can explicitly initiate unlink processing and garbage collection using the dlfm drop_dlm command on the DB2 Data Links Manager. It is recommended that a database backup be taken before dropping a DB2 Data Links Manager. In addition, ensure that all replication subscriptions have replicated all changes involving this DB2 Data Links Manager. If a backup was taken before the DB2 Data Links Manager was dropped from a database, and that backup image is used to restore after that DB2 Data Links Manager was dropped, restore or rollforward processing may put certain tables in datalink reconcile pending (DRP) state. ------------------------------------------------------------------------ 4.4 RECONCILE The following usage note should be added to the command description: During reconciliation, attempts are made to link files which exist according to table data, but which do not exist according to Data Links Manager metadata, if no other conflict exists. A required DB2 Data Links Manager is one which has a referenced DATALINK value in the table. Reconcile tolerates unavailability of a required DB2 Data Links Manager as well as those that are configured to the database but are not part of the table data. If an exception table is not specified, the exception report file (filename.exp) will have the host name, file name, column ID, and reason code for each of the DATALINK column values for which file references could not be re-established. If the file reference could not be re-established because the DB2 Data Links Manager itself was dropped from the database using the DROP DATALINKS MANAGER command, the file name reported in the exception report file is not the full file name; that is, the prefix part is missing. In the exception table for the DATALINK values whose DB2 Data Links Manager is dropped or is not available, the file name in the DATALINK value is not the full file name. The prefix part is missing. For example, if the original DATALINK value was 'http://host.com/dlfs/x/y/a.b', the value reported in the exception table will be 'http://host.com/x/y/a.b'; that is, the prefix name 'dlfs' will not be included. The exception report file in this case will have 'x/y/a.b'; that is, the prefix name 'dlfs' will not be included. At the end of the reconciliation process, the table is taken out of datalink reconcile pending (DRP) state only if reconcile processing is complete on all the required DB2 Data Links Managers. If reconcile processing is pending on any of the required DB2 Data Links Managers (because they were unavailable), the table will remain, or be placed, in DRP state. The following should be added to the list of possible violations: 00010-DB2 Data Links Manager referenced by the DATALINK value has been dropped from the database using the DROP DATALINKS MANAGER command. In this case, the corresponding DATALINK value in the exception table will not contain the prefix name. For example, if the original DATALINK value was 'http://host.com/dlfs/prfx/x/y/a.b', and '/dlfs/prfx' is the prefix name, the exception table will contain 'http://host.com/x/y/a.b'. ------------------------------------------------------------------------ 4.5 RESTORE DATABASE The second paragraph in the section entitled "DB2 Data Links Manager Considerations" should be replaced with: If one or more Data Links servers are configured for the database, the restore operation will succeed, even if a Data Links server is not available. When the Data Links server restarts, restore processing will be completed on that Data Links server before it becomes available to the database again. NOTE: If a database restore operation is still waiting for an unavailable Data Links server, any subsequent database or table space restore operations will fail. ------------------------------------------------------------------------ 4.6 ROLLFORWARD DATABASE The second paragraph in the section entitled "DB2 Data Links Manager Considerations" should be replaced with: If one or more Data Links servers are configured for the database, the rollforward operation will succeed, even if a Data Links server is not available. When the Data Links server restarts, rollforward processing will be completed on that Data Links server before it becomes available to the database again. ------------------------------------------------------------------------ DB2 Data Links Manager for AIX Quick Beginnings ------------------------------------------------------------------------ 5.1 Before You Begin/Determine hostname You must determine the names of each of your DB2 servers and Data Links servers. You will need to know these hostnames to verify the installation. When connecting to a DB2 Data Links File Manager, the DB2 UDB server internally sends the following information to the DLFM: * Database name * Instance name * Hostname The DLFM then compares this information with its internal tables to decide whether the connection should be allowed. It will allow the connection only if this combination of database name, instance name, and hostname has been registered with it, using the dlfm add_db command. The hostname that is used in the dlfm add_db command must exactly match the hostname that is internally sent by the DB2 UDB server. The exact hostname that should be used is obtained as follows: 1. Enter the hostname command on your DB2 server. For example, this command might return db2server. 2. Now enter the host db2server command, where db2server is the name obtained in the previous step. This command should return output similar to the following: db2server.services.com is 9.11.302.341, Aliases: db2server db2server.services.com is the value that should be used for the hostname when registering a DB2 UDB database using the dlfm add_db command. The DB2 server's internal connections to the DLFM will fail if any other aliases are used in the dlfm add_db command. A Data Links server is registered to a DB2 database using the DB2 "add datalinks manager for database database_alias using node hostname port port_number" command. The hostname is the name of the Data Links server. Any valid alias of the Data Links server can be used in this command. DATALINK values that are references to this Data Links server must specify hostname in the URL value; that is, the exact name that was used in the "add datalinks manager" command must be used when assigining URL values to DATALINK columns. Using a different alias will cause the SQL statement to fail. ------------------------------------------------------------------------ 5.2 Working with the Data Links File Manager: Cleaning up After Dropping a DB2 Data Links Manager from a DB2 Database When a DB2 Data Links Manager is dropped from a database using the DROP DATALINKS MANAGER command, the command itself does not clean up the corresponding information on the DB2 Data Links Manager. Users can explicitly initiate unlinking of any files linked to the database and garbage collection of backup information. This can be done using the dlfm drop_dlm command. This command initiates asynchronous deletion of all information for a particular database. The DB2 Data Links Manager must be running for this command to be successful. It is extremely important that this command only be used after dropping a DB2 Data Links Manager; otherwise, important information about the DB2 Data Links Manager will be lost and cannot be recovered. To initiate unlink processing and garbage collection of backup information for a particular database: 1. Log on to the system as the DB2 Data Links Manager Administrator. 2. Issue the following command: dlfm drop_dlm database instance hostname where: database is the name of the remote DB2 UDB database; instance is the instance under which the database resides; and hostname is the host name of the DB2 UDB server on which the database resides. 3. Log off. For a complete usage scenario that shows the context in which this command should be used, see the Command Reference. A new error code has been created for this command (see 5.3, DLFM1001E (New Error Message)). ------------------------------------------------------------------------ 5.3 DLFM1001E (New Error Message) DLFM1001E: Error in drop_dlm processing. Cause: The Data Links Manager was unable to initiate unlink and garbage collection processing for the specified database. This can happen because of any of the following reasons: * The Data Links Manager is not running. * An invalid combination of database, instance, and hostname was specified in the command. * There was a failure in one of the component services of the Data Links Manager. Action: Perform the following steps: 1. Ensure that the Data Links Manager is running. Start the Data Links Manager if it is not already running. 2. Ensure that the combination of database, instance, and hostname indeed identifies a registered database. You can do this using the "dlfm list registered databases" command on the Data Links Manager. 3. If an error still occurs, refer to information in the db2diag.log file to see if any component services (for example, the Connection Management Service, the Transaction Management Service, and so on) have failed. Note the error code in db2diag.log, and take the appropriate actions suggested under that error code. ------------------------------------------------------------------------ 5.4 DLFM Setup Configuration File Option The dlfm setup dlfm.cfg option has been removed. Any references to it in the documentation should be ignored. ------------------------------------------------------------------------ DB2 Data Links Manager for Windows NT Quick Beginnings ------------------------------------------------------------------------ 6.1 Before You Begin/Determine hostname You must determine the names of each of your DB2 servers and Data Links servers. You will need to know these hostnames to verify the installation. When connecting to a DB2 Data Links File Manager, the DB2 UDB server internally sends the following information to the DLFM: * Database name * Instance name * Hostname The DLFM then compares this information with its internal tables to decide whether the connection should be allowed. It will allow the connection only if this combination of database name, instance name, and hostname has been registered with it, using the dlfm add_db command. The hostname that is used in the dlfm add_db command must exactly match the hostname that is internally sent by the DB2 UDB server. The exact hostname that should be used is obtained as follows: 1. Enter the hostname command on your DB2 server. For example, this command might return db2server. 2. Now enter the nslookup db2server command, where db2server is the name obtained in the previous step. This command should return output similar to the following: Server: dnsserv.services.com Address: 9.21.14.135 Name: db2server.services.com Address: 9.21.51.178 db2server.services.com is the value that should be used for the hostname when registering a DB2 UDB database using the dlfm add_db command. The DB2 server's internal connections to the DLFM will fail if any other aliases are used in the dlfm add_db command. A Data Links server is registered to a DB2 database using the DB2 "add datalinks manager for database database_alias using node hostname port port_number" command. The hostname is the name of the Data Links server. Any valid alias of the Data Links server can be used in this command. DATALINK values that are references to this Data Links server must specify hostname in the URL value; that is, the exact name that was used in the "add datalinks manager" command must be used when assigining URL values to DATALINK columns. Using a different alias will cause the SQL statement to fail. ------------------------------------------------------------------------ 6.2 Working with the Data Links File Manager: Cleaning up After Dropping a DB2 Data Links Manager from a DB2 Database When a DB2 Data Links Manager is dropped from a database using the DROP DATALINKS MANAGER command, the command itself does not clean up the corresponding information on the DB2 Data Links Manager. Users can explicitly initiate unlinking of any files linked to the database and garbage collection of backup information. This can be done using the dlfm drop_dlm command. This command initiates asynchronous deletion of all information for a particular database. The DB2 Data Links Manager must be running for this command to be successful. It is extremely important that this command only be used after dropping a DB2 Data Links Manager; otherwise, important information about the DB2 Data Links Manager will be lost and cannot be recovered. To initiate unlink processing and garbage collection of backup information for a particular database: 1. Log on to the system as the DB2 Data Links Manager Administrator. 2. Issue the following command: dlfm drop_dlm database instance hostname where: database is the name of the remote DB2 UDB database; instance is the instance under which the database resides; and hostname is the host name of the DB2 UDB server on which the database resides. 3. Log off. For a complete usage scenario that shows the context in which this command should be used, see the Command Reference. A new error code has been created for this command (see 5.3, DLFM1001E (New Error Message)). ------------------------------------------------------------------------ 6.3 DLFM1001E (New Error Message) DLFM1001E: Error in drop_dlm processing. Cause: The Data Links Manager was unable to initiate unlink and garbage collection processing for the specified database. This can happen because of any of the following reasons: * The Data Links Manager is not running. * An invalid combination of database, instance, and hostname was specified in the command. * There was a failure in one of the component services of the Data Links Manager. Action: Perform the following steps: 1. Ensure that the Data Links Manager is running. Start the Data Links Manager if it is not already running. 2. Ensure that the combination of database, instance, and hostname indeed identifies a registered database. You can do this using the "dlfm list registered databases" command on the Data Links Manager. 3. If an error still occurs, refer to information in the db2diag.log file to see if any component services (for example, the Connection Management Service, the Transaction Management Service, and so on) have failed. Note the error code in db2diag.log, and take the appropriate actions suggested under that error code. ------------------------------------------------------------------------ 6.4 DLFM Setup Configuration File Option The dlfm setup dlfm.cfg option has been removed. Any references to it in the documentation should be ignored. ------------------------------------------------------------------------ Message Reference ------------------------------------------------------------------------ 7.1 SQL0357N (Changed) SQL0357N The DB2 Data Links Manager "" is not currently available. Reason code = "". Explanation: 04 The DB2 Data Links Manager is an unknown server. User Response: 04 Check that the DB2 Data Links Manager is available on the network. ------------------------------------------------------------------------ 7.2 SQL0358N (Changed) SQL0358N Unable to access file referenced by a DATALINK value. Reason code = "". User Response: 21 Correct the data location format. ------------------------------------------------------------------------ 7.3 SQL0368N (Changed) SQL0368N The DB2 Data Links Manager is not registered to the database. Explanation: The DB2 Data Links Manager is not registered to the database. Registered DB2 Data Links Managers are ignored if the database manager configuration parameter DATALINKS is set to NO. The DB2 Data Links Manager may have been dropped using the DROP DATALINKS MANAGER command. It is possible that a new incarnation of the DB2 Data Links Manager with the same name is currently registered; in that case, the error pertains to one or more previously dropped incarnations of that DB2 Data Links Manager. User Response: Ensure that the database manager configuration parameter DATALINKS is set to YES. DATALINK values that are links to previously dropped DB2 Data Links Managers should be removed using the reconcile utility. For additional details, see the usage notes for the DROP DATALINKS MANAGER command in the Command Reference. ------------------------------------------------------------------------ 7.4 SQL2077W (New) SQL2077W Reconcile processing completed successfully on DB2 Data Links Managers that were up and are pending on DB2 Data Links Managers that were down. Check the db2diag.log for details. Cause: Some/All of the DB2 Data Links Managers referred to in the table data were down during reconcile processing. Reconcile processing completed successfully on DB2 Data Links Managers that were up. Table is placed in DataLink Reconcile Pending (DRP) state, since reconcile processing is pending on DB2 Data Links Managers that were down. Action: Table will be taken out of DataLink Reconcile Pending state when reconcile is completed successfully on all DB2 Data Links Managers referred to in the table data. Bring up the DB2 Data Links Managers that were down and run reconcile again. ------------------------------------------------------------------------ 7.5 SQL2078N (New) SQL2078N The DB2 Data Links Manager could not be added or dropped successfully. Reason code = "". Explanation: The DB2 Data Links Manager could not be added or dropped, as indicated by one of the following reason codes: 01 The DB2 Data Links Manager being added is already registered to the database. 02 The DB2 Data Links Manager being dropped is not registered to the database. 03 The database manager configuration parameter DATALINKS is set to NO. 04 The database already has the maximum allowed number of DB2 Data Links Managers registered to it. User Response: The action depends on the reason code, as follows: 01 Do not attempt to add the DB2 Data Links Manager more than once. 02 Do not attempt to drop a DB2 Data Links Manager that is not registered. 03 Set the database manager configuration parameter DATALINKS to YES, using the UPDATE DATABASE MANAGER CONFIGURATION command, and attempt the operation again. 04 Do not attempt to add more than the maximum allowed number of DB2 Data Links Managers. ------------------------------------------------------------------------ 7.6 SQL2554N (New Reason Codes 10 and 11) The following reason codes have been added to message SQL2554N: Reason code 10 Under "Explanation": Table is in check pending state. Under "User Response": To run reconcile on a table, the table should not be in check pending state. To remove the check pending state, use the set constraints command. Reason code 11 Under "Explanation": A required DB2 Data Links Manager goes down during exception handling. Table is placed in DataLink Reconcile Pending state. Under "User Response": Run reconcile again. ------------------------------------------------------------------------ 7.7 DB210200I (New) DB210200I All applications must disconnect from the database before the changes become effective. Explanation: The ADD or DROP DATALINKS MANAGER command has been successfully processed. However, the change cannot be made effective until all applications have disconnected from the database. After the applications disconnect from the database, the first connection to the database will cause the changes to become effective. User Response: Ensure that all applications disconnect from the database and issue a CONNECT statement. ------------------------------------------------------------------------ 7.8 DB210201I (New) DB210201I Reconcile utility should be run on database tables that contain links to files on the DB2 Data Links Manager. The DB2 Data Links Manager does not perform any unlink processing for these files. Explanation: The DROP DATALINKS MANAGER command has been successfully processed. It is the user's responsibility to verify that no database tables contained links to files on the DB2 Data Links Manager before dropping that DB2 Data Links Manager. However, if such links exist, they should be removed from the database tables by using the reconcile utility. Note that the files themselves will remain in linked state on the file system. For additional details, see the usage notes for the DROP DATALINKS MANAGER command in the Command Reference. ------------------------------------------------------------------------ SQL Reference ------------------------------------------------------------------------ 8.1 Language Elements/DATALINK Values The manual incorrectly states that: * If a value for scheme is not specified, the default value "FILE" is used. * If a value for file server name is not specified, the default value is the name of the database server. In fact, both scheme and file server name must be explicitly specified in the URL. The description of these variables has been changed to: scheme For URLs this is a value such as HTTP or FILE. The value, no matter what case it is entered in, is stored in the database in upper case. file server name The complete address of the file server. The value, no matter what case it is entered in, is stored in the database in upper case. ------------------------------------------------------------------------ 8.2 Language Elements/DATALINK Assignments A paragraph in this section has been changed to the following: Note that the size of a URL parameter or function result is the same on both input or output and is bound by the length of the DATALINK column. However, in some cases the URL value returned has an access token attached. In situations where this is possible, the output location must have sufficient storage space for the access token and the length of the DATALINK column. Hence, the actual length of the comment and URL in its fully expanded form provided on input should be restricted to accommodate the output storage space. If the restricted length is exceeded, this error is raised.