15.00 - JournalDBSize - Teradata Database

Teradata Database Utilities

Product
Teradata Database
Release Number
15.00
Content Type
Configuration
Publication ID
B035-1102-015K
Language
English (United States)
Last Update
2018-09-25

JournalDBSize

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 255 sectors for systems that use small cylinders

42 through 255 sectors for systems that use large cylinders

A sector is 512 bytes.

On cliques with 4 KB aligned devices, the setting is rounded up to the next 8-sector multiple.

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

Changing this setting may affect system performance.

The PermDBSize and JournalDBSize settings in DBS Control apply to the uncompressed size of these types of DBs.

Systems initialized on Teradata Database 13.10 or later release use large cylinders. On these systems, the minimum data block size is 42 sectors (21504 bytes). The minimum data block size accepted by the CREATE TABLE and ALTER TABLE SQL statements is 21248 bytes (41.5 sectors). Tables created or altered to use this minimum size will have the value rounded up by the parser to 42 sectors, so these tables will have an actual minimum data block size of 21504 bytes.

Systems initialized on a Teradata Database release prior to 13.10 and subsequently upgraded use small cylinders. On these systems, the minimum data block size is 18 sectors (9216 bytes). The minimum data block size accepted by the CREATE TABLE and ALTER TABLE statements is 8960 bytes (17.5 sectors).

Tables created or altered to use this minimum size will have the value rounded up by the parser to 18 sectors, so these tables will have an actual minimum data block size of 9216 bytes.

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” on page 402.
  • JournalDBSize should be a low number to benefit analytic functions and High-Availability Transaction Processing (HATP) operations.
  • Note: The PermDBSize and JournalDBSize fields set the maximum multirow block size. A row that exceeds the current default size of a multirow data block is put into a block of its own.