How to I see tables that need reorg?

hozkr hozoryo · #1 (**permalink**) 08-31-10, 10:20

Hi,

I need your help.

Where I see that my db need reorg on tables? or runstats?

The db2diag.log say me that need reorg and runstat!!! but I don't know wich ones.

ARWinner · #2 (**permalink**) 08-31-10, 10:26

Which DB2 version and OS are you using?

For DB2 LUW:

To check the last runstats:

Select tabschema,tabname,stats_time from syscat.tables where type = 'T'

To check if reorg is necessary, look up REORGCHK in the manual.

Andy

hozkr hozoryo · #3 (**permalink**) 08-31-10, 12:13

Thanks.
But de reorgchk I can run on line? I will not get problems such as Locks?

I read and found that "you can find table need reorg if clasterratio in sysibm.sysindexe is < 90 row overflow in sysibm.systables is > 5 need to run reorg"

What do you thing?

ARWinner · #4 (**permalink**) 08-31-10, 12:17

If your runstats are up to date, the running REORGCHK will not be a problem and will actually give you valid results. It does those checks for you.

Andy

nvk@vhv · #5 (**permalink**) 08-31-10, 12:20

The reorgchk command works with the statistics information. So if you use 'current statistics' with the reorgchk, then you don't have any issue with locks.

Your quotation shows indicators for reorg. DB2 lists the indicators with the output of the reorgchk command.

hozkr hozoryo · #6 (**permalink**) 08-31-10, 12:57

Ok thanks, this forum is great.

and what about reorg command? I can run on line? maybe I will get locks? or the command is exclusive

ARWinner · #7 (**permalink**) 08-31-10, 13:55

I suggest you read the manual on the REORG command and in doing reorgs in general.

Andy

hozkr hozoryo · #8 (**permalink**) 08-31-10, 14:52

Ok thanks, what does the result of reorgchk means??

ARWinner · #9 (**permalink**) 08-31-10, 14:53

Read the manual for REORGCHK and it will tell you what everything means.

Andy

sabari321 · #10 (**permalink**) 09-07-10, 09:01

If its v9.1 you can use following cmd to list the tables needed reorg.

db2 "select TABSCHEMA, TABNAME from SYSIBMADM.ADMINTABINFO where REORG_PENDING = 'Y'"

hozkr hozoryo · #11 (**permalink**) 09-07-10, 13:05

Thak you very much, I will try.

ARWinner · #12 (**permalink**) 09-07-10, 14:29

Quote:

Originally Posted by sabari321

If its v9.1 you can use following cmd to list the tables needed reorg.

db2 "select TABSCHEMA, TABNAME from SYSIBMADM.ADMINTABINFO where REORG_PENDING = 'Y'"

I have some tables where reorgchk determines that a reorg is needed, but this say nothing. I am not sure where this will be useful.

Andy

hozkr hozoryo · #13 (**permalink**) 09-07-10, 16:15

Quote:

Originally Posted by ARWinner

I have some tables where reorgchk determines that a reorg is needed, but this say nothing. I am not sure where this will be useful.

Andy

Yes, I tried and this query say nothing, but I need a script for get all tables that need reorg because I have more than 1000 tables and It`s very hard get all tables with this command.

You Hava any idea?

ARWinner · #14 (**permalink**) 09-07-10, 16:23

This is what I do:

Code:

call sysproc.reorgchk_tb_stats('T','ALL')


select card,tsize,f1,reorg,table_schema,table_name from session.tb_stats where reorg <> '---' order by tsize

Andy

hozkr hozoryo · #15 (**permalink**) 09-07-10, 16:39

Quote:

Originally Posted by ARWinner

This is what I do:

Code:

call sysproc.reorgchk_tb_stats('T','ALL')


select card,tsize,f1,reorg,table_schema,table_name from session.tb_stats where reorg <> '---' order by tsize

Andy

Thanks, but this sp work with "current statistics"?...I will try

Internet Services

Web Development

Digital Marketing

Consumer Tech