/* 
-- TO BE EXECUTED BEFORE THE DEPLOYMENT OF 

CREATE VOLATILE TABLE MYCLEANUP
				(
				THENUMBER SMALLINT NOT NULL,
				THESTATEMENT VARCHAR(100) NOT NULL
				)
				PRIMARY INDEX (THENUMBER)
				ON COMMIT PRESERVE ROWS;
				
CREATE VOLATILE SET TABLE INSERTITUDE ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
	  STMTNR SMALLINT NOT NULL,
	  COLUMNNAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL ,
	  THEBLOB  VARCHAR(10000) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL  )
PRIMARY INDEX ( STMTNR )
ON COMMIT PRESERVE ROWS;

CREATE VOLATILE SET TABLE VT_COMP_VALUES_IS ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      DatabaseName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      TableName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      ColumnName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      COMP_REMAINS_LIST  VARCHAR(8192) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
	  COMPRESSED_VALUE VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
      COMPRESSION_ORDER_NR INTEGER NOT NULL )
PRIMARY INDEX ( DatabaseName, TableName, ColumnName, COMPRESSION_ORDER_NR )
ON COMMIT PRESERVE ROWS;

CREATE VOLATILE SET TABLE VT_COMP_CAND,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      DatabaseName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      TableName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      ColumnName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
	  COL_DATATYPE VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
	  COL_NULLABLE SMALLINT NOT NULL,
	  COL_CALC_VALUE INTEGER NOT NULL,
	  COL_VALUE VARCHAR(1000) CHARACTER SET UNICODE NOT CASESPECIFIC,
      COL_FREQUENCY BIGINT NOT NULL,
      COL_VAL_RANK INTEGER,
	  CURRENT_COMPRESSION SMALLINT )
PRIMARY INDEX ( DatabaseName, TableName, ColumnName, COL_VALUE )
ON COMMIT PRESERVE ROWS;

CREATE VOLATILE SET TABLE VT_COMP_LEVEL_SVG ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ColumnName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      COL_ORDER SMALLINT NOT NULL ,
      COMP_LEVEL SMALLINT NOT NULL ,
      SVGS_BYTES BIGINT NOT NULL,
      COMP_COST_BYTES_HDR  BIGINT NOT NULL,
      CHOSEN SMALLINT
       )
PRIMARY INDEX ( ColumnName, COMP_LEVEL )
ON COMMIT PRESERVE ROWS;

CREATE VOLATILE SET TABLE VT_RESULTS,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
	  INFO_TYPE VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
	  INFO_VALUE BIGINT NOT NULL )
PRIMARY INDEX ( INFO_TYPE )
ON COMMIT PRESERVE ROWS;

CREATE VOLATILE TABLE VT_COLBYTES
			(
			COLORDER SMALLINT NOT NULL,
			COLVALUE DECIMAL(38,18) NOT NULL
			)
			PRIMARY INDEX (COLORDER)
			ON COMMIT PRESERVE ROWS;

CREATE VOLATILE SET TABLE PARTCHECK ,NO FALLBACK ,
		NO BEFORE JOURNAL,
		NO AFTER JOURNAL,
		CHECKSUM = DEFAULT
		(
		COLUMNNAME VARCHAR(30) NOT NULL,
		PARTITION_STMT  VARCHAR(16000) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
		PARTITIONED_BY SMALLINT NOT NULL  )
		PRIMARY INDEX ( COLUMNNAME )
		ON COMMIT PRESERVE ROWS;

*/


REPLACE PROCEDURE DWHPRO_TITHONIZER(
IN ETLDB VARCHAR(30),
IN TABLENAME VARCHAR(30)
) 

DYNAMIC RESULT SETS 2

BEGIN

DECLARE ETLDB_IN VARCHAR(30) ;
DECLARE STGDB VARCHAR(30) ; 
DECLARE TABLENAME_IN VARCHAR(30) ;
DECLARE TheStep VARCHAR(4);
DECLARE LOAD_ID_IN SMALLINT;

DECLARE TDVersion SMALLINT;
DECLARE MyStepNum SMALLINT;
DECLARE MySQLQuery VARCHAR(32000) CHARACTER SET LATIN;
DECLARE MySQLCode INTEGER;
DECLARE MySQLState CHAR(5);
DECLARE ReportingActivityCount INTEGER;
DECLARE StartTS TIMESTAMP;
DECLARE SepStartTS TIMESTAMP;
DECLARE EndTS TIMESTAMP;
DECLARE THESESSION INTEGER; 
DECLARE MyCleanup VARCHAR(10000) ;
DECLARE CurrentCleanup VARCHAR(100);
DECLARE CleanupCounter SMALLINT;
DECLARE RBM_FULL_PREP VARCHAR(3000);
DECLARE NULLABILITYBITS SMALLINT; 
DECLARE NULLABILITYBYTES SMALLINT ;
DECLARE CURR_COMP_FINNS SMALLINT;
DECLARE CURR_COMP_BYTES SMALLINT ; 
DECLARE COMP_CANDIDATES SMALLINT ; 
DECLARE EST_SPACE_UNCOMP_AS_IS BIGINT;
DECLARE EMP_SPACE_AS_IS BIGINT;
DECLARE EST_SPACE_OPT_COMP BIGINT;
DECLARE COMP_BITS_LOPT SMALLINT ; 
DECLARE LOPT_NET_BITS SMALLINT ; 
DECLARE DOWNLEVEL_NET_SVGS BIGINT;
DECLARE TBL_ROWCOUNT DECIMAL(38,8);
DECLARE TBL_COLCOUNT SMALLINT;
DECLARE TBL_COMP_FINNS SMALLINT;
DECLARE VTCOUNTER SMALLINT; 
DECLARE COMP_THRESHOLD INTEGER;
DECLARE TOT_NET_PBYTES SMALLINT;
DECLARE SYSAMPNR INTEGER;
DECLARE HDR_COST_BREAK SMALLINT;
DECLARE PARTITION_EXP VARCHAR(16000) ;
DECLARE PART_COL_LIST VARCHAR(3000) ;
DECLARE PART_TD14_COND VARCHAR(300);
DECLARE RW_COL_LIST VARCHAR(3000) ;
DECLARE COLCOUNTER SMALLINT;

DECLARE sql_stmt2 VARCHAR(3000) ;

DECLARE c VARCHAR(3000) ;
DECLARE n SMALLINT DEFAULT 0;
DECLARE k INTEGER DEFAULT 0;
DECLARE lvl SMALLINT DEFAULT 0;
DECLARE WARNMSG VARCHAR(3000);

DECLARE RBM_UNC_PREP CURSOR WITH RETURN TO CLIENT FOR stmtRUP;

DECLARE EXIT  HANDLER  FOR  SQLEXCEPTION 
	BEGIN
	
	/* First, log the step the SP failed at */ 
	
	SET MyCleanup = MyCleanup||'SET QUERY_BAND =NONE FOR SESSION;';
	
	SET SepStartTS = CURRENT_TIMESTAMP;

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'/* ERROR! */' ||TheStep||COALESCE(WARNMSG,''),
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;

	/* Close the QB before removing VT */
	SET MySQlquery='SET QUERY_BAND =NONE FOR SESSION;';
	CALL DBC.SYSEXECSQL(MySQLQuery);
	
	/* Second, all collected steps  */
	
	SELECT COUNT(*) INTO CleanupCounter FROM MYCLEANUP; 
	
	IF CleanupCounter>= 1
	THEN
	SELECT THESTATEMENT INTO CurrentCleanup FROM MYCLEANUP WHERE THENUMBER=1;
	SET MySQlquery=CurrentCleanup;
	CALL DBC.SYSEXECSQL(MySQLQuery);
	ELSE
	SELECT 'DROP TABLE MYCLEANUP;' INTO CurrentCleanup;
	SET MySQlquery=CurrentCleanup;
	CALL DBC.SYSEXECSQL(MySQLQuery);	
	END IF;
	
	IF CleanupCounter>= 2
	THEN
	SELECT THESTATEMENT INTO CurrentCleanup FROM MYCLEANUP WHERE THENUMBER=2;
	SET MySQlquery=CurrentCleanup;
	CALL DBC.SYSEXECSQL(MySQLQuery);
	ELSE
	SELECT 'DROP TABLE MYCLEANUP;' INTO CurrentCleanup;
	SET MySQlquery=CurrentCleanup;
	CALL DBC.SYSEXECSQL(MySQLQuery);	
	END IF;
	
	IF CleanupCounter>= 3
	THEN
	SELECT THESTATEMENT INTO CurrentCleanup FROM MYCLEANUP WHERE THENUMBER=3;
	SET MySQlquery=CurrentCleanup;
	CALL DBC.SYSEXECSQL(MySQLQuery);
	ELSE
	SELECT 'DROP TABLE MYCLEANUP;' INTO CurrentCleanup;
	SET MySQlquery=CurrentCleanup;
	CALL DBC.SYSEXECSQL(MySQLQuery);	
	END IF;
	
	IF CleanupCounter>= 4
	THEN
	SELECT THESTATEMENT INTO CurrentCleanup FROM MYCLEANUP WHERE THENUMBER=4;
	SET MySQlquery=CurrentCleanup;
	CALL DBC.SYSEXECSQL(MySQLQuery);
	ELSE
	SELECT 'DROP TABLE MYCLEANUP;' INTO CurrentCleanup;
	SET MySQlquery=CurrentCleanup;
	CALL DBC.SYSEXECSQL(MySQLQuery);	
	END IF;
	
	IF CleanupCounter>= 5
	THEN
	SELECT THESTATEMENT INTO CurrentCleanup FROM MYCLEANUP WHERE THENUMBER=5;
	SET MySQlquery=CurrentCleanup;
	CALL DBC.SYSEXECSQL(MySQLQuery);
	ELSE
	SELECT 'DROP TABLE MYCLEANUP;' INTO CurrentCleanup;
	SET MySQlquery=CurrentCleanup;
	CALL DBC.SYSEXECSQL(MySQLQuery);	
	END IF;
	
	IF CleanupCounter>= 6
	THEN
	SELECT THESTATEMENT INTO CurrentCleanup FROM MYCLEANUP WHERE THENUMBER=6;
	SET MySQlquery=CurrentCleanup;
	CALL DBC.SYSEXECSQL(MySQLQuery);
	ELSE
	SELECT 'DROP TABLE MYCLEANUP;' INTO CurrentCleanup;
	SET MySQlquery=CurrentCleanup;
	CALL DBC.SYSEXECSQL(MySQLQuery);	
	END IF;
	
	IF CleanupCounter>= 7
	THEN
	SELECT THESTATEMENT INTO CurrentCleanup FROM MYCLEANUP WHERE THENUMBER=7;
	SET MySQlquery=CurrentCleanup;
	CALL DBC.SYSEXECSQL(MySQLQuery);
	ELSE
	SELECT 'DROP TABLE MYCLEANUP;' INTO CurrentCleanup;
	SET MySQlquery=CurrentCleanup;
	CALL DBC.SYSEXECSQL(MySQLQuery);	
	END IF;
	
	IF CleanupCounter>= 8
	THEN
	SELECT THESTATEMENT INTO CurrentCleanup FROM MYCLEANUP WHERE THENUMBER=8;
	SET MySQlquery=CurrentCleanup;
	CALL DBC.SYSEXECSQL(MySQLQuery);
	ELSE
	SELECT 'DROP TABLE MYCLEANUP;' INTO CurrentCleanup;
	SET MySQlquery=CurrentCleanup;
	CALL DBC.SYSEXECSQL(MySQLQuery);	
	END IF;
	
	IF CleanupCounter>= 9
	THEN
	SELECT THESTATEMENT INTO CurrentCleanup FROM MYCLEANUP WHERE THENUMBER=9;
	SET MySQlquery=CurrentCleanup;
	CALL DBC.SYSEXECSQL(MySQLQuery);
	ELSE
	SELECT 'DROP TABLE MYCLEANUP;' INTO CurrentCleanup;
	SET MySQlquery=CurrentCleanup;
	CALL DBC.SYSEXECSQL(MySQLQuery);	
	END IF;
	
	IF CleanupCounter>= 10
	THEN
	SELECT THESTATEMENT INTO CurrentCleanup FROM MYCLEANUP WHERE THENUMBER=10;
	SET MySQlquery=CurrentCleanup;
	CALL DBC.SYSEXECSQL(MySQLQuery);
	ELSE
	SELECT 'DROP TABLE MYCLEANUP;' INTO CurrentCleanup;
	SET MySQlquery=CurrentCleanup;
	CALL DBC.SYSEXECSQL(MySQLQuery);	
	END IF;
	
END;



/*****************************************************************************
 *****************************************************************************
 * * * * * * * * * * * * * * * * CHAPTER 0  * * * * * * * * * * * * * * * * *  
 * * * VARIABLE, KEY, QUERY BAND, AND TEXT PATTERN INITIALISATION * * * * * *  
 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 
 *****************************************************************************
*****************************************************************************/

/* Variable initialisation, taking over input parameter values, or start value setting */

SELECT SESSION INTO THESESSION; 

SELECT CAST(SUBSTR(TRIM(InfoData),1,2) AS INTEGER) INTO TDVersion from dbc.dbcInfo WHERE InfoKey='VERSION' ;

SET ETLDB_IN = ETLDB;
SELECT DATABASENAME INTO STGDB FROM DBC.DATABASES WHERE DATABASENAME = SUBSTR(ETLDB_IN,1,5)||'_STAGE';
SET TABLENAME_IN = TABLENAME;
SET MyStepNum = 100;
SET LOAD_ID_IN=-7;

SET TheStep='0000';
SET MyCleanup ='';
SET RBM_FULL_PREP='';
SET VTCOUNTER=0;
SELECT COUNT(VProc) INTO SYSAMPNR FROM DBC.TableSize where databasename = ETLDB_IN and tablename=TABLENAME_IN ; 
SELECT COALESCE(COUNT(*),0) INTO COLCOUNTER FROM DBC.COLUMNS WHERE DATABASENAME= ETLDB_IN AND TABLENAME= TABLENAME_IN ; 



/*****************************************************************************
* STEP 0010 Start Query Band
*****************************************************************************/

SET TheStep='0010';

SET MySQLQuery = 'SET QUERY_BAND =''ACTV=DWHPRO_TITHONIZER;T='|| TABLENAME_IN ||';EVALUATIONDAY='|| TRIM(CURRENT_DATE) || ';SESSION='||TRIM(THESESSION)||';'' FOR SESSION;';

SET SepStartTS = CURRENT_TIMESTAMP;

CALL DBC.SYSEXECSQL(MySQLQuery);

SET EndTS = CURRENT_TIMESTAMP;
SET MySQLState = SQLSTATE;
SET MySQLCode = SQLCODE;
SET ReportingActivityCount=ACTIVITY_COUNT;

INSERT INTO G00_SP_LOG_TEMP 
   (
TARGET_TABLE,
STEP_NUM,
STEP_DESC,
TM_START,
STEP_START,
SQL_REQUEST_TEXT,
SQL_STATE,
SQL_CODE,
NM_ACTIVITY_COUNT,
STEP_END,
LOAD_ID
   )
VALUES
   (
    TABLENAME_IN,
    :MyStepNum,
    '0010: Set SP Query Band',
    CURRENT_TIMESTAMP,
	:SepStartTS,
    :MySQLQuery,
	:MySQLState,
	:MySQLCode,
	:ReportingActivityCount,
	:EndTS,
    LOAD_ID_IN
   );
  
SET MyStepNum = MyStepNum + 100;



/*****************************************************************************
* STEP 0020 DATABSE AND TABLE INPUT CHECK
*****************************************************************************/

SET TheStep='0020';

SET WARNMSG='';
	
IF (SELECT COALESCE(COUNT(*),0) FROM DBC.DATABASES WHERE  DATABASENAME =ETLDB_IN ) =0 THEN 
SET WARNMSG=WARNMSG||'1.ETLDB '||ETLDB_IN||' NOT IN DBC.DATABASES!' ;
END IF; 

IF (SELECT COALESCE(COUNT(*),0) FROM DBC.TABLES WHERE TABLENAME =TABLENAME_IN AND TABLEKIND='T') =0 THEN 
SET WARNMSG=WARNMSG||',2.TABLE'||TABLENAME_IN||' NOT IN DBC.TABLES!' ;
END IF; 

IF COALESCE(WARNMSG,'')  ne '' THEN

/* let the SP fail on purpose with a false Statement */
SET MySQLQuery = 'TELETTTTE FROM '|| TABLENAME_IN ||' WHERE 1=2;';
CALL DBC.SYSEXECSQL(MySQLQuery);

ELSE

SET MySQLQuery = WARNMSG;

END IF;

SET EndTS = CURRENT_TIMESTAMP;
SET MySQLState = SQLSTATE;
SET MySQLCode = SQLCODE;
SET ReportingActivityCount=ACTIVITY_COUNT;

INSERT INTO G00_SP_LOG_TEMP 
   (
TARGET_TABLE,
STEP_NUM,
STEP_DESC,
TM_START,
STEP_START,
SQL_REQUEST_TEXT,
SQL_STATE,
SQL_CODE,
NM_ACTIVITY_COUNT,
STEP_END,
LOAD_ID
   )
VALUES
   (
    TABLENAME_IN,
    :MyStepNum,
    '0020: DB,TBL Input Check',
    CURRENT_TIMESTAMP,
	:SepStartTS,
    :WARNMSG,
	:MySQLState,
	:MySQLCode,
	:ReportingActivityCount,
	:EndTS,
    LOAD_ID_IN
   );
  
SET MyStepNum = MyStepNum + 100;



/*****************************************************************************
* STEP 0030 VT MYCLEANUP Creation
*****************************************************************************/

/* VT MYCLEANUP collects drop statements for objects to be removed if exit upon error */ 

SET TheStep='0030';

SET MySQLQuery = 'CREATE VOLATILE TABLE MYCLEANUP
				(
				THENUMBER SMALLINT NOT NULL,
				THESTATEMENT VARCHAR(100) NOT NULL
				)
				PRIMARY INDEX (THENUMBER)
				ON COMMIT PRESERVE ROWS;';
			
SET SepStartTS = CURRENT_TIMESTAMP;

CALL DBC.SYSEXECSQL(MySQLQuery);

SET EndTS = CURRENT_TIMESTAMP;
SET MySQLState = SQLSTATE;
SET MySQLCode = SQLCODE;
SET ReportingActivityCount=ACTIVITY_COUNT;

INSERT INTO G00_SP_LOG_TEMP 
   (
TARGET_TABLE,
STEP_NUM,
STEP_DESC,
TM_START,
STEP_START,
SQL_REQUEST_TEXT,
SQL_STATE,
SQL_CODE,
NM_ACTIVITY_COUNT,
STEP_END,
LOAD_ID
   )
VALUES
   (
	TABLENAME_IN,
	:MyStepNum,
	'0030 VT MYCLEANUP CREATE',
	CURRENT_TIMESTAMP,
	:SepStartTS,
	:MySQLQuery,
	:MySQLState,
	:MySQLCode,
	:ReportingActivityCount,
	:EndTS,
	LOAD_ID_IN
   );
  
SET MyStepNum = MyStepNum + 100;



/********************************************************************************************************************
* STEP 0040 VT RESULTS CREATION 
*********************************************************************************************************************/
 
SET TheStep='0040';

SET MySQLQuery = '
CREATE VOLATILE SET TABLE VT_RESULTS,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
	  INFO_TYPE VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
	  INFO_VALUE DECIMAL(38,8) NOT NULL 
	  )
PRIMARY INDEX ( INFO_TYPE )
ON COMMIT PRESERVE ROWS;'
;

SET SepStartTS = CURRENT_TIMESTAMP;

CALL DBC.SYSEXECSQL(MySQLQuery);

SET EndTS = CURRENT_TIMESTAMP;
SET MySQLState = SQLSTATE;
SET MySQLCode = SQLCODE;
SET ReportingActivityCount=ACTIVITY_COUNT;

INSERT INTO G00_SP_LOG_TEMP 
   (
TARGET_TABLE,
STEP_NUM,
STEP_DESC,
TM_START,
STEP_START,
SQL_REQUEST_TEXT,
SQL_STATE,
SQL_CODE,
NM_ACTIVITY_COUNT,
STEP_END,
LOAD_ID
   )
VALUES
   (
	TABLENAME_IN,
	:MyStepNum,
	'0040 VT RESULTS CREATE',
	CURRENT_TIMESTAMP,
	:SepStartTS,
	:MySQLQuery,
	:MySQLState,
	:MySQLCode,
	:ReportingActivityCount,
	:EndTS,
	LOAD_ID_IN
   );
  
SET MyStepNum = MyStepNum + 100;

SET VTCOUNTER=VTCOUNTER+1;
	
INSERT INTO MYCLEANUP (THENUMBER, THESTATEMENT) VALUES (VTCOUNTER, 'DROP TABLE VT_RESULTS;');



/********************************************************************************************************************
* STEP 0050 VT RESULTS TABLE ROWCOUNT STORAGE
*********************************************************************************************************************/

SET TheStep='0050';
 
SET MySQLQuery = 'INSERT INTO VT_RESULTS SELECT ''ROWCOUNT'', COALESCE(COUNT(*),0) AS ROWCOUNT FROM '||ETLDB_IN ||'.'||TABLENAME_IN ||' GROUP BY 1;';
 
SET SepStartTS = CURRENT_TIMESTAMP;

CALL DBC.SYSEXECSQL(MySQLQuery);

SELECT INFO_VALUE INTO TBL_ROWCOUNT FROM VT_RESULTS WHERE INFO_TYPE='ROWCOUNT';

SET EndTS = CURRENT_TIMESTAMP;
SET MySQLState = SQLSTATE;
SET MySQLCode = SQLCODE;
SET ReportingActivityCount=ACTIVITY_COUNT;

INSERT INTO G00_SP_LOG_TEMP 
   (
TARGET_TABLE,
STEP_NUM,
STEP_DESC,
TM_START,
STEP_START,
SQL_REQUEST_TEXT,
SQL_STATE,
SQL_CODE,
NM_ACTIVITY_COUNT,
STEP_END,
LOAD_ID
   )
VALUES
   (
	TABLENAME_IN,
	:MyStepNum,
	'0050 TBL ROWCOUNT:'||TRIM(COALESCE(TBL_ROWCOUNT,0)),
	CURRENT_TIMESTAMP,
	:SepStartTS,
	:MySQLQuery,
	:MySQLState,
	:MySQLCode,
	:ReportingActivityCount,
	:EndTS,
	LOAD_ID_IN
   );
  
SET MyStepNum = MyStepNum + 100;



/********************************************************************************************************************
* STEP 0060 COMPRESSION THRESHOLD CHECK
*********************************************************************************************************************/

/* 
Tables have to contain an amount of rows of at least 5 times the number AMPS of the system in order to be accepted to as subject to compression  
Tables with fewer rows are excluded from further processing.
This implicitly exlcudes empty tables as well.
*/

SET TheStep='0060';

select COALESCE(COUNT(VProc),0)* 5 INTO COMP_THRESHOLD 
FROM dbc.TableSize 
WHERE DATABASENAME= ETLDB_IN
AND TABLENAME = TABLENAME_IN
; 
 
SET SepStartTS = CURRENT_TIMESTAMP;

SELECT INFO_VALUE INTO TBL_ROWCOUNT FROM VT_RESULTS WHERE INFO_TYPE='ROWCOUNT';

SET EndTS = CURRENT_TIMESTAMP;
SET MySQLState = SQLSTATE;
SET MySQLCode = SQLCODE;
SET ReportingActivityCount=ACTIVITY_COUNT;

INSERT INTO G00_SP_LOG_TEMP 
   (
TARGET_TABLE,
STEP_NUM,
STEP_DESC,
TM_START,
STEP_START,
SQL_REQUEST_TEXT,
SQL_STATE,
SQL_CODE,
NM_ACTIVITY_COUNT,
STEP_END,
LOAD_ID
   )
VALUES
   (
	TABLENAME_IN,
	:MyStepNum,
	'0060 COMP_THRESHOLD:'||TRIM(COMP_THRESHOLD),
	CURRENT_TIMESTAMP,
	:SepStartTS,
	:MySQLQuery,
	:MySQLState,
	:MySQLCode,
	:ReportingActivityCount,
	:EndTS,
	LOAD_ID_IN
   );
  
SET MyStepNum = MyStepNum + 100;



/* ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ */
/* CONTINUE ONLY IF THE TABLE HAS MORE ROWS THAN THE COMPRESSION MINIMUM THRESHOLD! */
/* ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ */

IF TBL_ROWCOUNT>COMP_THRESHOLD THEN 

	/*****************************************************************************
	 *****************************************************************************
	 * * * * * * * * * * * * * * * *  CHAPTER 1  * * * * * * * * * * * * * * * * *  
	 * * * * * * * *  COMPRESSIBLE COLUMN CANDIDATE ANALYSIS  * * * * * * *  * * *
	 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 
	 *****************************************************************************
	*****************************************************************************/



	/*****************************************************************************
	* STEP 1010 CREATE VT VT_COMP_CAND to store COMPRESSION CANDIDATE Information
	*****************************************************************************/
	 
	SET TheStep='1010';

	SET MySQLQuery = '
	CREATE VOLATILE SET TABLE VT_COMP_CAND,NO FALLBACK ,
		 NO BEFORE JOURNAL,
		 NO AFTER JOURNAL,
		 CHECKSUM = DEFAULT
		 (
		  DatabaseName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
		  TableName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
		  ColumnName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
		  COL_DATATYPE VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
		  COL_NULLABLE SMALLINT NOT NULL,
		  COL_CALC_VALUE INTEGER NOT NULL,
		  COL_VALUE VARCHAR(1000) CHARACTER SET UNICODE NOT CASESPECIFIC,
		  COL_FREQUENCY BIGINT NOT NULL,
		  COL_VAL_RANK INTEGER,
		  CURRENT_COMPRESSION SMALLINT )
	PRIMARY INDEX ( DatabaseName, TableName, ColumnName, COL_VALUE )
	ON COMMIT PRESERVE ROWS;'
	;

	SET SepStartTS = CURRENT_TIMESTAMP;

	CALL DBC.SYSEXECSQL(MySQLQuery);

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'1010 VT_COMP_CAND CREATE',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;

	SET VTCOUNTER=VTCOUNTER+1;
		
	INSERT INTO MYCLEANUP (THENUMBER, THESTATEMENT) VALUES (VTCOUNTER, 'DROP TABLE VT_COMP_CAND;');



	/*****************************************************************************
	* STEP 1020 COMPRESSION CANDIDATES - VT FOR INSERTITUDE STORAGE CREATION
	*****************************************************************************/

	/* 
	Compression Candidates are all columns of a table that are
	a) not part of the PI
	b) not part of a partitioning expression
	c) not subject to Indexing
	d) of a compressible data type, i.e. NOT IN ('BO','CO','PD','PM','PS','PT','PZ','UT') , depending partly on TD version
	e) not part of a set of technical or otherwise exempt columns, as identified by their name
	f) not named equally as a TD reserved word
	*/

	SET TheStep='1020';

	SET MySQLQuery = '
	CREATE VOLATILE SET TABLE INSERTITUDE ,NO FALLBACK ,
		 NO BEFORE JOURNAL,
		 NO AFTER JOURNAL,
		 CHECKSUM = DEFAULT
		 (
		  STMTNR SMALLINT NOT NULL,
		  COLUMNNAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
		  THEBLOB  VARCHAR(10000) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL  )
	PRIMARY INDEX ( STMTNR )
	ON COMMIT PRESERVE ROWS;';

	SET SepStartTS = CURRENT_TIMESTAMP;

	CALL DBC.SYSEXECSQL(MySQLQuery);

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'1020 VT INSERTITUDE CREATE',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;

	SET VTCOUNTER=VTCOUNTER+1;
		
	INSERT INTO MYCLEANUP (THENUMBER, THESTATEMENT) VALUES (VTCOUNTER, 'DROP TABLE INSERTITUDE;');


	
	IF TDVersion <= 13 THEN  
		
		/*****************************************************************************
		* STEP 1030 COMPRESSION CANDIDATES - PARTITION EXPRESSION RETENTION (VS 13)
		*****************************************************************************/
		
		SET TheStep='1030';
		
		SELECT COALESCE(ConstraintText,'UNPARTITIONED!') INTO PARTITION_EXP
		FROM DBC.IndexConstraints
		WHERE ConstraintType = 'Q'
		AND DATABASENAME = ETLDB_IN
		and TABLENAME = TABLENAME_IN
		;
		
		SET SepStartTS = CURRENT_TIMESTAMP;

		SET MySQLQuery=COALESCE(PARTITION_EXP,'NO EXP');
		
		SET EndTS = CURRENT_TIMESTAMP;
		SET MySQLState = SQLSTATE;
		SET MySQLCode = SQLCODE;
		SET ReportingActivityCount=ACTIVITY_COUNT;

		INSERT INTO G00_SP_LOG_TEMP 
		   (
		TARGET_TABLE,
		STEP_NUM,
		STEP_DESC,
		TM_START,
		STEP_START,
		SQL_REQUEST_TEXT,
		SQL_STATE,
		SQL_CODE,
		NM_ACTIVITY_COUNT,
		STEP_END,
		LOAD_ID
		   )
		VALUES
		   (
			TABLENAME_IN,
			:MyStepNum,
			'1030 PARTITION_EXP Retention',
			CURRENT_TIMESTAMP,
			:SepStartTS,
			:MySQLQuery,
			:MySQLState,
			:MySQLCode,
			:ReportingActivityCount,
			:EndTS,
			LOAD_ID_IN
		   );
		  
		SET MyStepNum = MyStepNum + 100;

		
		
		/*****************************************************************************
		* STEP 1040 PARTITIONED COLUMNS LIST CREATION (VS13) - 1. VT PARTCHECK Creation
		*****************************************************************************/
		
		SET TheStep='1040';
		
		SET MySQLQuery = '
		CREATE VOLATILE SET TABLE PARTCHECK ,NO FALLBACK ,
		NO BEFORE JOURNAL,
		NO AFTER JOURNAL,
		CHECKSUM = DEFAULT
		(
		COLUMNNAME VARCHAR(30) NOT NULL,
		PARTITION_STMT  VARCHAR(16000) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
		PARTITIONED_BY SMALLINT NOT NULL  )
		PRIMARY INDEX ( COLUMNNAME )
		ON COMMIT PRESERVE ROWS;';

		SET SepStartTS = CURRENT_TIMESTAMP;

		CALL DBC.SYSEXECSQL(MySQLQuery);

		SET EndTS = CURRENT_TIMESTAMP;
		SET MySQLState = SQLSTATE;
		SET MySQLCode = SQLCODE;
		SET ReportingActivityCount=ACTIVITY_COUNT;

		INSERT INTO G00_SP_LOG_TEMP 
		   (
		TARGET_TABLE,
		STEP_NUM,
		STEP_DESC,
		TM_START,
		STEP_START,
		SQL_REQUEST_TEXT,
		SQL_STATE,
		SQL_CODE,
		NM_ACTIVITY_COUNT,
		STEP_END,
		LOAD_ID
		   )
		VALUES
		   (
			TABLENAME_IN,
			:MyStepNum,
			'1040 VT PARTCHECK CREATE',
			CURRENT_TIMESTAMP,
			:SepStartTS,
			:MySQLQuery,
			:MySQLState,
			:MySQLCode,
			:ReportingActivityCount,
			:EndTS,
			LOAD_ID_IN
		   );
	  
		SET MyStepNum = MyStepNum + 100;

		SET VTCOUNTER=VTCOUNTER+1;
		
		INSERT INTO MYCLEANUP (THENUMBER, THESTATEMENT) VALUES (VTCOUNTER, 'DROP TABLE PARTCHECK;');

	
		
		/*****************************************************************************
		* STEP 1050 PARTITIONED COLUMNS LIST CREATION (VS13) - 2. VT PARTCHECK Fill
		*****************************************************************************/
		
		SET TheStep='1050';
		
		SET MySQLQuery = '
		INSERT INTO PARTCHECK
		SELECT COLUMNNAME,
		'''||COALESCE(PARTITION_EXP,'''''')||''',
		0 
		FROM DBC.COLUMNS 
		WHERE DATABASENAME= '''||ETLDB_IN ||'''
		AND TABLENAME= '''||TABLENAME_IN ||'''
		;';
		
		SET SepStartTS = CURRENT_TIMESTAMP;

		CALL DBC.SYSEXECSQL(MySQLQuery);

		SET EndTS = CURRENT_TIMESTAMP;
		SET MySQLState = SQLSTATE;
		SET MySQLCode = SQLCODE;
		SET ReportingActivityCount=ACTIVITY_COUNT;

		INSERT INTO G00_SP_LOG_TEMP 
		   (
		TARGET_TABLE,
		STEP_NUM,
		STEP_DESC,
		TM_START,
		STEP_START,
		SQL_REQUEST_TEXT,
		SQL_STATE,
		SQL_CODE,
		NM_ACTIVITY_COUNT,
		STEP_END,
		LOAD_ID
		   )
		VALUES
		   (
			TABLENAME_IN,
			:MyStepNum,
			'1050 VT PARTCHECK Fill',
			CURRENT_TIMESTAMP,
			:SepStartTS,
			:MySQLQuery,
			:MySQLState,
			:MySQLCode,
			:ReportingActivityCount,
			:EndTS,
			LOAD_ID_IN
		   );
	  
		SET MyStepNum = MyStepNum + 100;
		
		
		
		/*****************************************************************************
		* STEP 1060 PARTITIONED COLUMNS LIST CREATION (VS13) - 3. VT PARTCHECK Update
		*****************************************************************************/
		
		SET TheStep='1060';
		
		SET MySQLQuery = '
		UPDATE PARTCHECK SET PARTITIONED_BY=1 WHERE POSITION(COLUMNNAME IN PARTITION_STMT) >0;
		';

		SET SepStartTS = CURRENT_TIMESTAMP;

		CALL DBC.SYSEXECSQL(MySQLQuery);

		SET EndTS = CURRENT_TIMESTAMP;
		SET MySQLState = SQLSTATE;
		SET MySQLCode = SQLCODE;
		SET ReportingActivityCount=ACTIVITY_COUNT;

		INSERT INTO G00_SP_LOG_TEMP 
		   (
		TARGET_TABLE,
		STEP_NUM,
		STEP_DESC,
		TM_START,
		STEP_START,
		SQL_REQUEST_TEXT,
		SQL_STATE,
		SQL_CODE,
		NM_ACTIVITY_COUNT,
		STEP_END,
		LOAD_ID
		   )
		VALUES
		   (
			TABLENAME_IN,
			:MyStepNum,
			'1060 VT PARTCHECK Update',
			CURRENT_TIMESTAMP,
			:SepStartTS,
			:MySQLQuery,
			:MySQLState,
			:MySQLCode,
			:ReportingActivityCount,
			:EndTS,
			LOAD_ID_IN
		   );
	  
		SET MyStepNum = MyStepNum + 100;
		
		
		
		/*****************************************************************************
		* STEP 1070 PARTITIONED COLUMNS LIST CREATION (VS13) - 4. PARTCOL_LIST Creation
		*****************************************************************************/
		
		SET TheStep='1070';
		
		SET n=0;
		SET c='';
		SET PART_COL_LIST='';
		SET SepStartTS = CURRENT_TIMESTAMP;
		
		SET lvl=0;
		ThePartColsFinder:
		LOOP
		SET lvl=lvl+1;
		SELECT  ''''||TRIM(COLUMNNAME)||'''' INTO c
		FROM 
		(Sel COLUMNNAME, RANK() OVER(PARTITION BY 1 ORDER BY COLUMNNAME) AS THERANK FROM PARTCHECK WHERE PARTITIONED_BY=1 ) P
		WHERE THERANK=lvl ;
		
		IF CHARACTER_LENGTH(c) >2 THEN
		SET PART_COL_LIST = PART_COL_LIST||( CASE WHEN  lvl=1 THEN c ELSE ','||c END ) ;
		END IF;
		
		SET c='';
		
		IF lvl >= COLCOUNTER THEN
		LEAVE ThePartColsFinder;
		END IF;
		END LOOP ThePartColsFinder;
		
		SET MySQLQuery=COALESCE(PART_COL_LIST,'NOT PARTITIONED!');
		
		IF PART_COL_LIST='' THEN
		SET PART_COL_LIST='''AdamBertilCesar''';
		END IF;
		
		SET EndTS = CURRENT_TIMESTAMP;
		SET MySQLState = SQLSTATE;
		SET MySQLCode = SQLCODE;
		SET ReportingActivityCount=ACTIVITY_COUNT;

		INSERT INTO G00_SP_LOG_TEMP 
		   (
		TARGET_TABLE,
		STEP_NUM,
		STEP_DESC,
		TM_START,
		STEP_START,
		SQL_REQUEST_TEXT,
		SQL_STATE,
		SQL_CODE,
		NM_ACTIVITY_COUNT,
		STEP_END,
		LOAD_ID
		   )
		VALUES
		   (
			TABLENAME_IN,
			:MyStepNum,
			'1070 PART BY:'||PART_COL_LIST,
			CURRENT_TIMESTAMP,
			:SepStartTS,
			:MySQLQuery,
			:MySQLState,
			:MySQLCode,
			:ReportingActivityCount,
			:EndTS,
			LOAD_ID_IN
		   );
		  
		SET MyStepNum = MyStepNum + 100;

	END IF;	

	
		
	/*************************************************************************************************
	* STEP 1080 COMPRESSION CANDIDATES - EXCLUDE COLUMNS NAMED LIKE RESTRICTED WORDS: 1. LIST BUILDUP 
	**************************************************************************************************/
	
	SET TheStep='1080';
	
	SET lvl=0;
	SET RW_COL_LIST ='';
	SET c='';
	
	TheRWColsFinder:
	LOOP
		SET lvl=lvl+1;
	
		SELECT TRIM(COLUMNNAME) INTO c
		FROM (
		SELECT C.COLUMNNAME, RANK() OVER (PARTITION BY C.DATABASENAME, C.TABLENAME ORDER BY C.COLUMNID ) AS THERANK
		FROM
		( SELECT restricted_word FROM TABLE (SYSLIB.SQLRestrictedWords_TBF()) AS t1
				  WHERE CAST( SUBSTR(release_introduced,1,2)  AS INTEGER) <= trim(TDVersion) AND
				  (  CAST(SUBSTR(release_dropped,1,2) AS INTEGER)  > trim(TDVersion) OR release_dropped IS NULL) ) RW
		INNER JOIN
		DBC.COLUMNS C
		ON RW.restricted_word = C.COLUMNNAME
		AND C.DATABASENAME= ETLDB_IN
		AND C.TABLENAME= TABLENAME_IN
		) C
		WHERE THERANK=lvl;
		
		IF c ne '' THEN
		SET RW_COL_LIST = RW_COL_LIST||( CASE WHEN  POSITION(',' IN RW_COL_LIST)=0 THEN '"'||coalesce(c,'')||'"' ELSE ','|| '"'||coalesce(c,'')||'"' END ) ;
		END IF;
		
		SET c='';
		
		IF lvl >= COLCOUNTER THEN
		LEAVE TheRWColsFinder;
		END IF;
	END LOOP TheRWColsFinder;

	SET MySQLQuery=COALESCE(RW_COL_LIST,'NO COLUMNS NAMED LIKE RESERVED WORDS');
	
	IF RW_COL_LIST ='' THEN 
	SET RW_COL_LIST='''AdamBertilCesar''';
	END IF;
	
	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'1080 EXCLUDED:'||RW_COL_LIST,
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;

	
	
	/*****************************************************************************
	* STEP 1090 COMPRESSION CANDIDATES - VT FOR INSERTITUDE FILL
	*****************************************************************************/

	SET TheStep='1090';

	IF TDVersion <= 13 THEN 
	
		SET PART_COL_LIST= ' COLUMNNAME IN ( '||COALESCE(PART_COL_LIST,'AdamBertilCesar')|| ')';
		SET RW_COL_LIST= ' COLUMNNAME IN ( '||RW_COL_LIST||')';
		
		INSERT INTO INSERTITUDE 
		SELECT 
		ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY 1) AS STMTNR,
		 trim(columnName),
		 'INSERT INTO VT_COMP_CAND SELECT ''' 
		|| trim(databasename) ||''',  '''
		|| trim(tablename) ||''','''
		|| trim(columnName) || ''','''
		|| trim(ColumnType) || ''','
		|| CASE WHEN Nullable='Y' THEN 1 ELSE 0 END ||','
		|| trim(CASE 
		 WHEN ColumnType NOT IN ('CF','CV') THEN trim(columnLength)
		 WHEN ColumnType IN ('CF') THEN trim(columnLength*CharType)
		 WHEN ColumnType IN ('CV') THEN  'COALESCE(CHARACTER_LENGTH('|| trim(columnName) ||')*'||trim(CharType)||','||trim(columnLength*CharType)||' )'  END) || ','
		 || trim(columnName) 
		 || ',CAST( (DIEANZAHL) AS BIGINT)  AS COL_FREQUENCY,
		 RANK() OVER (PARTITION BY 1 ORDER BY DIEANZAHL DESC , '|| trim(columnName) ||' ) AS COL_VAL_RANK,
		 0 AS CURRENT_COMPRESSION 
		 FROM
		 ( 
		 SELECT ' ||trim(columnName)|| ',
		 CAST(COUNT(*) AS DECIMAL(38,2))  AS DIEANZAHL 
		 FROM ' || trim(databasename) ||'.'|| trim(tablename) ||' 
		 GROUP BY 1
		'||CASE WHEN  ColumnType IN ('CF','CV') THEN  
		 'HAVING TRANSLATE_CHK('||trim(columnName) ||'  USING UNICODE_TO_LATIN)  = 0 
			AND POSITION(''?'' IN '||trim(columnName)||') =0 
			AND POSITION(''´'' IN '||trim(columnName)||') =0 
			AND POSITION(''`'' IN '||trim(columnName)||') =0 
			AND POSITION(''+'' IN '||trim(columnName)||') =0 
			AND POSITION(''('' IN '||trim(columnName)||') =0
			AND POSITION('')'' IN '||trim(columnName)||') =0
			AND POSITION('''''''' IN '||trim(columnName)||') =0 '
		 ELSE '' END ||' ) B
		 QUALIFY RANK() OVER (PARTITION BY 1 ORDER BY COL_FREQUENCY DESC , ' ||trim(columnName)|| ' )<=255
		;'  AS COMPRESSION_VALUES
		from dbc.columns
		where databasename=ETLDB_IN
		and columnname NOT IN ('LOAD_ID','CALENDAR_DT','START_DT','END_DT')
		AND TABLENAME = TABLENAME_IN
		and ColumnType NOT IN ('BO','CO','PD','PM','PS','PT','PZ','UT','SZ','TS')
		AND ColumnLength <=1000
		AND (databasename, TABLENAME, columnname) NOT IN 
		(SELECT DatabaseName, TableName, ColumnName FROM DBC.Indices where databasename=ETLDB_IN AND TABLENAME = TABLENAME_IN )
		AND (  '"'||TRIM(columnname)||'"' ) NOT IN 
		(
		SELECT '"'||TRIM(COLUMNNAME)||'"' as COLUMNNAME
		FROM (
		SELECT C.COLUMNNAME, RANK() OVER (PARTITION BY C.DATABASENAME, C.TABLENAME ORDER BY C.COLUMNID ) AS THERANK
		FROM
		( SELECT restricted_word FROM TABLE (SYSLIB.SQLRestrictedWords_TBF()) AS t1
				  WHERE CAST( SUBSTR(release_introduced,1,2)  AS INTEGER) <= trim(TDVersion) AND
				  (  CAST(SUBSTR(release_dropped,1,2) AS INTEGER)  > trim(TDVersion) OR release_dropped IS NULL) ) RW
		INNER JOIN
		DBC.COLUMNS C
		ON RW.restricted_word = C.COLUMNNAME
		AND C.DATABASENAME= ETLDB_IN
		AND C.TABLENAME= TABLENAME_IN
		) C
		WHERE THERANK>0
		 )
		;
		
	ELSE
	
		SET PART_TD14_COND=' AND PartitioningColumn=''N'' ';
	
		SET RW_COL_LIST= ' COLUMNNAME IN ( '||COALESCE(RW_COL_LIST,'AdamBertilCesar')|| ')';
	
		INSERT INTO INSERTITUDE 
		SELECT 
		ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY 1) AS STMTNR,
		trim(columnName),
		 'INSERT INTO VT_COMP_CAND SELECT ''' 
		|| trim(databasename) ||''',  '''
		|| trim(tablename) ||''','''
		|| trim(columnName) || ''','''
		|| trim(ColumnType) || ''','
		|| CASE WHEN Nullable='Y' THEN 1 ELSE 0 END ||','
		|| trim(CASE 
		 WHEN ColumnType NOT IN ('CF','CV') THEN trim(columnLength)
		 WHEN ColumnType IN ('CF') THEN trim(columnLength*CharType)
		 WHEN ColumnType IN ('CV') THEN  'COALESCE(CHARACTER_LENGTH('|| trim(columnName) ||')*'||trim(CharType)||','||trim(columnLength*CharType)||' )'  END) || ','
		 || trim(columnName) 
		 || ',CAST( (DIEANZAHL) AS BIGINT)  AS COL_FREQUENCY,
		 RANK() OVER (PARTITION BY 1 ORDER BY DIEANZAHL DESC , '|| trim(columnName) ||' ) AS COL_VAL_RANK,
		 0 AS CURRENT_COMPRESSION 
		 FROM
		 ( 
		 SELECT ' ||trim(columnName)|| ',
		 CAST(COUNT(*) AS DECIMAL(38,2))  AS DIEANZAHL 
		 FROM ' || trim(databasename) ||'.'|| trim(tablename) ||' 
		 GROUP BY 1
		'||CASE WHEN  ColumnType IN ('CF','CV') THEN  
		 'HAVING TRANSLATE_CHK('||trim(columnName) ||'  USING UNICODE_TO_LATIN)  = 0 
			AND POSITION(''?'' IN '||trim(columnName)||') =0 
			AND POSITION(''´'' IN '||trim(columnName)||') =0 
			AND POSITION(''`'' IN '||trim(columnName)||') =0 
			AND POSITION(''+'' IN '||trim(columnName)||') =0
			AND POSITION(''('' IN '||trim(columnName)||') =0
			AND POSITION('')'' IN '||trim(columnName)||') =0
			AND POSITION('''''''' IN '||trim(columnName)||') =0 '
		 ELSE '' END ||' ) B
		 QUALIFY RANK() OVER (PARTITION BY 1 ORDER BY COL_FREQUENCY DESC , ' ||trim(columnName)|| ' )<=255
		;'  AS COMPRESSION_VALUES
		from dbc.columnsVX
		where databasename=ETLDB_IN
		and columnname NOT IN ('LOAD_ID','CALENDAR_DT','START_DT','END_DT')
		AND TABLENAME = TABLENAME_IN
		and ColumnType NOT IN ('BO','CO','PD','PM','PS','PT','PZ','UT')
		AND ColumnLength <=1000
		AND (databasename, TABLENAME, columnname) NOT IN 
		(SELECT DatabaseName, TableName, ColumnName FROM DBC.Indices where databasename=ETLDB_IN AND TABLENAME = TABLENAME_IN)
		AND (  '"'||TRIM(columnname)||'"' ) NOT IN 
		(
		SELECT '"'||TRIM(COLUMNNAME)||'"' as COLUMNNAME
		FROM (
		SELECT C.COLUMNNAME, RANK() OVER (PARTITION BY C.DATABASENAME, C.TABLENAME ORDER BY C.COLUMNID ) AS THERANK
		FROM
		( SELECT restricted_word FROM TABLE (SYSLIB.SQLRestrictedWords_TBF()) AS t1
				  WHERE CAST( SUBSTR(release_introduced,1,2)  AS INTEGER) <= trim(TDVersion) AND
				  (  CAST(SUBSTR(release_dropped,1,2) AS INTEGER)  > trim(TDVersion) OR release_dropped IS NULL) ) RW
		INNER JOIN
		DBC.COLUMNS C
		ON RW.restricted_word = C.COLUMNNAME
		AND C.DATABASENAME= ETLDB_IN
		AND C.TABLENAME= TABLENAME_IN
		) C
		WHERE THERANK>0
		 )
		;
		
		SET MySQLQuery='
		DELETE FROM INSERTITUDE 
		WHERE COLUMNNAME IN (
		SELECT COLUMNNAME from dbc.columnsVX WHERE databasename= '''||ETLDB_IN||''' and TABLENAME = '''||TABLENAME_IN||''' and PartitioningColumn <> ''N'' )
		;';
		
		CALL DBC.SYSEXECSQL(MySQLQuery);
		
	END IF;
	
	SET SepStartTS = CURRENT_TIMESTAMP;

	SET MySQLQuery=trim(TDVersion)||','||PART_COL_LIST;
	
	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'1090 VT INSERTITUDE FILL',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;


	
	/*****************************************************************************
	* STEP 1100 COMPRESSION CANDIDATES - VT_COMP_CAND FILL
	*****************************************************************************/
	 
	SET TheStep='1100';

	/* Fill VT_COMP_CAND FILL with the up to 255 compression value candidates, for every column that is itself a candidate for compression */
	 
	SET n=0;
	SET MySQLQuery='';

	SET SepStartTS = CURRENT_TIMESTAMP;

	TheBlobLoop:
	LOOP
		SET n =n +1;
		IF n>255 THEN
		LEAVE TheBlobLoop;
		END IF;
		SELECT THEBLOB INTO MySQLQuery FROM INSERTITUDE WHERE STMTNR= n ;
		CALL DBC.SYSEXECSQL(MySQLQuery);
		SET MySQLQuery='';
	END LOOP TheBlobLoop;

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'1100 VT_COMP_CAND FILL'||n,
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;


	
	/*****************************************************************************
	* STEP 1110 VT_COMP_CAND "GRAVEL FILTER" 1. discard all candidates initially
	*****************************************************************************/
	 
	/* 
	Especially relevant to avoid bloating small tables: Avoid compressing on values that hardly occur. Also, do not compress excessivively on tiny tables.
	Operationalisation: delete from VT_COMP_CAND where AMPNR * LN(rowcount) > col_calc_Value * Col_frequency.
	LN() is used both to restrict compression for tables just above the technical minimum threshold, while being permissive for middle and large tables
	*/
	
	SET TheStep='1110';
	
	SET MySQLQuery='UPDATE VT_COMP_CAND SET COL_VAL_RANK = -1*COL_VAL_RANK WHERE COL_VAL_RANK>0 ;';
	
	SET SepStartTS = CURRENT_TIMESTAMP;
		
	CALL DBC.SYSEXECSQL(MySQLQuery);

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'1110 GRAVEL FILTER - discard all',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;
	
	
	
	/***********************************************************************************
	* STEP 1120 VT_COMP_CAND "GRAVEL FILTER" 2. reaccept all above the gravel threshold
	************************************************************************************/
	
	SET TheStep='1120';
	
	SET MySQLQuery='
	UPDATE VT_COMP_CAND
	FROM 
	(
	SELECT databasename, tablename, columnname, MAX(ABS(COL_VAL_RANK)) AS MX_RANK_FEASIBLE
	from VT_COMP_CAND  
	where COL_CALC_VALUE* COL_FREQUENCY >=  LN('||TRIM(TBL_ROWCOUNT)||')*'||TRIM(SYSAMPNR)||'
	GROUP BY 1,2,3
	) ZZ
	SET COL_VAL_RANK = -1*COL_VAL_RANK
	WHERE VT_COMP_CAND.databasename = ZZ.databasename
	AND VT_COMP_CAND.tablename = ZZ.tablename
	AND VT_COMP_CAND.columnname = ZZ.columnname
	AND ABS(VT_COMP_CAND.COL_VAL_RANK)<=MX_RANK_FEASIBLE
	;';

	SET SepStartTS = CURRENT_TIMESTAMP;
		
	CALL DBC.SYSEXECSQL(MySQLQuery);

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'1120 GRAVEL FILTER - reaccept above threshold',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;
	
	
	
	/***********************************************************************************
	* STEP 1130 VT_COMP_CAND "GRAVEL FILTER" 3. remove those below threshold for good
	************************************************************************************/
	
	SET TheStep='1130';
	
	SET MySQLQuery='DELETE FROM VT_COMP_CAND WHERE COL_VAL_RANK<= 0 ;';

	SET SepStartTS = CURRENT_TIMESTAMP;
		
	CALL DBC.SYSEXECSQL(MySQLQuery);

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'1130 GRAVEL FILTER - remove below threshold',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;
	
	
	
	/*****************************************************************************
	 *****************************************************************************
	 * * * * * * * * * * * * * * * *  CHAPTER 2  * * * * * * * * * * * * * * * * *  
	 * * * * * * * * * * *   CURRENT COMPRESSION ANALYSIS  * * * * * * * * * * * * 
	 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 
	 *****************************************************************************
	*****************************************************************************/

	SET TheStep='2010';

	IF TDVersion <=13 THEN 
	
	SELECT COALESCE(SUM(CASE WHEN CompressValueList IS NOT NULL then 1 ELSE 0 END),0) INTO CURR_COMP_FINNS
	FROM dbc.columns
	WHERE databasename=ETLDB_IN
	AND columnname NOT IN ('LOAD_ID','CALENDAR_DT','START_DT','END_DT')
	AND TABLENAME = TABLENAME_IN
	AND ColumnType NOT IN ('BO','CO','PD','PM','PS','PT','PZ','UT','SZ','TS')
	AND (databasename, TABLENAME, columnname) NOT IN 
	(SELECT DatabaseName, TableName, ColumnName FROM DBC.Indices )
	;
	
	ELSE
	
	SELECT COALESCE(SUM(CASE WHEN CompressValueList IS NOT NULL then 1 ELSE 0 END),0) INTO CURR_COMP_FINNS
	FROM dbc.columns
	WHERE databasename=ETLDB_IN
	AND columnname NOT IN ('LOAD_ID','CALENDAR_DT','START_DT','END_DT')
	AND TABLENAME = TABLENAME_IN
	AND ColumnType NOT IN ('BO','CO','PD','PM','PS','PT','PZ','UT')
	AND (databasename, TABLENAME, columnname) NOT IN 
	(SELECT DatabaseName, TableName, ColumnName FROM DBC.Indices )
	;
	
	END IF;
	
	SET SepStartTS = CURRENT_TIMESTAMP;

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'2010 CURRENTLY '||trim(CURR_COMP_FINNS)||' COLS COMPR!',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;



	/*********************************************************************************
	* STEP 2020 CURRENT COMPRESSION DETERMINATION: VT_COMP_VALUES_IS CREATION
	**********************************************************************************/

	/* VT for stepwise unwinding of CompressValueList into separate values */

	SET TheStep='2020';

	SET MySQLQuery='
	CREATE VOLATILE SET TABLE VT_COMP_VALUES_IS ,NO FALLBACK ,
		 NO BEFORE JOURNAL,
		 NO AFTER JOURNAL,
		 CHECKSUM = DEFAULT
		 (
		  DatabaseName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
		  TableName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
		  ColumnName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
		  COMP_REMAINS_LIST  VARCHAR(8192) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
		  COMPRESSED_VALUE VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
		  COMPRESSION_ORDER_NR INTEGER NOT NULL )
	PRIMARY INDEX ( DatabaseName, TableName, ColumnName, COMPRESSION_ORDER_NR )
	ON COMMIT PRESERVE ROWS;';


	SET SepStartTS = CURRENT_TIMESTAMP;
		
	CALL DBC.SYSEXECSQL(MySQLQuery);

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'2020 VT_COMP_VALUES_IS CREATION',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;

	SET VTCOUNTER=VTCOUNTER+1;
		
	INSERT INTO MYCLEANUP (THENUMBER, THESTATEMENT) VALUES (VTCOUNTER, 'DROP TABLE VT_COMP_VALUES_IS;');

	

	IF CURR_COMP_FINNS >0 THEN 

		/*********************************************************************************
		* STEP 2030 CURRENT COMPRESSION DETERMINATION:  VT Fill Sequence: Seed level 1
		**********************************************************************************/

		/*  
		Seed level with rank 1 = the full CompressionValueList string net of brackets, 
		given at least two values are in the list (as identified by the presence of a comma in the string)
		*/

		SET TheStep='2030';

		SET MySQLQuery='
		INSERT INTO VT_COMP_VALUES_IS 
		SELECT DatabaseName, TableName, ColumnName, SUBSTR(trim(CompressValueList),2,CHARACTER_LENGTH(trim(CompressValueList))-2), 
		SUBSTR( CompressValueList,2, 
		INDEX(SUBSTR(trim(CompressValueList),2,CHARACTER_LENGTH(trim(CompressValueList))-2) ,'','') -1 ) AS COMPRESSED_VALUE ,
		1
		FROM dbc.columns
		where CompressValueList is not null 
		AND CompressValueList LIKE ''%,%''
		and (tablename, columnname) IN 
		( SELECT DISTINCT tablename, columnname FROM VT_COMP_CAND )
		;';

		SET SepStartTS = CURRENT_TIMESTAMP;
			
		CALL DBC.SYSEXECSQL(MySQLQuery);

		SET EndTS = CURRENT_TIMESTAMP;
		SET MySQLState = SQLSTATE;
		SET MySQLCode = SQLCODE;
		SET ReportingActivityCount=ACTIVITY_COUNT;

		INSERT INTO G00_SP_LOG_TEMP 
		   (
		TARGET_TABLE,
		STEP_NUM,
		STEP_DESC,
		TM_START,
		STEP_START,
		SQL_REQUEST_TEXT,
		SQL_STATE,
		SQL_CODE,
		NM_ACTIVITY_COUNT,
		STEP_END,
		LOAD_ID
		   )
		VALUES
		   (
			TABLENAME_IN,
			:MyStepNum,
			'2030 CURR COMP DET: VT Seed Level 1',
			CURRENT_TIMESTAMP,
			:SepStartTS,
			:MySQLQuery,
			:MySQLState,
			:MySQLCode,
			:ReportingActivityCount,
			:EndTS,
			LOAD_ID_IN
		   );
		  
		SET MyStepNum = MyStepNum + 100;



		/************************************************************************************
		* STEP 2040 CURRENT COMPRESSION DETERMINATION: VT Fill Sequence: All Levels 2 to 255
		*************************************************************************************/

		SET TheStep='2040';

		/* Per Cursor all levels 2 to 255 */

		SET SepStartTS = CURRENT_TIMESTAMP;

		SET MySQLQuery='';

		SET lvl=1;

		levelsupto255:
		LOOP
		set lvl=lvl+1;
		INSERT INTO VT_COMP_VALUES_IS 
		SELECT DatabaseName, TableName, ColumnName, 
		CASE 
		WHEN INDEX(SUBSTR(trim(COMP_REMAINS_LIST),2),',')  >0 THEN
		SUBSTR( COMP_REMAINS_LIST,
		INDEX(SUBSTR(trim(COMP_REMAINS_LIST),2,CHARACTER_LENGTH(trim(COMP_REMAINS_LIST)) ) ,',') +2 ) 
		ELSE 'END OF LIST REACHED!' END AS COMP_REMAINS_LIST ,
		NULL AS COMPRESSED_VALUE , 
		COMPRESSION_ORDER_NR+1 AS COMPRESSION_ORDER_NR
		FROM VT_COMP_VALUES_IS
		where  COMPRESSION_ORDER_NR= lvl
		AND ( tablename, columnname) IN 
		(select distinct tablename, columnname FROM VT_COMP_CAND )
		; 
		IF lvl = 255 THEN
		LEAVE levelsupto255;
		END IF;
		END LOOP levelsupto255;

		SET EndTS = CURRENT_TIMESTAMP;
		SET MySQLState = SQLSTATE;
		SET MySQLCode = SQLCODE;
		SET ReportingActivityCount=ACTIVITY_COUNT;

		INSERT INTO G00_SP_LOG_TEMP 
		   (
		TARGET_TABLE,
		STEP_NUM,
		STEP_DESC,
		TM_START,
		STEP_START,
		SQL_REQUEST_TEXT,
		SQL_STATE,
		SQL_CODE,
		NM_ACTIVITY_COUNT,
		STEP_END,
		LOAD_ID
		   )
		VALUES
		   (
			TABLENAME_IN,
			:MyStepNum,
			'2040 CURR COMP DET: VT levels 2-255',
			CURRENT_TIMESTAMP,
			:SepStartTS,
			:MySQLQuery,
			:MySQLState,
			:MySQLCode,
			:ReportingActivityCount,
			:EndTS,
			LOAD_ID_IN
		   );
		  
		SET MyStepNum = MyStepNum + 100;



		/*************************************************************************************************
		* STEP 2050 CURRENT COMPRESSION DETERMINATION: VT Fill with Column Compressions of One Value only 
		**************************************************************************************************/

		/* 
		Insert all remaining compressions not covered yet in VT_COMP_VALUES_IS: 
		those with only one Value. Done AFTER having handled the multi-compression cases 
		*/

		SET TheStep='2050';

		SET MySQLQuery='
		INSERT INTO VT_COMP_VALUES_IS 
		SELECT DatabaseName, TableName, ColumnName, 
		SUBSTR(trim(CompressValueList),2,CHARACTER_LENGTH(trim(CompressValueList))-2) AS COMP_REMAINS_LIST, 
		SUBSTR(trim(CompressValueList),2,CHARACTER_LENGTH(trim(CompressValueList))-2) AS COMPRESSED_VALUE, 
		1
		FROM dbc.columns
		where CompressValueList is not null 
		AND CompressValueList NOT LIKE ''%,%''
		AND ( tablename, columnname) IN 
		(select distinct tablename, columnname FROM VT_COMP_CAND )
		AND ( tablename, columnname) NOT IN
		(select distinct tablename, columnname FROM VT_COMP_VALUES_IS )
		;';

		SET SepStartTS = CURRENT_TIMESTAMP;
			
		CALL DBC.SYSEXECSQL(MySQLQuery);

		SET EndTS = CURRENT_TIMESTAMP;
		SET MySQLState = SQLSTATE;
		SET MySQLCode = SQLCODE;
		SET ReportingActivityCount=ACTIVITY_COUNT;

		INSERT INTO G00_SP_LOG_TEMP 
		   (
		TARGET_TABLE,
		STEP_NUM,
		STEP_DESC,
		TM_START,
		STEP_START,
		SQL_REQUEST_TEXT,
		SQL_STATE,
		SQL_CODE,
		NM_ACTIVITY_COUNT,
		STEP_END,
		LOAD_ID
		   )
		VALUES
		   (
			TABLENAME_IN,
			:MyStepNum,
			'2050 CURR COMP DET: VT Seed Level 1',
			CURRENT_TIMESTAMP,
			:SepStartTS,
			:MySQLQuery,
			:MySQLState,
			:MySQLCode,
			:ReportingActivityCount,
			:EndTS,
			LOAD_ID_IN
		   );
		  
		SET MyStepNum = MyStepNum + 100;



		/********************************************************************************************************************
		* STEP 2060 CURRENT COMPRESSION DETERMINATION: Derive Compression Value per Rank in VT_COMP_VALUES_IS
		*********************************************************************************************************************/

		SET TheStep='2060';

		SET MySQLQuery='
		UPDATE VT_COMP_VALUES_IS FROM
		(
		SELECT V1.*,
		CASE WHEN INDEX(V1.COMP_REMAINS_LIST,'','') >0 THEN
		SUBSTR(V1.COMP_REMAINS_LIST,1, 
		( CHARACTER_LENGTH(TRIM(V2.COMP_REMAINS_LIST)) - CHARACTER_LENGTH(TRIM(V1.COMP_REMAINS_LIST)) -1 ) )
		ELSE V1.COMP_REMAINS_LIST END AS COMP_VAL_DERIV
		FROM VT_COMP_VALUES_IS V1
		LEFT OUTER JOIN 
		( select * from VT_COMP_VALUES_IS WHERE COMP_REMAINS_LIST NE ''END OF LIST REACHED!'' )V2
		ON V1.DatabaseName = V2.DatabaseName 
		AND  V1.tablename = V2.tablename 
		AND  V1.columnname = V2.columnname 
		AND V1.COMPRESSION_ORDER_NR = V2.COMPRESSION_ORDER_NR+1
		where (V1. tablename,V1.columnname) IN
		(select distinct tablename, columnname FROM VT_COMP_CAND )
		AND V1.COMP_REMAINS_LIST NE ''END OF LIST REACHED!''
		AND COMP_VAL_DERIV IS NOT NULL
		) ZZ
		  SET COMPRESSED_VALUE= ZZ.COMP_VAL_DERIV
		WHERE VT_COMP_VALUES_IS.DatabaseName = ZZ.DatabaseName
		  AND VT_COMP_VALUES_IS.tablename = ZZ.tablename
		  AND VT_COMP_VALUES_IS.columnname = ZZ.columnname
		  AND VT_COMP_VALUES_IS.COMPRESSION_ORDER_NR = ZZ.COMPRESSION_ORDER_NR
		;';

		SET SepStartTS = CURRENT_TIMESTAMP;
			
		CALL DBC.SYSEXECSQL(MySQLQuery);

		SET EndTS = CURRENT_TIMESTAMP;
		SET MySQLState = SQLSTATE;
		SET MySQLCode = SQLCODE;
		SET ReportingActivityCount=ACTIVITY_COUNT;

		INSERT INTO G00_SP_LOG_TEMP 
		   (
		TARGET_TABLE,
		STEP_NUM,
		STEP_DESC,
		TM_START,
		STEP_START,
		SQL_REQUEST_TEXT,
		SQL_STATE,
		SQL_CODE,
		NM_ACTIVITY_COUNT,
		STEP_END,
		LOAD_ID
		   )
		VALUES
		   (
			TABLENAME_IN,
			:MyStepNum,
			'2060 CURR COMP DET: derive',
			CURRENT_TIMESTAMP,
			:SepStartTS,
			:MySQLQuery,
			:MySQLState,
			:MySQLCode,
			:ReportingActivityCount,
			:EndTS,
			LOAD_ID_IN
		   );
		  
		SET MyStepNum = MyStepNum + 100;



		/********************************************************************************************************************
		* STEP 2070 CURRENT COMPRESSION DETERMINATION: Update CURRENT_COMPRESSION Info in VT_COMP_CAND from VT_COMP_VALUES_IS
		*********************************************************************************************************************/

		/* Update the empirical rank of the compressed values for all of the VT_COMP_CAND values stored */

		SET TheStep='2070';

		SET MySQLQuery='
		UPDATE VT_COMP_CAND FROM
		(
		SELECT Databasename, Tablename, columnname, COMPRESSED_VALUE, COMPRESSION_ORDER_NR
		FROM VT_COMP_VALUES_IS 
		WHERE COMP_REMAINS_LIST NE ''END OF LIST REACHED!''
		AND COMPRESSION_ORDER_NR>0 
		) ZZ
		  SET CURRENT_COMPRESSION= ZZ.COMPRESSION_ORDER_NR
		  WHERE VT_COMP_CAND.DatabaseName = ZZ.DatabaseName
		  AND VT_COMP_CAND.tablename = ZZ.tablename
		  AND VT_COMP_CAND.columnname = ZZ.columnname
		  AND 
		  (CASE 
		  WHEN VT_COMP_CAND.COL_DATATYPE IN (''CV'',''CF'') 
		  THEN ''''||trim(VT_COMP_CAND.col_value)||''''
		  ELSE trim(VT_COMP_CAND.col_value) END ) = TRIM(ZZ.COMPRESSED_VALUE)
		;';


		SET SepStartTS = CURRENT_TIMESTAMP;
			
		CALL DBC.SYSEXECSQL(MySQLQuery);

		SET EndTS = CURRENT_TIMESTAMP;
		SET MySQLState = SQLSTATE;
		SET MySQLCode = SQLCODE;
		SET ReportingActivityCount=ACTIVITY_COUNT;

		INSERT INTO G00_SP_LOG_TEMP 
		   (
		TARGET_TABLE,
		STEP_NUM,
		STEP_DESC,
		TM_START,
		STEP_START,
		SQL_REQUEST_TEXT,
		SQL_STATE,
		SQL_CODE,
		NM_ACTIVITY_COUNT,
		STEP_END,
		LOAD_ID
		   )
		VALUES
		   (
			TABLENAME_IN,
			:MyStepNum,
			'2070 CURR COMP DET: update',
			CURRENT_TIMESTAMP,
			:SepStartTS,
			:MySQLQuery,
			:MySQLState,
			:MySQLCode,
			:ReportingActivityCount,
			:EndTS,
			LOAD_ID_IN
		   );
		  
		SET MyStepNum = MyStepNum + 100;


	END IF;



	/*****************************************************************************
	 *****************************************************************************
	 * * * * * * * * * * * * * * * *  CHAPTER 3  * * * * * * * * * * * * * * * * *  
	 * * * * * * *    SPACE CONSUMPTION CALCULATION    * * * * * * * * * * * * * *
	 * * * uncompressed, compressed as is, and optimally compressed * * * * * * * * 
	 *****************************************************************************
	*****************************************************************************/



	/********************************************************************************************************************
	* STEP 3010 SPACE CONSUMPTION CALCULATION: TOTAL EXTRA BITS and BYTES NECESSARY FOR NULLABILITY COVERAGE (ALL COLUMNS!)
	*********************************************************************************************************************/

	SET TheStep='3010';

	-- NULLABILITY IN BITS

	SELECT COALESCE(COL_NULLABLE,0) INTO NULLABILITYBITS
	FROM 
	(
	select SUM(CASE WHEN NULLABLE = 'Y' THEN 1 ELSE 0 END ) AS COL_NULLABLE
	FROM dbc.columns
	WHERE databasename =ETLDB_IN
	and tablename = TABLENAME_IN
	) MNL ;

	-- NULLABILITY IN WHOLE BYTES 
	SELECT  CAST(COALESCE(COL_NULLABLE,0) AS INTEGER ) / 8 +1 INTO NULLABILITYBYTES  
	FROM 
	(
	select SUM(CASE WHEN NULLABLE = 'Y' THEN 1 ELSE 0 END ) AS COL_NULLABLE
	FROM dbc.columns
	WHERE databasename = ETLDB_IN
	and tablename = TABLENAME_IN
	) MNL ;
	  
	SET SepStartTS = CURRENT_TIMESTAMP;
		
	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'3010 NULL BITS:'||TRIM(NULLABILITYBITS)||',BYTES:'||TRIM(NULLABILITYBYTES),
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;



	/********************************************************************************************************************
	* STEP 3020 SPACE CONSUMPTION CALCULATION: CURRENT EXTRA BYTES FOR COMPRESSION AS IS, out of VT_COMP_VALUES_IS
	*********************************************************************************************************************/

	SET TheStep='3020';

	IF (SELECT COUNT(*) FROM VT_COMP_VALUES_IS ) >0 THEN 

		SELECT SUM(  CAST( CAST(MCC.NR_EMP_COMP  AS DECIMAL(8,2))/8 AS INTEGER) +1 )  INTO CURR_COMP_BYTES
		FROM 
		  ( select Databasename, Tablename, columnname, max(COMPRESSION_ORDER_NR) AS NR_EMP_COMP
		 FROM VT_COMP_VALUES_IS
		 WHERE COMP_REMAINS_LIST NE 'END OF LIST REACHED!'
		 AND COMPRESSION_ORDER_NR>0 
		 GROUP BY 1,2,3) MCC
		 ;
	 
	ELSE 

		SET CURR_COMP_BYTES = 0;

	END IF;

	SET SepStartTS = CURRENT_TIMESTAMP;
		
	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'3020 '||TRIM(CURR_COMP_BYTES)||' CURRENT EXTRA BYTES FOR COMPRESSION',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;



	/**********************************************************************************
	* STEP 3030 ROW-BYTE SPACE METRIC FOR ENTIRE UNCOMPRESSED TABLE: VT COLBYTES CREATE
	***********************************************************************************/

	SET TheStep='3030';

	SET MySQLQuery='
		CREATE VOLATILE TABLE VT_COLBYTES
			(
			COLORDER SMALLINT NOT NULL,
			COLVALUE DECIMAL(38,18)NOT NULL
			)
			PRIMARY INDEX (COLORDER)
			ON COMMIT PRESERVE ROWS;';
	
	SET SepStartTS = CURRENT_TIMESTAMP;
		
	CALL DBC.SYSEXECSQL(MySQLQuery);
	
	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'3030 VT COLBYTES CREATE',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;

	SET VTCOUNTER=VTCOUNTER+1;
		
	INSERT INTO MYCLEANUP (THENUMBER, THESTATEMENT) VALUES (VTCOUNTER, 'DROP TABLE VT_COLBYTES;');

	
		
	/*********************************************************************************************
	* STEP 3040 ROW-BYTE SPACE METRIC FOR ENTIRE UNCOMPRESSED TABLE: VT COLBYTES: Fixed Part 
	**********************************************************************************************/
	
	SET TheStep='3040';
	
	SET MySQLQuery='
	INSERT INTO VT_COLBYTES 
	SELECT 0, ( CAST( 16.0+'||TRIM(CASE WHEN NULLABILITYBYTES<=1 THEN 0 ELSE NULLABILITYBYTES-1 END)||'.0 AS DECIMAL(38,18) ) )*'||TRIM(TBL_ROWCOUNT)||';';
	
	SET SepStartTS = CURRENT_TIMESTAMP;
		
	CALL DBC.SYSEXECSQL(MySQLQuery);
	
	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'3040 VT COLBYTES Fixed Part',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;

	
	
	/*********************************************************************************************
	* STEP 3050 ROW-BYTE SPACE METRIC FOR ENTIRE UNCOMPRESSED TABLE: VT COLBYTES FILL from Cursor
	**********************************************************************************************/
	
	SET TheStep='3050';

	SET  sql_stmt2='
	SELECT 
	 ''INSERT INTO VT_COLBYTES SELECT ''||COLORDER||'', SUM(CAST( ''||COLVALUE||'' AS DECIMAL(38,18) ) ) FROM '||ETLDB_IN||'.'||TABLENAME_IN||';''
	 FROM 
	 (
	 SELECT 
	 RANK() OVER (PARTITION BY 1 ORDER BY ColumnID) AS COLORDER,
	    CASE 
	WHEN ColumnType=''CF'' THEN trim(columnLength*CharType)
	WHEN ColumnType=''CV'' THEN ''COALESCE(CHARACTER_LENGTH(''||trim(columnname)||'')*''||trim(CharType)||'',''||trim(columnLength*CharType)||'')+2 ''
	ELSE trim(columnLength) END  AS COLVALUE
	FROM   dbc.columns 
	where databasename = '''||ETLDB_IN||'''
	and tablename = '''||TABLENAME_IN||'''
	) A;';
	
	/* run cursor to gather column values in one VT */

	PREPARE stmtRUP FROM sql_stmt2;
	OPEN RBM_UNC_PREP; 
	labello:
	LOOP
	   SET n =n +1;
	   FETCH RBM_UNC_PREP INTO c;
	   IF (SQLSTATE = '02000') THEN   
	   LEAVE labello;
	   END IF;
	   SET RBM_FULL_PREP = coalesce(c,'');
	   SET MySQLQuery=RBM_FULL_PREP;
	   CALL DBC.SYSEXECSQL(MySQLQuery);
	END LOOP labello;
	CLOSE RBM_UNC_PREP;
	
	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'3050 VT COLBYTES FILL',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;

	

	/********************************************************************************************************************
	* STEP 3060 SPACE CONSUMPTION CALCULATION: TOTAL OF TABLE, UNCOMPRESSED
	*********************************************************************************************************************/

	SET TheStep='3060';

	SET MySQLQuery = 'INSERT INTO VT_RESULTS 
	SELECT ''SPACE UNCOMPRESSED'', SUM(COLVALUE) FROM VT_COLBYTES;';
	 
	SET SepStartTS = CURRENT_TIMESTAMP;

	CALL DBC.SYSEXECSQL(MySQLQuery);

	SELECT COALESCE(INFO_VALUE,0) INTO EST_SPACE_UNCOMP_AS_IS FROM VT_RESULTS WHERE INFO_TYPE='SPACE UNCOMPRESSED';

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'3060 EST SPACE UNCOMPR:'||TRIM(EST_SPACE_UNCOMP_AS_IS),
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;



	/*****************************************************************************
	 *****************************************************************************
	 * * * * * * * * * * * * * * * *  CHAPTER 4  * * * * * * * * * * * * * * * * *  
	 * * * * * * * *   OPTIMAL COMPRESSION DETERMINATION   * * * * * * * * * * * *
	 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 
	 *****************************************************************************
	*****************************************************************************/



	/********************************************************************************************************************
	* STEP 4010 DETERMINE OPTIMAL COMPRESSION  - CREATE VT VT_COMP_LEVEL_SVG
	*********************************************************************************************************************/

	SET TheStep='4010';

	/* 
	Define a VT that holds the Compression benefits and costs per columns from level to level
	A Level is a total of values that can be subsumed such that a given number of presence bits is used, e.g. level 3= the first 7 values 
	*/

	SET MySQLQuery = '
	CREATE VOLATILE SET TABLE VT_COMP_LEVEL_SVG ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ColumnName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      COL_ORDER SMALLINT NOT NULL ,
      COMP_LEVEL SMALLINT NOT NULL ,
      SVGS_BYTES BIGINT NOT NULL,
      COMP_COST_BYTES_HDR  BIGINT NOT NULL,
      CHOSEN SMALLINT
      )
	 PRIMARY INDEX ( ColumnName, COMP_LEVEL )
	 ON COMMIT PRESERVE ROWS;'
	 ;

	SET SepStartTS = CURRENT_TIMESTAMP;

	CALL DBC.SYSEXECSQL(MySQLQuery);

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'4010 CREATE VT VT_COMP_LEVEL_SVG',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;

	SET VTCOUNTER=VTCOUNTER+1;
		
	INSERT INTO MYCLEANUP (THENUMBER, THESTATEMENT) VALUES (VTCOUNTER, 'DROP TABLE VT_COMP_LEVEL_SVG;');



	/**************************************************************************************************************************
	* STEP 4020 DETERMINE OPTIMAL COMPRESSION - FILL VT_COMP_LEVEL_SVG WITH COMPRESSION SAVINGS AND TABLE HEADER COST PER LEVEL
	***************************************************************************************************************************/

	SET TheStep='4020';

	SET MySQLQuery = '
	INSERT INTO VT_COMP_LEVEL_SVG
	SELECT
	B.ColumnName,
	C.CR, 
	B.COMP_LEVEL,
	B.SVGS_BYTES,
	B.COMP_COST_BYTES_HDR,
	B.CHOSEN
	FROM
	(
	SELECT
	ColumnName,
	COMP_LEVEL,
	SUM(SAVINGS_BYTES) AS SVGS_BYTES,
	SUM(COL_CALC_VALUE) AS COMP_COST_BYTES_HDR,
	0 AS CHOSEN
	FROM
	(
	sel 
	columnname, 
	(CASE 
		WHEN COL_VAL_RANK=1 THEN 1
		WHEN COL_VAL_RANK<=3 THEN 2
		WHEN COL_VAL_RANK<=7 THEN 3
		WHEN COL_VAL_RANK<=15 THEN 4
		WHEN COL_VAL_RANK<=31 THEN 5
		WHEN COL_VAL_RANK<=63 THEN 6
		WHEN COL_VAL_RANK<=127 THEN 7
		WHEN COL_VAL_RANK<=255 THEN 8
		ELSE 0 END) AS COMP_LEVEL,

	COL_FREQUENCY*COL_CALC_VALUE AS SAVINGS_BYTES,
	COL_CALC_VALUE 
	 from
	 VT_COMP_CAND 
	 ) A
	 GROUP BY ColumnName, COMP_LEVEL
	 ) B
	 INNER JOIN 
	 ( select
	ColumnName,
	RANK() OVER(PARTITION BY 1  ORDER BY columnname ) AS CR 
	FROM 
	(select distinct ColumnName FROM VT_COMP_CAND ) A
	 ) C
	 ON 
	 B.ColumnName = C.ColumnName
	 ;';
	
	SET SepStartTS = CURRENT_TIMESTAMP;

	CALL DBC.SYSEXECSQL(MySQLQuery);

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'4020 FILL VT_COMP_LEVEL_SVG',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;



	/**************************************************************************************************************************
	* STEP 4030 DETERMINE OPTIMAL COMPRESSION - REMOVE VT_COMP_LEVEL_SVG W
	***************************************************************************************************************************/

	SET TheStep='4030';

	SET MySQLQuery = '
	DELETE FROM VT_COMP_LEVEL_SVG SV
	WHERE (COLUMNNAME, COMP_LEVEL) IN 
	(
	SELECT SV.columnname, SV.COMP_LEVEL
	 FROM VT_COMP_LEVEL_SVG SV
	JOIN
	(
	SELECT columnname, 
	MAX(
	CASE 
	WHEN COL_VAL_RANK=1 THEN 1
	WHEN COL_VAL_RANK<=3 THEN 2
	WHEN COL_VAL_RANK<=7 THEN 3
	WHEN COL_VAL_RANK<=15 THEN 4
	WHEN COL_VAL_RANK<=31 THEN 5
	WHEN COL_VAL_RANK<=63 THEN 6
	WHEN COL_VAL_RANK<=127 THEN 7
	WHEN COL_VAL_RANK<=255 THEN 8
	ELSE 0 END) AS MAX_LEVEL
	FROM VT_COMP_CAND  
	GROUP BY 1) MX
	ON SV.columnname = MX.COLUMNNAME
	AND SV.COMP_LEVEL >MX.MAX_LEVEL
	) 
	;';

	SET SepStartTS = CURRENT_TIMESTAMP;

	CALL DBC.SYSEXECSQL(MySQLQuery);

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'4030 VT_COMP_LEVEL_SVG REDUCE TO NET LEVELS',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;



	/**************************************************************************************************************************
	* STEP 4050 DETERMINE OPTIMAL COMPRESSION  - DEFINE THE DOMINANT PATH 
	***************************************************************************************************************************/

	SET TheStep='4050';

	/* 
	The DOMINANT PATH is the sequence of compression levels over columns, ordered by the SVG_PER_HDR_COST_RATIO, Compression level and column.
	First in line are level1 compressions of columns that have the highest Byte savings per table header byte cost incurred. These are not 
	necessarily those with the absolutely highest byte savings! The Dominant path definition is table header cost orientated in the sense
	that the compressed values that fit in to the table header limit shall be maximised, with preference to those levels of columns that
	cover larger parts of the column value distribution.
	*/

	SET MySQLQuery = '
	UPDATE VT_COMP_LEVEL_SVG FROM
	(
	SELECT
	ColumnName,
	COL_ORDER,
	COMP_LEVEL,
	SVGS_BYTES,
	COMP_COST_BYTES_HDR,
	CASE WHEN CUM_HDR_COST<=8192 THEN DOMINANT_PATH ELSE 0 END AS CHOSEN
	FROM
	(
	sel 
	ColumnName,
	COL_ORDER,
	COMP_LEVEL,
	SVGS_BYTES,
	COMP_COST_BYTES_HDR,
	CASE WHEN COMP_COST_BYTES_HDR>0 THEN CAST(SVGS_BYTES AS DECIMAL(38,2)) / (CAST(COMP_COST_BYTES_HDR AS DECIMAL(38,2)) ) ELSE CAST(SVGS_BYTES AS DECIMAL(38,2)) END AS SVG_PER_HDR_COST_RATIO,
	 RANK() OVER(PARTITION BY 1  ORDER BY SVG_PER_HDR_COST_RATIO DESC,COMP_LEVEL,COL_ORDER ) AS DOMINANT_PATH,
	SUM(COMP_COST_BYTES_HDR) OVER 
	(ORDER BY SVG_PER_HDR_COST_RATIO DESC,COMP_LEVEL,COL_ORDER ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )  AS CUM_HDR_COST 
	from 
	VT_COMP_LEVEL_SVG 
	) A
	WHERE CHOSEN >0
	) ZZ
	SET CHOSEN = ZZ.CHOSEN
	WHERE  VT_COMP_LEVEL_SVG.columnname = ZZ.columnname
	AND VT_COMP_LEVEL_SVG.COMP_LEVEL = ZZ.COMP_LEVEL;
	';

	SET SepStartTS = CURRENT_TIMESTAMP;

	CALL DBC.SYSEXECSQL(MySQLQuery);

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'4050 LOCAL OPTIMUM',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;


	
	/*******************************************************************************************************************************
	* STEP 4055 DETERMINE OPTIMAL COMPRESSION - DOMINANT PATH HDR_COST_BREAK CORRECTION
	********************************************************************************************************************************/
	
	/* Cut the dominant path back to the last level that saves more bytes than it costs in the header over all AMPS */
	
	SET TheStep='4055';
	
	SELECT COALESCE(MIN(CHOSEN),0) INTO HDR_COST_BREAK from VT_COMP_LEVEL_SVG WHERE CHOSEN>0 AND COMP_COST_BYTES_HDR*SYSAMPNR >=SVGS_BYTES ; 

	IF HDR_COST_BREAK>0 THEN
	
		UPDATE VT_COMP_LEVEL_SVG 
		SET CHOSEN=-1*CHOSEN
		WHERE CHOSEN>0
		AND CHOSEN >=HDR_COST_BREAK
		;

	END IF; 
	
	SET SepStartTS = CURRENT_TIMESTAMP;

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'4055 DP HDR_COST_BREAK CORRECTION',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;



	/*******************************************************************************************************************************
	* STEP 4060 DETERMINE OPTIMAL COMPRESSION  - USED BITS OF LAST BYTE AT CURRENT END OF DOMINANT PATH AND DOWNLEVEL DETERMINATION 
	********************************************************************************************************************************/

	SET TheStep='4060';

	SELECT COALESCE(SUM(COMP_LEVEL),0) INTO COMP_BITS_LOPT FROM (SELECT COLUMNNAME, MAX(COMP_LEVEL) AS COMP_LEVEL FROM VT_COMP_LEVEL_SVG WHERE CHOSEN >0 GROUP BY 1) A ; 
	
	SET LOPT_NET_BITS= 
	CASE WHEN ( COMP_BITS_LOPT+ NULLABILITYBITS -7 ) <=7 
	THEN ABS( COMP_BITS_LOPT+ NULLABILITYBITS-7 )
	ELSE 
	 ( COMP_BITS_LOPT+ NULLABILITYBITS -7 ) MOD 8  
	END ;
	
	SET SepStartTS = CURRENT_TIMESTAMP;

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'4060:'||COMP_BITS_LOPT||' USED BITS OF LAST BYTE AT CURRENT END OF DOMINANT PATH',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;


	
	/***************************************************************************************
	* STEP 4070 DETERMINE OPTIMAL COMPRESSION  -  DOWNLEVEL DETERMINATION 
	****************************************************************************************/

	SET TheStep='4070';

	SELECT COALESCE(SUM(SVGS_BYTES),0) INTO DOWNLEVEL_NET_SVGS FROM VT_COMP_LEVEL_SVG WHERE CHOSEN > (select max(chosen) from VT_COMP_LEVEL_SVG) - LOPT_NET_BITS;
	
	IF  DOWNLEVEL_NET_SVGS < (select INFO_VALUE FROM VT_RESULTS WHERE INFO_TYPE='ROWCOUNT' ) AND LOPT_NET_BITS ne 0 THEN 
	
		/* remove the last n steps from the dominant path when they cost one entire Presence Byte, but save less in total, given that we were in a situation with a not fully used PB */
		
		UPDATE VT_COMP_LEVEL_SVG SET CHOSEN =-1*CHOSEN WHERE CHOSEN > (select max(chosen) from VT_COMP_LEVEL_SVG) - LOPT_NET_BITS;
		
		/* Recalculate the COMP BITS usage */
		
		SELECT COALESCE(SUM(COMP_LEVEL),0) INTO COMP_BITS_LOPT FROM VT_COMP_LEVEL_SVG WHERE CHOSEN >0 ; 
		
		SET LOPT_NET_BITS= 
		CASE WHEN ( COMP_BITS_LOPT+ NULLABILITYBITS -7 ) <=7 
		THEN ABS( COMP_BITS_LOPT+ NULLABILITYBITS-7 )
		ELSE 
		 ( COMP_BITS_LOPT+ NULLABILITYBITS -7 ) MOD 8  
		END  ;
	
	END IF;
	
	
	SET SepStartTS = CURRENT_TIMESTAMP;

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'4070:'||trim(DOWNLEVEL_NET_SVGS)||' DLV svgs at DP end ,'||trim(LOPT_NET_BITS)||'to downlevel',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;


	
	/***************************************************************************************
	* STEP 4080 DETERMINE OPTIMAL COMPRESSION - DOWNLEVEL DETERMINATION 
	****************************************************************************************/

	SET TheStep='4080';

	/* Total Non-Mandatory Net Presence Bytes */
	SET TOT_NET_PBYTES =
	CASE WHEN (COMP_BITS_LOPT+ NULLABILITYBITS -7) MOD 8 =0 THEN 
	CAST( CAST((COMP_BITS_LOPT+ NULLABILITYBITS -7)AS DECIMAL(8,2)) /  8.0 AS INTEGER ) ELSE CAST( CAST((COMP_BITS_LOPT+ NULLABILITYBITS -7)AS DECIMAL(8,2)) /  8.0 AS INTEGER ) +1 END;
	
	SET SepStartTS = CURRENT_TIMESTAMP;

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'4080:'||trim(TOT_NET_PBYTES)||' Tot. NET PB',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;
	
	
	
	/*****************************************************************************
	 *****************************************************************************
	 * * * * * * * * * * * * * * * *  CHAPTER 5  * * * * * * * * * * * * * * * * *  
	 * * * * * * * * * *   OPTIMAL COMPRESSION RESULT   * * * * * * * * * * * * *
	 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 
	 *****************************************************************************
	*****************************************************************************/

	/****************************************************************************
	* STEP 5010 OPTIMAL COMPRESSION RESULT - TITHONOS TABLE CREATION 
	*****************************************************************************/

	/* 
	TITHONOS holds the final compression list for every column to be compressed in the column COMPRESSIONLIST. 
	The entry in COMPRESSIONLIST is built up sequentially over the following steps.
	When finished, COMPRESSIONLIST can be used in the following way 'ALTER TABLE '||databasename||'.'||tablename||' ADD '||columnname||' COMPRESS('||COMPRESSIONLIST||');' 
	*/

	SET TheStep='5010';

	IF (SELECT COUNT(*) FROM DBC.TABLES WHERE DATABASENAME = STGDB AND TABLENAME='TITHONOS' ) >0 THEN 

		SET MySQLQuery = 'DELETE FROM '||STGDB||'.TITHONOS WHERE DatabaseName = '''||ETLDB_IN||''' AND Tablename='''||TABLENAME_IN||''';';

	ELSE 

		SET MySQLQuery = '
		CREATE SET TABLE '||STGDB||'.TITHONOS,NO FALLBACK ,
		 NO BEFORE JOURNAL,
		 NO AFTER JOURNAL,
		 CHECKSUM = DEFAULT
		 (
		  DatabaseName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
		  TableName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
		  ColumnName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
		  COMPRESSIONLIST VARCHAR(30000) CHARACTER SET UNICODE NOT CASESPECIFIC  )
		PRIMARY INDEX ( DatabaseName, TableName, ColumnName );';

	END IF;

	SET SepStartTS = CURRENT_TIMESTAMP;

	CALL DBC.SYSEXECSQL(MySQLQuery);

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'5010 CREATE/EMPTY TBL TITHONOS',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;



	/**************************************************************************************************************************
	* STEP 5020 OPTIMAL COMPRESSION RESULT - TITHONOS SEED ENTRY
	***************************************************************************************************************************/

	/* The first entry for every column to be compressed */

	SET TheStep='5020';

	SET MySQLQuery = '
	INSERT INTO TITHONOS 
	SELECT '''||ETLDB_IN||''' AS DATABASENAME,'''|| TABLENAME_IN||''' AS TABLENAME, CC.COLUMNNAME,
	CASE WHEN CC.COL_VAL_RANK=1 THEN '''' ELSE '','' END||CASE 
	WHEN COL_DATATYPE IN (''AT'') THEN ''TIME'' ||''''''''||COL_VALUE ||''''''''
	WHEN COL_DATATYPE IN (''CF'',''CV'') THEN ''''''''||COL_VALUE||''''''''
	WHEN COL_DATATYPE IN (''DA'') THEN ''DATE'' ||''''''''||COL_VALUE||''''''''
	WHEN COL_DATATYPE IN (''TS'') THEN ''TIMESTAMP'' ||''''''''||COL_VALUE||'''''''' 
	WHEN COL_DATATYPE IN (''SZ'') THEN ''TIMESTAMP('' ||''''''''||COL_VALUE||''''''''||'')WITH TIME ZONE''
	ELSE TRIM(COL_VALUE) END AS COL_VALUE
	FROM 
	VT_COMP_CAND CC
	WHERE CC.COL_VAL_RANK=1
	AND COLUMNNAME IN 
	(
	SELECT DISTINCT COLUMNNAME
	FROM VT_COMP_LEVEL_SVG
	WHERE CHOSEN>0
	) 
	;';

	SET SepStartTS = CURRENT_TIMESTAMP;

	CALL DBC.SYSEXECSQL(MySQLQuery);

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'5020 TITHONOS seed entry',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;


	
	/**************************************************************************************************************************
	* STEP 5025 OPTIMAL COMPRESSION RESULT - TITHONOS SEED ENTRY INTERMEDIATE LIST CORRECTION FOR NULL AS RANK 1
	***************************************************************************************************************************/

	/* 
	If the seed entry led to a NULL compression list (because the rank 1 value is the NULL), then set it to the fantasy value '*§N§U§L§L§*' instead.
	NULL values at ranks >1 are exempt from further inclusion at the post-seed entry loop.
	The string '*§N§U§L§L§*' will be removed again later.
	*/
	
	SET TheStep='5025';

	SET MySQLQuery = '
	UPDATE TITHONOS SET COMPRESSIONLIST=''*§N§U§L§L§*'' WHERE COMPRESSIONLIST IS NULL;';
	
	SET SepStartTS = CURRENT_TIMESTAMP;

	CALL DBC.SYSEXECSQL(MySQLQuery);

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'5025 seed entry NULL correction',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;


	
	/**************************************************************************************************************************
	* STEP 5030 OPTIMAL COMPRESSION RESULT - DWHPRO_TITHONIZER REMAINING ENTRIES 2 TO 255
	***************************************************************************************************************************/

	/* Stepwise entry of levels 2 to 255 to build the COMPRESSIONLIST */

	SET TheStep='5030';

	SET SepStartTS = CURRENT_TIMESTAMP;

	SET lvl=1;

	Compressario2to255:
	LOOP
	SET lvl=lvl+1;
	SET MySQLQuery='
	UPDATE TITHONOS FROM 
	(
	SELECT '''||ETLDB_IN||''' AS DATABASENAME,'''||TABLENAME_IN||''' AS TABLENAME, CC.COLUMNNAME,
	CASE WHEN CC.COL_VAL_RANK=1 THEN '''' ELSE '','' END||
	CASE 
	WHEN COL_DATATYPE IN (''AT'') THEN ''TIME'' ''''''''||COL_VALUE||''''''''
	WHEN COL_DATATYPE IN (''CF'',''CV'') THEN ''''''''||COL_VALUE||''''''''
	WHEN COL_DATATYPE IN (''DA'') THEN ''DATE'' ''''''''||COL_VALUE||''''''''
	WHEN COL_DATATYPE IN (''TS'') THEN ''TIMESTAMP'' ''''''''||COL_VALUE||''''''''
	WHEN COL_DATATYPE IN (''SZ'') THEN ''TIMESTAMP('' ''''''''||COL_VALUE||''''''''||'')WITH TIME ZONE''
	ELSE TRIM(COL_VALUE) END AS COL_VALUE
	FROM 
	VT_COMP_CAND CC
	WHERE COLUMNNAME IN 
	(
	SELECT DISTINCT COLUMNNAME
	FROM VT_COMP_LEVEL_SVG
	WHERE CHOSEN>0
	) 
	AND CC.COL_VAL_RANK=' ||lvl|| '
	AND COL_VALUE IS NOT NULL
	) ZZ
	SET COMPRESSIONLIST= COMPRESSIONLIST||ZZ.COL_VALUE
	WHERE TITHONOS.DatabaseName = ZZ.DatabaseName
	AND TITHONOS.TableName = ZZ.TableName
	AND TITHONOS.COLUMNNAME = ZZ.COLUMNNAME
	;';
	CALL DBC.SYSEXECSQL(MySQLQuery);
	IF lvl = 255 THEN
	LEAVE Compressario2to255;
	END IF;
	END LOOP Compressario2to255;

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'5030 TITHONOS Levels 2-255',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;



	/**************************************************************************************************************************
	* STEP 5040 OPTIMAL COMPRESSION RESULT - ESTIMATED SPACE UNDER OPTIMAL COMPRESSION STORAGE IN VT_RESULTS
	***************************************************************************************************************************/

	SET TheStep='5040';

	SET MySQLQuery = '
	INSERT INTO VT_RESULTS
	SELECT B.INFO_TYPE, COALESCE( S.INFO_VALUE - B.INFO_VALUE + (R.INFO_VALUE*'||TRIM(TOT_NET_PBYTES)||') ,0)
	FROM
	(
	SELECT ''OPTIMAL COMPRESSION'' AS INFO_TYPE , SUM(SVGS_BYTES) AS INFO_VALUE
	FROM 
	VT_COMP_LEVEL_SVG CC
	WHERE CHOSEN>0
	) B
	INNER JOIN
	(SELECT INFO_VALUE FROM VT_RESULTS WHERE INFO_TYPE=''ROWCOUNT'' ) R
	ON 1=1
	INNER JOIN
	(SELECT INFO_VALUE FROM VT_RESULTS WHERE INFO_TYPE=''SPACE UNCOMPRESSED'' ) S
	ON 1=1
	;';

	SET SepStartTS = CURRENT_TIMESTAMP;

	CALL DBC.SYSEXECSQL(MySQLQuery);

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'5040 OPT COMP VT_RESULTS ENTRY',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;



	/*****************************************************************************
	 *****************************************************************************
	 * * * * * * * * * * * * * * * *  CHAPTER 6  * * * * * * * * * * * * * * * * *  
	 * * * * * * * * * * * * *  CLEANUP AND LOGGING  * * * * * * * * * * * * * * *
	 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 
	 *****************************************************************************
	*****************************************************************************/

	/*****************************************************************************
	* STEP 6000 LOGGING SUMMARY
	******************************************************************************/

	/* LOGGING SQL TO SUMMARIZE THE FINDINGS */

	SET TheStep='6000';

	SELECT COALESCE(COUNT(*),0) INTO TBL_COLCOUNT FROM DBC.COLUMNS WHERE DATABASENAME = ETLDB_IN  AND TABLENAME=TABLENAME_IN;
	SELECT COALESCE(COUNT( DISTINCT COLUMNNAME),0) INTO TBL_COMP_FINNS FROM VT_COMP_VALUES_IS;
	SELECT COALESCE(COUNT(DISTINCT COLUMNNAME),0) INTO COMP_CANDIDATES FROM VT_COMP_CAND;
	SELECT COALESCE(INFO_VALUE,0) INTO EST_SPACE_OPT_COMP FROM VT_RESULTS WHERE INFO_TYPE='OPTIMAL COMPRESSION';
	SELECT COALESCE( CAST( SUM(CurrentPerm) AS BIGINT ),0) INTO EMP_SPACE_AS_IS FROM dbc.TableSize WHERE DATABASENAME = ETLDB_IN AND TABLENAME = TABLENAME_IN;

	SET MySQLQuery = '
	Rows in Table:'||trim(TBL_ROWCOUNT)||'0A'XC ||
	'Columns in total:'||trim(TBL_COLCOUNT)||'0A'XC|| 
	'of which currently '||trim(TBL_COMP_FINNS)||' are compressed over'||'0A'XC|| 
	'Columns found candidates for compression:'||trim(COMP_CANDIDATES)||'0A'XC|| 
	'estimated space uncompressed:'||trim(EST_SPACE_UNCOMP_AS_IS)||'0A'XC|| 
	'empirical space as is:'||trim(EMP_SPACE_AS_IS)||'0A'XC|| 
	'estimated space under optimal compression:'||trim(EST_SPACE_OPT_COMP)||'0A'XC|| 
	'empirical space under optimal compression: n.a.';

	SET SepStartTS = CURRENT_TIMESTAMP;

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'6000 SUMMARY',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;



	/*****************************************************************************
	* STEP 6002 EXTRA LOG FOR CURRENT SPACE EMPIRICAL 
	******************************************************************************/

	SET TheStep='6002';

	SET SepStartTS = CURRENT_TIMESTAMP;

	SET MySQLQuery =trim(EMP_SPACE_AS_IS); 

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'6002 SPACE EMPIRICAL',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;



	/*****************************************************************************
	* STEP 6004 EXTRA LOG FOR ESTIMATED SPACE UNCOMPRESSED 
	******************************************************************************/

	SET TheStep='6004';

	SET SepStartTS = CURRENT_TIMESTAMP;

	SET MySQLQuery =trim(EST_SPACE_UNCOMP_AS_IS); 

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'6004 SPACE UNCOMPRESSED',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;



	/*****************************************************************************
	* STEP 6006 EXTRA LOG FOR ESTIMATED SPACE OPTIMALLY COMPRESSED
	******************************************************************************/

	SET TheStep='6006';

	SET SepStartTS = CURRENT_TIMESTAMP;

	SET MySQLQuery =trim(EST_SPACE_OPT_COMP); 

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'6006 SPACE OPT COMPRESSED',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;



	/*****************************************************************************
	* STEP 6010 DROP TABLE VT_COMP_CAND
	******************************************************************************/

	SET TheStep='6010';

	SET MySQLQuery = 'DROP TABLE VT_COMP_CAND;';

	SET SepStartTS = CURRENT_TIMESTAMP;

	CALL DBC.SYSEXECSQL(MySQLQuery);

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'6010 DROP VT_COMP_CAND',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;



	/*****************************************************************************
	* STEP 6020 DROP TABLE VT_COMP_VALUES_IS
	******************************************************************************/

	SET TheStep='6020';

	SET MySQLQuery = 'DROP TABLE VT_COMP_VALUES_IS;';


	SET SepStartTS = CURRENT_TIMESTAMP;

	CALL DBC.SYSEXECSQL(MySQLQuery);

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'6020 DROP VT_COMP_VALUES_IS',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;



	/*****************************************************************************
	* STEP 6030 DROP TABLE INSERTITUDE
	******************************************************************************/

	SET TheStep='6030';

	SET MySQLQuery = 'DROP TABLE INSERTITUDE;';


	SET SepStartTS = CURRENT_TIMESTAMP;

	CALL DBC.SYSEXECSQL(MySQLQuery);

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'6030 DROP INSERTITUDE',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;



	/*****************************************************************************
	* STEP 6040 DROP TABLE VT_COLBYTES
	******************************************************************************/

	SET TheStep='6040';

	SET MySQLQuery = 'DROP TABLE VT_COLBYTES;';


	SET SepStartTS = CURRENT_TIMESTAMP;

	CALL DBC.SYSEXECSQL(MySQLQuery);

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'6040 DROP VT_COLBYTES',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;



	/*****************************************************************************
	* STEP 6050 DROP TABLE VT_COMP_LEVEL_SVG
	******************************************************************************/

	SET TheStep='6050';

	SET MySQLQuery = 'DROP TABLE VT_COMP_LEVEL_SVG;';


	SET SepStartTS = CURRENT_TIMESTAMP;

	CALL DBC.SYSEXECSQL(MySQLQuery);

	SET EndTS = CURRENT_TIMESTAMP;
	SET MySQLState = SQLSTATE;
	SET MySQLCode = SQLCODE;
	SET ReportingActivityCount=ACTIVITY_COUNT;

	INSERT INTO G00_SP_LOG_TEMP 
	   (
	TARGET_TABLE,
	STEP_NUM,
	STEP_DESC,
	TM_START,
	STEP_START,
	SQL_REQUEST_TEXT,
	SQL_STATE,
	SQL_CODE,
	NM_ACTIVITY_COUNT,
	STEP_END,
	LOAD_ID
	   )
	VALUES
	   (
		TABLENAME_IN,
		:MyStepNum,
		'6050 DROP VT_COMP_LEVEL_SVG',
		CURRENT_TIMESTAMP,
		:SepStartTS,
		:MySQLQuery,
		:MySQLState,
		:MySQLCode,
		:ReportingActivityCount,
		:EndTS,
		LOAD_ID_IN
	   );
	  
	SET MyStepNum = MyStepNum + 100;

	IF TDVersion<=13 THEN

		/*****************************************************************************
		* STEP 6060 DROP TABLE PARTCHECK
		******************************************************************************/

		SET TheStep='6060';

		SET MySQLQuery = 'DROP TABLE PARTCHECK;';


		SET SepStartTS = CURRENT_TIMESTAMP;

		CALL DBC.SYSEXECSQL(MySQLQuery);

		SET EndTS = CURRENT_TIMESTAMP;
		SET MySQLState = SQLSTATE;
		SET MySQLCode = SQLCODE;
		SET ReportingActivityCount=ACTIVITY_COUNT;

		INSERT INTO G00_SP_LOG_TEMP 
		   (
		TARGET_TABLE,
		STEP_NUM,
		STEP_DESC,
		TM_START,
		STEP_START,
		SQL_REQUEST_TEXT,
		SQL_STATE,
		SQL_CODE,
		NM_ACTIVITY_COUNT,
		STEP_END,
		LOAD_ID
		   )
		VALUES
		   (
			TABLENAME_IN,
			:MyStepNum,
			'6060 DROP PARTCHECK',
			CURRENT_TIMESTAMP,
			:SepStartTS,
			:MySQLQuery,
			:MySQLState,
			:MySQLCode,
			:ReportingActivityCount,
			:EndTS,
			LOAD_ID_IN
		   );
		  
		SET MyStepNum = MyStepNum + 100;

	END IF;
	
END IF;


/*****************************************************************************
* STEP 6070 DROP TABLE VT_RESULTS
******************************************************************************/

SET TheStep='6070';

SET MySQLQuery = 'DROP TABLE VT_RESULTS;';


SET SepStartTS = CURRENT_TIMESTAMP;

CALL DBC.SYSEXECSQL(MySQLQuery);

SET EndTS = CURRENT_TIMESTAMP;
SET MySQLState = SQLSTATE;
SET MySQLCode = SQLCODE;
SET ReportingActivityCount=ACTIVITY_COUNT;

INSERT INTO G00_SP_LOG_TEMP 
(
TARGET_TABLE,
STEP_NUM,
STEP_DESC,
TM_START,
STEP_START,
SQL_REQUEST_TEXT,
SQL_STATE,
SQL_CODE,
NM_ACTIVITY_COUNT,
STEP_END,
LOAD_ID
)
VALUES
(
TABLENAME_IN,
:MyStepNum,
'6070 DROP VT_RESULTS',
CURRENT_TIMESTAMP,
:SepStartTS,
:MySQLQuery,
:MySQLState,
:MySQLCode,
:ReportingActivityCount,
:EndTS,
LOAD_ID_IN
);

SET MyStepNum = MyStepNum + 100;



/*****************************************************************************
* STEP 6080 DROP TABLE MYCLEANUP
******************************************************************************/

SET TheStep='6080';

SET MySQLQuery = 'DROP TABLE MYCLEANUP;';


SET SepStartTS = CURRENT_TIMESTAMP;

CALL DBC.SYSEXECSQL(MySQLQuery);

SET EndTS = CURRENT_TIMESTAMP;
SET MySQLState = SQLSTATE;
SET MySQLCode = SQLCODE;
SET ReportingActivityCount=ACTIVITY_COUNT;

INSERT INTO G00_SP_LOG_TEMP 
   (
TARGET_TABLE,
STEP_NUM,
STEP_DESC,
TM_START,
STEP_START,
SQL_REQUEST_TEXT,
SQL_STATE,
SQL_CODE,
NM_ACTIVITY_COUNT,
STEP_END,
LOAD_ID
   )
VALUES
   (
	TABLENAME_IN,
	:MyStepNum,
	'6080 DROP MYCLEANUP',
	CURRENT_TIMESTAMP,
	:SepStartTS,
	:MySQLQuery,
	:MySQLState,
	:MySQLCode,
	:ReportingActivityCount,
	:EndTS,
	LOAD_ID_IN
   );
  
SET MyStepNum = MyStepNum + 100;



/*****************************************************************************
* STEP 6100 Close Query Band
*****************************************************************************/

SET TheStep='6100';

SET MySQLQuery = 'SET QUERY_BAND =NONE FOR SESSION;';

SET SepStartTS = CURRENT_TIMESTAMP;

CALL DBC.SYSEXECSQL(MySQLQuery);

SET EndTS = CURRENT_TIMESTAMP;
SET MySQLState = SQLSTATE;
SET MySQLCode = SQLCODE;
SET ReportingActivityCount=ACTIVITY_COUNT;

INSERT INTO G00_SP_LOG_TEMP 
   (
TARGET_TABLE,
STEP_NUM,
STEP_DESC,
TM_START,
STEP_START,
SQL_REQUEST_TEXT,
SQL_STATE,
SQL_CODE,
NM_ACTIVITY_COUNT,
STEP_END,
LOAD_ID
   )
VALUES
   (
	TABLENAME_IN,
	:MyStepNum,
	'6100 CLOSE QUERY BAND',
	CURRENT_TIMESTAMP,
	:SepStartTS,
	:MySQLQuery,
	:MySQLState,
	:MySQLCode,
	:ReportingActivityCount,
	:EndTS,
	LOAD_ID_IN
   );
  
SET MyStepNum = MyStepNum + 100;

END;

DROP TABLE INSERTITUDE; 
DROP TABLE VT_COLBYTES; 
DROP TABLE VT_COMP_VALUES_IS; 
DROP TABLE VT_COMP_CAND; 
DROP TABLE VT_COMP_LEVEL_SVG; 
DROP TABLE VT_RESULTS; 
DROP TABLE PARTCHECK;
DROP TABLE MYCLEANUP ;