Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS Database Administration - NCR

NCR Teradata RDBMS Database Administration - NCR , 2004. - 616 p.
Download (direct link): teradatadatabaseadmin2004.pdf
Previous << 1 .. 52 53 54 55 56 57 < 58 > 59 60 61 62 63 64 .. 218 >> Next

permanent space one or more free cylinders succeeds
less than one free cylinder fails with a disk full error
spool space more free cylinders than the amount specified in Cylinders Saved for PERM succeeds
less free cylinders than the amount specified in Cylinders Saved for PERM fails with a disk full error

This means that requests needing spool space might fail more often than requests needing perm space. This can be advantageous because failure of a request for spool space rarely involves a rollback.

Setting Data Block Size Limits

In the Teradata RDBMS file system, a data block is a segment of one or more data rows preserved on disk. This segment contains the actual data rows, all of which are members of the same subtable. Any single data row is fully contained within a single data block. Rows are not split across data blocks. A row that exceeds the size of a multi-row data block is put in a block of its own.

Every data block must be fully contained within a cylinder. The cylinder is a group of consecutive disk sectors (also called segments) that is not necessarily identical to a physical disk cylinder. The file system normally uses an averaged cylinder size, and each cylinder is logically independent from other cylinders.

You can set the maximum size of data blocks with the following tools: I ___

Tool Parameter Description
DBS Control Utility File System fields of DBSCONTROLGDO: PermDBAllocUnit PermDBSize JournalDBSize Global parameters used to determine the maximum size of permanent data blocks that hold multiple rows. (A row that exceeds the size of a multi-row data block is put into a block of its own.) You can override this value at the table level. Note: The default and/or minimum data block size depends on the cylinder size used for your site. Cylinder size is a global value, shown as: SectsPerCyl (with the Filer Utility) Number of Disk Blocks Per Cylinder (with the PDE Config Utility)

3 - 2

Teradata RDBMS Database Administration Chapter 3: Space Considerations

Defining Permanent Space Limits

Tool Parameter Description
CREATE/ ALTER TABLE Statement DATABLOCKSIZE = n [BYTES/KBYTES/ KILOBYTES] n is an integer that sets the maximum multi-row data block size to be used when storing rows of this table. For this parameter to improve performance, the row length should be short enough to write two or more rows to one data block.

Setting Free Space Percent Limits

Free Space Percent is the percentage of cylinder space to leave free during load operations. You can control this space at the global level and the table level:

Tool Parameter Description
DBS Control Utility (File System Fields) FreeSpace Percent Global parameter used by DBS and the File System to determine the percentage of space to leave unused on each cylinder during bulk loading operations such as MultiLoad and FastLoad. This default can be overridden at the table level with the FREESPACE clause.
CylindersSaved-forPERM Used to save some number of cylinders for permanent data only. Note: If the number of free cylinders falls below this value, any allocation of cylinders for spool data results in an abort of the requesting transaction.
CREATE TABLE Statement FREESPACE = n n percent of cylinder space to remain free on each cylinder when bulk loading this table (where n is an integer constant).
ALTER TABLE Statement The options to this statement let you reset the current freespace percent for a table at the global or table level:
Use this option ... To reset Free Space to ...
DEFAULT FREESPACE the value defined in the FreeSpacePercent field of the DBS Control Record GDO.
FREESPACE = n [PERCENT] a new percentage of space to leave free on each cylinder when bulk loading this table.


3 - 2 Teradata RDBMS Database Administration

Chapter 3: Space Considerations

Defining Permanent Space Limits

Specifying Spool Space Limits

Spool space is used to hold intermediate rows during processing, and to hold the rows in the answer set of a transaction.

Spool Space and Capacity Planning

Spool space is critical to the operation of Teradata RDBMS, yet it is frequently overlooked in capacity planning. Size requirements vary from user to user, table to table, and application to application. For example:

The spool space of a user is used to hold the response rows of every query run by that user during a session. Thus, each user needs a high enough spool allocation to contain the biggest anticipated answer set.

Very large tables usually require more available spool space than smaller tables, because intermediate rows are held in spool space during query execution.

Spool Space Categories

Spool falls into three categories of space: volatile, intermediate, and output.

Type Description
Volatile Spool Volatile spool is retained until the: Transaction completes (unless the table was created with ON COMMIT PRESERVE ROW) Table is dropped manually during the session Session ends Teradata RDBMS resets
Previous << 1 .. 52 53 54 55 56 57 < 58 > 59 60 61 62 63 64 .. 218 >> Next