how to reduce/avoid Page Reorgs during update

how to reduce/avoid Page Reorgs during update

Post by Nan » Fri, 27 Jun 2003 02:16:18



Hi everyone,

I am trying to update a varchar(50) column with a string of 10
characters for the entire table. Table has about 2 mil rows and the
rows are updated using the primary key obtained from a cursor.
(Embedded SQL c program using a commit count of 10000. Just FYI, I
have tried smaller / larger commit counts).

When I do a db2 get snapshot for tables for DB, I see a lot of Page
Reorgs going on for that table (no overflows) resulting in a very poor
performance. Is there a way to reduce or avoid the Page reorgs ?

BTW, db2 version is 7.2 on AIX.
TIA,
Nana

 
 
 

how to reduce/avoid Page Reorgs during update

Post by pm3iinc-nospa » Fri, 27 Jun 2003 15:42:26


I guess it could relate to
table pctfree
index MINPCTUSED

http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/v...
nter.d2w/report?target=mainFrame&fn=t0005312.htm

load and reorg can be used to add/remove space in the pages (number of rows
per page)
maybe pages are close to full (in bytes vs pagesize)

see f2 and f3 in reorgchk.
maybe also TSIZE / NP would provide you the average bytes per page. (compage
with the TS page size)
It's just an average but if it's close, it could give you indications on
space useage.

You should get a page reorg on update if there is enough room on the page
but it needs some 'defrag'.
Otherwise, you should get an overflowed row.

What is 'a lot of page reorgs' in percentage vs the table size (2 millions)?

PM



Quote:> Hi everyone,

> I am trying to update a varchar(50) column with a string of 10
> characters for the entire table. Table has about 2 mil rows and the
> rows are updated using the primary key obtained from a cursor.
> (Embedded SQL c program using a commit count of 10000. Just FYI, I
> have tried smaller / larger commit counts).

> When I do a db2 get snapshot for tables for DB, I see a lot of Page
> Reorgs going on for that table (no overflows) resulting in a very poor
> performance. Is there a way to reduce or avoid the Page reorgs ?

> BTW, db2 version is 7.2 on AIX.
> TIA,
> Nana


 
 
 

how to reduce/avoid Page Reorgs during update

Post by Mark » Fri, 27 Jun 2003 20:24:53


Quote:> > Hi everyone,

> > I am trying to update a varchar(50) column with a string of 10
> > characters for the entire table. Table has about 2 mil rows and the
> > rows are updated using the primary key obtained from a cursor.
> > (Embedded SQL c program using a commit count of 10000. Just FYI, I
> > have tried smaller / larger commit counts).

> > When I do a db2 get snapshot for tables for DB, I see a lot of Page
> > Reorgs going on for that table (no overflows) resulting in a very poor
> > performance. Is there a way to reduce or avoid the Page reorgs ?

> > BTW, db2 version is 7.2 on AIX.
> > TIA,
> > Nana

Sounds like the current content of the VARCHAR column is than 10 and there
is not much (if any) percent free on the table. That means that none of rows
will fit on the same page when updated (not good).

You unload the data and then do a load replace. The data in the varchar
column could be seeded during the unload (with another column value that is
at least char 10), or you manipulate the load file. If you needed to run the
update again it would run much faster.

If that column was likely to updated in the future with longer data, you
should consider using percent free on your table.

I would use a commit count closer to 1000 than 10,000, which includes any
commit interval that was performed during the load replace.

 
 
 

how to reduce/avoid Page Reorgs during update

Post by Nan » Sat, 28 Jun 2003 05:04:32


Hi,
Thanks you both for your responses.

I did not let the whole update finish on that table since it was
taking too long. Instead, I just took 15000 rows and created another
table. Updated that table (all rows). I get about 2500 page over
flows. (close to 17%).
I do have pctfree set to 30 % (and reorged the table).

Thanks,
Nana

 
 
 

1. Overflows and Page Reorgs

Hello !

IBM DB2 7.1 sp 6
Sun Solaris 7
6 CPU
8GB RAM

Our DB2 is crushing time to time and I am looking for possible reasons.
I have the table snapshot on and obtain Overflows and Page Reorgs for
certain tables:
Can these be a potential cause of DB2 crashes and performance problems ?
What does it mean Overflows on mostly read tables ?
What About Page Reorgs ?

sincerely Olek

 Table Schema        = SYSIBM
 Table Name          = SYSCOLDIST
 Table Type          = Catalog
 Rows Read           = 594360
 Rows Written        = 594360
 Overflows           = 0
 Page Reorgs         = 6557

 Table Schema        = USER
 Table Name          = TABLE_1
 Table Type          = User
 Rows Read           = 2429949180
 Rows Written        = 651599
 Overflows           = 0
 Page Reorgs         = 3938

 Table Schema        = USER
 Table Name          = TABLE_2
 Table Type          = User
 Rows Read           = 2355032
 Rows Written        = 1127
 Overflows           = 332728
 Page Reorgs         = 23

 Table Schema        = USER
 Table Name          = TABLE_3
 Table Type          = User
 Rows Read           = 2111825
 Rows Written        = 21
 Overflows           = 275165
 Page Reorgs         = 0

 Table Schema        = USER
 Table Name          = TABLE_4
 Table Type          = User
 Rows Read           = 1867158
 Rows Written        = 182
 Overflows           = 172787
 Page Reorgs         = 7

2. SQL Server 7.0 Desktop Edition

3. Reducing the Avg Page density of a page

4. SQL 7.0 Issue?

5. avoid adp to connect SQL server during sturtup

6. call stored procedure using ADO in vc++?

7. How to avoid rollback during some INSERTs ?

8. Avoiding Trigger during batch process using Role name

9. How to avoid rounding during typecast? PDXWin 5.0

10. Avoiding container format during a redirected restore

11. reduce Maximum number of pages in block I/O BUFFER CACHE