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.

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

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 ?

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

Thank you.

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

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

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.

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.

Great. You will want to add PARTITION_NUMBER to the result set so you know where each partition is on the table reorgs.

Andy

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.

I do not know, although I would hazard to guess the same. Twice as much space that the indexes use.

Andy