Results 1 to 1 of 1
  1. #1
    Join Date
    Jun 2008
    Posts
    1

    Unanswered: reorg in paused state - why?

    Hi there,

    I am trying to reorg a table in a DB2 8.2 database - running on Windows.
    The database is IBM ITCAM for RTT called tmtp.

    The command I am using is:
    db2 reorg table DB2ADMIN.AGGREGATEDATA inplace allow write access

    when I run : db2pd -reorgs -db tmtp
    I initially get:

    Database Partition 0 -- Database TMTP -- Active -- Up 2 days 18:52:10

    Table Reorg Stats:
    Address TbspaceID TableID TableName Start End PhaseStart
    MaxPhase Phase CurCount MaxCount Type Status Completion IndexID TempSpaceID
    0x058EFB04 3 6 EVENTMSGPARAMS 18/06/2008 23:20:49.679598 18/06/2008 23:21:21.236583 n/a
    n/a n/a 4448 2 Online Done 0 0 3
    0x01B60504 2 41 GENERICEVENTLOG 18/06/2008 23:20:49.904775 18/06/2008 23:21:35.848066 n/a
    n/a n/a 7008 2 Online Done 0 0 2
    0x050F2984 2 47 NODE 18/06/2008 23:54:46.752669 18/06/2008 23:54:54.236643 n/a
    n/a n/a 83 1 Online Done 0 2 2
    0x050F2E84 2 48 RELATIONMAP 18/06/2008 23:20:50.670087 18/06/2008 23:20:53.810309 n/a
    n/a n/a 88 2 Online Done 0 0 2
    0x050F3884 2 49 INSTANCEDATA 18/06/2008 23:20:50.060863 19/06/2008 10:40:37.067335 n/a
    n/a n/a 543 1 Online Stopped 4294967295 2 2
    0x050F3384 2 52 AGGREGATEDATA 19/06/2008 10:41:09.556201 01/01/1970 00:00:00.000000 n/a
    n/a n/a 95 1 Online Started 4294967295 3 2

    However, running the same command a few mins later, I get the following output:

    Database Partition 0 -- Database TMTP -- Active -- Up 2 days 18:53:50

    Table Reorg Stats:
    Address TbspaceID TableID TableName Start End PhaseStart
    MaxPhase Phase CurCount MaxCount Type Status Completion IndexID TempSpaceID
    0x058EFB04 3 6 EVENTMSGPARAMS 18/06/2008 23:20:49.679598 18/06/2008 23:21:21.236583 n/a
    n/a n/a 4448 2 Online Done 0 0 3
    0x01B60504 2 41 GENERICEVENTLOG 18/06/2008 23:20:49.904775 18/06/2008 23:21:35.848066 n/a
    n/a n/a 7008 2 Online Done 0 0 2
    0x050F2984 2 47 NODE 18/06/2008 23:54:46.752669 18/06/2008 23:54:54.236643 n/a
    n/a n/a 83 1 Online Done 0 2 2
    0x050F2E84 2 48 RELATIONMAP 18/06/2008 23:20:50.670087 18/06/2008 23:20:53.810309 n/a
    n/a n/a 88 2 Online Done 0 0 2
    0x050F3884 2 49 INSTANCEDATA 18/06/2008 23:20:50.060863 19/06/2008 10:40:37.067335 n/a
    n/a n/a 543 1 Online Stopped 4294967295 2 2
    0x050F3384 2 52 AGGREGATEDATA 19/06/2008 10:41:09.556201 19/06/2008 10:42:18.124246 n/a
    n/a n/a 347 1 Online Paused 4294967295 3 2


    Tablespace #2 is as follows:

    Tablespace ID = 2
    Name = USERSPACE1
    Type = System managed space
    Contents = Any data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 665012
    Useable pages = 665012
    Used pages = 665012
    Free pages = Not applicable
    High water mark (pages) = Not applicable
    Page size (bytes) = 4096
    Extent size (pages) = 32
    Prefetch size (pages) = 32
    Number of containers = 1



    Why does it go into a paused state?


    The table has the following rows:
    C:\>db2 select count(*) from DB2ADMIN.AGGREGATEDATA

    1
    -----------
    4478196

    1 record(s) selected.



    with reorgchk I get:

    C:\>db2 reorgchk update statistics on table DB2ADMIN.AGGREGATEDATA

    Doing RUNSTATS ....


    Table statistics:

    F1: 100 * OVERFLOW / CARD < 5
    F2: 100 * (Effective Space Utilization of Data Pages) > 70
    F3: 100 * (Required Pages / Total Pages) > 80

    SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
    ----------------------------------------------------------------------------------------
    Table: DB2ADMIN.AGGREGATEDATA
    DB2ADMIN AGGREGATEDATA 4478196 0 81820 83800 - 3.13e+008 0 92 97 ---
    ----------------------------------------------------------------------------------------

    Index statistics:

    F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
    F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((NLEAF - NUM EMPTY LEAFS) * INDEXPAGESIZE) > 50
    F6: (100 - PCTFREE) * ((INDEXPAGESIZE - 96) / (ISIZE + 12)) ** (NLEVELS - 2) * (INDEXPAGESIZE - 96) / (KEYS * (ISIZE + 9
    ) + (CARD - KEYS) * 5) < 100
    F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) < 20
    F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20

    SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS F4 F5 F6 F7 F8 REORG
    -------------------------------------------------------------------------------------------------
    Table: DB2ADMIN.AGGREGATEDATA
    DB2ADMIN AGGREGATEDATA_INDX 4e+006 29905 0 4 15 0 4e+006 73 87 73 0 0 *----
    DB2ADMIN AGGREGATE_INDX1 4e+006 7347 2 3 4 0 3872 6 74 4 0 0 *----
    DB2ADMIN AGGREGATE_INDX2 4e+006 7017 0 3 10 0 4324 98 78 2 0 0 -----
    DB2ADMIN AGGREGATE_INDX3 4e+006 17806 0 3 14 0 2e+006 6 86 0 0 0 *----
    DB2ADMIN PK_AGGREGATEDATA 4e+006 23700 0 3 10 0 4e+006 96 87 0 0 0 -----
    -------------------------------------------------------------------------------------------------

    CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary
    for indexes that are not in the same sequence as the base table. When multiple
    indexes are defined on a table, one or more indexes may be flagged as needing
    REORG. Specify the most important index for REORG sequencing.

    Tables defined using the ORGANIZE BY clause and the corresponding dimension
    indexes have a '*' suffix to their names. The cardinality of a dimension index
    is equal to the Active blocks statistic of the table.


    any help would be appreciated...

    many thanks
    Tobe


    other information asked for:

    C:\>db2level
    DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08020" with
    level identifier "03010106".
    Informational tokens are "DB2 v8.1.7.445", "s040812", "WR21342", and FixPak
    "7".
    Product is installed at "D:\IBM\SQLLIB".


    C:\>db2licm -l ESE
    Product Name = "DB2 Enterprise Server Edition"
    Product Identifier = "DB2ESE"
    Version Information = "8.2"
    Expiry Date = "Permanent"
    Registered Connect User Policy = "Disabled"
    Number Of Entitled Connect Users = "5"
    Enforcement Policy = "Soft Stop"
    Number of processors = "2"
    Number of licensed processors = "1"
    Database partitioning feature = "Not entitled"
    Annotation = ""
    Other information = ""

    Product Name = "DB2 High Availability Disaster Recovery Option"
    Product Identifier = "DB2HADR"
    Version Information = "8.2"
    Expiry Date = "Permanent"
    Annotation = ""
    Other information = ""

    Product Name = "DB2 Advanced Security Option"
    Product Identifier = "DB2ASO"
    Version Information = "8.2"
    Expiry Date = "Permanent"
    Annotation = ""
    Other information = ""
    Last edited by TobeP; 06-19-08 at 07:41.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •