IBM Support

admin_move_table failed with SQL0668N RC=10

Troubleshooting


Problem

After upgrade database from v9.5 to an upper level, admin_move_table failed with SQL0668N RC=10 for a some specific tables

Symptom


admin_move_table() failed with SQL0668N RC=10:

db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','TABLE1','TS_LARGE','TS_LARGE','TS_LARGE','','','','','',' MOVE')"

SQL0668N Operation not allowed for reason code "10" on table "DB2INST1.TABLE1". SQLSTATE=57016

From db2diag.log:

2015-01-21-01.24.22.006255+480 I10284545A1026 LEVEL: Severe
PID : 13631506 TID : 7713 PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-71 APPID: *LOCAL.db2inst1.150120161758
AUTHID : DB2INST1 HOSTNAME: db202
EDUID : 7713 EDUNAME: db2agent (SAMPLE)
FUNCTION: DB2 UDB, catalog services, sqlrlalt, probe:9122
MESSAGE : ZRC=0x8004002E=-2147221458=SQLD_ACCESSRESTRICTED
"ACCESS TABLE WHEN IN RESTRICTED STATE"
DIA8070C Attempt to access a table in a restricted state.
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: 0 sqlerrml: 0
sqlerrmc:
sqlerrp : SQL10014
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:

Cause

During the execution of admin_move_table(), DB2 needs to rename the source table (e.g. DB2INST1.TABLE1 in above case), however, if the table was create in V9.5 and the table has at least a check constraint or generated column, the RENAME statement will fail with SQL750N error. For details of the RENAME failure, refer to this technote: http://www-01.ibm.com/support/docview.wss?uid=swg21445314

Diagnosing The Problem

1. Collect trace for ADMIN_MOVE_TABLE():
db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','TABLE1','TS_LARGE','TS_LARGE','TS_LARGE','','','','','',' MOVE,TRACE')"

From the running trace, you can see the RENAME statement failed with SQL0750N:

Line 3033: . . . (2490)ENTRY: adminotm_StoredProcedure_execDirect | RENAME TABLE "DB2INST1"."TABLE1 " TO "TABLE1ABN3iGo" | 1117afd70
Line 3035: . . . . . (778)DATA: adminotm_fillSqlca | 1 | SQL0750N The statement failed because the table or column cannot be renamed. SQLSTATE=42986

2. Collect PD data for the table:
db2cat -d <db name> -s DB2INST1 -n TABLE1 -o cat.out

From cat.out, you can find entries as following:

Length of descriptor : 768
Release number : 0x c00 ==> indicating the table is create in V9.5
...
CONSTRAINT NAME : SQL130627112330750
...
Default order-by collation name: IDENTITY
Default order-by collation ID in hex: B'00 00 00 02 FF 00 FF FF
Flags : SYSGEN ASSIGN

Resolving The Problem

Follow the instructions in above technote 1445314 to resolve the problem.

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Performance - Admin views and table functions","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7;10.1;10.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21962981

Page Feedback