|Drops a DB2 Data Links Manager from the list of registered DB2 Data Links |Managers for a specified database.
|Authorization
|One of the following: |
|Command Syntax
|>>-DROP DATALINKS MANAGER FOR----+-DATABASE-+--dbname---USING---> | '-DB-------' | |>----name------------------------------------------------------>< |
|Command Parameters |
|Example
|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. |
|
| 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.
|select tabname, colname from syscat.columns where substr(dl_features,2,1)='F'
| select count(*) from t where dlurlserver(t.c) = 'MICKY.ALMADEN.IBM.COM'
|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'
|commit
|
| drop datalinks manager for db test using node micky.almaden.ibm.com
| terminate
| 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. |
| add datalinks manager for db test using node micky.almaden.ibm.com port 14578
| 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')) | commit | terminate
| drop datalinks manager for db test using micky.almaden.ibm.com
| 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.
| add datalinks manager for db test using node micky.almaden.ibm.com port 14578
| connect to test | insert into t values(2, dlvalue('file://micky.almaden.ibm.com/pictures/ | tahoe.jpg')) | commit |
| 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.