My first post on this forum.
I am looking to see if somebody could shed some light on the following.
I'm performing some analysis on the data stored in the overflow area of my table data. To help with this I'm pulling together an SQL script that shows me information from SYSCOLUMNS, SYSTABLESTAT and SYSCOLUMNSTAT. Basically I'm trying to identify if I need to adjust the ALLOCATE value on my VARCHAR and LOB columns.
This is what I've come up with.
select c.table_schema, c.table_name, ts.number_rows, ts.overflow, c.column_name, c.DATA_TYPE , c.LENGTH, c.INLINE_LENGTH, cs.AVERAGE_COLUMN_LENGTH, cs.MAXIMUM_COLUMN_LENGTH, LENGTH_AT_90TH_PERCENTILE, cs.OVERFLOW_ROWS
from qsys2.syscolumns2 as c
left outer join qsys2.syscolumnstat as cs on c.table_schema=cs.table_schema and c.table_name=cs.table_name and c.column_name=cs.column_name
left outer join qsys2.systablestat as ts on c.table_schema=ts.table_schema and c.table_name=ts.table_name
where c.table_schema='RMTEMP' and cs.OVERFLOW_ROWS is not null;
So far so good and on the whole I'm happy with the details but there is one piece of information that I don't understand.
The SYSTABLESTAT view includes a column called OVERFLOW that is described as "The estimated number of rows that have overflowed to variable length segments.". The value I'm seeing for one particular table just doesn't look right to me - not even close - but maybe it is and there's something else going on that I'm not seeing.
The table in question has 270,880 rows with an OVERFLOW value of 270,879 so it's suggesting all but one of the rows is in the table overflow area.
When I look at the column stats for this table I'm seeing the following.
I would therefore expect the number of overflow rows, on SYSTABLESTAT, to be around 23k rows. The column level stats look good to me.
Have I misunderstood what the SYSTABLESTAT OVERFLOW column is telling me?
Note that I have recently increased the length of the EVENT_MESSAGE BLOB column from 64k to 256k and, unfortunately, I didn't check the SYSTABLESTAT view before the change.
------------------------------
Richard Moulton
------------------------------
#SQL