Seemingly Successful Inplace Reorg returns -1 Completion Status

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • sethwai@yahoo.com
    #1

    Seemingly Successful Inplace Reorg returns -1 Completion Status

    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+Seque nce 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+Seque nce 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

    Tags: None
    • Shashi Mannepalli
      #2
      Re: Seemingly Successful Inplace Reorg returns -1 Completion Status

      Check the status of the REORG using this command

      db2pd -db <database-reorgs

      cheers...
      Shashi Mannepalli


      sethwai@yahoo.c om 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+Seque nce 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+Seque nce 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

        Comment

        • sethwai@yahoo.com
          #3
          Re: Seemingly Successful Inplace Reorg returns -1 Completion Status

          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
          >
          >
          sethwai@yahoo.c om 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+Seque nce 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+Seque nce 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

            Comment

            Previous template Next
            Working...