Here's the query from a little procedure I use to watch for Identity Column counts getting too large:
select Dec ( CASE
WHEN TableStats.Number_Rows > 0 THEN ((TableStats.Number_Rows/Column.Identity_maximum) * 100 )
) as Percent_Full
, TableStats.Number_Rows as Row_Count
, Table.System_Table_Schema as Schema
, Table.System_Table_Name as Table
, Table.Table_Name as SQL_Table
, Table.Table_Text as Table_Desc
, Column.ordinal_Position as Col_Position
, Column.System_Column_Name as Column
, Column.Column_Name as SQL_Column
, Column.Column_Text as Column_Desc
, Column.Numeric_scale as Dec
, CHAR (CASE
WHEN Column.Has_Default = 'I' THEN 'ALWAYS'
WHEN Column.Has_Default = 'J' THEN 'By DEFAULT'
) as Generated
, Column.Identity_Start as Start
, Column.Identity_Increment as Increment
, Column.Identity_Minimum as Minimum
, Column.Identity_maximum as Maximum
, Column.Identity_Cycle as Cycle
, Column.Identity_Cache as Cache
, SUBSTR(Column.Column_Heading,1,20) as Heading1
, SUBSTR(Column.Column_Heading,21,20) as Heading2
, SUBSTR(Column.Column_Heading,41,20) as Heading3
from syscolumns as Column
join systables as Table
on Column.System_Table_Name = Table.System_Table_Name
and Column.System_Table_Schema = Table.System_Table_Schema
join systablestat as TableStats
on Table.System_Table_Name = TableStats.System_Table_Name
and Table.System_Table_Schema = TableStats.System_Table_Schema
where Column.is_identity = 'YES'
and Table.Table_Type = 'T'
and Table.System_Table_Schema = 'MYLIB'
Order By Percent_Full Desc
Fetch First 10 Rows Only
The query works fine, but is very slow on the first invocation due to many 'MCH1210 Receiver value too small to hold result.' Escape messages. Subsequent invocations get the same errors, but execute fast.
I've found, through experimenting, that the problem is first colum listed in the Order By clause, Percent_Full. When I leave it out, no errors (but of course, also no Top 10). Additional experimenting shows that about half of the selected columns generate these same errors when used in the Order By clause and there does not seem to be any rhyme or reason to which ones work and which ones do not. Some are numeric and some are character.
Anyone have any idea as to why this is? Does it act the same on your system? (be sure to change the Schema in the Where clause to one on your system) I'm on V6 R1.