1

Topic: 2216N-1224 or "Failure by reorganization".

There was a subject.
Checked up db2dart, received:
[spoiler] Table inspection start: DB2ADMIN.R_COMPONENTS_VALUE
Data inspection phase start. Data obj: 6 In pool: 2
Data inspection phase end.
Index inspection phase start. Index obj: 6 In pool: 2
Index inspection phase start. Index obj: 6 In pool: 2
Warning: Index object flags indicate invalid state, requires index be re-built;
errors reported for this object may be due to this state.
Page 1428642: Key 1 Error: Overlaps with Free Block
Error: in page 1428642, pool page 1428642, of obj 6, in tablespace 2.
Error: Page data will be dumped to report.
Error: This phase encountered an error and did not complete.
Error: Index page next leaf field 0 but pagetype (16777732) not last leaf
Error: in page 1428736, pool page 1428736, of obj 6, in tablespace 2.
Error: Page data will be dumped to report.
Page 1428736: Key 1 Error: Overlaps with Free Block
Error: in page 1428736, pool page 1428736, of obj 6, in tablespace 2.
Error detected in index.
Index inspection phase end.
Table inspection end. [/spoiler]
I.e., on mine  to concepts, the data is whole, the index was covered.
The index  does not give, the table speaks is in such superlocked state that the index cannot be touched in any way. Probably, Z - superexclusive. Probably, as on reorganization into it (state) entered, and did not quit, in connection with an error.
Now a command db2dart/DDEL I cut the data. Like, are cut successfully, already for 2/3 transited.
I plan  the table, , to flood from received *.del reversely.
Question: And what to do, if not  what is blocked?

2

Re: 2216N-1224 or "Failure by reorganization".

Fair teapot;
What error is returned on it?

 db2 "reorg indexes all for table DB2ADMIN.R_COMPONENTS_VALUE" 

3

Re: 2216N-1224 or "Failure by reorganization".

Addition:
Does not give .
Swears:
[spoiler] [IBM] [CLI Driver] [DB2/NT64] SQL0290N Reversal to the tabular
It is not authorized to space. SQLSTATE=55039
Explanation:
Attempt to address to tabular space, which has been made
There is in a wrong state, and a given reversal for it not
It is authorized.
* If the tabular space is in the stabilized state
("Quiesced: SHARE", "Quiesced: UPDATE" or "Quiesced: EXCLUSIVE");
Only to processes which save tabular space in
The stabilized state, it is authorized to address to this tabular
To space.
* If the tabular space is in any other state;
That only to process which fulfills the action which has caused it
The state, is authorized to address to this tabular space.
* System or user temporal tabular space
It is impossible to discard, if it contains the active system temporal
The tables, the created temporary tables or the declared temporal
Tables.
* API call SET CONTAINER cannot be used for the list job
Containers if the tabular space is not in a state
The postponed recovery.
Actions of the user:
Possible actions:
* If the tabular space is in the stabilized state;
Try to translate tabular space in a state
The stabilized sharing or a state
The stabilized modification. Or try to cancel stabilizing
Tabular space.
* If the tabular space is in any other state;
Before to address to tabular space, wait
Resettings of tabular space in a normal state.
The additional information on states of tabular spaces look in
Manual e.
sqlcode:-290
sqlstate: 55039
[/spoiler]

4

Re: 2216N-1224 or "Failure by reorganization".

Mark Barinstein;
REORG INDEXES ALL FOR TABLE DB2ADMIN.R_COMPONENTS_VALUE; swears the same as on attempt  something:
SQL0290N Reversal to tabular space is not authorized. SQLSTATE=55039
[spoiler]------------------------------ the Gated in commands------------------------------
REORG INDEXES ALL FOR TABLE DB2ADMIN.R_COMPONENTS_VALUE;
------------------------------------------------------------------------------
REORG INDEXES ALL FOR TABLE DB2ADMIN.R_COMPONENTS_VALUE
SQL0290N Reversal to tabular space is not authorized. SQLSTATE=55039
SQL0290N Reversal to tabular space is not authorized.
Explanation:
Attempt to address to tabular space, which has been made
There is in a wrong state, and a given reversal for it not
It is authorized.
* If the tabular space is in the stabilized state
("Quiesced: SHARE", "Quiesced: UPDATE" or "Quiesced: EXCLUSIVE");
Only to processes which save tabular space in
The stabilized state, it is authorized to address to this tabular
To space.
* If the tabular space is in any other state;
That only to process which fulfills the action which has caused it
The state, is authorized to address to this tabular space.
* System or user temporal tabular space
It is impossible to discard, if it contains the active system temporal
The tables, the created temporary tables or the declared temporal
Tables.
* API call SET CONTAINER cannot be used for the list job
Containers if the tabular space is not in a state
The postponed recovery.
Actions of the user:
Possible actions:
* If the tabular space is in the stabilized state;
Try to translate tabular space in a state
The stabilized sharing or a state
The stabilized modification. Or try to cancel stabilizing
Tabular space.
* If the tabular space is in any other state;
Before to address to tabular space, wait
Resettings of tabular space in a normal state.
The additional information on states of tabular spaces look in
Manual e.
sqlcode:-290
sqlstate: 55039
[/spoiler]

5

Re: 2216N-1224 or "Failure by reorganization".

At attempt any request to the table to send, it silently hangs and hangs, yet you will not cut down, or does not fall off on .

6

Re: 2216N-1224 or "Failure by reorganization".

Fair teapot;
What status at spaces in which the table lies?
Show appropriate records from:
db2 list tablespaces

7

Re: 2216N-1224 or "Failure by reorganization".

Mark Barinstein wrote:

the Fair teapot;
What status at spaces in which the table lies?
Show appropriate records from:
db2 list tablespaces

The space one, it is stabilized: SHARE.
[spoiler]------------------------------ the Gated in commands------------------------------
list tablespaces;
------------------------------------------------------------------------------
list tablespaces
Tabular spaces of a current database
ID tabular space = 0
Name = SYSCATSPACE
Type = Space,
Contained = All
State = 0x0000
Detailed explanation:
Normal
ID tabular space = 1
Name = TEMPSPACE1
Type = Space,
Contents = the System temporal data
State = 0x0000
Detailed explanation:
Normal
ID tabular space = 2
Name = USERSPACE1
Type = Space,
Contained = All
State = 0x0001
Detailed explanation:
It is stabilized: SHARE
ID tabular space = 3
Name = SYSTOOLSPACE
Type = Space,
Contained = All
State = 0x0000
Detailed explanation:
Normal
ID tabular space = 4
Name = SYSTOOLSTMPSPACE
Type = Space,
Contents = the User temporal data
State = 0x0000
Detailed explanation:
Normal
ID tabular space = 5
Name = TBSP32K0000
Type = Space,
Contained = All
State = 0x0000
Detailed explanation:
Normal
ID tabular space = 6
Name = TBSP32KTMP0000
Type = Space,
Contents = the System temporal data
State = 0x0000
Detailed explanation:
Normal
ID tabular space = 7
Name = TBSP32K0001
Type = Space,
Contained = All
State = 0x0000
Detailed explanation:
Normal
ID tabular space = 8
Name = TBSP32KTMP0001
Type = Space,
Contents = the System temporal data
State = 0x0000
Detailed explanation:
Normal
ID tabular space = 9
Name = ARCHIVESPACE
Type = Space,
Contained = All
State = 0x0000
Detailed explanation:
Normal
[/spoiler]

8

Re: 2216N-1224 or "Failure by reorganization".

Fair teapot;
That produces:

SELECT
Q.QUIESCER_AGENT_ID, Q.QUIESCER_TS_ID, Q.QUIESCER_OBJ_ID
, Q.QUIESCER_STATE, Q.QUIESCER_AUTH_ID
, D.TABSCHEMA, D.TABNAME
FROM SYSIBMADM.SNAPTBSP_QUIESCER Q
LEFT JOIN SYSCAT.DATAPARTITIONS D ON Q.QUIESCER_TS_ID=D.TBSPACEID AND Q.QUIESCER_OBJ_ID=D.PARTITIONOBJECTID;

9

Re: 2216N-1224 or "Failure by reorganization".

Mark Barinstein;

QUIESCER_AGENT_ID QUIESCER_TS_ID QUIESCER_OBJ_ID QUIESCER_STATE QUIESCER_AUTH_ID TABSCHEMA TABNAME
0 2 6 SHARE DB2ADMIN DB2ADMIN R_COMPONENTS_VALUE 

10

Re: 2216N-1224 or "Failure by reorganization".

Fair teapot;
It is necessary to be connected to basis by user DB2ADMIN and to fulfill for reset quiesced space states:

 db2 "quiesce tablespaces for table DB2ADMIN.R_COMPONENTS_VALUE reset" 

After that the space status should be normal.
If so try to make request about the table. If indexes in an unoperated state, they start to be rebuilt. It can be seen in db2diag.log.

11

Re: 2216N-1224 or "Failure by reorganization".

Mark Barinstein;
The space status became normal.
At attempt  or to delete indexes swears in a new fashion and not instantly, and in some minutes:
SQL0911N Because of an impasse or because of the expiry of the term has been fulfilled
Rollback of current transaction. The code of the reason "68". SQLSTATE=40001
[spoiler]
------------------------------ The gated in commands------------------------------
reorg indexes all for table DB2ADMIN. "R_COMPONENTS_VALUE";
------------------------------------------------------------------------------
reorg indexes all for table DB2ADMIN. "R_COMPONENTS_VALUE"
SQL0911N Because of an impasse or because of the expiry of the term has been fulfilled
Rollback of current transaction. The code of the reason "68". SQLSTATE=40001
SQL0911N Because of an impasse or because of the expiry of the term rollback of current transaction has been fulfilled. The code of the reason "68".
Explanation:
The active working unit faced irrepressible conflict at
Usage of object also should be rolled away.
The reason codes:
2
Transaction rollback because of an impasse is fulfilled.
68
Transaction rollback because of the lock expiry of the term is fulfilled.
72
Transaction rollback is fulfilled because of an error of the manager of communications of the data
DB2 During transaction.
73
Transaction rollback is fulfilled that a threshold of queue, such
As CONCURRENTDBCOORDACTIVITIES, led to a deadlock state
Two or  numbers of actions. The additional information
Look in section "Threshold CONCURRENTDBCOORDACTIVITIES" in
Information center DB2.
Rollback to the previous point of acceptance is fulfilled.
Actions of the user:
The changes connected to working unit, it is necessary to repeat.
Impasses or the expiry of the term of lock for long
Executable programs or for programs where the deadlock are probable
Situations, it is possible to avoid, fulfilling operations COMMIT more often.
Users of system of join: the impasse could happen in
To system of the server of join or on data source. Does not exist
The mechanism which would find out the impasses spread
On data sources and potentially on join system. It is possible
To define, what of data sources could not process the requirement
(Procedure of determination of this source look in a manual on
To diagnostics of errors).
Impasses are often normal or expected for certain combinations
Operators SQL. It is recommended to build programs so that on
Possibilities to avoid similar combinations.
If the impasse state is reached because of a queue threshold;
Such as CONCURRENTDBCOORDACTIVITIES, increase value of this threshold.
sqlcode:-911
sqlstate: 40001
The information connected to the given:
Configuration setting locktimeout - period of waiting of lock
Waiting of locks and waiting periods
Control of locks
Resolution of problems with impasses [/spoiler]
After select * from db2admin. R_COMPONETS_VALUE reflected minutes on 10.
In Windows 3 events are produced:
1. 1 or some indexes are marked as bad and will be rebuilt, (we here marked It in correspondence with https://www.ibm.com/developerworks/ru/l … index.html command "db2dart CService/MI/TSI 2/OI 6")
2. We rebuild 5 indexes
3. We rebuild 1 an index at table 6 in space 2. ( correct)
I.e., dual any position. Whether it will be repaired, whether is not present...
But indexes after request to rebuild went.

12

Re: 2216N-1224 or "Failure by reorganization".

Fair teapot;
It it is necessary to look from other session in whom it is stuck reorg that it eventually lock timeout receives.
See SYSIBMADM.MON_LOCKWAITS
Well and to understand, this originator of lock that hinders reorg' is how much important to work...

13

Re: 2216N-1224 or "Failure by reorganization".

Mark Barinstein wrote:

the Fair teapot;
It it is necessary to look from other session in whom it is stuck reorg that it eventually lock timeout receives.
See SYSIBMADM.MON_LOCKWAITS
Well and to understand, this originator of lock that hinders reorg' is how much important to work...

Like, there is nobody. Connections only the local. MON_LOCKWAITS empty an unaided sight...

14

Re: 2216N-1224 or "Failure by reorganization".

the teapot wrote:

Like, is not present anybody. Connections only the local. MON_LOCKWAITS empty an unaided sight...

So launch once again and look in a request output from time to time.
Well or in EVENT MONITOR FOR LOCKING if it at you is, look.

15

Re: 2216N-1224 or "Failure by reorganization".

Recover CSERVICE, it will be faster than than indexes on R_COMPONENTS_VALUE to rebuild.

16

Re: 2216N-1224 or "Failure by reorganization".

I about one year transferred this table to separate tabular space back