Skip to content

Posts from the ‘Teradata’ Category

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” *)

Teradata – DBQL DETAIL TABLES did not load

ISSUE:

PDCR job failed with below error:
LD_Error6: The DBQL DETAIL TABLES did not load. SqlState = ‘T2646’, SqlCode = 2646 when executing SQL statement number 106

or

[Teradata Database] [TeraJDBC 15.10.00.09] [Error 2646] [SQLState HY000] No more spool space in PDCRAdmin.

DESCRIPTION:

PDCR job will fail with this error if spool for PDCRADMIN is not big enough. Try to increase the spool for PDCRAdmin user and re-run the job. No other action is needed to fix this issue.