· 6 years ago · Jun 21, 2019, 12:38 PM
125 Jul 2013
2We use a SP to generate the Compression Script --
3The 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.
4
5How to call the procedure: call SYSADMIN.pCompression_TD ('ABCD','XXXXXXXX',3,:p_SuccessOrFail);
6
7
8
9It requires 3 input parameters and one output parameter and results two dynamic result sets.
10
11
12
13Par 1 - Databasename
14
15Par 2 – Tablename
16
17Par 3 – Rank- to number of top values (distinct) that need to be compressed
18
19p_SuccessOrFail - Status of the procedure
20
21Dynamic Result Set 1 – Status of the each step that executed via SP
22
23Dynamic Result Set 2 – Expected Compression Savings
24
25
26
27
28
29Script:
30
31______
32-- Metadata Table that keeps Business Databases added
33
34CREATE SET TABLE META.DATABASES ,NO FALLBACK ,
35 NO BEFORE JOURNAL,
36 NO AFTER JOURNAL,
37 CHECKSUM = DEFAULT
38 (
39 DATABASENAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ',
40 REAL_DATABASENAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ',
41 ENVR_ID CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ',
42 VIEWDATABASENAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ',
43 DATABASE_TYPE_CDE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ',
44 ETLVIEWDATABASENAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ',
45 ROW_ADD_DTE DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE )
46PRIMARY INDEX ( DATABASENAME );
47
48-- Metadata Table that keeps Business Tables added
49
50CREATE SET TABLE META.TABLES ,NO FALLBACK ,
51 NO BEFORE JOURNAL,
52 NO AFTER JOURNAL,
53 CHECKSUM = DEFAULT
54 (
55 DATABASENAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ',
56 TABLENAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ',
57 SHDW_FLG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT 'N',
58 VIEWNAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ')
59UNIQUE PRIMARY INDEX ( DATABASENAME ,TABLENAME );
60
61---COMPRESSION Tables
62
63--Collects Compression Table Info
64
65CREATE MULTISET TABLE sysadmin.COMPRESSION_TABLEINFO_tbl ,NO FALLBACK ,
66 NO BEFORE JOURNAL,
67 NO AFTER JOURNAL,
68 CHECKSUM = DEFAULT
69 (
70 LOGDATE DATE FORMAT 'YYYY-MM-DD',
71 DATABASENAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
72 TABLENAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
73 RANK_NBR INTEGER,
74 Row_ADD_User CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC)
75PRIMARY INDEX ( DATABASENAME ,TABLENAME );
76
77--Collects Compression Table and Columns Info
78
79CREATE MULTISET TABLE sysadmin.COMPRESSION_COLINFO_tbl ,NO FALLBACK ,
80 NO BEFORE JOURNAL,
81 NO AFTER JOURNAL,
82 CHECKSUM = DEFAULT
83 (
84 LOGDATE DATE FORMAT 'YYYY-MM-DD',
85 DATABASENAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
86 TABLENAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
87 COLUMNNAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
88 DEFAULTVALUE VARCHAR(1024) CHARACTER SET LATIN NOT CASESPECIFIC,
89 COLUMNLENGTH INTEGER,
90 COLUMNID SMALLINT,
91 COLUMNTYPE CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC)
92PRIMARY INDEX ( DATABASENAME ,TABLENAME );
93
94
95--Collects Compression values for columns
96
97CREATE MULTISET TABLE sysadmin.COMPRESSION_COL_VALCNT_tbl ,NO FALLBACK ,
98 NO BEFORE JOURNAL,
99 NO AFTER JOURNAL,
100 CHECKSUM = DEFAULT
101 (
102 DATABASENAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
103 TABLENAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
104 COLUMNNAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
105 VAL_TXT VARCHAR(3000) CHARACTER SET LATIN NOT CASESPECIFIC,
106 COUT_NBR DECIMAL(18,0),
107 COMPILE_DT DATE FORMAT 'YY/MM/DD' DEFAULT DATE ,
108 RANK_NBR INTEGER)
109PRIMARY INDEX UPI ( DATABASENAME ,TABLENAME );
110
111--Approximate Saving
112
113
114CREATE MULTISET TABLE sysadmin.COMPRESSION_EXP_SAV_tbl ,NO FALLBACK ,
115 NO BEFORE JOURNAL,
116 NO AFTER JOURNAL,
117 CHECKSUM = DEFAULT
118 (
119 DATABASENAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
120 TABLENAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
121 COLUMNNAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
122 ColumnLength INTEGER,
123 NUM_ROWS DECIMAL(18,0),
124 MAXSIZE DECIMAL(18,0),
125 RANK001 DECIMAL(18,0),
126 SAVINGS001 DECIMAL(18,0),
127 RANK003 DECIMAL(18,0),
128 SAVINGS003 DECIMAL(18,0),
129 DELTA003 DECIMAL(18,0),
130 RANK007 DECIMAL(18,0),
131 SAVINGS007 DECIMAL(18,0),
132 DELTA007 DECIMAL(18,0),
133 RANK015 DECIMAL(18,0),
134 SAVINGS015 DECIMAL(18,0),
135 DELTA015 DECIMAL(18,0),
136 CollectTimeStamp TIMESTAMP(6))
137PRIMARY INDEX ( DATABASENAME ,TABLENAME );
138
139--Debug option
140
141CREATE MULTISET TABLE sysadmin.COMPRESSION_LOG ,NO FALLBACK ,
142 NO BEFORE JOURNAL,
143 NO AFTER JOURNAL,
144 CHECKSUM = DEFAULT
145 (
146 EXECUTION_DT DATE FORMAT 'YYYY-MM-DD' NOT NULL,
147 EXECUTION_TM INTEGER FORMAT '99:99:99' NOT NULL,
148 ERROR_CDE INTEGER,
149 ERROR_MSG VARCHAR(200) CHARACTER SET LATIN NOT CASESPECIFIC)
150PRIMARY INDEX ( EXECUTION_DT );
151
152--Script Generator
153
154CREATE MULTISET TABLE sysadmin.COMPRESSION_Script ,FALLBACK ,
155 NO BEFORE JOURNAL,
156 NO AFTER JOURNAL,
157 CHECKSUM = DEFAULT
158 (
159 part INTEGER,
160 Script VARCHAR(2000) CHARACTER SET LATIN NOT CASESPECIFIC)
161PRIMARY INDEX ( part );
162
163--Status of Procedure
164
165CREATE MULTISET TABLE sysadmin.COMPRESSION_SP_Status ,FALLBACK ,
166 NO BEFORE JOURNAL,
167 NO AFTER JOURNAL,
168 CHECKSUM = DEFAULT
169 (
170 Detail VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
171 dte_tim TIMESTAMP(6))
172PRIMARY INDEX ( dte_tim );
173
174
175
176--Procedure Stats here
177
178REPLACE PROCEDURE SYSADMIN.pCompression_TD (
179IN SOURCE_DB VARCHAR(30), -- Database from which the table DDL needs to be copied
180IN SOURCE_TBL VARCHAR(30), -- Table that needs to be created
181IN RANK_NBR INTEGER, -- Totoal Number of Values that needs to pass
182Out p_SuccessOrFail VARCHAR(200) -- Status
183)
184
185
186/*
187SP created in Teradata Version
188
189Version 1.0
190Initial Release Date: XXXXXXXXXXX XXXX
191Author: Teradata DBA Team - XXXXXXXXXXXXXX
192Procedure is created help DBAs to look for compression options on tables
193Sample Script to call the procedure:
194
195call SYSADMIN.pCompression_TD ('CONFDIM','ACCOUNTING_CLASS',2,:p_SuccessOrFail);
196
197*/
198
199
200DYNAMIC RESULT SETS 2
201
202L1: BEGIN
203
204DECLARE VARIABLE1 INTEGER;
205DECLARE VARIABLE2 VARCHAR(30) ;
206DECLARE VARIABLE3 VARCHAR(30) ;
207DECLARE VARIABLE4 INTEGER ;
208DECLARE VARIABLE5 VARCHAR(300) ;
209DECLARE VARIABLE6 VARCHAR(1000) ;
210
211
212DECLARE RESULTS1 CURSOR WITH RETURN ONLY FOR
213SELECT distinct Detail SP_Step_Status from sysadmin.COMPRESSION_SP_Status order by dte_tim;
214
215DECLARE RESULTS2 CURSOR WITH RETURN ONLY FOR
216SELECT * from sysadmin.COMPRESSION_EXP_SAV_tbl WHERE TRIM(DATABASENAME)=trim(:VARIABLE2) and TRIM(tablename)=trim(:VARIABLE3);
217
218 del from sysadmin.COMPRESSION_SP_Status;
219
220--Step 1
221
222-- Check the Source Databases given exists or NOT
223
224SET VARIABLE1=0;
225
226FOR REC1 AS TEST1 CURSOR FOR
227SEL 1 "CHK1" FROM DBC.Databases
228WHERE TRIM(DATABASENAME)=trim(:SOURCE_DB)
229GROUP BY 1
230
231DO
232SET VARIABLE1 = REC1."CHK1";
233END FOR;
234
235IF VARIABLE1<>1 THEN
236SET VARIABLE5 = 'FAILURE: Source Database doesnt exist';
237CALL DBC.SYSEXECSQL('INSERT INTO SYSADMIN.COMPRESSION_LOG ('''||cast(DATE as format 'YYYY-MM-DD')||''','''||TIME||''','||TRIM(3801)||','''||TRIM(VARIABLE5)||''');');
238SET p_SuccessOrFail=VARIABLE5;
239Leave L1;
240END IF;
241
242-- Check the Source Table/VIEW given exists or NOT
243
244IF SOURCE_DB is NOT NULL THEN
245
246SET VARIABLE1=0;
247
248FOR REC2 AS TEST1 CURSOR FOR
249SEL 1 CHK1, DATABASENAME,tablename, coalesce((sel :RANK_NBR),3) RANK_NBR
250FROM DBC.tables
251WHERE TRIM(DATABASENAME)=trim(:SOURCE_DB)
252and TRIM(tablename)=trim(:SOURCE_TBL)
253and tablekind in ('T','V')
254GROUP BY 1,2,3,4
255
256DO
257SET VARIABLE1 = REC2.CHK1;
258Set VARIABLE2=REC2.DATABASENAME;
259Set VARIABLE3=REC2.tablename;
260Set VARIABLE4=REC2.RANK_NBR;
261
262END FOR;
263
264IF VARIABLE1<>1 THEN
265SET VARIABLE5 = 'FAILURE: Source Table given doesnt exist';
266CALL DBC.SYSEXECSQL('INSERT INTO SYSADMIN.COMPRESSION_LOG ('''||cast(DATE as format 'YYYY-MM-DD')||''','''||TIME||''','||TRIM(3802)||','''||TRIM(VARIABLE5)||''');');
267SET p_SuccessOrFail=VARIABLE5;
268Leave L1;
269END IF;
270END IF;
271
272SET p_SuccessOrFail='Database and Table Exists';
273
274
275Insert into sysadmin.COMPRESSION_SP_Status ('Step 1: Verified Database '||trim(VARIABLE2)||' and table '||trim(VARIABLE3)||' exists',current_timestamp);
276
277--Step 2
278
279--Validate the table length is less than 29 and the SP works fine
280
281If char_length(VARIABLE2)>28 Then
282SET VARIABLE5 = 'FAILURE: Procedure cannot work for tables with name more than 28 letters';
283CALL DBC.SYSEXECSQL('INSERT INTO SYSADMIN.COMPRESSION_LOG ('''||cast(DATE as format 'YYYY-MM-DD')||''','''||TIME||''','||TRIM(9999)||','''||TRIM(VARIABLE5)||''');');
284SET p_SuccessOrFail=VARIABLE5;
285Leave L1;
286END IF;
287
288--Step 3
289
290--Clean Up Phase
291
292del from sysadmin.COMPRESSION_Script;
293del from SYSADMIN.COMPRESSION_TABLEINFO_tbl ;
294del from SYSADMIN.COMPRESSION_COLINFO_tbl ;
295del from SYSADMIN.COMPRESSION_COL_VALCNT_tbl;
296del from SYSADMIN.COMPRESSION_EXP_SAV_tbl where cast(CollectTimeStamp as date)<date-7;
297
298
299Insert into sysadmin.COMPRESSION_SP_Status ('Step 3: Clean Up Completed for COMPRESSION Tables ',current_timestamp);
300
301--Step 4
302
303 --Capture DB , Table and Value Count info
304
305Insert into SYSADMIN.COMPRESSION_TABLEINFO_tbl (cast(DATE as format 'YYYY-MM-DD'),VARIABLE2,VARIABLE3,VARIABLE4,User);
306
307Insert into sysadmin.COMPRESSION_SP_Status ('Step 4: Captured DB,Table and Value/Rank Count info Given',current_timestamp);
308
309
310--Step 5
311
312 --Capture Table and Column Details
313
314Insert into SYSADMIN.COMPRESSION_COLINFO_tbl
315sel Date,databasename,tablename,columnname,DefaultValue,COLUMNLENGTH,ColumnId,COLUMNTYPE
316from dbc.columns
317where columntype in ('I','DA','D','CF','F')
318and columnname not like '%row_add%'
319and columnname not like '%row_update%'
320and (databasename,tablename,columnname) not in (
321SELECT databasename, tablename, columnname
322FROM DBC.indices
323where indextype in ('K', 'P','I','Q')
324and (databasename,tablename) in (sel databasename,tablename
325from SYSADMIN.COMPRESSION_TABLEINFO_tbl
326where LOGDATE>date-2
327)
328group by 1,2,3
329)
330and (databasename,tablename) in (sel databasename,tablename
331from SYSADMIN.COMPRESSION_TABLEINFO_tbl
332where LOGDATE>date-2
333);
334
335
336Insert into sysadmin.COMPRESSION_SP_Status ('Step 5: Table and Column Details Loaded to COMPRESSION_COLINFO_tbl',current_timestamp);
337
338
339
340--Step 6
341
342 --Capture Top Repeating values
343
344
345FOR REC3 AS TEST CURSOR FOR
346Sel 'INSERT INTO SYSADMIN.COMPRESSION_COL_VALCNT_tbl SELECT '''
347|| TRIM(COMPRESSION_COLINFO_tbl.DATABASENAME) || ''','''
348|| TRIM(COMPRESSION_COLINFO_tbl.TABLENAME) || ''','''
349|| TRIM(COMPRESSION_COLINFO_tbl.COLUMNNAME) || ''','
350|| ' CAST( ' || trim(COMPRESSION_COLINFO_tbl.COLUMNNAME) || ' AS VARCHAR(3000))'
351|| ', 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 '
352|| TRIM(COMPRESSION_COLINFO_tbl.DATABASENAME)
353|| '.'
354|| TRIM(COMPRESSION_COLINFO_tbl.TABLENAME)
355|| ' GROUP BY 1,2,3,4
356qualify Rnk<'|| TRIM(RANK_NBR) ||';' Script
357 from SYSADMIN.COMPRESSION_COLINFO_tbl ,
358 SYSADMIN.COMPRESSION_TABLEINFO_tbl
359 WHERE COMPRESSION_COLINFO_tbl .DATABASENAME = COMPRESSION_TABLEINFO_tbl.DATABASENAME
360AND COMPRESSION_COLINFO_tbl .TABLENAME = COMPRESSION_TABLEINFO_tbl.TABLENAME
361order by 1
362DO
363
364SET VARIABLE6 = trim(REC3.Script);
365
366CALL DBC.SYSEXECSQL (VARIABLE6);
367
368END FOR;
369
370Insert into sysadmin.COMPRESSION_SP_Status ('Step 6: Data Captured to SYSADMIN.COMPRESSION_COL_VALCNT_tbl',current_timestamp);
371
372
373
374--Step 7
375
376 --Capture Saving Info
377
378
379 Insert into SYSADMIN.COMPRESSION_EXP_SAV_tbl
380SELECT
381 COMPRESSION_COL_VALCNT_tbl .DATABASENAME
382, COMPRESSION_COL_VALCNT_tbl .TABLENAME
383, COMPRESSION_COL_VALCNT_tbl .COLUMNNAME
384, COLUMNLENGTH
385, SUM(COUT_NBR) AS NUM_ROWS
386, CAST(NUM_ROWS * COLUMNLENGTH AS DECIMAL(18,0)) AS MAXSIZE
387, SUM(CASE WHEN RANK_NBR < 2 THEN COUT_NBR ELSE 0 END) AS RANK001
388, RANK001 * COLUMNLENGTH - RANK001 *1/8 AS SAVINGS001
389, SUM(CASE WHEN RANK_NBR < 4 THEN COUT_NBR ELSE 0 END) AS RANK003
390, RANK003 * COLUMNLENGTH - RANK003 *2/8 AS SAVINGS003
391, SAVINGS003 - SAVINGS001 AS DELTA003
392, SUM(CASE WHEN RANK_NBR < 8 THEN COUT_NBR ELSE 0 END) AS RANK007
393, RANK007 * COLUMNLENGTH - RANK007 *3/8 AS SAVINGS007
394, SAVINGS007 - SAVINGS003 AS DELTA007
395, SUM(CASE WHEN RANK_NBR < 16 THEN COUT_NBR ELSE 0 END) AS RANK015
396, RANK015 * COLUMNLENGTH - RANK015 *4/8 AS SAVINGS015
397, SAVINGS015 - SAVINGS007 AS DELTA015
398/*
399, SUM(CASE WHEN RANK_NBR < 32 THEN COUT_NBR ELSE 0 END) AS RANK031
400, RANK031 * COLUMNLENGTH - RANK031 *5/8 AS SAVINGS031
401, SAVINGS031 - SAVINGS015 AS DELTA031
402, SUM(CASE WHEN RANK_NBR < 64 THEN COUT_NBR ELSE 0 END) AS RANK063
403, RANK063 * COLUMNLENGTH - RANK063 *6/8 AS SAVINGS063
404, SAVINGS063 - SAVINGS031 AS DELTA063
405, SUM(CASE WHEN RANK_NBR < 128 THEN COUT_NBR ELSE 0 END) AS RANK127
406, RANK127 * COLUMNLENGTH - RANK127 *7/8 AS SAVINGS127
407, SAVINGS127 - SAVINGS063 AS DELTA127
408, SUM(CASE WHEN RANK_NBR < 256 THEN COUT_NBR ELSE 0 END) AS RANK255
409, RANK255 * COLUMNLENGTH - RANK255 *8/8 AS SAVINGS255
410, SAVINGS255 - SAVINGS127 AS DELTA255
411*/
412,Current_Timestamp
413
414FROM SYSADMIN.COMPRESSION_COL_VALCNT_tbl ,
415SYSADMIN.COMPRESSION_COLINFO_tbl
416WHERE SYSADMIN.COMPRESSION_COL_VALCNT_tbl .DATABASENAME = COMPRESSION_COLINFO_tbl.DATABASENAME
417AND SYSADMIN.COMPRESSION_COL_VALCNT_tbl .TABLENAME = COMPRESSION_COLINFO_tbl.TABLENAME
418AND SYSADMIN.COMPRESSION_COL_VALCNT_tbl .COLUMNNAME = COMPRESSION_COLINFO_tbl.COLUMNNAME
419GROUP BY 1,2,3,4;
420
421Insert 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);
422
423
424--Step 8
425
426--Build Script that apply compression and save to sysadmin.COMPRESSION_Script
427
428Insert Into sysadmin.COMPRESSION_Script
429sel part,Script
430from
431(SELECT 0 (NAMED part ) ,
432CAST( 'Create Table ' || TRIM ( DATABASENAME ) || '.' || TRIM ( TABLENAME ) || '_N as ' || TRIM ( DATABASENAME ) || '.' || TRIM ( TABLENAME ) || ' with no data and stats;' AS VARCHAR ( 2000 ) ) (NAMED Script )
433FROM SYSADMIN.COMPRESSION_TABLEINFO_tbl
434WHERE LOGDATE > DATE - 2
435GROUP BY 1 , 2
436
437UNION ALL
438
439SELECT 1 (NAMED part ) ,
440CAST( 'Alter Table ' || TRIM ( DATABASENAME ) || '.' || TRIM ( TABLENAME ) || '_N ADD ' || TRIM ( COLUMNNAME ) || ' ' || TRIM ( CompressionScript ) || ';' AS VARCHAR ( 2000 ) ) (NAMED Script )
441FROM (
442SELECT DATABASENAME , TABLENAME , COLUMNNAME , 'Compress (' ||
443CAST(
444MAXIMUM ( CASE WHEN RANK_NBR = 1 THEN TRIM ( NewVAL_TXT ) ELSE '' END ) ||
445MAXIMUM ( CASE WHEN RANK_NBR = 2 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) ||
446MAXIMUM ( CASE WHEN RANK_NBR = 3 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) ||
447MAXIMUM ( CASE WHEN RANK_NBR = 4 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) ||
448MAXIMUM ( CASE WHEN RANK_NBR = 5 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) ||
449MAXIMUM ( CASE WHEN RANK_NBR = 6 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) ||
450MAXIMUM ( CASE WHEN RANK_NBR = 7 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) ||
451MAXIMUM ( CASE WHEN RANK_NBR = 8 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) ||
452MAXIMUM ( CASE WHEN RANK_NBR = 9 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) ||
453MAXIMUM ( CASE WHEN RANK_NBR = 10 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) ||
454MAXIMUM ( CASE WHEN RANK_NBR = 11 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) ||
455MAXIMUM ( CASE WHEN RANK_NBR = 12 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) ||
456 MAXIMUM ( CASE WHEN RANK_NBR = 13 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) ||
457MAXIMUM ( CASE WHEN RANK_NBR = 14 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END ) ||
458MAXIMUM ( CASE WHEN RANK_NBR = 15 THEN ',' || TRIM ( NewVAL_TXT ) ELSE '' END )
459AS VARCHAR ( 20000 ) ) || ') ' (NAMED CompressionScript )
460FROM (
461SELECT COMPRESSION_COL_VALCNT_tbl.DATABASENAME ,
462COMPRESSION_COL_VALCNT_tbl.TABLENAME ,
463COMPRESSION_COL_VALCNT_tbl.COLUMNNAME , RANK_NBR ,
464CASE WHEN columntype IN ( 'CF' ) THEN '''' || TRIM ( VAL_TXT ) || ''''
465WHEN columntype IN ( 'DA' ) THEN 'Date ''' || TRIM ( VAL_TXT ) || '''' ELSE TRIM ( VAL_TXT ) END (NAMED NewVAL_TXT )
466FROM SYSADMIN.COMPRESSION_COL_VALCNT_tbl , SYSADMIN.COMPRESSION_COLINFO_tbl
467WHERE SYSADMIN.COMPRESSION_COL_VALCNT_tbl.DATABASENAME = COMPRESSION_COLINFO_tbl.DATABASENAME
468AND SYSADMIN.COMPRESSION_COL_VALCNT_tbl.TABLENAME = COMPRESSION_COLINFO_tbl.TABLENAME
469AND SYSADMIN.COMPRESSION_COL_VALCNT_tbl.COLUMNNAME = COMPRESSION_COLINFO_tbl.COLUMNNAME
470AND RANK_NBR < 6
471GROUP BY 1 , 2 , 3 , 4 , 5 ) tmp
472GROUP BY 1 , 2 , 3 ) tmp1
473
474UNION ALL
475
476SELECT 2 (NAMED part ) ,
477CAST( 'Comment on Table ' || TRIM ( a.DATABASENAME ) || '.' || TRIM ( a.TABLENAME ) || '_N as ''' || TRIM ( COALESCE( Commentstring , a.TABLENAME ) ) || ''' ;' AS VARCHAR ( 2000 ) ) (NAMED Script )
478FROM SYSADMIN.COMPRESSION_TABLEINFO_tbl a , dbc.tables b
479WHERE a.tablename = b.tablename
480AND a.databasename = b.databasename
481AND LOGDATE > DATE - 2
482GROUP BY 1 , 2
483
484UNION ALL
485
486SELECT 3 (NAMED part ) ,
487CAST( 'Comment on column ' || TRIM ( a.DATABASENAME ) || '.' || TRIM ( a.TABLENAME ) || '_N.' || TRIM ( b.Columnname ) || ' as ''' || TRIM ( COALESCE( Commentstring ,b.Columnname ) ) || ''' ;' AS VARCHAR ( 2000 ) ) (NAMED Script )
488FROM SYSADMIN.COMPRESSION_TABLEINFO_tbl a , dbc.columns b
489WHERE a.tablename = b.tablename
490AND a.databasename = b.databasename
491AND LOGDATE > DATE - 2
492GROUP BY 1 , 2
493
494UNION ALL
495
496SELECT 4 (NAMED part ) ,
497 CAST( 'Insert Into ' || TRIM ( a.DATABASENAME ) || '.' || TRIM ( a.TABLENAME ) || '_N sel * from ' || TRIM ( a.DATABASENAME ) || '.' || TRIM ( a.TABLENAME ) || ';' AS VARCHAR ( 2000 ) ) (NAMED Script )
498FROM SYSADMIN.COMPRESSION_TABLEINFO_tbl a , dbc.columns b
499WHERE a.tablename = b.tablename
500AND a.databasename = b.databasename
501AND LOGDATE > DATE - 2
502GROUP BY 1 , 2
503
504UNION ALL
505
506SELECT 5 (NAMED part ) ,
507CAST( 'Collect Stats on ' || TRIM ( a.DATABASENAME ) || '.' || TRIM ( a.TABLENAME ) || '_N;' AS VARCHAR ( 2000 ) ) (NAMED Script )
508FROM SYSADMIN.COMPRESSION_TABLEINFO_tbl a , dbc.columns b
509WHERE a.tablename = b.tablename
510AND a.databasename = b.databasename
511AND LOGDATE > DATE - 2
512GROUP BY 1 , 2
513
514
515UNION ALL
516
517SELECT 6 (NAMED part ) ,
518CAST( '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 )
519FROM dbc.All_RI_Parents , SYSADMIN.COMPRESSION_TABLEINFO_tbl
520WHERE DATABASENAME = childdb
521AND TABLENAME = childtable
522GROUP BY 1 , 2
523
524UNION ALL
525
526SELECT 7 (NAMED part ) ,
527CAST( '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 )
528FROM SYSADMIN.COMPRESSION_TABLEINFO_tbl a , dbc.columns b
529WHERE a.tablename = b.tablename
530AND a.databasename = b.databasename
531AND LOGDATE > DATE - 2
532GROUP BY 1 , 2
533
534
535UNION ALL
536
537SELECT 8 (NAMED part ) ,
538CAST( '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 )
539FROM (
540SELECT a.DATABASENAME (NAMED DATABASENAME ) , a.TABLENAME (NAMED TABLENAME ) ,
541b.VIEWDATABASENAME , b.ETLVIEWDATABASENAME
542FROM META.TABLES a , META.DATABASES b , SYSADMIN.COMPRESSION_TABLEINFO_tbl c
543WHERE a.DATABASENAME = b.DATABASENAME
544AND a.TABLENAME = c.TABLENAME
545AND ( b.DATABASENAME = c.DATABASENAME
546OR b.VIEWDATABASENAME = c.DATABASENAME
547OR b.ETLVIEWDATABASENAME = c.DATABASENAME )
548AND LOGDATE > DATE - 2
549GROUP BY 1 , 2 , 3 , 4 ) tmp
550GROUP BY 1 , 2
551
552
553UNION ALL
554
555SELECT 9 (NAMED part ) ,
556CAST( '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 )
557FROM (
558SELECT a.DATABASENAME (NAMED DATABASENAME ) , a.TABLENAME (NAMED TABLENAME ) ,
559b.VIEWDATABASENAME , b.ETLVIEWDATABASENAME
560FROM META.TABLES a , META.DATABASES b , SYSADMIN.COMPRESSION_TABLEINFO_tbl c
561WHERE a.DATABASENAME = b.DATABASENAME
562AND a.TABLENAME = c.TABLENAME
563AND ( b.DATABASENAME = c.DATABASENAME
564OR b.VIEWDATABASENAME = c.DATABASENAME
565OR b.ETLVIEWDATABASENAME = c.DATABASENAME )
566AND LOGDATE > DATE - 2
567GROUP BY 1 , 2 , 3 , 4 ) tmp
568GROUP BY 1 , 2
569
570
571UNION ALL
572
573SELECT 10 (NAMED part ) ,
574CAST( '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 )
575FROM dbc.All_RI_Parents , SYSADMIN.COMPRESSION_TABLEINFO_tbl
576WHERE DATABASENAME = childdb
577AND TABLENAME = childtable
578GROUP BY 1 , 2
579) tmp;
580
581Insert 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);
582
583
584--Step 9
585
586-- Apply Compression
587
588FOR REC4 AS TEST CURSOR FOR
589Sel Script
590from sysadmin.COMPRESSION_Script
591order by part
592
593DO
594SET VARIABLE6 = trim(REC4.Script);
595
596/*
597For actual execution UNCOMMENT CALL DBC.SYSEXECSQL script , else manually run the script " sel * from sysadmin.COMPRESSION_Script order by 1"
598**************************************************************************************************************************************************************
599*/
600--CALL DBC.SYSEXECSQL (VARIABLE6);
601
602END FOR;
603
604Insert into sysadmin.COMPRESSION_SP_Status ('Step 9: Compression Applied ',current_timestamp);
605
606Set p_SuccessOrFail='Success: Compression Procedure ran Successfully.';
607
608OPEN RESULTS1;
609OPEN RESULTS2;
610
611END L1;