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.