How to run compress/uncompress command via ferret in Teradata
In that blog we will see how to compress/uncompress table via ferret. Following things has been shown in that blog:
1. How to compres/uncompress singe table via ferret
2. How to compress/uncompress command via ferret in batch mode
1. How to compres/uncompress singe table via ferret
cnsterm 6
Attempting to connect to CNS...Completed.
Hello
Input Supervisor Command:
> start ferret
start ferret
Started 'ferret' in window 1
at Thu May 16 08:50:32 2019
Input Supervisor Command:
> ^C
# cnsterm 1
Attempting to connect to CNS...Completed.
Hello
Ferret ==>
quit
Waiting for Ferret Slave Tasks to exit
Ferret Exited
Ferret Utility
_______
| | |
| ___ __ ____ | ____ __|__ ____
| / |/ \ ____| ____| ____| | ____|
| --- | / | / | / | | / |
| \___ | \____| \____| \____| |__ \____|
Release 16.20.24.01 Version 16.20.24.01
Ferret
Waiting for 229 Ferret background tasks to start
All Background tasks have been started
FERRET will run in Workload Def: WD-Default
The SCOPE has been set
Ferret ==>
> scope table "PDCRDATA"."DBQLogTbl_Hst"*
scope table "PDCRDATA"."DBQLogTbl_Hst"*
The SCOPE has been set
Ferret ==>
showblocks
-- Uncompress Table
Ferret ==>
> uncompress "PDCRDATA.DBQLogTbl_Hst"
uncompress "PDCRDATA.DBQLogTbl_Hst"
Thu May 16, 2019 09:00:06 : UNCOMPRESSion of data blocks will be started for all subtables of
PDCRDATA.DBQLogTbl_Hst
Do you wish to continue...? (Y/N)
> Y
Y
Thu May 16, 2019 09:00:14 : UNCOMPRESS has been started On all AMP vprocs
Type 'ABORT' to stop the command before completion
Type 'INQUIRE' to check on progress of command
> INQUIRE
INQUIRE
Inquire request has been sent
Thu May 16, 2019 09:06:02
Slowest vproc 40 is 10% done
Fastest vproc 185 is 20% done
The UNCOMPRESS is about 14% done
Type 'ABORT' to stop the command before completion
Type 'INQUIRE' to check on progress of command
Thu May 16, 2019 09:29:13: Allocated 63428 ( 00F7C4 ) large cylinders
(A Large Cylinder is 6 times the size of a small cylinder)
UNCOMPRESS has completed
Ferret ==>
showblocks
showblocks
-- Compress Table
Ferret ==>
compress "PDCRDATA.DBQLogTbl_Hst"
compress "PDCRDATA.DBQLogTbl_Hst"
Mon May 20, 2019 08:30:47 : COMPRESSion of data blocks will be started for all subtables of
PDCRDATA.DBQLogTbl_Hst
Do you wish to continue...? (Y/N)
> Y
Y
Mon May 20, 2019 08:30:52 : COMPRESS has been started On all AMP vprocs
Type 'ABORT' to stop the command before completion
Type 'INQUIRE' to check on progress of command
> INQUIRE
INQUIRE
Inquire request has been sent
Mon May 20, 2019 08:31:21
Slowest vproc 106 is 0% done
Fastest vproc 233 is 1% done
The COMPRESS is about 0% done
Type 'ABORT' to stop the command before completion
Type 'INQUIRE' to check on progress of command
>
Mon May 20, 2019 09:07:25 : Freed up 393806 ( 06024E ) large cylinders
(A Large Cylinder is 6 times the size of a small cylinder)
COMPRESS has completed
Ferret ==>
showblocks
2. How to compress/uncompress command via ferret in batch mode
If we want to compress/uncompress many table then manually do it one by one will take time. Following way we can compress/uncomress tables in many database at once.
Run cnsrun command on PDN to run ferret in batch mode:
# cnsrun -utility ferret -file command.txt -log uncompress.log -nostop -prompt '>' &
Describe UNCOMPRESS command in 'command.txt' like below:
uncompress /Y "DB1.*"
uncompress /Y "DB2.*"
...
quit
"databasename.*" specifies all tables in the database.
you can see the progress in file 'uncompress.log'
NOTE: you cannot abort un/compression when you run ferret in batch mode.
You can abort compress/uncompress command when you run it in interactive mode.
But ferret just goes to the next table when you run the command at database level.
e.g. you run compress /y "DB1.*" to compress all tables in DB1 and you abort it while compressing DB1.Tbl1
ferret just goes to compress the next table(e.g. DB1.Tbl2). ABORT command does not abort entire compress operation.
How to find table compressed with BLC
> SHOWCOMPRESS
