Purpose
Specifies the maximum size for multirow data blocks in permanent tables. Rows that are larger than PermDBSize are stored in single-row data blocks, which are not limited by PermDBSize.
Field Group
File System
Valid Range
42 through 2047 sectors for systems that use large cylinders
18 through 512 sectors for systems that use small 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
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.
PermDBSize and System Performance
Database performance can be affected by the relationship of data block size to the type of work typically performed by the database:
- When database queries are tactical in nature, involving one or a few table rows, it is advantageous to have fewer rows stored per data block to speed data access. Online transaction processing (OLTP) is an example of this type of work.
- Alternatively, when database queries are strategic in nature, involving complex queries that involve many table rows per table, it is advantageous to have many rows stored in each data block, to minimize costly data I/O operations. Decision support software (DSS) and complex report generation are examples of this type of work.
PermDBSize sets the default maximum size used by the system for multirow data blocks in permanent tables. Use a larger value if the database is used primarily for strategic work, and a smaller value if the database is used primarily for tactical work.
In a mixed-work environment, determine a value for PermDBSize based on the kind of work typically performed by the database. For tables involved in other types of work, PermDBSize can be overridden on a table-by-table basis using the DATABLOCKSIZE option of the CREATE TABLE and ALTER TABLE SQL statements.