CREATE SET TABLE DBA.T_CMPR_CAND ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (
      DATABASENAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      TABLENAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      COLUMNNAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      VAL_TXT VARCHAR(3000) CHARACTER SET LATIN NOT CASESPECIFIC,
      COUT_NBR DECIMAL(18,0),
      COMPILE_DT DATE FORMAT 'YY/MM/DD' DEFAULT DATE ,
      RANK_NBR INTEGER)
UNIQUE PRIMARY INDEX UPI ( DATABASENAME ,TABLENAME ,COLUMNNAME ,
VAL_TXT ,COMPILE_DT )
INDEX NUSI1 ( DATABASENAME ,TABLENAME ,COLUMNNAME );


REPLACE MACRO DBA.M_GET_COL_COUT_CMD
 (DATABASENAME VARCHAR(30)
)
AS
(
select 'CALL SYSSTP.COL_COUT
(''' || TRIM(DATABASENAME) || ''',''' || TRIM(TABLENAME) || ''',''' || TRIM(COLUMNNAME) || ''');'  
from dbc.columns 
where databasename = :DATABASENAME
AND columntype in ('I','DA','D','CF','F') 
AND (databasename, tablename,  columnname) 
NOT IN (SELECT databasename, tablename,  columnname FROM DBC.indices where indextype in ('K', 'P')) 
AND (DATABASENAME, TABLENAME) IN (SELECT DATABASENAME, TABLENAME FROM DBC.TABLES WHERE TABLEKIND = 'T')
ORDER BY 1;
);


REPLACE MACRO DBA.M_GET_CMPR_RSLT
 (DATABASENAME VARCHAR(30)
 , COMPILE_DT DATE
)
 AS
 (
LOCKING DBA.T_CMPR_CAND FOR ACCESS
SELECT COMPILE_DT
, DBA.T_CMPR_CAND.DATABASENAME
, DBA.T_CMPR_CAND.TABLENAME
, DBA.T_CMPR_CAND.COLUMNNAME
, COLUMNLENGTH
, SUM(COUT_NBR) AS NUM_ROWS
, CAST(NUM_ROWS * COLUMNLENGTH AS DECIMAL(18,0)) AS MAXSIZE
, SUM(CASE WHEN RANK_NBR < 2 THEN COUT_NBR ELSE 0 END) AS RANK001
, RANK001 * COLUMNLENGTH - RANK001 *1/8 AS SAVINGS001
, SUM(CASE WHEN RANK_NBR < 4 THEN COUT_NBR ELSE 0 END) AS RANK003
, RANK003 * COLUMNLENGTH - RANK003 *2/8 AS SAVINGS003
, SAVINGS003  - SAVINGS001 AS DELTA003
, SUM(CASE WHEN RANK_NBR < 8 THEN COUT_NBR ELSE 0 END) AS RANK007
, RANK007 * COLUMNLENGTH - RANK007 *3/8 AS SAVINGS007
, SAVINGS007  - SAVINGS003 AS DELTA007
, SUM(CASE WHEN RANK_NBR < 16 THEN COUT_NBR ELSE 0 END) AS RANK015
, RANK015 * COLUMNLENGTH - RANK015 *4/8 AS SAVINGS015
, SAVINGS015  - SAVINGS007 AS DELTA015
, SUM(CASE WHEN RANK_NBR < 32 THEN COUT_NBR ELSE 0 END) AS RANK031
, RANK031 * COLUMNLENGTH - RANK031 *5/8 AS SAVINGS031
, SAVINGS031  - SAVINGS015 AS DELTA031
, SUM(CASE WHEN RANK_NBR < 64 THEN COUT_NBR ELSE 0 END) AS RANK063
, RANK063 * COLUMNLENGTH - RANK063 *6/8 AS SAVINGS063
, SAVINGS063  - SAVINGS031 AS DELTA063
, SUM(CASE WHEN RANK_NBR < 128 THEN COUT_NBR ELSE 0 END) AS RANK127
, RANK127 * COLUMNLENGTH - RANK127 *7/8 AS SAVINGS127
, SAVINGS127  - SAVINGS063 AS DELTA127
, SUM(CASE WHEN RANK_NBR < 256 THEN COUT_NBR ELSE 0 END) AS RANK255
, RANK255 * COLUMNLENGTH - RANK255 *8/8 AS SAVINGS255
, SAVINGS255  - SAVINGS127 AS DELTA255
FROM DBA.T_CMPR_CAND
WHERE DBA.T_CMPR_CAND.DATABASENAME = DBC.COLUMNS.DATABASENAME
AND DBA.T_CMPR_CAND.TABLENAME = DBC.COLUMNS.TABLENAME
AND DBA.T_CMPR_CAND.COLUMNNAME = DBC.COLUMNS.COLUMNNAME
AND DBA.T_CMPR_CAND.DATABASENAME = :DATABASENAME
AND DBA.T_CMPR_CAND.COMPILE_DT = :COMPILE_DT
GROUP BY 1,2,3,4,5
ORDER BY 1,2,3,4,5;
);


REPLACE MACRO DBA.M_BLD_CMPR_LIST
 (DATABASENAME VARCHAR(30)
,  TABLENAME VARCHAR(30)
, COLUMNNAME VARCHAR(30)
, RANK_NBR INTEGER
, COMPILE_DT DATE
, COLUMNLENGTH INTEGER
)
 AS
(
LOCKING DBA.T_CMPR_CAND FOR ACCESS
SELECT DBA.T_CMPR_CAND.COLUMNNAME, 
CASE 
WHEN DBC.COLUMNS.COLUMNTYPE = 'CF' THEN ',''' || SUBSTR(VAL_TXT, 1, :COLUMNLENGTH) || '''' 
WHEN DBC.COLUMNS.COLUMNTYPE = 'DA' THEN ',' || TRIM(CAST(CAST(VAL_TXT as DATE) as INTEGER))  
ELSE ',' || TRIM(VAL_TXT) END
FROM DBA.T_CMPR_CAND
, DBC.COLUMNS
WHERE
DBA.T_CMPR_CAND.DATABASENAME = :DATABASENAME
AND DBA.T_CMPR_CAND.TABLENAME = :TABLENAME
AND
((DBA.T_CMPR_CAND.COLUMNNAME = :COLUMNNAME AND RANK_NBR < :RANK_NBR + 1)

)
AND COMPILE_DT = :COMPILE_DT
AND DBA.T_CMPR_CAND.DATABASENAME = DBC.COLUMNS.DATABASENAME
AND DBA.T_CMPR_CAND.TABLENAME = DBC.COLUMNS.TABLENAME
AND DBA.T_CMPR_CAND.COLUMNNAME = DBC.COLUMNS.COLUMNNAME
ORDER BY 1,2;
);