15.00 - DATABLOCKSIZE - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

DATABLOCKSIZE

DATABLOCKSIZE sets the maximum data block size for blocks that contain multiple rows. The data block is the physical I/O unit for the Teradata file system.

Larger block sizes enhance full table scan operations by selecting more rows in a single I/O. Smaller block sizes are best for transaction-oriented tables to minimize overhead by retrieving only what is needed.

A row that is larger than the DATABLOCKSIZE setting is stored in its own data block. Teradata Database does not split rows across data blocks.

A sector is normally 4 KB. If the computed value for the DATABLOCKSIZE attribute does not fall within the allowable range, the request aborts and the system returns an error message to the requestor.

If you do not specify a value for DATABLOCKSIZE, then Teradata Database uses the system‑wide default data block size specified by the PermDBSize field in the DBS Control record (see Utilities: Volume 1 (A-K) for information about the DBS Control record). The default value is typically 254 sectors and the maximum is 255 sectors for all systems.

The default DATABLOCKSIZE settings depend on the cylinder size that is set for your system. Cylinder size is a system-level value reported as SectsPerCyl by the Filer utility (see Support Utilities).

 

FOR a system that is …

YOU can specify a DATABLOCKSIZE value of no fewer than …

not running with large cylinders

8,960 bytes (17.5 sectors), but Teradata Database rounds the value up to 9,216 bytes (18 sectors) internally, and that is the actual data block size the system uses.

running with large cylinders

21,248 bytes (41.5 sectors), but Teradata Database rounds the value up to 21,504 bytes (42 sectors) internally, and that is the actual data block size the system uses.

Note the following release‑specific rules for minimum data block sizes.

 

IF your system …

THEN it uses …

was upgraded to release 13.10 from either release 12.0 or release 13.0 using the standard conversion process but has not been reinitialized using the sysinit utility

small cylinders.

The minimum DATABLOCKSIZE Teradata Database enforces for such a system is 9,216 bytes, or 18 sectors.

CREATE TABLE requests accept a specified DATABLOCKSIZE value of 8,960 bytes (17.5 sectors); however, Teradata Database rounds that value up internally to the actual minimum DATABLOCKSIZE of 9,216 bytes.

This rule is also true for systems reinitialized using the sysinit utility on release 13.10, but whose vconfig.txt was configured to run without large cylinders.

is newly installed or has been upgraded to release 13.10 and reinitialized using the sysinit utility

large cylinders.

The minimum DATABLOCKSIZE value Teradata Database enforces for such a system is 21,504 bytes, or 42 sectors.

CREATE TABLE requests accept a minimum DATABLOCKSIZE of 21,248 bytes (41.5 sectors); however, Teradata Database rounds that value up internally to the actual minimum DATABLOCKSIZE of 21,504 bytes.

The DATABLOCKSIZE value returned in response to a SHOW TABLE request is either the value specified in the CREATE TABLE request for the table or the value specified in the most recently entered ALTER TABLE request.

For additional information about the data block size, see:

  • Database Administration
  • Database Design
  • Utilities: Volume 1 (A-K)