Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103

    Unanswered: Status of Classic reorg

    DB2 V8/AIX 5.3

    [1] Any method available to check the status of classic reorg ( Restriction: I cannot use db2pd tool as I dont have SYSADM authority)
    [2] When a classic reorg is issued against a partitioned table. Will the reorg run in parallel across all partitions ?
    [3] I read that to monitor the reorganization status of a table, you can use GET SNAPSHOT FOR TABLES ON database to check the reorg status. But what I know what information I need to look on the output of table snapshot to find the reorg progress.
    Sample Output:
    Table Schema = ETL
    Table Name = T00501
    Table Type = User
    Data Object Pages = 11
    Rows Read = 10128
    Rows Written = 0
    Overflows = 0
    Page Reorgs = 0

    Table Schema = SMTUMT
    Table Name = SQUISH_VIN
    Table Type = User
    Data Object Pages = 310
    Index Object Pages = 62
    Rows Read = 4710182
    Rows Written = 36410
    Overflows = 104250
    Page Reorgs = 1360


    Thanks in advance.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    1 & 3) select snapshot_timestamp,varchar(table_name,60) as table,reorg_status,reorg_start,reorg_end,productio n.udf_ts_difference(coalesce(reorg_end,snapshot_ti mestamp),reorg_start,'MINUTES') as minutes,case when reorg_max_counter = 0 then 0 else (reorg_current_counter * 100)/reorg_max_counter end as percent,reorg_current_counter,reorg_max_counter,re org_phase,reorg_max_phase from table(snapshot_tbreorg('MyDB',-1)) as x order by reorg_start

    2) If you do not specify whcih partitions to reorg, or do not leave any excluded, I would guess that they are done in parallel.

    HTH

    Andy

  3. #3
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103
    Thanks for the response.

    Would you be kind enough to post the code for production.udf_ts_difference UDF code as well. Thanks.
    -----
    I started a classic reorg using a script and I started it as a nohup & background process.
    Script:
    db2 connect to card
    db2 values current timestamp
    db2 reorg indexes all for table card.txn_part allow no access
    db2 values current timestamp

    # card.txn_part is a logically partitioned table spread across node 1,2,3
    # Reorg script was started from Node 0
    But when the issues the SQL to check for the reorg in process it returns 0 rows in my database as below.
    DBA PP:/home/carddba > eorg_max_phase from table(snapshot_tbreorg('CARD',-1)) as x order by reorg_start" <

    SNAPSHOT_TIMESTAMP TABLE REORG_STATUS REORG_START REORG_END PERCENT REORG_CURRENT_COUNTER REORG_MAX_COUNTER REORG_PHASE REORG_MAX_PHASE
    -------------------------- ------------------------------------------------------------ ------------ -------------------------- -------------------------- -------------------- --------------------- -------------------- -------------------- ---------------

    0 record(s) selected.

    DBA PP:/home/carddba >


    Did I miss something ?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    That UDF just computes the difference of the two timestamps in minutes. Sorry, I cannot post the source code. You will need to issue the select of the snapshot from a different connection while the reorg is running. It works fine on a single partition DB2 DB under linux.

    Andy

  5. #5
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103
    Thank you.

    Unfortunately the SQL is not showing the reorg which is in progress on my environment.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If you change the -1 paramenter to the snapshot_tbreorg function to -2 and include the column partition_number in the result set, do you get anything then?

    Andy

  7. #7
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103
    No.

    db2 => select snapshot_timestamp,varchar(table_name,60) as table,reorg_status,reorg_start,reorg_end,case when reorg_max_counter = 0 then 0 else (reorg_current_counter * 100)/reorg_max_counter end as percent,reorg_current_counter,reorg_max_counter,re org_phase,reorg_max_phase from table(snapshot_tbreorg('CARD',-2)) as x order by reorg_start

    SNAPSHOT_TIMESTAMP TABLE REORG_STATUS REORG_START REORG_END PERCENT REORG_CURRENT_COUNTER REORG_MAX_COUNTER REORG_PHASE REORG_MAX_PHASE
    -------------------------- ------------------------------------------------------------ ------------ -------------------------- -------------------------- -------------------- --------------------- -------------------- -------------------- ---------------

    0 record(s) selected.

  8. #8
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103
    I made a mistake this time (supplied a different db name). But I got results on this execution.

    db2 => select snapshot_timestamp,varchar(table_name,60) as table,reorg_status,reorg_start,reorg_end,case when reorg_max_counter = 0 then 0 else (reorg_current_counter * 100)/reorg_max_counter end as percent,reorg_current_counter,reorg_max_counter,re org_phase,reorg_max_phase from table(snapshot_tbreorg('CARD_2',-2)) as x order by reorg_start

    SNAPSHOT_TIMESTAMP TABLE REORG_STATUS REORG_START REORG_END PERCENT REORG_CURRENT_COUNTER REORG_MAX_COUNTER REORG_PHASE REORG_MAX_PHASE
    -------------------------- ------------------------------------------------------------ ------------ -------------------------- -------------------------- -------------------- --------------------- -------------------- -------------------- ---------------
    2007-04-17-14.29.09.805508 TRANSACTION 1 2007-04-17-14.24.06.913580 - 0 0 756108 2 4
    2007-04-17-14.29.09.805508 TRANSACTION 1 2007-04-17-14.24.07.284608 - 3 23007 731612 2 4
    2007-04-17-14.29.09.805508 TRANSACTION 1 2007-04-17-14.24.07.485912 - 3 23447 646240 2 4

    3 record(s) selected.

    db2 =>


    Thanks Andy.

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Great. You will want to add PARTITION_NUMBER to the result set so you know where each partition is on the table reorgs.

    Andy

  10. #10
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103
    Sure. I will do that.

    I have another question, related to the space required to rebuild an index. IBM's information center says that to perform a classic reorg of table I need as much space as data but it doesnt explain much about the space required to reorg an index. Can you please share your thoughts on this.

  11. #11
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not know, although I would hazard to guess the same. Twice as much space that the indexes use.

    Andy

Posting Permissions

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