DB2 FOR LUW / DB2FORLUW / 970 - DB2

We can not cancel admin_move_table after log full condition. It 
fails with SQL2104N RC='9' as below: 
 
$ db2 "call sysproc.admin_move_table ('tabschema', 'tabname', 
'target_tabname', '', 'CANCEL')" 
SQL2104N  The ADMIN_MOVE_TABLE procedure could not be completed 
at this time by this user.  Reason code: "9".  SQLSTATE=5UA0M . 
 
db2diag.log : 
2010-10-27-11.32.35.495997+000 E1501A846          LEVEL: Error 
PID     : 454666               TID  : 1111        PROC : db2fmp 
(C) 0 
INSTANCE: db2x01               NODE : 000 
EDUID   : 1111                 EDUNAME: db2fmp (C) 0 
FUNCTION: DB2 UDB, database application extension for utili, 
adminotm_online_table_move, probe:17531 
DATA #1 : String, 5 bytes 
SAPR3 
DATA #2 : String, 4 bytes 
MARI 
DATA #3 : String, 6 bytes 
CANCEL 
DATA #4 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes 
 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -2104   sqlerrml: 3 
 sqlerrmc: 9 
 sqlerrp : SQL09071 
 sqlerrd : (1) 0x00000000      (2) 0x00000000      (3) 
0x00000000 
           (4) 0x00000000      (5) 0x00000000      (6) 
0x00000000 
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6) 
           (7)      (8)      (9)      (10)        (11) 
 sqlstate: 
 
DB2 traces will show an entry similar to: 
adminotm_Otm_setLock exit [rc = 0xFFFFF7C8 = -2104] 
 
The problem is easy to hit whenever ADMIN_MOVE_TABLE is 
interrupted for some reason. 
 
Following query will show an entry for problematic table with 
KEY value as LOCK . 
 
db2 "select substr(TABSCHEMA,1,20), substr(TABNAME,1,20), 
substr(KEY,1,20), substr(VALUE,1,60) from 
SYSTOOLS.ADMIN_MOVE_TABLE where TABNAME='tabname'"

**************************************************************** 
* USERS AFFECTED:                                              * 
* all                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade the DB2 server to v9.7 Fix Pack 10                   * 
**************************************************************** 
CALL SYSPROC.ADMIN_MOVE_TABLE WITH CANCEL" IS FAILING WITH 
SQL2104N RC=9 AFTER LOG FULL CONDITION.

1. Remove any files with name - *adminotm* from db2dumo 
   directory. Retry calling sysproc.admin_move_table . 
2. If above suggestion does not work, then you can delete an 
   entry from protocol table SYSTOOLS.ADMIN_MOVE_TABLE where 
   key = 'LOCK' and TABNAME='tabname'. 
 
db2 "delete from SYSTOOLS.ADMIN_MOVE_TABLE where key = 'LOCK' 
and TABNAME='tabname'"

This problem was first fixed in DB2 V9.7 Fix Pack 10

1. Remove any files with name - *adminotm* from db2dumo 
directory. Retry calling sysproc.admin_move_table .          2. 
If above suggestion does not work, then you can delete an 
entry from protocol table SYSTOOLS.ADMIN_MOVE_TABLE where 
key = 'LOCK' and TABNAME='tabname'. 
db2 "delete from SYSTOOLS.ADMIN_MOVE_TABLE where key = 'LOCK' 
and TABNAME='tabname'"

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

10.11.2010
05.01.2015
05.01.2015

9.7.FP10