Very Computer

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

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

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.

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