Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Find out which tables need REORG

3,278 views
Skip to first unread message

Gregor Kovač

unread,
Nov 8, 2012, 5:30:47 AM11/8/12
to
Sometimes I want to know which tables need REORG performed before I get the SQL668.
You can get this info with:
SELECT TABSCHEMA, TABNAME FROM SYSIBMADM.ADMINTABINFO WHERE REORG_PENDING = 'Y' WITH UR

but this SQL is VERY slow. It takes about 5-8 minutes in my case.
Is there any faster way to get the same information?

Bruce

unread,
Nov 8, 2012, 6:16:26 AM11/8/12
to
it takes forever on my servers as well...as far as I know there isn't
any other way to get this information.

-B

TheBoss

unread,
Nov 8, 2012, 5:10:20 PM11/8/12
to
=?UTF-8?Q?Gregor_Kova=C4=8D?= <kov...@gmail.com> wrote in
news:c466d14c-9d2f-4db1...@googlegroups.com:
(Note: please always specify DB2 version, OS and platform)

Did you try REORGCHK? Or its SP variant:
call sysproc.reorgchk_tb_stats('T','ALL')
followed by a select on session.tb_stats.

Here's a link to the InfoCenter for this routine
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?
topic=/com.ibm.db2.luw.sql.rtn.doc/doc/r0011872.html

The equivalent for indexes is this:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?
topic=/com.ibm.db2.luw.sql.rtn.doc/doc/r0011871.html


A fellow DB2 DBA (Philip Carrington) has a couple of interesting blog
articles using this to automate the Reorg process:

http://philipcarrington.wordpress.com/2011/01/26/automated-db2-reorg/
https://philipcarrington.wordpress.com/2011/02/02/auto-db2-index-
reorganisation/

And an update for the first article:
http://philipcarrington.wordpress.com/2011/12/11/automated-db2-
reorganisation-runstats-rebinds-version-2/


HTH

Cheers!

--
Jeroen

Lennart Jonsson

unread,
Nov 9, 2012, 1:57:21 AM11/9/12
to
Not as far as I know (I haven’t tried Jeroen's suggestion). I assume
SYSIBMADM.ADMINTABINFO is not a regular table but some kind of special
trick, since it takes very long time if you ask for info on a table that
does not exists. Given the trouble with SYSIBMADM.ADMINTABINFO we
decided that we always do a reorg when we apply "destructive" operations
to a table. This strategy works for us since we always have a service
window when we deploy a new version of the schema.


/Lelle

Kelly Fitzgerald

unread,
Nov 9, 2012, 11:11:24 AM11/9/12
to
have you reorged and runstats your system tables? and dont be afraid to add an index to the system tables. just make sure the index makes sense. no more than 5 indexes on a table or add a column to a index to make it float with more versatility in your queries.

TheBoss

unread,
Nov 22, 2012, 6:56:30 PM11/22/12
to
TheBoss <The...@invalid.nl> wrote in
news:XnsA105EBBBEEE...@194.109.133.133:
I forgot to mention another alternative that was offered a couple of
seasons ago in one of the "DB2NightShows":

select ‘REORG TABLE ’ || TABSCHEMA || ‘.’ || TABNAME || ‘ INPLACE ALLOW
WRITE ACCESS;’
from sysibmadm.snaptab
where (ROWS_READ > 999)
AND (((OVERFLOW_ACCESSES * 100) / (ROWS_READ + 1) > 3)

For the full story:
< http://www.dbisoftware.com/blog/db2nightshow.php?id=198 >

In short: a table is in need of reorganisation when a significant part
of the Read I/O to the table results in extra reads because of overflows
due to the imperfect organisation of the table.
The query above uses a value of 3% overflows, you can adapt this to your
own liking.

Cheers!


--
Jeroen

TheBoss

unread,
Nov 22, 2012, 7:02:01 PM11/22/12
to
TheBoss <The...@invalid.nl> wrote in
news:XnsA114996B826...@194.109.133.246:
It's also described on Scott Hayes' Performance blog:

http://www.dbisoftware.com/blog/db2_performance.php?id=116

[TBROVP - The percentage of Overflows over Rows Read]

--
Jeroen

Lennart Jonsson

unread,
Nov 23, 2012, 4:07:07 AM11/23/12
to
On 11/23/2012 12:56 AM, TheBoss wrote:
[...]
>
> select ‘REORG TABLE ’ || TABSCHEMA || ‘.’ || TABNAME || ‘ INPLACE ALLOW
> WRITE ACCESS;’
> from sysibmadm.snaptab
> where (ROWS_READ > 999)
> AND (((OVERFLOW_ACCESSES * 100) / (ROWS_READ + 1) > 3)
>

It's a common trick (Scott uses it a lot in his scripts) to add a small
number to the denominator to avoid division by zero (in this case
(ROWS_READ + 1)
^^^^
Another option is to use nullif which IMO makes it easier to understand
the purpose of the construction:

((OVERFLOW_ACCESSES * 100) / NULLIF(ROWS_READ, 0) > 3)


Just a thought. Trying to keep the average number of posts per month in
this group above 0 ;-)


/Lennart

Frederik Engelen

unread,
Nov 23, 2012, 4:11:53 AM11/23/12
to
On Thursday, November 8, 2012 11:30:48 AM UTC+1, Gregor Kovač wrote:
> Sometimes I want to know which tables need REORG performed before I get the SQL668. You can get this info with: SELECT TABSCHEMA, TABNAME FROM SYSIBMADM.ADMINTABINFO WHERE REORG_PENDING = 'Y' WITH UR but this SQL is VERY slow. It takes about 5-8 minutes in my case. Is there any faster way to get the same information?

Gregor,

You can speed it up a little by only checking the tables that you're interested in by using the function instead of the view. Example

SELECT * FROM TABLE(ADMIN_GET_TAB_INFO( 'SYSIBM', 'SYSTABLES' )) AS T

Depending on your version of DB2, you'll perhaps have to append a _V97 or similar to it's name.

For your use case, the NUM_REORG_REC_ALTERS column is also really interesting as it allows you to check in advance whether you can do one more alter.

--
Frederik Engelen

Lennart Jonsson

unread,
Nov 23, 2012, 4:30:12 AM11/23/12
to
On 11/23/2012 10:07 AM, Lennart Jonsson wrote:
> On 11/23/2012 12:56 AM, TheBoss wrote:
> [...]
>>
>> select ‘REORG TABLE ’ || TABSCHEMA || ‘.’ || TABNAME || ‘ INPLACE ALLOW
>> WRITE ACCESS;’
>> from sysibmadm.snaptab
>> where (ROWS_READ > 999)
>> AND (((OVERFLOW_ACCESSES * 100) / (ROWS_READ + 1) > 3)
>>
>

Jeroen, I hit the send button to fast. I believe the idea behind this is
slightly different from what the OP had in mind. Scotts formula will
detect that data is fragmented due to manipulation of content, say
update of varchar columns. I think what the OP is interested in is to
determine whether he will run into:

SQL0668N Operation not allowed for reason code "<reason-code>" on table
"<table-name>".

when the table is accessed. This is typicaly caused by destructive
operations such as alter <table> drop <column>. AFAIK you can do a
number of such operations (I've seen the number 3 somewhere) before you
run into SQL0668N. I don’t think you will encounter that due to
manipulation of data. Adding a column will probably cause overflows, so
in that case Scotts formula will alert for possible SQL0668N.


/Lennart



Lennart Jonsson

unread,
Nov 23, 2012, 4:32:10 AM11/23/12
to
Nice, I'll look into this. As a bonus I wont have to post here for the
next couple of months since the sliding average is way over 0 by now ;-)


/Lennart



The Boss

unread,
Nov 26, 2012, 7:07:36 AM11/26/12
to
On Friday, November 23, 2012 10:30:28 AM UTC+1, Lennart Jonsson wrote:
> On 11/23/2012 10:07 AM, Lennart Jonsson wrote:
>> On 11/23/2012 12:56 AM, TheBoss wrote:
>> [...]
>>>
>>> select �REORG TABLE � || TABSCHEMA || �.� || TABNAME || � INPLACE ALLOW
>>> WRITE ACCESS;�
>>> from sysibmadm.snaptab
>>> where (ROWS_READ > 999)
>>> AND (((OVERFLOW_ACCESSES * 100) / (ROWS_READ + 1) > 3)
>>>
>>
>
> Jeroen, I hit the send button to fast. I believe the idea behind this is
> slightly different from what the OP had in mind. Scotts formula will
> detect that data is fragmented due to manipulation of content, say
> update of varchar columns. I think what the OP is interested in is to
> determine whether he will run into:
>
> SQL0668N Operation not allowed for reason code "<reason-code>" on table
> "<table-name>".
>
> when the table is accessed. This is typicaly caused by destructive
> operations such as alter <table> drop <column>. AFAIK you can do a
> number of such operations (I've seen the number 3 somewhere) before you
> run into SQL0668N. I don�t think you will encounter that due to
> manipulation of data. Adding a column will probably cause overflows, so
> in that case Scotts formula will alert for possible SQL0668N.
>
> /Lennart

Ah, yes, thanks for that.
I fully concentrated on the OP's request for an alternative way of checking for tables in need of reorganisation.
I didn't even notice he mentioned SQL0668N, I should have checked that first.

Regarding your remark in the other reply:
I fully agree NULLIF is conceptually a neater way to avoid dividing by zero.
I'll evaluate all my "DBI" scripts for this to see if there may be is any performance reason why Scott prefers to use the '+1'-trick ;-)

Cheers!

--
Jeroen

Gregor Kovač

unread,
Dec 12, 2012, 3:36:03 AM12/12/12
to
Thanks for all you answers. :) They were very helpfull.
I forgot to say that I use the SYSIBMADM.ADMINTABINFO view mainly to find out what tables I have to reorg and the reorg is a result of couple of ALTER TABLES.
I susspect that SQLs published in several answers will not help me the mentioned situation.

Any pointers?

Best regards.
0 new messages