Skip to content

Posts tagged ‘Ferret Utility’

How to check table compression (block level) in Teradata?

How to check table compression (block level) in Teradata?

1) From Viewpoint open Remote Console and connect to Ferret Utility

2) Under Ferret Utility set scope to the table for which you want to check compression.

SCOPE table “DB_Name.Table_Name” *

NOTE: * at the end is needed.

Ferret ==>
SCOPE table “DB_Name.Table_Name” *The SCOPE has been set

3) Now run the command Showblocks

Ferret ==>
showblockShowblocks has been started on all AMP vprocs in the SCOPE.

SCRIPT MODE is Enabled.
ABORT command will not be allowed

+————+——+———-+————+—————————————————————————–+—————–+————+———–+
| |Compr-|Estimated |Estimated % | Distribution of data block sizes | Data block | Total | Total |
| |ession| Compres- | of Blocks | (by range of number of sectors) | size statistics | Number | Number |
| Table ID |Status| sion | Un- +—-+—-+—-+—-+—-+—-+—-+—-+—-+—-+—-+—-+—–+—–+—–+ (sectors) | of | of |
| | | Ratio | compressed | 1-| 9-| 25-| 65-|121-|169-|217-|257-|361-|457-|513-|761-|1025-|1305-|1633-|—–+—–+—–+ Data | Cylinders |
| | | | | 8 | 24 | 64 |120 |168 |216 |256 |360 |456 |512 |760 |1024|1304 |1632 |2048 | Min | Avg | Max | Blocks | |
+————+——+———-+————+—-+—-+—-+—-+—-+—-+—-+—-+—-+—-+—-+—-+—–+—–+—–+—–+—–+—–+————+———–+
| 1 11195 | C | 41.92% | 0.00% | | | | | | | | 75%| 5%| 1%| 19%| | | | | 264 | 341 | 565 | 3112897 | 51494*|
+————+——+———-+————+—-+—-+—-+—-+—-+—-+—-+—-+—-+—-+—-+—-+—–+—–+—–+—–+—–+—–+————+———–+
* Reported in units of Large Cylinders
(A Large Cylinder is 6 times the size of a small cylinder)

“Compression Status” : C = Fully Compressed
PC = Partially Compressed
U = Fully Uncompressed
N = Not Compressible

Here you can see that Table1 have a Table id of 11195 and is 41.92% compressed.

NOTE: If you want to check compression for multiple tables, either follow the above 3 setups for each table or use below command to set Scope to all the required tables:

scope table (“DB_Name.Table_Name” *, “DB_Name.Table_Name” *, “DB_Name.Table_Name” *)