One or more of these VARCHAR columns is being lengthened during an UPDATE.
The suggestions about an UPDATE trigger or snapshots for detection purposes are good.
A solution from a prevention perspective would be to increase the table's PCTFREE value. This is done with ALTER TABLE x PCTFREE y. Where y is a percentage up to 99.
This makes LOAD and REORG leave "filler" space in every page so that row lengthening (caused by VARCHAR lengthening or ALTER TABLE ADD COLUMN) can be accomodated within the existing page. After running ALTER you need to REORG or LOAD the table for it to take effect.
Oddly, you can't specify PCTFREE at CREATE TABLE time but only at ALTER TABLE time.
Best,
Hal Steiner
Bloomberg LP
-----Original Message-----
From: Hehir John <db2udbdba-ezmlmshield-x15177775.[Email address protected]
To: LazyDBA Discussion <[Email address protected]
Sent: Tue, 25 Apr 2006 13:26:59 -0400
Subject: RE: Overflow question
I have a couple of other thoughts on this:
1. You could set up an update trigger that could more or less keep a
count as to which updates are predominant.
2. If you perform snapshots on the table then you can see the overflows
increment
Kind of round about ways but thought they could help isolate what the
culprit is
John Hehir
-----Original Message-----
From: Rai Amit
[mailto:db2udbdba-ezmlmshield-x58342008.[Email address protected]
Sent: Tuesday, April 25, 2006 1:22 PM
To: LazyDBA Discussion
Subject: RE: Overflow question
Updates to varchar data, normally cause overflow....
You have quite a few varchar columns. Check dynamic sql snapshot and
packages to find out, sqls updating these columns.
-- Amit
-----Original Message-----
From: Kelleher. Jean (IT Solutions)
[mailto:db2udbdba-ezmlmshield-x96934534.[Email address protected]
Sent: Tuesday, April 25, 2006 11:38 AM
To: LazyDBA Discussion
Subject: Overflow question
Hi All,
I have a query from a user who is asking if we can identify what
fields are causing records in a table to become overflow records. The
table in question has more than 25% of the records which are overflow.
The table structure is as follows
UC_POD_EXT VARCHAR 50
/BIC/UCATFKWH DECIMAL 17
/BIC/UCADKWH DECIMAL 17
/BIC/UCANKWH DECIMAL 17
/BIC/UCMAXKVA DECIMAL 17
UCINSTALLA VARCHAR 10
UCPREMISE VARCHAR 10
/BIC/ARBILLCLA VARCHAR 4
/BIC/UCOPC_MPS CHARACTER 1
/BIC/UCOPK_MIC DECIMAL 17
/BIC/ARSUBNO VARCHAR 9
RECORDMODE CHARACTER 1
/BIC/AROUTLET VARCHAR 2
/BIC/ARAREANO VARCHAR 3
/BIC/UCNUMMPRN INTEGER 4
The tablespace has a pagesize of 16384 bytes. Can anyone tell me if
there is a way to identify why 25% are going into overflow.
Regards,
Jean
* ** *** ** * ** *** ** * ** *** ** *
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they are
addressed.
Any views or opinions presented are solely those of the author, and do
not necessarily represent those of ESB.
If you have received this email in error please notify the sender.
Although ESB scans e-mail and attachments for viruses, it does not
guarantee that either are virus-free and accepts no liability for any
damage sustained as a result of viruses.
* ** *** ** * ** *** ** * ** *** ** *
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
------------------------------------------------------------------------
-------------------
***National City made the following annotations
------------------------------------------------------------------------
-------------------
This communication is a confidential and proprietary business
communication.
It is intended solely for the use of the designated recipient(s). If
this communication is received in error, please contact the sender and
delete this communication.
========================================================================
===================
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page