How to retrieve when and for which columns the distributions were last built.

Problem(Abstract)

How to retrieve when and for which columns the distributions were last built.

Resolving the problem

INTRODUCTION

This article shows a SQL statement which can be executed to retrieve when and for which columns the distributions were last built. However, if a column does not have a distribution it will not appear in the query output.

STEPS

select systables.tabname, syscolumns.colname, 
      sysdistrib.constructed,mode
from sysdistrib,systables,syscolumns
where systables.tabid > 99
and systables.tabid = syscolumns.tabid
and sysdistrib.tabid=systables.tabid
and sysdistrib.colno = syscolumns.colno
group by 1,2,3,4
order by tabname,colname;

Example

Assuming that 

UPDATE STATISTICS HIGH for table customer (customer_num) 

was run, what follows is the corresponding information returned by the query for the table customer: 

 
tabname      customer 
colname      customer_num 
constructed  12/15/2006 
mode         H 

http://www-01.ibm.com/support/docview.wss?uid=swg21251926