Question & Answer
Question
The following symptom has been reported for table reorg attempts, even with the fix for IC80929 applied: The following error messages are logged in the db2diag.log when trying do resume/stop an online REORG controlled by Tivoli Storage Manager server: 2013-10-17-18.43.58.519064+120 E20233177A602 LEVEL: Error PID : 10682384 TID : 63914 PROC : db2sysc 0 INSTANCE: xgtsm21 NODE : 000 DB : TSMDB1 APPHDL : 0-36083 APPID: *LOCAL.xgtsm21.131017165701 AUTHID : XGTSM21 EDUID : 63914 EDUNAME: db2agent (TSMDB1) 0 FUNCTION: DB2 UDB, data management, sqldOLRValidate, probe:100 MESSAGE : ADM5590E The specified INPLACE table reorganization action on table "TSMDB1 .DF_MIGRCANDIDATES" is not allowed on this node because of SQLCODE -2219 reason code "2". . 2013-10-17-18.43.58.522063+120 E20233780A603 LEVEL: Error PID : 10682384 TID : 63914 PROC : db2sysc 0 INSTANCE: xgtsm21 NODE : 000 DB : TSMDB1 APPHDL : 0-36083 APPID: *LOCAL.xgtsm21.131017165701 AUTHID : XGTSM21 EDUID : 63914 EDUNAME: db2agent (TSMDB1) 0 FUNCTION: DB2 UDB, data management, sqldOLRValidate, probe:100 MESSAGE : ADM5590E The specified INPLACE table reorganization action on table "TSMDB1 .DF_MIGRCANDIDATES" is not allowed on this node because of SQLCODE -2219 reason code "11".
Cause
This looks like a case of DB2 giving Tivoli Storage Manager conflicting information.
From a TBREORG trace we see:
16:18:10.776 [127][tbnsql.c][1418][RdbExecuteSQLToStrings]:Executing sql 'SELECT sr.tabname AS TABNAME, sr.reorg_status AS STATUS FROM sysibmadm.snaptab_reorg sr WHERE ((sr.reorg_start IS NOT NULL AND sr.reorg_end IS NULL) OR (sr.reorg_start IS NOT NULL AND sr.reorg_status='PAUSED'))'.
16:18:10.794 [127][tbreorg.c][5019][FindDB2InFlightReorg]:Found in-flight row for table DF_MIGRCANDIDATES with pauseState PAUSED.
16:18:10.794 [127][tbreorg.c][7601][HandleInFlightReorgs]:reorgVarsP->db2ShortName *none* is replaced with new inflight tablename DF_MIGRCANDIDATES.
16:18:10.794 [127][tbreorg.c][7611][HandleInFlightReorgs]:reorgVarsP->reorgState 1 is replaced with ReorgStatRTblPaused.
In the preceeding lines, DB2 told us that DF_MIGRCANDIDATES was a table reorg in paused state. So Tivoli Storage Manager server decides to resume that reorg, and we try to do so in the following lines:
16:18:10.802 [127][tbreorg.c][4306][DoReorg]:db2Name "TSMDB1"."DF_MIGRCANDIDATES" - Using connection 1203dd5a8.
16:18:10.804 [127][tbreorg.c][4375][DoReorg]:psDB2Reorg(TblResume)(inFlags 0x100000, finalFlags 0x10180000) for table "TSMDB1"."DF_MIGRCANDIDATES" has result code -2219.
16:18:10.804 [127][tbreorg.c][7045][AnalyzeSQLCA]:db2Reorg sqlcode -2219, sqlerrmc tblName='TSMDB1.DF_MIGRCANDIDATES', reasonCode='2'.
The resume failed with SQL2219N reason code 2 which translates to: "Resuming a reorganization failed because there is no paused reorganization to resume for the specified table."
16:18:10.805 [127][tbreorg.c][7320][HandleDb2Reorg_INV_ACTION_Errors]:Entry for table "TSMDB1"."DF_MIGRCANDIDATES", reorgType 2, sqlca.sqlCode -2219, reasoncode 2.
16:18:10.806 [127][tbreorg.c][7409][HandleDb2Reorg_INV_ACTION_Errors]:Error resuming. Going to stop the reorg.
So we try to stop that reorg since we can't resume it.....
16:18:10.807 [127][tbreorg.c][7172][HandleDb2ReorgErrors]:psDB2Reorg(stop) for table "TSMDB1"."DF_MIGRCANDIDATES" has result code -2219.
16:18:10.808 [127][tbreorg.c][7177][AnalyzeSQLCA]:db2Reorg sqlcode -2219, sqlerrmc tblName='TSMDB1.DF_MIGRCANDIDATES', reasonCode='11'.
Now it's reason code 11 which translates to: "An attempt to pause or stop a reorganization failed because there is no table reorganization to pause or stop."
Answer
As a local fix you might submit the following command to restart the reorg and get out of the loop:
db2 connect to tsmdb1
db2 set schema tsmdb1
db2 "reorg table <AFFECTED_TABLE> inplace allow write access"
The symptom has been identified with the following DB2 defect:
IC79773: PAUSING INPLACE TABLE REORG RETURNS SQL2219N RC=11, BUT OLR SHOWS AS IN PROGRESS
See also Tivoli Storage Manager defect:
IC80929: TIVOLI STORAGE MANAGER SERVER MIGHT BE AFFECTED BY DB2 APAR IC79773
(see the related link section for links to the APAR details)
A fix is available with Tivoli Storage Manager V6.3.5 and newer code levels.
Was this topic helpful?
Document Information
Modified date:
17 June 2018
UID
swg21665394