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_ROWSfrom qsys2.syscolumns2 as cleft 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_nameleft outer join qsys2.systablestat as ts on c.table_schema=ts.table_schema and c.table_name=ts.table_namewhere 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
------------------------------

Hi Richard,

Is there another varchar or LOB column in that table that does not have column statistics but that does overflow its inline allocation? QSYS2.SYSCOLUMNSTAT will only return information for those columns that have had column statistics collected over them, which means that you may not be seeing the whole picture if there are some (overflowing) columns that do not have column statistics.

------------------------------
Tim Clark
DB2 for IBM i / SQL Optimizer
------------------------------

Original Message:
Sent: Thu March 07, 2024 06:55 AM
From: Richard Moulton
Subject: OVERFLOW rows and SYSTABLESTAT

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_ROWSfrom qsys2.syscolumns2 as cleft 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_nameleft outer join qsys2.systablestat as ts on c.table_schema=ts.table_schema and c.table_name=ts.table_namewhere 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
------------------------------