Top_10_Identity_Columns_Approaching_Max()
Last Post 13 Dec 2012 02:58 PM by Craig R. Lockhart. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Nelson Smith
New Member
New Member
Posts:10

--
13 Dec 2012 09:23 AM

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 )
   ELSE 0
  END 
  , 5 
  , 2
  ) as Percent_Full
 , Column.Data_Type
 , 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.Length
 , Column.Numeric_scale as Dec
 , CHAR (CASE 
   WHEN Column.Has_Default = 'I' THEN 'ALWAYS'
   WHEN Column.Has_Default = 'J' THEN 'By DEFAULT'
   ELSE 'Error'
  END 
  ,10
  ) 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
 , Table.System_Table_Name
 , Table.System_Table_Schema

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.

Craig R. Lockhart
Veteran Member
Veteran Member
Posts:1167
Avatar

--
13 Dec 2012 12:51 PM

The obvious answer is that the 5,2 is too small to hold the result (maybe for an intermediary result).

select  Dec ( CASE 
   WHEN TableStats.Number_Rows > 0 THEN ((TableStats.Number_Rows/Column.Identity_maximum) * 100 )
   ELSE 0
  END 
  , 5 
  , 2
  ) as Percent_Full 
You may not be interested in this, but Rank() may be useful to you.  Reason = it can display ties. If the 10th row and 11th row were equal, for example.

 Good Luck.

Nelson Smith
New Member
New Member
Posts:10

--
13 Dec 2012 12:59 PM
I've tried replacing the Dec 5,2 with everything from Float to Bigint to Dec 31,2. They all get the same error.
Nelson Smith
New Member
New Member
Posts:10

--
13 Dec 2012 01:29 PM
Adding a Rank() column and removing the Order By Clause did give the same results, but it also showed the same MCH1210 errors in the joblog.

BTW, the Decimal 5,2 column works just fine ( with no errors in the joblog ), if I just remove Percent_Full from the Order By Clause.

The errors are coming from that first column in the Order By, but I don't know why. Some (but not all) of the other selected columns give the same error when used in the Order By.

I was sorta hoping someone could cut & paste the statement into their system and see if I just have a local problem on my machine (like PTF's or something).
Craig R. Lockhart
Veteran Member
Veteran Member
Posts:1167
Avatar

--
13 Dec 2012 02:58 PM

We do not have any indentity columns in production.  I ran this over my play library on development (so I would get some results).

No errors.  V6R1.

You are not authorized to post a reply.

Acceptable Use Policy