JournalDBSize - Advanced SQL Engine - Teradata Database

Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
June 2020
Language
English (United States)
Last Update
2020-06-15
dita:mapPath
boh1556732696163.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1102
lifecycle
previous
Product Category
Teradata Vantageā„¢

Purpose

Determines the maximum size of permanent journal table multirow data blocks (DBs), in 512-byte sectors. For tables using journaling, rows are written to this journal during INSERT, UPDATE, and DELETE processing. Rows that are larger than JournalDBSize are stored in single-row data blocks, which are not limited by JournalDBSize.

Field Group

File System

Valid Range

18 through 512 sectors for systems that use small cylinders.

42 through 2047 sectors for systems that use large cylinders.

A sector is 512 bytes.

Default

254 sectors

Setting this field to 0 causes Teradata Database to use the system default size for this type of data block.

Changes Take Effect

After the DBS Control Record has been written.

Usage Notes

When database tables are initially populated, Teradata Database stores as many rows as possible into each data block, until the block reaches the size specified by the various DB size settings in DBS Control. As tables are subsequently modified, rows can grow such that the existing data blocks would exceed the maximum size. When this happens, the data block is split, and roughly half the rows are moved to a new data block, with the result that the original and new data blocks are each one half of the original size. The result of this type of growth and splitting is that data blocks for heavily modified tables tend to be about 75% of the maximum size defined in DBS Control.

If DBs are compressed, this setting applies to the size of the uncompressed DBs.

If table rows are very long, or many rows are being manipulated, try increasing JournalDBSize. A larger size also can produce significant savings if the system is I/O bound.

In general, the maximum multirow data block size for journals should agree with the data row length. If the modified rows are short, the journal data block size can be small. If the modified rows are long, the journal data block size can be large.

If you base data block size on processing activity, the following rules are generally successful for good performance when the workload is mixed:

  • PermDBSize should be a large number to optimize decision support, especially for queries involving full table scans. See PermDBSize.
  • JournalDBSize should be a low number to benefit analytic functions and High-Availability Transaction Processing (HATP) operations.