By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,236 Members | 1,449 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,236 IT Pros & Developers. It's quick & easy.

Effect of manually updating AVGCOMPRESSEDROWSIZE?

P: n/a
Greetings all.

We recently upgraded from DB2 v8.2 to 9.1 FP1 and tested the AUTO_REORG
functionality. As recommended by SAP, we scheduled the job REORGCHK_ALL
weekly. This spews out an error message for each table as follows:

2007-05-11-16.39.57.174155-420 I6104A453 LEVEL: Error
PID : 786566 TID : 1 PROC : db2fmp (569410) 0
INSTANCE: db2prj NODE : 000
FUNCTION: DB2 UDB, command line process, clp_reorgchk_calculate_f2, probe:134
MESSAGE : Invalid number of fpages
DATA #1 : unsigned integer, 8 bytes
1
DATA #2 : unsigned integer, 8 bytes
1
DATA #3 : unsigned integer, 4 bytes
16384
DATA #4 : unsigned integer, 2 bytes
65535

I realised right away this was a spurious error. The main issue for me was
that with 38,000 tables it generated 30MB db2diag.log files in a couple of
hours. Couldn't even open the file with VI to see what was happening.

I opened a call with IBM and they told me this is due to APAR LI72154 and the
workaround is to set DIAGLEVEL to 1.

I find this totally unacceptable. Running at DIAGLEVEL 1 is pretty much
useless.

Now the explanation they gave me was as follows:

"Code changes in DB2 9 which add additional calculations. These calculations
are triggered by value AVGCOMPRESSEDROWSIZE which can be found in SYSSTAT.
TABLES. This column does not exist in DB2 V8 and the value is set to -1
during a database migration, because of the additional calculations we end up
in fpages being less than 1 which in return triggers the db2diag.log messages.
"

I looked at SYSSTAT.TABLES and sure enough most of the values for
AVGCOMPRESSEDROWSIZE were in fact "-1". However, there were some 200 tables
where the value was "0".

My question to this forum is, since "-1" is apparently not a valid value,
what would be the effect if I updated AVGCOMPRESSEDROWSIZE to "0" for all
tables? Would this get rid of the error without messing up something else and
allow me to run at DIAGLEVEL 3 without generating unmanagable database log
files?

Anybody?

--
Message posted via http://www.dbmonster.com

Jun 22 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
cccompton via DBMonster.com wrote:
Greetings all.

We recently upgraded from DB2 v8.2 to 9.1 FP1 and tested the
AUTO_REORG functionality. As recommended by SAP, we scheduled the job
REORGCHK_ALL weekly. This spews out an error message for each table
as follows:

2007-05-11-16.39.57.174155-420 I6104A453 LEVEL: Error
PID : 786566 TID : 1 PROC : db2fmp (569410) 0
INSTANCE: db2prj NODE : 000
FUNCTION: DB2 UDB, command line process, clp_reorgchk_calculate_f2,
probe:134 MESSAGE : Invalid number of fpages
DATA #1 : unsigned integer, 8 bytes
1
DATA #2 : unsigned integer, 8 bytes
1
DATA #3 : unsigned integer, 4 bytes
16384
DATA #4 : unsigned integer, 2 bytes
65535

I realised right away this was a spurious error. The main issue for
me was that with 38,000 tables it generated 30MB db2diag.log files in
a couple of hours. Couldn't even open the file with VI to see what
was happening.

I opened a call with IBM and they told me this is due to APAR LI72154
and the workaround is to set DIAGLEVEL to 1.

I find this totally unacceptable. Running at DIAGLEVEL 1 is pretty
much useless.

Now the explanation they gave me was as follows:

"Code changes in DB2 9 which add additional calculations. These
calculations are triggered by value AVGCOMPRESSEDROWSIZE which can be
found in SYSSTAT. TABLES. This column does not exist in DB2 V8 and
the value is set to -1 during a database migration, because of the
additional calculations we end up in fpages being less than 1 which
in return triggers the db2diag.log messages. "

I looked at SYSSTAT.TABLES and sure enough most of the values for
AVGCOMPRESSEDROWSIZE were in fact "-1". However, there were some 200
tables where the value was "0".

My question to this forum is, since "-1" is apparently not a valid
value, what would be the effect if I updated AVGCOMPRESSEDROWSIZE to
"0" for all tables? Would this get rid of the error without messing
up something else and allow me to run at DIAGLEVEL 3 without
generating unmanagable database log files?

Anybody?
According to the Infocenter for DB2 9, "-1" is an indication that no
statistics have been collected for those tables:
http://publib.boulder.ibm.com/infoce...c/r0001073.htm

HTH

--
Jeroen
Jun 22 '07 #2

P: n/a
Thanks for the pointer, Boss.
I guess I was expecting the AUTO_RUNSTATS to take care of this.
By forcing update statistics on all tables in the database syscat.tables is
updated and the annoying messages seem to have stopped.
Cheers!

The Boss wrote:
>Greetings all.
[quoted text clipped - 48 lines]
>>
Anybody?

According to the Infocenter for DB2 9, "-1" is an indication that no
statistics have been collected for those tables:
http://publib.boulder.ibm.com/infoce...c/r0001073.htm

HTH
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200706/1

Jun 22 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.