Performance-related Issues on a Restored DB

[esmith2112]

We observed an interesting phenomenon that is driving us crazy. We have
an archive process that takes rows off of a primary database, then
inserts them into a history database. After loading the exported rows
into the history database, we observed abysmal performance (queries
increasing from 2 minutes to 2 hours). No amount of runstats or
explaining/tuning queries yielded anything significant.

In an effort to study the problem on a different server so we wouldn't
kill the primary server with these long-running queries, we took a
backup and restored it onto the new server. It was a similarly classed
machine. Much to our surprise, the problem didn't exist on the new
server. We looked at all the differences in the configurations and
never found anything significant.

For grins, we decided to restore the same database back to itself on
the primary server, and again, we were were surprised to see our
problem go away.

We just completed a new archive cycle and the problem has resurfaced. I
have two questions:
1. Does a restore do an implicit reorg of the tables? (I had always
thought that it just copied the data pages verbatim and reloaded them
in the same order.)
2. Why does the load create such chaos? Doesn't it allocate data on
contiguous new pages appended to the end? (We don't delete anything on
target DB, so there shouldn't be any "holes" in the data pages...)
Any enlightenment on this issue would be greatly appreciated.
Platform = UDB 7.2 FP 12 on AIX 5.2

Thanks,
Evan

[Pierre Saint-Jacques]

As to point 1)
No, there is no reorg during restore. DB2 puts the table in the proper
tablespaces(containers) and the pages of the table where they belong
located in the proper extents. There is no attempt to reorh, reclaim
space, restructure anything of your data.

As to point 2)
Do you collect stats (STATISTICS YES) when you do the loads?
It will allocate continuous pages of an extent in DMS or in SMS if you
have set db2empfa. In SMS , and no empfa (your db cfg has a parm that
induicates if it is set for the db) the you'll get whatever "contoguity"
the file system decides.
If it is reallly as you describe, set db2empfa if SMS and alter the
target tables to be APPEND ON, then load with statistics yes.
Test and hopefully the problem disappers. If not, we'd need a little
more info as to the sequence of events as well as some parms setting.
HTYH' Pierre.
PS: If appls. are running from boud applications a rebind may noy huirt
after stats.!

esmith2112 wrote:

We observed an interesting phenomenon that is driving us crazy. We have
an archive process that takes rows off of a primary database, then
inserts them into a history database. After loading the exported rows
into the history database, we observed abysmal performance (queries
increasing from 2 minutes to 2 hours). No amount of runstats or
explaining/tuning queries yielded anything significant.

In an effort to study the problem on a different server so we wouldn't
kill the primary server with these long-running queries, we took a
backup and restored it onto the new server. It was a similarly classed
machine. Much to our surprise, the problem didn't exist on the new
server. We looked at all the differences in the configurations and
never found anything significant.

For grins, we decided to restore the same database back to itself on
the primary server, and again, we were were surprised to see our
problem go away.

We just completed a new archive cycle and the problem has resurfaced. I
have two questions:
1. Does a restore do an implicit reorg of the tables? (I had always
thought that it just copied the data pages verbatim and reloaded them
in the same order.)
2. Why does the load create such chaos? Doesn't it allocate data on
contiguous new pages appended to the end? (We don't delete anything on
target DB, so there shouldn't be any "holes" in the data pages...)
Any enlightenment on this issue would be greatly appreciated.
Platform = UDB 7.2 FP 12 on AIX 5.2

Thanks,
Evan

--
Pierre Saint-Jacques - Reply to: sescons at attglobal dot net
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.