Is there any automated tool to compress the data in Teradata..

Database
l_k
Enthusiast

Is there any automated tool to compress the data in Teradata..

Hi,

We have 600 tables in the production server.I am analysing the tables for
applying the COMPRESS option for the columns in all the tables.

I f i go manually for each table,it looks like difficult to process all the tables by taking sample records from each tables to proceed.

Instead of this manual operation,do we have the TOOL in teradata to do the compress
or provide suggestion automatically based on the data inside the table?

Thanks

Lavakumar
Tags (1)
7 REPLIES
Teradata Employee

Re: Is there any automated tool to compress the data in Teradata..

Hi,

So far .... we don't have any such tool in Teradata.

Though .... there are third-party tools available for this.

Regards,

MAC
Enthusiast

Re: Is there any automated tool to compress the data in Teradata..

It seems such an obvious piece of functionality. I think its such a needless task for a DBA to do, analyse the tables and pick their own compression values..... There should be no tool, to use you it should be inbuilt the dba should just tell teradata compress column A and it should do it.

The issue I have is that you need to keep checking the compression values to make sure they are still right after a few months!

I can see it now....the dim and distant past the presentation by Teradata to our management saying how few DBAs will be required if we move to Teradata!

We are an order of magnitude up on the number of tables. Welcome to our nightmare! ;-)

random
l_k
Enthusiast

Re: Is there any automated tool to compress the data in Teradata..

Thank you for your reply random....
Enthusiast

Re: Is there any automated tool to compress the data in Teradata..

There is some code floating around on the internet that provides a foundation from which you can develop an automated maintenance routine for multi-value compression. As Random_Thought has indicated, the difficulty comes in the periodic reassessment of your compression value list to determine if you need to revise what values you are compressing.

I have been working on a routine in my spare time based on the code I found floating around on the internet that would automate the first pass of multi-value compression and maintain an log table of the values that were identified for compression and the date which they were identified as suitable candidates for compression.

I'm certain with a little additional work this log table could be used to extend the routine further to handle periodic maintenance either by alerting the DBA to the tables and columns that need to be addressed or in an automated fashion.

Re: Is there any automated tool to compress the data in Teradata..

can anybody expalin steps of compression in teradata.

Enthusiast

Re: Is there any automated tool to compress the data in Teradata..

I would recommend PRISE Compress Wizard to implement MVC compression, you can get a full functional free trial here: https://www.prisetools.com/productrequest

Enthusiast

Re: Is there any automated tool to compress the data in Teradata..

We use a SP to generate the Compression Script --

The procedure can apply Multi Value Compression (MVC) up to 15 values. The table that we try to compress needs to have an entry in some metadata tables and it should of length less than 29 Char. Only tables are accepted- no views.

How to call the procedure: call SYSADMIN.pCompression_TD  ('ABCD','XXXXXXXX',3,:p_SuccessOrFail);

It requires 3 input parameters and one output parameter and results two dynamic result sets.

Par 1                                      - Databasename

Par 2                                      – Tablename

Par 3                                      – Rank- to number of top values (distinct) that need to be compressed

p_SuccessOrFail               - Status of the procedure

Dynamic Result Set 1      – Status of the each step that executed via SP

Dynamic Result Set 2      – Expected Compression Savings

Script:

______

-- Metadata Table that keeps Business Databases added

CREATE SET TABLE META.DATABASES ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      DATABASENAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT '                              ',

      REAL_DATABASENAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT '                              ',

      ENVR_ID CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ',

      VIEWDATABASENAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT '                              ',

      DATABASE_TYPE_CDE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ',

      ETLVIEWDATABASENAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT '                              ',

      ROW_ADD_DTE DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE )

PRIMARY INDEX ( DATABASENAME );

-- Metadata Table that keeps Business Tables added

CREATE SET TABLE META.TABLES ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      DATABASENAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT '                              ',

      TABLENAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT '                              ',

      SHDW_FLG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT 'N',

      VIEWNAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT '                              ')

UNIQUE PRIMARY INDEX ( DATABASENAME ,TABLENAME );

---COMPRESSION Tables

--Collects Compression Table Info

CREATE MULTISET TABLE sysadmin.COMPRESSION_TABLEINFO_tbl ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      LOGDATE DATE FORMAT 'YYYY-MM-DD',

      DATABASENAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      TABLENAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      RANK_NBR INTEGER,

      Row_ADD_User CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC)

PRIMARY INDEX ( DATABASENAME ,TABLENAME );

--Collects Compression Table and Columns Info

CREATE MULTISET TABLE sysadmin.COMPRESSION_COLINFO_tbl ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      LOGDATE DATE FORMAT 'YYYY-MM-DD',

      DATABASENAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      TABLENAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      COLUMNNAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      DEFAULTVALUE VARCHAR(1024) CHARACTER SET LATIN NOT CASESPECIFIC,

      COLUMNLENGTH INTEGER,

      COLUMNID SMALLINT,

      COLUMNTYPE CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC)

PRIMARY INDEX ( DATABASENAME ,TABLENAME );

--Collects Compression values for columns

CREATE MULTISET TABLE sysadmin.COMPRESSION_COL_VALCNT_tbl ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      DATABASENAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      TABLENAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      COLUMNNAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      VAL_TXT VARCHAR(3000) CHARACTER SET LATIN NOT CASESPECIFIC,

      COUT_NBR DECIMAL(18,0),

      COMPILE_DT DATE FORMAT 'YY/MM/DD' DEFAULT DATE ,

      RANK_NBR INTEGER)

PRIMARY INDEX UPI ( DATABASENAME ,TABLENAME );

--Approximate Saving

CREATE MULTISET TABLE sysadmin.COMPRESSION_EXP_SAV_tbl ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      DATABASENAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      TABLENAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      COLUMNNAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      ColumnLength INTEGER,

      NUM_ROWS DECIMAL(18,0),

      MAXSIZE DECIMAL(18,0),

      RANK001 DECIMAL(18,0),

      SAVINGS001 DECIMAL(18,0),

      RANK003 DECIMAL(18,0),

      SAVINGS003 DECIMAL(18,0),

      DELTA003 DECIMAL(18,0),

      RANK007 DECIMAL(18,0),

      SAVINGS007 DECIMAL(18,0),

      DELTA007 DECIMAL(18,0),

      RANK015 DECIMAL(18,0),

      SAVINGS015 DECIMAL(18,0),

      DELTA015 DECIMAL(18,0),

      CollectTimeStamp TIMESTAMP(6))

PRIMARY INDEX ( DATABASENAME ,TABLENAME );

--Debug option

CREATE MULTISET TABLE sysadmin.COMPRESSION_LOG ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      EXECUTION_DT DATE FORMAT 'YYYY-MM-DD' NOT NULL,

      EXECUTION_TM INTEGER FORMAT '99:99:99' NOT NULL,

      ERROR_CDE INTEGER,

      ERROR_MSG VARCHAR(200) CHARACTER SET LATIN NOT CASESPECIFIC)

PRIMARY INDEX ( EXECUTION_DT );

--Script Generator

CREATE MULTISET TABLE sysadmin.COMPRESSION_Script ,FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      part INTEGER,

      Script VARCHAR(2000) CHARACTER SET LATIN NOT CASESPECIFIC)

PRIMARY INDEX ( part );

--Status of Procedure

CREATE MULTISET TABLE sysadmin.COMPRESSION_SP_Status ,FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      Detail VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,

      dte_tim TIMESTAMP(6))

PRIMARY INDEX ( dte_tim );

--Procedure Stats here

REPLACE PROCEDURE  SYSADMIN.pCompression_TD  (

IN SOURCE_DB VARCHAR(30), -- Database from which the table DDL needs to be copied

IN SOURCE_TBL VARCHAR(30), -- Table that needs to be created

IN RANK_NBR INTEGER, -- Totoal Number of Values that needs to pass

Out p_SuccessOrFail VARCHAR(200) -- Status

)

/*

SP created in Teradata Version

Version 1.0

Initial Release Date: XXXXXXXXXXX XXXX

Author: Teradata DBA Team - XXXXXXXXXXXXXX

Procedure is created help DBAs to look for compression options on tables

Sample Script to call the procedure:  

call SYSADMIN.pCompression_TD  ('CONFDIM','ACCOUNTING_CLASS',2,:p_SuccessOrFail);

*/

DYNAMIC RESULT SETS 2

L1: BEGIN

DECLARE VARIABLE1 INTEGER;

DECLARE VARIABLE2 VARCHAR(30) ; 

DECLARE VARIABLE3 VARCHAR(30) ; 

DECLARE VARIABLE4 INTEGER ; 

DECLARE VARIABLE5 VARCHAR(300) ; 

DECLARE VARIABLE6 VARCHAR(1000) ;

DECLARE RESULTS1 CURSOR WITH RETURN ONLY FOR

SELECT distinct Detail  SP_Step_Status from sysadmin.COMPRESSION_SP_Status order by dte_tim;

DECLARE RESULTS2 CURSOR WITH RETURN ONLY FOR

SELECT *  from  sysadmin.COMPRESSION_EXP_SAV_tbl   WHERE   TRIM(DATABASENAME)=trim(:VARIABLE2) and TRIM(tablename)=trim(:VARIABLE3);

 del from sysadmin.COMPRESSION_SP_Status;

--Step 1 

-- Check the Source Databases  given exists or NOT

SET VARIABLE1=0;

FOR REC1 AS TEST1 CURSOR FOR

SEL 1  "CHK1" FROM  DBC.Databases

WHERE   TRIM(DATABASENAME)=trim(:SOURCE_DB)

GROUP BY 1

DO 

SET VARIABLE1 = REC1."CHK1";

END FOR;           

IF VARIABLE1<>1 THEN

SET VARIABLE5 = 'FAILURE: Source Database doesnt exist';

CALL  DBC.SYSEXECSQL('INSERT INTO SYSADMIN.COMPRESSION_LOG ('''||cast(DATE  as  format 'YYYY-MM-DD')||''','''||TIME||''','||TRIM(3801)||','''||TRIM(VARIABLE5)||''');');

SET p_SuccessOrFail=VARIABLE5;

Leave L1;

END IF;

-- Check the Source Table/VIEW  given exists or NOT

IF SOURCE_DB is NOT NULL THEN

SET VARIABLE1=0;

FOR REC2 AS TEST1 CURSOR FOR

SEL 1  CHK1, DATABASENAME,tablename, coalesce((sel :RANK_NBR),3) RANK_NBR 

FROM  DBC.tables

WHERE   TRIM(DATABASENAME)=trim(:SOURCE_DB)

and TRIM(tablename)=trim(:SOURCE_TBL)

and tablekind in ('T','V')

GROUP BY 1,2,3,4

DO         

SET VARIABLE1 = REC2.CHK1;

Set VARIABLE2=REC2.DATABASENAME;

Set VARIABLE3=REC2.tablename;

Set VARIABLE4=REC2.RANK_NBR;

END FOR;           

IF VARIABLE1<>1 THEN

SET VARIABLE5 = 'FAILURE: Source Table given doesnt exist';

CALL  DBC.SYSEXECSQL('INSERT INTO SYSADMIN.COMPRESSION_LOG ('''||cast(DATE  as  format 'YYYY-MM-DD')||''','''||TIME||''','||TRIM(3802)||','''||TRIM(VARIABLE5)||''');');

SET p_SuccessOrFail=VARIABLE5;

Leave L1;

END IF;

END IF;

SET p_SuccessOrFail='Database and Table Exists';

Insert into sysadmin.COMPRESSION_SP_Status ('Step 1: Verified Database '||trim(VARIABLE2)||' and table '||trim(VARIABLE3)||' exists',current_timestamp);

--Step 2

--Validate the table length is less than 29 and the SP works fine

If char_length(VARIABLE2)>28 Then 

SET VARIABLE5 = 'FAILURE: Procedure cannot work for tables with name more than 28 letters';

CALL  DBC.SYSEXECSQL('INSERT INTO SYSADMIN.COMPRESSION_LOG ('''||cast(DATE  as  format 'YYYY-MM-DD')||''','''||TIME||''','||TRIM(9999)||','''||TRIM(VARIABLE5)||''');');

SET p_SuccessOrFail=VARIABLE5;

Leave L1;

END IF;

--Step 3

--Clean Up Phase

del from  sysadmin.COMPRESSION_Script;

del from  SYSADMIN.COMPRESSION_TABLEINFO_tbl ;

del from  SYSADMIN.COMPRESSION_COLINFO_tbl ;

del from  SYSADMIN.COMPRESSION_COL_VALCNT_tbl;

del from SYSADMIN.COMPRESSION_EXP_SAV_tbl where cast(CollectTimeStamp as date)<date-7;

Insert into sysadmin.COMPRESSION_SP_Status ('Step 3: Clean Up Completed for COMPRESSION Tables ',current_timestamp);

--Step 4

 --Capture DB , Table and Value Count info

Insert into SYSADMIN.COMPRESSION_TABLEINFO_tbl (cast(DATE  as  format 'YYYY-MM-DD'),VARIABLE2,VARIABLE3,VARIABLE4,User);

Insert into sysadmin.COMPRESSION_SP_Status ('Step 4: Captured DB,Table and Value/Rank Count info Given',current_timestamp);

--Step 5

 --Capture Table and Column Details

Insert into  SYSADMIN.COMPRESSION_COLINFO_tbl 

sel Date,databasename,tablename,columnname,DefaultValue,COLUMNLENGTH,ColumnId,COLUMNTYPE

from dbc.columns 

where columntype in ('I','DA','D','CF','F') 

and columnname not like '%row_add%'

and columnname not like '%row_update%'

and (databasename,tablename,columnname) not in (

SELECT databasename, tablename,  columnname 

FROM DBC.indices 

where indextype in ('K', 'P','I','Q')

and (databasename,tablename) in (sel databasename,tablename

from  SYSADMIN.COMPRESSION_TABLEINFO_tbl

where LOGDATE>date-2

)

group by 1,2,3

)

and (databasename,tablename) in (sel databasename,tablename

from  SYSADMIN.COMPRESSION_TABLEINFO_tbl

where LOGDATE>date-2

);

Insert into sysadmin.COMPRESSION_SP_Status ('Step 5: Table and Column Details Loaded to COMPRESSION_COLINFO_tbl',current_timestamp);

--Step 6

 --Capture Top Repeating values

FOR REC3 AS TEST CURSOR FOR

Sel 'INSERT INTO SYSADMIN.COMPRESSION_COL_VALCNT_tbl  SELECT '''

|| TRIM(COMPRESSION_COLINFO_tbl.DATABASENAME) || ''','''

|| TRIM(COMPRESSION_COLINFO_tbl.TABLENAME) || ''','''

|| TRIM(COMPRESSION_COLINFO_tbl.COLUMNNAME) || ''','

|| ' CAST( ' || trim(COMPRESSION_COLINFO_tbl.COLUMNNAME) || ' AS VARCHAR(3000))'

|| ',  COUNT(*) CNT, DATE, RANK () Over (partition by  '''|| TRIM(COMPRESSION_COLINFO_tbl.DATABASENAME) ||''','''|| TRIM(COMPRESSION_COLINFO_tbl.TABLENAME) ||''','''|| TRIM(COMPRESSION_COLINFO_tbl.COLUMNNAME) ||''' Order By CNT DESC ) Rnk FROM ' 

|| TRIM(COMPRESSION_COLINFO_tbl.DATABASENAME) 

|| '.' 

|| TRIM(COMPRESSION_COLINFO_tbl.TABLENAME) 

|| ' GROUP BY 1,2,3,4

qualify  Rnk<'|| TRIM(RANK_NBR) ||';' Script

 from  SYSADMIN.COMPRESSION_COLINFO_tbl ,

 SYSADMIN.COMPRESSION_TABLEINFO_tbl

 WHERE COMPRESSION_COLINFO_tbl .DATABASENAME = COMPRESSION_TABLEINFO_tbl.DATABASENAME

AND COMPRESSION_COLINFO_tbl .TABLENAME = COMPRESSION_TABLEINFO_tbl.TABLENAME

order by 1

DO

SET VARIABLE6 =  trim(REC3.Script);

CALL  DBC.SYSEXECSQL (VARIABLE6);

END FOR;       

Insert into sysadmin.COMPRESSION_SP_Status ('Step 6: Data Captured to SYSADMIN.COMPRESSION_COL_VALCNT_tbl',current_timestamp);

--Step 7

 --Capture Saving Info

 Insert into SYSADMIN.COMPRESSION_EXP_SAV_tbl

SELECT 

 COMPRESSION_COL_VALCNT_tbl .DATABASENAME

, COMPRESSION_COL_VALCNT_tbl .TABLENAME

, COMPRESSION_COL_VALCNT_tbl .COLUMNNAME

, COLUMNLENGTH

, SUM(COUT_NBR) AS NUM_ROWS

, CAST(NUM_ROWS * COLUMNLENGTH AS DECIMAL(18,0)) AS MAXSIZE

, SUM(CASE WHEN RANK_NBR < 2 THEN COUT_NBR ELSE 0 END) AS RANK001

, RANK001 * COLUMNLENGTH - RANK001 *1/8 AS SAVINGS001

, SUM(CASE WHEN RANK_NBR < 4 THEN COUT_NBR ELSE 0 END) AS RANK003

, RANK003 * COLUMNLENGTH - RANK003 *2/8 AS SAVINGS003

, SAVINGS003  - SAVINGS001 AS DELTA003

, SUM(CASE WHEN RANK_NBR < 8 THEN COUT_NBR ELSE 0 END) AS RANK007

, RANK007 * COLUMNLENGTH - RANK007 *3/8 AS SAVINGS007

, SAVINGS007  - SAVINGS003 AS DELTA007

, SUM(CASE WHEN RANK_NBR < 16 THEN COUT_NBR ELSE 0 END) AS RANK015

, RANK015 * COLUMNLENGTH - RANK015 *4/8 AS SAVINGS015

, SAVINGS015  - SAVINGS007 AS DELTA015

/*

, SUM(CASE WHEN RANK_NBR < 32 THEN COUT_NBR ELSE 0 END) AS RANK031

, RANK031 * COLUMNLENGTH - RANK031 *5/8 AS SAVINGS031

, SAVINGS031  - SAVINGS015 AS DELTA031

, SUM(CASE WHEN RANK_NBR < 64 THEN COUT_NBR ELSE 0 END) AS RANK063

, RANK063 * COLUMNLENGTH - RANK063 *6/8 AS SAVINGS063

, SAVINGS063  - SAVINGS031 AS DELTA063

, SUM(CASE WHEN RANK_NBR < 128 THEN COUT_NBR ELSE 0 END) AS RANK127

, RANK127 * COLUMNLENGTH - RANK127 *7/8 AS SAVINGS127

, SAVINGS127  - SAVINGS063 AS DELTA127

, SUM(CASE WHEN RANK_NBR < 256 THEN COUT_NBR ELSE 0 END) AS RANK255

, RANK255 * COLUMNLENGTH - RANK255 *8/8 AS SAVINGS255

, SAVINGS255  - SAVINGS127 AS DELTA255

*/

,Current_Timestamp 

FROM SYSADMIN.COMPRESSION_COL_VALCNT_tbl ,

SYSADMIN.COMPRESSION_COLINFO_tbl 

WHERE SYSADMIN.COMPRESSION_COL_VALCNT_tbl .DATABASENAME = COMPRESSION_COLINFO_tbl.DATABASENAME

AND SYSADMIN.COMPRESSION_COL_VALCNT_tbl .TABLENAME = COMPRESSION_COLINFO_tbl.TABLENAME

AND SYSADMIN.COMPRESSION_COL_VALCNT_tbl .COLUMNNAME = COMPRESSION_COLINFO_tbl.COLUMNNAME

GROUP BY 1,2,3,4;

Insert into sysadmin.COMPRESSION_SP_Status ('Step 7: Data Captured to SYSADMIN.COMPRESSION_EXP_SAV_tbl, sel * from  sysadmin.COMPRESSION_EXP_SAV_tbl order by 1; history maintained for 7 days',current_timestamp);

--Step 8

--Build Script that apply compression and save to sysadmin.COMPRESSION_Script

Insert Into  sysadmin.COMPRESSION_Script

sel part,Script

from 

(SELECT 0 (NAMED part ) , 

CAST( 'Create Table  ' || TRIM ( DATABASENAME ) || '.' || TRIM ( TABLENAME ) || '_N as  ' || TRIM ( DATABASENAME ) || '.' || TRIM ( TABLENAME ) || ' with no data and stats;'  AS VARCHAR ( 2000 )  ) (NAMED Script ) 

FROM SYSADMIN.COMPRESSION_TABLEINFO_tbl 

WHERE LOGDATE > DATE - 2 

GROUP BY 1 , 2 

UNION  ALL 

SELECT 1 (NAMED part ) , 

CAST( 'Alter Table ' || TRIM ( DATABASENAME ) || '.' || TRIM ( TABLENAME ) || '_N ADD ' || TRIM ( COLUMNNAME ) || ' ' || TRIM ( CompressionScript ) || ';'  AS VARCHAR ( 2000 )  ) (NAMED Script ) 

FROM ( 

SELECT DATABASENAME , TABLENAME , COLUMNNAME , 'Compress (' || 

CAST( 

MAXIMUM ( CASE WHEN RANK_NBR = 1 THEN TRIM ( NewVAL_TXT ) ELSE '' END ) || 

MAXIMUM ( CASE WHEN RANK_NBR = 2 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) || 

MAXIMUM ( CASE WHEN RANK_NBR = 3 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) || 

MAXIMUM ( CASE WHEN RANK_NBR = 4 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) || 

MAXIMUM ( CASE WHEN RANK_NBR = 5 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) || 

MAXIMUM ( CASE WHEN RANK_NBR = 6 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) || 

MAXIMUM ( CASE WHEN RANK_NBR = 7 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) || 

MAXIMUM ( CASE WHEN RANK_NBR = 8 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) || 

MAXIMUM ( CASE WHEN RANK_NBR = 9 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) || 

MAXIMUM ( CASE WHEN RANK_NBR = 10 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) || 

MAXIMUM ( CASE WHEN RANK_NBR = 11 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) || 

MAXIMUM ( CASE WHEN RANK_NBR = 12 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) ||

 MAXIMUM ( CASE WHEN RANK_NBR = 13 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) || 

MAXIMUM ( CASE WHEN RANK_NBR = 14 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) || 

MAXIMUM ( CASE WHEN RANK_NBR = 15 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END )  

AS VARCHAR ( 20000 )  ) || ') ' (NAMED CompressionScript ) 

FROM ( 

SELECT COMPRESSION_COL_VALCNT_tbl.DATABASENAME , 

COMPRESSION_COL_VALCNT_tbl.TABLENAME ,

COMPRESSION_COL_VALCNT_tbl.COLUMNNAME , RANK_NBR , 

CASE WHEN columntype IN ( 'CF' ) THEN '''' || TRIM ( VAL_TXT ) || '''' 

WHEN columntype IN ( 'DA' ) THEN 'Date ''' || TRIM ( VAL_TXT ) || '''' ELSE TRIM ( VAL_TXT ) END (NAMED NewVAL_TXT ) 

FROM SYSADMIN.COMPRESSION_COL_VALCNT_tbl , SYSADMIN.COMPRESSION_COLINFO_tbl 

WHERE SYSADMIN.COMPRESSION_COL_VALCNT_tbl.DATABASENAME = COMPRESSION_COLINFO_tbl.DATABASENAME 

AND SYSADMIN.COMPRESSION_COL_VALCNT_tbl.TABLENAME = COMPRESSION_COLINFO_tbl.TABLENAME 

AND SYSADMIN.COMPRESSION_COL_VALCNT_tbl.COLUMNNAME = COMPRESSION_COLINFO_tbl.COLUMNNAME 

AND RANK_NBR < 6 

GROUP BY 1 , 2 , 3 , 4 , 5 ) tmp 

GROUP BY 1 , 2 , 3 ) tmp1 

UNION  ALL 

SELECT 2 (NAMED part ) , 

CAST( 'Comment on Table   ' || TRIM ( a.DATABASENAME ) || '.' || TRIM ( a.TABLENAME ) || '_N as ''' || TRIM ( COALESCE( Commentstring , a.TABLENAME ) ) || ''' ;'  AS VARCHAR ( 2000 )  ) (NAMED Script ) 

FROM SYSADMIN.COMPRESSION_TABLEINFO_tbl a , dbc.tables b 

WHERE a.tablename = b.tablename 

AND a.databasename = b.databasename 

AND LOGDATE > DATE - 2 

GROUP BY 1 , 2 

UNION ALL 

SELECT 3 (NAMED part ) , 

CAST( 'Comment on column   ' || TRIM ( a.DATABASENAME ) || '.' || TRIM ( a.TABLENAME ) || '_N.' || TRIM ( b.Columnname ) || ' as ''' || TRIM ( COALESCE( Commentstring ,b.Columnname ) ) || ''' ;'  AS VARCHAR ( 2000 )  ) (NAMED Script ) 

FROM SYSADMIN.COMPRESSION_TABLEINFO_tbl a , dbc.columns b 

WHERE a.tablename = b.tablename 

AND a.databasename = b.databasename 

AND LOGDATE > DATE - 2 

GROUP BY 1 , 2 

UNION ALL 

SELECT 4 (NAMED part ) ,

 CAST( 'Insert Into   ' || TRIM ( a.DATABASENAME ) || '.' || TRIM ( a.TABLENAME ) || '_N sel * from  ' || TRIM ( a.DATABASENAME ) || '.' || TRIM ( a.TABLENAME ) || ';'  AS VARCHAR ( 2000 )  ) (NAMED Script ) 

FROM SYSADMIN.COMPRESSION_TABLEINFO_tbl a , dbc.columns b 

WHERE a.tablename = b.tablename 

AND a.databasename = b.databasename 

AND LOGDATE > DATE - 2 

GROUP BY 1 , 2 

UNION  ALL 

SELECT 5 (NAMED part ) , 

CAST( 'Collect Stats on  ' || TRIM ( a.DATABASENAME ) || '.' || TRIM ( a.TABLENAME ) || '_N;'  AS VARCHAR ( 2000 )  ) (NAMED Script ) 

FROM SYSADMIN.COMPRESSION_TABLEINFO_tbl a , dbc.columns b 

WHERE a.tablename = b.tablename 

AND a.databasename = b.databasename 

AND LOGDATE > DATE - 2 

GROUP BY 1 , 2 

UNION  ALL 

SELECT 6 (NAMED part ) , 

CAST( 'alter table ' || TRIM ( childdb ) || '.' || TRIM ( childtable ) || ' drop  ' || CASE WHEN IndexName IS NOT NULL THEN ' constraint ' || TRIM ( IndexName ) ELSE ' foreign key ( ' || TRIM ( ChildKeyColumn ) || ' ) references ' || TRIM ( parentdb ) || '.' || TRIM ( parenttable ) || ' ( ' || TRIM ( ParentKeyColumn ) END || ');'  AS VARCHAR ( 2000 )  ) (NAMED Script ) 

FROM dbc.All_RI_Parents , SYSADMIN.COMPRESSION_TABLEINFO_tbl 

WHERE DATABASENAME = childdb 

AND TABLENAME = childtable 

GROUP BY 1 , 2

UNION  ALL 

SELECT 7 (NAMED part ) , 

CAST( 'Rename Table   ' || TRIM ( a.DATABASENAME ) || '.' || TRIM ( a.TABLENAME ) || ' to  ' || TRIM ( a.DATABASENAME ) || '.' || TRIM ( a.TABLENAME ) || '_O; Rename Table   ' || TRIM ( a.DATABASENAME ) || '.' || TRIM ( a.TABLENAME ) || '_N to  ' || TRIM ( a.DATABASENAME ) || '.' || TRIM ( a.TABLENAME ) || ';'  AS VARCHAR ( 2000 )  ) (NAMED Script ) 

FROM SYSADMIN.COMPRESSION_TABLEINFO_tbl a , dbc.columns b 

WHERE a.tablename = b.tablename 

AND a.databasename = b.databasename 

AND LOGDATE > DATE - 2 

GROUP BY 1 , 2 

UNION  ALL 

SELECT 8 (NAMED part ) , 

CAST( 'REPLACE VIEW  ' || TRIM ( VIEWDATABASENAME ) || '.' || TRIM ( tablename ) || ' AS LOCK ROW FOR ACCESS SELECT * FROM ' || TRIM ( DATABASENAME ) || '.' || TRIM ( tablename ) || ' ;REPLACE VIEW  ' || TRIM ( ETLVIEWDATABASENAME ) || '.' || TRIM ( tablename ) || ' AS LOCK ROW FOR ACCESS SELECT * FROM ' || TRIM ( DATABASENAME ) || '.' || TRIM ( tablename ) || ';'  AS VARCHAR ( 2000 )  ) (NAMED Script ) 

FROM ( 

SELECT a.DATABASENAME (NAMED DATABASENAME ) , a.TABLENAME (NAMED TABLENAME ) ,

b.VIEWDATABASENAME , b.ETLVIEWDATABASENAME 

FROM META.TABLES a , META.DATABASES b , SYSADMIN.COMPRESSION_TABLEINFO_tbl c 

WHERE a.DATABASENAME = b.DATABASENAME 

AND a.TABLENAME = c.TABLENAME 

AND ( b.DATABASENAME = c.DATABASENAME 

OR b.VIEWDATABASENAME = c.DATABASENAME 

OR b.ETLVIEWDATABASENAME = c.DATABASENAME ) 

AND LOGDATE > DATE - 2 

GROUP BY 1 , 2 , 3 , 4 ) tmp 

GROUP BY 1 , 2 

UNION  ALL 

SELECT 9 (NAMED part ) , 

CAST( 'REPLACE VIEW  ' || TRIM ( VIEWDATABASENAME ) || '.' || TRIM ( tablename ) || ' AS LOCK ROW FOR ACCESS SELECT * FROM ' || TRIM ( DATABASENAME ) || '.' || TRIM ( tablename ) || ' ; REPLACE VIEW  ' || TRIM ( ETLVIEWDATABASENAME ) || '.' || TRIM ( tablename ) || ' AS LOCK ROW FOR ACCESS SELECT * FROM ' || TRIM ( DATABASENAME ) || '.' || TRIM ( tablename ) || ';'  AS VARCHAR ( 2000 )  ) (NAMED Script ) 

FROM ( 

SELECT a.DATABASENAME (NAMED DATABASENAME ) , a.TABLENAME (NAMED TABLENAME ) ,

b.VIEWDATABASENAME , b.ETLVIEWDATABASENAME 

FROM META.TABLES a , META.DATABASES b , SYSADMIN.COMPRESSION_TABLEINFO_tbl c 

WHERE a.DATABASENAME = b.DATABASENAME 

AND a.TABLENAME = c.TABLENAME 

AND ( b.DATABASENAME = c.DATABASENAME 

OR b.VIEWDATABASENAME = c.DATABASENAME 

OR b.ETLVIEWDATABASENAME = c.DATABASENAME ) 

AND LOGDATE > DATE - 2 

GROUP BY 1 , 2 , 3 , 4 ) tmp 

GROUP BY 1 , 2 

UNION  ALL 

SELECT 10 (NAMED part ) , 

CAST( 'alter table ' || TRIM ( childdb ) || '.' || TRIM ( childtable ) || '  ADD ' || CASE WHEN IndexName IS NULL THEN '' ELSE ' CONSTRAINT  ' || TRIM ( IndexName ) END || ' foreign key ( ' || TRIM ( ChildKeyColumn ) || ' ) references WITH NO CHECK OPTION ' || TRIM ( parentdb ) || '.' || TRIM ( parenttable ) || ' ( ' || TRIM ( ParentKeyColumn ) || ');'  AS VARCHAR ( 2000 )  ) (NAMED Script ) 

FROM dbc.All_RI_Parents , SYSADMIN.COMPRESSION_TABLEINFO_tbl 

WHERE DATABASENAME = childdb 

AND TABLENAME = childtable 

GROUP BY 1 , 2   

) tmp;

Insert into sysadmin.COMPRESSION_SP_Status ('Step 8: Built Script that apply compression and save to sysadmin.COMPRESSION_Script, sel * from  sysadmin.COMPRESSION_Script order by 1; ',current_timestamp);        

--Step 9

-- Apply Compression

FOR REC4 AS TEST CURSOR FOR

Sel Script 

from sysadmin.COMPRESSION_Script

order by part

DO

SET VARIABLE6 =  trim(REC4.Script);

/*

For actual execution UNCOMMENT  CALL  DBC.SYSEXECSQL script , else manually run the script " sel * from  sysadmin.COMPRESSION_Script order by 1"

**************************************************************************************************************************************************************

*/

--CALL  DBC.SYSEXECSQL (VARIABLE6); 

END FOR;       

Insert into sysadmin.COMPRESSION_SP_Status ('Step 9: Compression Applied ',current_timestamp);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          

Set p_SuccessOrFail='Success: Compression Procedure ran Successfully.';

OPEN RESULTS1;

OPEN RESULTS2;

END L1;