|
Problem IT18876
|
Status: Closed |
ADMIN_MOVE_TABLE SWAP TAKES LOCKS IN SUBOPTIMAL ORDER |
| product: |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 |
| Problem description: |
ADMIN_MOVE_TABLE SWAP first locks target table which reduces the
likelihood that the ADMIN_MOVE_TABLE SWAP operation succeeds.
You may also notice a high number of deadlocks, while
ADMIN_MOVE_TABLE SWAP is in progress:
db2 "select max(snapshot_timestamp), max(LOCK_WAITS)
max_LOCK_WAITS, max(LOCK_TIMEOUTS) max_LOCK_TIMEOUTS, DEADLOCKS
from STAT_SN_V10R5_DB where snapshot_timestamp > current
timestamp - 21 days group by DEADLOCKS "
1 MAX_LOCK_WAITS
MAX_LOCK_TIMEOUTS DEADLOCKS
-------------------------- --------------------
-------------------- --------------------
2016-12-29-09.57.29.802280 7091369
271 249
2016-12-31-00.42.39.892967 7139970
272 250
2016-12-31-12.54.25.835489 7156259
273 251
A trace would reveal the following reoccuring functions calls:
. . . (840)EXIT: adminotm_fillSqlca | 100
. . . (2564)ENTRY: adminotm_StoredProcedure_commit
| a000000e1fe6838
. . . (2573)EXIT: adminotm_StoredProcedure_commit
| 0
. . (14615)EXIT: adminotm_Otm_replayOneIteration
| 100 | 0
. . (14719)DATA: adminotm_Otm_replayStaging | start
from beginning | true
. . (14768)DATA: adminotm_Otm_replayStaging | start
from beginning
. . (14461)ENTRY: adminotm_Otm_replayOneIteration
| 28cc79870 | true | false | 1024
. . . (1594)ENTRY: adminotm_Otm_setLockTimeout | 30 |
28cc79870
. . . (1635)EXIT: adminotm_Otm_setLockTimeout | 0
. . . (12046)ENTRY: adminotm_Otm_setDeadlockPriority
| 10 | 28cc79870
. . . (12093)EXIT: adminotm_Otm_setDeadlockPriority
| 0
. . . (776)ENTRY: adminotm_fillSqlca | 3 | 131076 |
28cc79870
. . . . (793)DATA: adminotm_fillSqlca | 1 | SQL0100W
No row was found for FETCH, UPDATE or DELETE; or the result of a
query is an empty table. SQLSTATE=02000 |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Users running admin_move_table *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 10.5 Fixpack 9. *
**************************************************************** |
| Local Fix: |
|
| Solution |
First fixed in DB2 Version 10.5 Fix Pack 9. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 18.01.2017
27.09.2017
27.09.2017 |
| Problem solved at the following versions (IBM BugInfos) |
9.0.
|
| Problem solved according to the fixlist(s) of the following version(s) |
