Seemingly Successful Inplace Reorg returns -1 Completion Status

[sethwai]

Hi,

I have a nightly script that executes inplace reorgs allow write
access for several tables after a previous script does a large number
of delete operations. It usually has been executing fine. The other
night 3 of them failed due to the log file system filling up. I
resumed them individually and they all completed successfully.

To eliminate the log space problem I changed the script to wait for
one to complete before startng the next one. Well the same 3 that
failed the day before returned a -1 completion status but they all
succeeded according to the list history reorg command. I'm wondering
why that is. There weren't any errors (or warnings) in the diag or
notify logs. Below is a table snapshot and a list history for one of
the failed reorgs.

Thanks.

Lew

Table Schema = DB2AI
Table Name = TABLE1
Table Type = User
Data Object Pages = 106453
Index Object Pages = 97299
Rows Read = 4827049
Rows Written = 995492
Overflows = 0
Page Reorgs = 92806
Table Reorg Information:
Reorg Type =
Reclaiming
Inplace Table Reorg
Allow Write Access
Reorg Index = 0
Reorg Tablespace = 2
Start Time = 09/06/2006 01:07:48.282006
Reorg Phase =
Max Phase =
Phase Start Time =
Status = Completed
Current Counter = 106432
Max Counter = 106452
Completion = -1
End Time = 09/06/2006 01:14:41.682621

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------
--------------
G T 20060906010748 N S0005436.LOG

----------------------------------------------------------------------------
Table: "DB2AI "."TABLE1"
----------------------------------------------------------------------------
Comment: REORG START
Start Time: 20060906010748
End Time: 20060906010748
Status: A

----------------------------------------------------------------------------
EID: 7056
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------
--------------
G T 20060906011441 N S0005444.LOG

----------------------------------------------------------------------------
Table: "DB2AI "."TABLE1"
----------------------------------------------------------------------------
Comment: REORG Done
Start Time: 20060906011441
End Time: 20060906011441
Status: A

----------------------------------------------------------------------------
EID: 7065

[Shashi Mannepalli]

Check the status of the REORG using this command

db2pd -db <database-reorgs

cheers...
Shashi Mannepalli
se*****@yahoo.com wrote:

Hi,

I have a nightly script that executes inplace reorgs allow write
access for several tables after a previous script does a large number
of delete operations. It usually has been executing fine. The other
night 3 of them failed due to the log file system filling up. I
resumed them individually and they all completed successfully.

To eliminate the log space problem I changed the script to wait for
one to complete before startng the next one. Well the same 3 that
failed the day before returned a -1 completion status but they all
succeeded according to the list history reorg command. I'm wondering
why that is. There weren't any errors (or warnings) in the diag or
notify logs. Below is a table snapshot and a list history for one of
the failed reorgs.

Thanks.

Lew

Table Schema = DB2AI
Table Name = TABLE1
Table Type = User
Data Object Pages = 106453
Index Object Pages = 97299
Rows Read = 4827049
Rows Written = 995492
Overflows = 0
Page Reorgs = 92806
Table Reorg Information:
Reorg Type =
Reclaiming
Inplace Table Reorg
Allow Write Access
Reorg Index = 0
Reorg Tablespace = 2
Start Time = 09/06/2006 01:07:48.282006
Reorg Phase =
Max Phase =
Phase Start Time =
Status = Completed
Current Counter = 106432
Max Counter = 106452
Completion = -1
End Time = 09/06/2006 01:14:41.682621

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------
--------------
G T 20060906010748 N S0005436.LOG

----------------------------------------------------------------------------
Table: "DB2AI "."TABLE1"
----------------------------------------------------------------------------
Comment: REORG START
Start Time: 20060906010748
End Time: 20060906010748
Status: A

----------------------------------------------------------------------------
EID: 7056
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------
--------------
G T 20060906011441 N S0005444.LOG

----------------------------------------------------------------------------
Table: "DB2AI "."TABLE1"
----------------------------------------------------------------------------
Comment: REORG Done
Start Time: 20060906011441
End Time: 20060906011441
Status: A

----------------------------------------------------------------------------
EID: 7065

[sethwai]

Thanks for the info. The db2pd command shows pretty much the same
thing:

Address TbspaceID TableID TableName Start
End PhaseStart MaxPhase
Phase CurCount MaxCount Type Status Completion IndexID
TempSpaceID
0x40E61404 2 2 TABLE1 09/06/2006
01:07:48.282006 09/06/2006 01:14:41.682621 n/a
n/a n/a 106432 2 Online Done 4294967295
0 2

That huge number 4294967295 is actually all Fs which is equivalent to
-1
Shashi Mannepalli wrote:

Check the status of the REORG using this command

db2pd -db <database-reorgs

cheers...
Shashi Mannepalli
se*****@yahoo.com wrote:

Hi,

I have a nightly script that executes inplace reorgs allow write
access for several tables after a previous script does a large number
of delete operations. It usually has been executing fine. The other
night 3 of them failed due to the log file system filling up. I
resumed them individually and they all completed successfully.

To eliminate the log space problem I changed the script to wait for
one to complete before startng the next one. Well the same 3 that
failed the day before returned a -1 completion status but they all
succeeded according to the list history reorg command. I'm wondering
why that is. There weren't any errors (or warnings) in the diag or
notify logs. Below is a table snapshot and a list history for one of
the failed reorgs.

Thanks.

Lew

Table Schema = DB2AI
Table Name = TABLE1
Table Type = User
Data Object Pages = 106453
Index Object Pages = 97299
Rows Read = 4827049
Rows Written = 995492
Overflows = 0
Page Reorgs = 92806
Table Reorg Information:
Reorg Type =
Reclaiming
Inplace Table Reorg
Allow Write Access
Reorg Index = 0
Reorg Tablespace = 2
Start Time = 09/06/2006 01:07:48.282006
Reorg Phase =
Max Phase =
Phase Start Time =
Status = Completed
Current Counter = 106432
Max Counter = 106452
Completion = -1
End Time = 09/06/2006 01:14:41.682621

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------
--------------
G T 20060906010748 N S0005436.LOG

----------------------------------------------------------------------------
Table: "DB2AI "."TABLE1"
----------------------------------------------------------------------------
Comment: REORG START
Start Time: 20060906010748
End Time: 20060906010748
Status: A

----------------------------------------------------------------------------
EID: 7056
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------
--------------
G T 20060906011441 N S0005444.LOG

----------------------------------------------------------------------------
Table: "DB2AI "."TABLE1"
----------------------------------------------------------------------------
Comment: REORG Done
Start Time: 20060906011441
End Time: 20060906011441
Status: A

----------------------------------------------------------------------------
EID: 7065