Internal Storage Options Teradata

A frustrated PC user was once quoted as saying, "Who's General Failure and why's he reading my disk?" Most people don't know what is going on inside the disks and in most cases just don't care. In a data warehouse environment having the ability to influence how data is stored inside the disks can be a great advantage. Some tables will be utilized by applications designed to read millions of records while others will operate in an OLTP type environment where updating single records is the focus.

Teradata gives the table creator the ability to influence how the data is stored on the disk. The two optional table level attributes are DATABLOCKSIZE and FREESPACE PERCENTAGE. If either of these options is not specified in the CREATE TABLE, they default to values established in the DBC Control record at the system level. Unless you are a Teradata expert let the system defaults do their job. However, if you can understand the concepts that are about to be explained, as well as your application requirements, you can customize your disk environment to maximize effectiveness.

DATABLOCKSIZE

The DATABLOCKSIZE determines a maximum block size for multiple row storage on disk. The data block is the physical I/O unit for the Teradata file system. Larger block sizes enhance full table scan operations by retrieving more rows in a single I/O. Smaller block sizes are best for on-line transaction-oriented tables to minimize overhead by retrieving smaller blocks. You can specify this number in BYTES, KILOBYTES or KBYTES. BYTES specifications are rounded to the nearest sector of 512 BYTES. KBYTES and KILOBYTES are in set increments of 1024 BYTES.

Teradata is designed to take advantage of DATABLOCKSIZE settings because of the unpredictable nature of the data warehouse environment. While most databases ask that you define table space Teradata does not. This is because Teradata uses variable length blocks. This means that when a table is small it is allocated a few disk sectors at 512 bytes each. Then, as the table grows, the number of sectors used by the block grows with the new rows until it reaches the maximum block size. It is like a balloon. As more air is inserted, the balloon expands. Eventually the balloon gets to the maximum size and the only way to save more air is to get a second balloon.

Teradata's variable block design allows the system to handle the space instead of making the DBA do it. The DATABLOCKSIZE is the setting that determines when it is time to get additional blocks (balloons). On large data warehouse applications that read millions of rows it is best to have a few big balloons. On applications that update on one or two records at a time it is better to have many smaller balloons.

If DATABLOCKSIZE is not specified, the size used is a default of either 32256 (63 sectors) or 65024 (127 sectors), depending on the cylinder size setting. Cylinder size is a systemlevel value shown as SectsPerCyl with the Filer utility or Number of Disk Blocks Per Cylinder with the pdeconfig utility. Prior to V2R3.0 the default cylinder setting was 1488 sectors. With V2R3.0, the size increased the default cylinder setting to 3872 sectors.

Additionally, the block size is only the maximum when there is more than one row in a block. If a single row exceeds the DATABLOCKSIZE setting it is stored in its own data block. The block will be the length of the row and rows are never split into multiple data blocks.

MINIMUM DATABLOCKSIZE sets the minimum or smallest data block size for blocks that contain multiple rows to the minimum legal value of either 6144 or 7168 bytes (12 sectors or 14 sectors), depending on the cylinder size setting in the DBS Control Record. You can use the keywords of MINIMUM DATABLOCKSIZE or MIN DATABLOCKSIZE to change the default.

MAXIMUM DATABLOCKSIZE is the largest possible DATABLOCKSIZE setting for the table, which is 65024 bytes (127 sectors). You can use the keywords MAXIMUM DATABLOCKSIZE or MAX DATABLOCKSIZE to change the default.

Normally, larger block sizes require less physical disk space. This is because large blocks contain more rows. Therefore, fewer blocks are needed and since each block requires a block header and control data, less space needs to be allocated for them.

FREESPACE PERCENTAGE

The FREESPACE PERCENTAGE tells the system at what percentage Teradata should keep a cylinder free of rows on data loads when using Fastload and Multiload load utilities. A FREESPACE 10 PERCENT keeps 10% of a cylinder's sector space free when loading the data. Valid values for the percentage of free space range from 0-75.

The value used for FREESPACE should mimic the usage of the table. As the number of rows being inserted by clients, other than Fastload and Multiload increases, the value of FREESPACE might also increase. This causes the secondary row inserts to execute faster because space is already available on disk to store the new rows in the same cylinder. Therefore, fewer cylinder splits will occur as a result of insufficient space at insert time. In other words, we don't blow the "balloon" all the way up. We know we will be utilizing SQL to do more inserts and we want extra room for the balloon to expand.

On the other hand, if the tables only receive rows loaded by Fastload and Multiload, the FREESPACE value can be set to 0 for maximum utilization of the disk space within each cylinder since inserts are not performed by other clients. If we are not going to use SQL commands to insert additional data we can blow the balloon all the way up because it won't need to expand any further.

Since these parameters have defaults kept in system parameters as part of the DBS Control record, they are seldom used in the CREATE TABLE statement, but Teradata gives you the option of over-riding the default on any particular table.

The following CREATE TABLE specifies FALLBACK and establishes values for both DATABLOCKSIZE and FREESPACE:

As previously mentioned, if you don't specify a DATABLOCKSIZE or FREESPACE PERCENT then the system builds the table using the default parameters for DATABLOCKSIZE and FREESPACE PERCENT found in the DBS Control Record. Never specify these without a discussion with your database administrator. Remember, to change and to change for the better are two different things. If you don't know it – don't blow it!


Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics