版次注意事項


|10.15 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: |

|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. |

|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. |

  1. |Take a database backup for database TEST.
  2. |If there are any links to micky.almaden.ibm.com, unlink them:

    |

    1. |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.
    2. |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'
    3. |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'
    4. |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'
    5. |Commit this SQL UPDATE:

      |commit
      |
  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'))
    |   commit
    |   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'))
    |   commit
    | 
  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.


[ 頁面頂端 | 前一頁 | 下一頁 | 目錄 | 索引 ]