SPLText
REPLACE PROCEDURE  SYSSTP.COL_COUT
( IN DATABASENAME VARCHAR(30)
, IN TABLENAME VARCHAR(30)
, IN COLUMNNAME VARCHAR(30)
)
/**************************************************/
/*  NAME:  COL_COUT - COLUMN_COUNTER            */
/* DESC - INSERTS COLUMN COUNTS INTO TABLE FOR COMPRESSION STUDY   */
/* INPUTS:                                        */
/* DATABASENAME                                   */
/* HISTORY:                                       */
/* 12/22/2003            CREATED.                 */               
/* 1/7/2004              Cleaned up to add lessons learned */
/**************************************************/
      
     
COLUMN_LABEL:      BEGIN
   
DELETE FROM DBA.T_GLBL_CMPR_CAND;        
      
CALL DBC.SYSEXECSQL (
'INSERT INTO DBA.T_GLBL_CMPR_CAND (DATABASENAME, TABLENAME, COLUMNNAME
, VAL_TXT, COUT_NBR) SELECT '''
|| TRIM(:DATABASENAME) || ''','''
|| TRIM(:TABLENAME) || ''','''
|| TRIM(:COLUMNNAME) || ''','
|| ' CAST( ' || :COLUMNNAME || ' AS VARCHAR(3000))'
|| ',  COUNT(*) FROM ' 
|| TRIM(:DATABASENAME) 
|| '.' 
|| TRIM(:TABLENAME) 
|| ' GROUP BY 1,2,3,4;' ) ;

UPDATE DBA.T_GLBL_CMPR_CAND
FROM
(SELECT DATABASENAME, TABLENAME, COLUMNNAME, VAL_TXT
, RANK() OVER 
(PARTITION BY DATABASENAME, TABLENAME, COLUMNNAME ORDER BY COUT_NBR DESC)
FROM DBA.T_GLBL_CMPR_CAND
WHERE DBA.T_GLBL_CMPR_CAND.DATABASENAME = :DATABASENAME
AND DBA.T_GLBL_CMPR_CAND.TABLENAME = :TABLENAME
AND DBA.T_GLBL_CMPR_CAND.COLUMNNAME = :COLUMNNAME
AND DBA.T_GLBL_CMPR_CAND.COMPILE_DT = DATE
 ) A(DATABASENAME, TABLENAME, COLUMNNAME, VAL_TXT, RANK_NBR)
SET RANK_NBR =  A.RANK_NBR
WHERE DBA.T_GLBL_CMPR_CAND.DATABASENAME = A.DATABASENAME
AND DBA.T_GLBL_CMPR_CAND.TABLENAME = A.TABLENAME
AND DBA.T_GLBL_CMPR_CAND.COLUMNNAME = A.COLUMNNAME
AND DBA.T_GLBL_CMPR_CAND.VAL_TXT = A.VAL_TXT
AND DBA.T_GLBL_CMPR_CAND.DATABASENAME = :DATABASENAME
AND DBA.T_GLBL_CMPR_CAND.TABLENAME = :TABLENAME
AND DBA.T_GLBL_CMPR_CAND.COLUMNNAME = :COLUMNNAME
AND DBA.T_GLBL_CMPR_CAND.COMPILE_DT = DATE;

INSERT INTO DBA.T_CMPR_CAND SELECT * FROM DBA.T_GLBL_CMPR_CAND;

END COLUMN_LABEL;