BLOCK LEVEL COMPRESSION IN TERADATA

Share via:

Dear Readers,

In this article, we will learn about BLOCK LEVEL COMPRESSION IN TERADATA.

Block Level Compression (BLC) is a space reduction technique in Teradata. It has been introduced in 13.10 and is used to apply compression to all the permanent data blocks in a table. The compression rate can be achieved by this technique is 60% on an average.

How BLC works:

Data is stored in the disk array in the form of data blocks and this data blocks are stored in the cylinder. Currently Teradata uses Lempel-Ziv algorithm for block level compression. After applying the BLC, each cylinder will hold more numbers of data blocks in it.

Decompression in BLC:

Once the block level is applied in a table, if a query needs to access a single row also, the entire data block needs to be decompressed first. In order to decompress the data block, the compressed version of the data block first will be brought into the FSG cache which is dedicated to the data block, and then decompress into its original version.

One important thing is that this decompressed data block in FSG cache is not sharable across other sessions. That means if some other user wants data from the same data block which is present in FSG cache, needs to decompress the data block again to access data.

Here are the steps to implement BLC

1.Turn on compression in DBSControl

a.ssh into the main node i.Note: When starting a Linux session, run tdatcmd from the command line to set up the

Teradata Database environment.

b.Rundbscontrol from the command line

c.InDBSControl, enter ‘DISPLAY COMPRESSION’ to display the compression setting fields (Figure 1 below)

d.To turn on compression, type ‘MODIFY COMPRESSION 1=ON’

e.To control how aggressively compression is applied, modify the CompressionLevel field i.Default value is 6

ii.Higher values will compress tables more aggressively, but will require more CPU overhead when querying

iii.Type ‘MODIFY COMPRESSION 10=#’ to change the CompressionLevel field

f.To commit changes, enter ‘WRITE’

g.Type ‘QUIT’ to exit DBSControl

2.Apply compression to desired tables using the Ferret Utility

a.Start Ferret from the Remote Console i.Using Viewpoint

1.Open the Remote Console widget within Viewpiont

2.Select the Teradata system you want to administer

3.Select the utility you want to run (Ferret)

ii.Using the command line from the node 1.ssh into the main node

2.Issue the tdatcmd to setup the Teradata environment

3.Enter ‘cnsterm 6’ to connect to the CNS Terminal and input Supervisor Commands (Figure 2 below)

4.Enter ‘start ferret’ and make note of which window it says ferret is started in (Will probably be window 1)

5.Type Ctrl-C to exit back out to the command prompt

6.Enter ‘cnsterm<window #>’ to enter Ferret Utility (Figure 3 below)

b.Once in Ferret (Figure 3 below) i.Type ‘enable scriptmode’ to turn script mode on

ii.Enter ‘compress “<database_name>.<table_name>”;

iii.Wait for the compression to complete and then compress the next table (you will not be able to see the updated table size in TD Administrator at this point)

iv.Once all tables are compressed, type “quit;” to exit Ferret and ‘Ctrl-C’ to exit back to the command prompt

3.Use the Update Space Utility to show the new compressed table sizes

a.From the command prompt, enter ‘cnsterm 6’ to start the CNS Terminal Supervisor Command utility

b.Type ‘start updatespace’ and note the window it says the Update Space Utility is started in

c.Enter ‘Ctrl-C’ to exit the CNS Terminal

d.From the command prompt, type ‘cnsterm<window#> to enter the Update Space Utility

e.Enter ‘update space for all databases;’ to update the tablespace in DBC

f.Type ‘quit;’ when the update is completed to exit the utility

4.Applying BLC using Query Band

a.As an alternative to ferret, Query Banding can be used to apply BLC i.NOTE: THIS ONLY WORKS ON EMPTY TABLES

ii.Again, BLC must be turned on in DBSControl for this to work

iii.In the field, it has been noticed that better compression results are obtained through the use of ferret

b.Prior to submitting your insert statement, issue the following command: SET QUERY_BAND = ‘BlockCompression=Yes;’ FOR SESSION;

c.If the table is not empty, the set Query Band command will be ignored.

DBSControlSettings:

Enter a command, HELP, or QUIT:

DISPLAY COMPRESSION

DBS Control Record – Compression Fields:

  1. BlockLevelCompression     = ON
  2. CompressPermDBs           = ONLYIFQBYES (UNLESSQBNO, NEVER, ONLYIFQBYES)
  3. CompressSpoolDBs          = NEVER       (ALWAYS, NEVER, IFNOTCACHED)
  4. CompressGlobalTempDBs     = NEVER       (UNLESSQBNO, NEVER, ONLYIFQBYES)
  5. CompressMloadWorkDBs      = NEVER       (UNLESSQBNO, NEVER, ONLYIFQBYES)
  6. CompressPJDBs             = NEVER       (ALWAYS, NEVER)
  7. MinDBSectsToCompress      = 16
  8. MinPercentCompReduction   = 20%
  9. CompressionAlgorithm      = ZLIB        (ZLIB)
  10. CompressionLevel          = 6           (1-9; Default value = 6)
  11. (Reserved for future use)
  12. UncompressReservedSpace   = 20%

Example :

 

 

Use below query to get the list of BLC compressed tables:

If the query doesn’t return the results as expected, collect stats on the PI with the below syntax and rerun the query.

 

After TD16.0, dbc.statstbl reserved2 column name was changed to  BLCCompRatio, please modify it.

Limitations on BLC:

  • Strictly used to reduce storage space
  • Does not have any performance benefits
  • Can be very costly due to the following factors:
  • Initial cost of applying the compression
  • Ongoing cost of decompressing data blocks when they are accessed
  • Ongoing cost of recompressing data blocks after they have been modified
  • Should not apply for tables that contain WARM or HOT data if they have a CPU utilization rate greater than 80%
  • Extent of compression that can be achieved depends on the characteristics of the data contained within a data block as well as the maximum size that has been specified for permanent data blocks
  • Individual data blocks within a table might have different size reduction rates, so data blocks frequently show more size variance after being block‑level compressed than they did in their original state

 

  • Best not used in combination with algorithmic compression because of the combined cost of their decompression
  • Cannot use for data dictionary tables, WAL data blocks, cylinder indexes, table headers, or other internal file structures
  • If either multi‑value compression or algorithmic compression is defined on a table, the extent of storage reduction achieved by adding block‑level compression is less than if no compression had been present at the time block‑level compression was added
  • Works optimally for data blocks defined with the maximum size of 127.5KB
  • Larger maximum data block sizes at the time of compression allow cylinders to be more fully utilized, which can also impact the degree of compressibility

 

As a table matures, it experiences updates and data block splits, which reduce the size of some data blocks

During the compression process, Teradata Database reforms data blocks into their maximum size

With larger block sizes defined, Teradata Database can achieve a greater degree of block‑level compression

Thank you for giving your valuable time to read the above information.
Follow us on 
Website  www.ktexperts.com
Facebook Page KTExperts Facebook
Linkedin Page : KT EXPERTS Linkedin

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

Add Comment