15.00 - DBS Control Fields (Settings) - 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

DBS Control Fields (Settings)

The following table summarizes the DBS Control fields and indicates their groups. The fields are described in more detail in the sections that follow.

Note: The default settings documented in this chapter apply to the Teradata Active Enterprise Data Warehouse platform. Default settings on other purpose-built platforms, such as data appliances, may be different.

 

Field

Description

Group

“AccessLockForUncomRead”

Determines whether SELECT statements embedded in INSERT...SELECT, UPDATE...SELECT, DELETE...SELECT statements place READ or ACCESS locks on the source table.

General

“All Other Temperature”
see
“Storage Group”

Determines the initial temperature assigned to data loaded or inserted into tables of a type not otherwise specified by Storage group settings.

Storage

“AutoCylPackColdData”

Determines whether AutoCylPack operations are performed on cylinders storing cold (infrequently accessed) data, in addition to cylinders storing more frequently accessed data.

Note: This field is effective only if you have purchased Teradata Virtual Storage. For more information, see Teradata Virtual Storage.

File System

“AutoCylPackFreeCylThresh”

The number of free cylinders below which AutoCylPack will not operate.

File System

“AutoCylPackFSP”

The percentage of storage space within a cylinder that AutoCylPack operations leave free of data, to allow for future table growth.

File System

“AutoCylPackInterval”

The amount of time that the AutoCylPack task waits between checks for new AutoCylPack work.

File System

“AutoCylPackIOThresh”

The number of pending I/O operations above which the AutoCylPack task waits for I/Os to complete before continuing.

File System

“AutoCylPackStyle”

Determines whether the AutoCylPack task is restricted to packing data more tightly on cylinders, unpacking data, or if it is unrestricted, and can both pack and unpack data to achieve the target FSP.

File System

“AutoCylPackThresh”

The threshold difference between actual free space on a cylinder and the target AutoCylPack FSP, below which AutoCylPack will not process the cylinder.

File System

“Bkgrnd Age Cycle Interval”

Determines the amount of time that elapses between background cycles to write a subset of modified segments in the cache to disk.

File System

“BlockLevelCompression”

Indicates whether the block-level compression (BLC) feature is enabled.

Compression

“Century Break”

Defines how to interpret character-format Teradata Database date input when the input format only has two digits representing the year.

General

“ChecktableTableLockRetryLimit”

Specifies that in nonconcurrent mode, CheckTable will retry a table check for a specified limit when the table is locked by another application.

General

“Client Reset Timeout”

Specifies how long the Relay Services Gateway (RSG) should wait for an intermediary to reconnect before taking action.

General

“CompressGlobalTempFallbackDBs”

Specifies conditions under which Global Temporary Table data blocks in fallback subtables will be compressed by default.

Compression

“CompressGlobalTempFallbackCLOBDBs”

Specifies conditions under which Global Temporary Table CLOB data blocks in fallback tables will be compressed by default.

Compression

“CompressGlobalTempPrimaryDBs”

Specifies conditions under which Global Temporary Table data blocks in primary data subtables will be compressed by default.

Compression

“CompressGlobalTempPrimaryCLOBDBs”

Specifies conditions under which Global Temporary Table CLOB data blocks in primary data subtables will be compressed by default.

Compression

“CompressionAlgorithm”

Specifies the algorithm to use for compressing DBs.

Compression

“CompressionLevel”

Determines whether DB compression favors speed or degree of compression.

Compression

“CompressionZLIBMethod”

Determines whether the ZLIB compression uses the Intel Integrated Performance Primitives library for better performance on Intel platforms.

Compression

“CompressPermFallbackDBs”

Specifies conditions under which permanent data blocks in fallback subtables will be compressed by default.

Compression

“CompressPermFallbackCLOBDBs”

Specifies conditions under which permanent CLOB data blocks in fallback subtables will be compressed by default.

Compression

“CompressPermPrimaryDBs”

Specifies conditions under which permanent data blocks in primary data subtables will be compressed by default.

Compression

“CompressPermPrimaryCLOBDBs”

Specifies conditions under which permanent CLOB data blocks in primary data subtables will be compressed by default.

Compression

“CompressPJDBs”

Specifies whether Permanent Journal DBs will be compressed by default.

Compression

“CompressSpoolDBs”

Specifies conditions under which spool and volatile data blocks will be compressed by default.

Compression

“CompressMloadWorkDBs”

Specifies conditions under which work data blocks will be compressed by default.

Compression

“CostProfileId”

Contains the system standard cost profile ID number, which defines the cost profile that the system will use.

General

“CurHashBucketSize”

Indicates the number of bits used to identify a hash bucket in the current system configuration.

General

“Cylinders Saved for PERM”

Saves some number of cylinders for permanent data only.

File System

“DateForm”

Defines whether Integer Date or ANSI Date formatting is used for a session.

General

“DBQL CPU/IO Collection”

Sets the algorithm used by DBQL to collect CPU and input/output statistics. DBQL CPU/IO collection captures performance data for the execution of a query at the request level and, optionally, at the step level.

General

“DBQLFlushRate”

Controls the length of time between flushing the Database Query Log (DBQL) cache.

General

“DBQLLOBCacheSize”

Determines the amount of cache allocated for DBQL data dictionary tables with LOB columns.

Performance

“DBQLLogLastResp”

Determines whether DBQL logs a pseudo step labeled “RESP” after the last response of a DBQL logged request completes.

General

“DBQL Options”

This field is unused, but reserved for future DBQL use. It should not be changed.

General

“DBQLXMLPlanMemLimit”

Specifies the amount of memory used by the XMLPLAN option of DBQL.

Performance

“DBSCacheCtrl”

Enables or disables the performance enhancements associated with the Cache Control Page-Release Interface associated with the DBSCacheThr field.

Performance

“DBSCacheThr”

Specifies the percentage value to use for calculating the cache threshold when the DBSCacheCtrl field is enabled.

Performance

“DeadlockTimeOut”

Used by the Dispatcher to determine the interval (in seconds) between deadlock timeout detection cycles.

General

“DefaultCaseSpec”

Determines whether character string comparisons consider character case and whether character columns are considered case specific by default in Teradata session mode.

General

“Default Character Set”

Specifies the default character set assigned to users if a default character set is not explicitly specified in the CREATE USER statement.

General

“DefaultTableMode”

Determines how BLC is managed for Permanent storage tables that have the BLOCKCOMPRESSION option set to DEFAULT, or that do not have the BLOCKCOMPRESSION option explicitly specified when the table is created.

Compression

“DefaultTimeThreshold”

Defines a default time threshold value in days for statistics collection.

Optimizer Statistics

“DefaultUserChangeThreshold”

Defines a default data change percentage threshold value for statistics collection.

Optimizer Statistics

“DefragLowCylProd”

Determines the number of free cylinders below which cylinder defragmentation can begin.

File System

“DEPOT Temperature”
see
“Storage Group”

Determines the initial temperature assigned to data loaded or inserted into tables of this type.

Storage

“DictionaryCacheSize”

Defines the size of the dictionary cache for each PE on the system.

Performance

“DictionaryCacheSegmentSize”

Defines the size of the dictionary segment cache for each PE on the system.

Performance

“DisableAutoCylPack”

Disables AutoCylPack automatic background cylinder packing. Does not affect MiniCylPack operations, which happen when the number of free cylinders is critically low.

File System

“DisableJSONValidation”

Determines whether validation is enabled or disabled for the native Teradata Database JSON data type.

General

“DisableMergeBlocks”

Determines whether the file system automatically merges data blocks together on storage cylinders during full table modify operations. Merging several small data blocks into a single larger block can improve system performance by reducing disk I/O.

File System

“DisablePeekUsing”

Enables or disables the performance enhancements associated with exposed USING values in parameterized queries.

Performance

“DisableRecoverableNetProtocol”

Determines whether the Recoverable Network Protocol is enabled.

General

“DisableRestartableBLC”

Determines whether BLC compression and decompression operations will resume after being interrupted by a system restart.

File System

“DisableSyncScan”

Enables or disables the performance enhancements associated with synchronized full table scans.

Performance

“DisableUDTImplCastForSysFuncOp”

Disables/enables implicit cast/conversion of UDT expressions passed to built-in system operators/functions.

General

“DisableWAL”

Forces the writing of data blocks and cylinder indexes directly to disk rather than writing the changes to the WAL log.

File System

“DisableWALforDBs”

Forces changed data blocks to be written directly to disk rather than having the changes written to the WAL log.

File System

“DisplacementOnOverlap”

Determines whether values that represent times during the closing hour of DST, but that do not include a time zone offset value, are interpreted by Teradata Database as DST or standard time.

General

“DMLStatementShipping”

This field is deprecated, and should not be used.

General

“EnableCostProfileTLE”

Determines whether Optimizer Cost Estimation Subsystem (OCES) diagnostics are enabled in combination with Target Level Emulation (TLE).

General

“EnableEON”

Confirms object naming rules are enforced. This field value cannot be changed.

General

“EnableJSON”

Determines whether native JSON support is enabled in Teradata Database.

General

“EnableLogonsMsg”

Enables or disables the display of “Logons are enabled” message in /var/log/messages on all nodes of a multinode system.

General

“EnableTempBLC”

Determines whether temperature-based block-level compression (TBBLC) is enabled.

Compression

“EnabNonTempoOp”

Determines whether nontemporal operations such as NONTEMPORAL DELETE, NONTEMPORAL UPDATE, NONTEMPORAL INSERT, NONTEMPORAL CREATE TABLE AS, and NONTEMPORAL ALTER TABLE can be used.

General

“EnableSetCostProfile”

Controls use of DIAGNOSTIC SET PROFILE statements, to dynamically change cost profiles, which are used for query planning.

General

“ExportOrderBySegmentLimit”

Specifies the maximum number of segments that can be allocated for FastExport redistribution when data is to be sorted.

General

“Export Width Table ID”

Controls the export width of a character in bytes.

General

“ExternalAuthentication”

Controls whether Teradata Database users can be authenticated outside (external) of the Teradata Database software authentication system.

General

“FixSRandomRange”

Fixes a rare issue with the RANDOM(lower_bound, upper_bound) function that causes it to return a value outside of the specified bounds under certain circumstances.

General

“Free Cylinder Cache Size”

Determines how many cylinders are to be managed in File System cache for use as spool cylinders.

File System

“FreeSpacePercent”

Determines the percentage of free space reserved on cylinders during bulk load operations.

File System

“GLOBAL TEMP Fallback Temperature”
see
“Storage Group”

Determines the initial temperature assigned to data loaded or inserted into tables of this type.

Storage

“GLOBAL TEMP Fallback CLOB Temperature”
see
“Storage Group”

Determines the initial temperature assigned to data loaded or inserted into tables of this type.

Storage

“GLOBAL TEMP Primary Temperature”
see
“Storage Group”

Determines the initial temperature assigned to data loaded or inserted into tables of this type.

Storage

“GLOBAL TEMP Primary CLOB Temperature”
see
“Storage Group”

Determines the initial temperature assigned to data loaded or inserted into tables of this type.

Storage

“HashFuncDBC”

Defines the hashing function that Teradata Database uses.

General

“HTMemAlloc”

Specifies the percentage of memory to be allocated to a hash table for a hash join.

Performance

“IAMaxWorkloadCache”

Defines the maximum size of the Index Wizard workload cache when performing analysis operations.

Performance

“IdCol Batch Size”

Indicates the size of a pool of numbers reserved for generating numbers for rows to be inserted into a table with an identity column.

General

“InclNTforGrntOrRevokAll”

Determines whether the NONTEMPORAL privilege is included when GRANT ALL and REVOKE ALL statements specify a user, database, view, or table.

General

“IVMaxWorkloadCache”

Defines the maximum size of the Index Wizard workload cache when performing validation operations.

Performance

“JSON_MaxDepth”

Determines the level of JSON value nesting allowed during JSON syntax validation.

General

“JSON_AttributeSize”

Denotes the size of attributes returned by the JSONExtractValue method and dot notation.

General

“JournalDBSize”

Determines the maximum size of permanent journal table multirow data blocks, in 512-byte sectors. Rows are written to this journal during INSERT, UPDATE, and DELETE processing.

File System

“LargeDepotCylsPerPdisk”

Determines the number of Depot cylinders the file system allocates to contain large slots (1 MB). A large slot can hold several data blocks during Depot operations.

File System

“LimitInlistCVal”

Limits the number of combined values in the IN list.

Performance

“LockLogger”

Defines the system default for the lock logger.

General

“LockLogger Delay Filter”

Controls whether or not to filter out blocked lock requests based on delay time.

General

“LockLogger Delay Filter Time”

Indicates the value at which blocked lock requests with a delay time greater than this value are logged.

General

“LockLogSegmentSize”

Specifies the size of the Lock Logger segment.

General

“MaxDecimal”

Defines the maximum number of Decimal Digits in the default maximum value used in expression typing.

General

“MaxDownRegions”

Determines the number of regions (ranges of rows) in a data or index subtable that can be marked as down before the entire subtable is marked down on all AMPs.

General

“MaxJoinTables”

Influences the maximum number of tables that can be joined per query block.

Performance

“MaxLoadAWT”

Works together with MaxLoadTasks to limit the number of load utility jobs that can run concurrently on the system.

General

“MaxLoadTasks”

Works together with MaxLoadAWT to limit the number of load utility jobs that can run concurrently on the system.

General

“MaxParseTreeSegs”

Defines the maximum number of 64 KB tree segments that the parser allocates while parsing a request.

Performance

“MaxRequestsSaved”

Specifies the number of request-to-step cache entries allowed on each PE on a Teradata Database system.

Performance

“MaxRowHashBlocksPercent”

Specifies the proportion of available locks that can be used for rowhash locks by a transaction before the transaction is automatically aborted.

General

“MaxSyncWALWrites”

Determines the maximum number of outstanding WAL log writes to allow before tasks requiring synchronous writes are delayed to achieve better buffering.

File System

“MDS Is Enabled”

Controls whether the rsgmain program is started in the RSG vproc when Teradata Database starts.

General

“Memory Limit Per Transaction”

Specifies the maximum amount of in-memory, temporary storage that the Relay Services Gateway (RSG) can use to store the records for one transaction.

General

“MergeBlockRatio”

Specifies the maximum size of a new data block that results from the merging of several smaller blocks. The size is expressed as the ratio of the new block size to the maximum block size specified in the table definition or by the PermDBSize setting in DBS Control.

File System

“MinDBSectsToCompress”

Specifies the minimum size DB that will be compressed. DBs smaller than this size will not be compressed.

Compression

“MiniCylPackLowCylProd”

Determines the number of free cylinders below which the File System will start to perform the MiniCylPack operation in anticipation of the need for additional free cylinders.

File System

“MLoadDiscardDupRowUPI”

Determines whether the DBS MultiLoad protocol should silently discard rows with UPI violations or log these rows to the application error table.

General

“MinPercentCompReduction”

Specifies the minimum percentage by which the DB size must be reduced by compression. If compression will not reduce the DB by this amount, the DB will not be compressed.

Compression

“MonSesCPUNormalization”

Determines whether CPU data in workload management (PM/API and Open API) calls is normalized. This affects workload rules defined in Teradata Viewpoint Workload Designer portlet.

General

“MPS_IncludePEOnlyNodes”

Excludes PE-only (AMP-less) nodes from MONITOR PHYSICAL SUMMARY Workload Management API statistics calculations.

General

“NameValidationRule”

Determines the rules used by Teradata Database to validate object names.

General

“NewHashBucketSize”

Specifies the number of bits that will be used to identify hash buckets on the system after the next sysinit or reconfig.

General

“NoDot0Backdown”

Enables several point release features which, if enabled, prevent a system from being backed down to the prior (“dot 0”) release without a sysinit.

General

“NumStatisticsCacheSegs”

Specifies the number of segments maintained for the Optimizer statistics cache on each PE vproc.

Performance

“ObjectUseCountCollectRate”

Specifies the amount of time between collections of object use-count data.

General

“PERMANENT JOURNAL Temperature”
see
“Storage Group”

Determines the initial temperature assigned to data loaded or inserted into tables of this type.

Storage

“PermDBAllocUnit”

Determines the allocation unit for multirow data blocks in units of 512-byte sectors for permanent tables.

File System

“PermDBSize”

Determines the maximum size, in consecutive 512-byte sectors, of the multirow data blocks of a permanent table.

File System

“PERM Fallback Temperature”
see
“Storage Group”

Determines the initial temperature assigned to data loaded or inserted into tables of this type.

Storage

“PERM Fallback CLOB Temperature”
see
“Storage Group”

Determines the initial temperature assigned to data loaded or inserted into tables of this type.

Storage

“PERM Primary Temperature”
see
“Storage Group”

Determines the initial temperature assigned to data loaded or inserted into tables of this type.

Storage

“PERM Primary CLOB Temperature”
see
“Storage Group”

Determines the initial temperature assigned to data loaded or inserted into tables of this type.

Storage

“PMPC_SessionRateThreshold”

Determines the threshold above which the session cache is refreshed on demand, based on the session rate and last collection time. Only effective when TDWM Workloads are enabled.

General

“PMPC_TimeoutSecs”

Sets timeout interval for certain supported PM API commands.

General

“PPICacheThrP”

The PPICacheThrP field is used to specify the percentage of cache memory that is available (per query) for multiple-context operations (such as joins and aggregations) on PPI tables and join indexes

Performance

“PrimaryIndexDefault”

Determines whether Teradata Database automatically creates primary indexes for tables created by CREATE TABLE statements that do not include PRIMARY INDEX, NO PRIMARY INDEX, PRIMARY KEY, or UNIQUE clauses.

General

“PRPDMaxSkewedVals”

The maximum number of skewed values in a join that should be considered for a PRPD optimization plan.

Performance

“PRPDSkewPct”

The percentage of rows in a join to be considered for skew by the optimizer, which determines whether PRPD optimization is used.

Performance

“ReadAhead”

Enables or disables the performance enhancements associated with the Read-Ahead Sequential File Access Workload operation.

Performance

“Read Ahead Count”

Specifies the number of data blocks that will be preloaded in advance of the current file position while performing sequential scans.

Performance

“ReadLockOnly”

Enables or disables the special read-or-access lock protocol on the DBC.AccessRights table during privileges validation and on other dictionary tables accessed by read-only queries during request parsing.

Performance

“RedistBufSize”

Determines the size (in KB) of units of hashed row redistribution buffers for use by load utilities.

Performance

“RedriveDefaultParticipation”

Determines whether or not the Redrive feature is used for all sessions by default.

General

“RedriveProtection”

Determines system-level support for the Redrive feature.

General

“RepCacheSegSize”

This field is deprecated, and should not be used.

General

“RevertJoinPlanning”

Determines whether the Teradata Database query Optimizer uses newer or older join planning techniques.

Performance

“RollbackPriority”

Defines the system default for the rollback performance group.

General

“RollForwardLock”

Defines the system default for the RollForward using Row Hash Locks option.

General

“RoundHalfwayMagUp”

Indicates how rounding should be performed when computing values of DECIMAL types.

General

“RoundNumberAsDec”

Determines whether NUMBER data type will use RoundHalfwaysMagUp rounding or will be rounded away from zero.

General

“SessionMode”

Defines the system default transaction mode, case sensitivity, and character truncation rule for a session.

General

“SHOW IN XML Formatted Output”

Controls the formatting of what is returned by the SHOW statement when the IN XML option is used.

General

“SHOW IN XML Memory Limit”

Sets the maximum amount of memory in kilobytes used for internal processing of a request of SHOW statement when the IN XML option is used.

Performance

“SkewAllowance”

Specifies a percentage factor used by the optimizer in deciding the size of each hash join partition.

Performance

“SmallDepotCylsPerPdisk”

Determines the number of Depot cylinders the file system allocates to contain small slots (1 MB). A small slot can hold a single data block during Depot operations.

File System

“SnapBypassAggrCache”

Specifies whether snapshot dumps are suppressed if resource exhaustion is detected when reserving memory for aggregate processing.

General

“Spill File Path”

Specifies a directory that the Relay Services Gateway (RSG) can use for spill files.

General

“SPOOL Temperature”
see
“Storage Group”

Determines the initial temperature assigned to data loaded or inserted into tables of this type.

Storage

“SpoolBigDBAllocUnit”

Determines the allocation unit in sectors for multi-row big data blocks in spool tables.

File System

“StandAloneReadAheadCount”

Specifies the number of data blocks the Teradata utilities will preload when the utilities or File System startup run as standalone tasks.

Performance

“StepsSegmentSize”

Defines the maximum size (in KB) of the plastic steps segment (also known as OptSeg).

Performance

“SyncScanCacheThr”

Specifies the percentage of File Segment (FSG) cache that is expected to be available for all synchronized full-file scans occurring simultaneously.

Performance

“SysInit”

Ensures the system has been initialized properly using the System Initializer utility. Read only.

General

“SysChangeThresholdOption”

Defines a change threshold option for statistics recollection.

Optimizer Statistics

“SysSampleOption”

Defines a sample option for statistics recollection.

Optimizer Statistics

“SysInitRelease”

Specifies the major release number on which the system was last initialized using the System Initialization (sysinit) utility. Read only.

General

“System TimeZone Hour”

Defines the System Time Zone Hour offset from Universal Coordinated Time (UTC).

General

“System TimeZone Minute”

Defines the System Time Zone Minute offset from Universal Coordinated Time (UTC).

General

“System TimeZone String”

Displays the currently set System Time Zone String.

General

“Target Level Emulation”

Allows a test engineer to set the costing parameters considered by the Optimizer.

General

“TempBLCInterval”

Specifies the amount of time in minutes that the background TBBLC task waits after compressing or uncompressing data before checking for other data to process.

Compression

“TempBLCIOThresh”

Specifies the maximum threshold number of outstanding I/O operations that can exist on a node above which the background TBBLC task pauses for a short period

Compression

“TempBLCPriority”

Specifies the priority at which the background TBBLC compression task runs.

Compression

“TempBLCRescanPeriod”

The number of days the background TBBLC task waits before revalidating the state of TBBLC data to reconfirm that data is appropriately compressed.

Compression

“TempBLCThresh”

Specifies the temperature at and below which data blocks are automatically compressed if TBBLC is enabled.

Compression

“TempBLCSpread”

Specifies the percentage above and below the TempBLCThresh setting at which data should be compressed or uncompressed if TBBLC is enabled.

Compression

“TempLargePageSize”

Specifies the size (in KB) of the large memory allocation storage page used for Relay Services Gateway (RSG) temporary storage.

General

“Temporal Behavior”

Determines the default temporal behavior for temporal DDL, queries, and DML statements.

General

“Temporary Storage Page Size”

Specifies the size (in KB) of the standard memory allocation storage page size used for Relay Services Gateway (RSG) temporary storage.

General

“TIM Cache Load Disabled”

Determines whether the Teradata Intelligent Memory (TIM) cache preload is enabled. This feature loads the most frequently accessed data into cache during system startup, if the FSG cache was not otherwise preserved.

Storage

“TIM Cache Load Throttle”

Throttles the Teradata Intelligent Memory (TIM) background cache loading task based on the current number of I/O operations.

Storage

“TimeDateWZControl”

Determines whether the time and time zone values returned by built in current time and date functions reflect the session time and time zone.

General

“UseVirtualSysDefault”

This field is no longer used. For cost profiling information, see “CostProfileId” on page 274.

General

“UncompressReservedSpace”

Specifies the minimum percentage of storage space that must remain available while DBs are uncompressed using the Ferret UNCOMPRESS command.

Compression

“UtilityReadAheadCount”

Specifies the number of data blocks the Teradata utilities will preload when performing sequential scans.

Performance

“Version”

Indicates the version number of the DBS Control Record.

General

“WAL Buffers”

Determines the number of WAL append buffers to allocate.

File System

“WAL Checkpoint Interval”

Determines the amount of time that elapses between WAL checkpoints.

File System

“WAL Temperature”
see
“Storage Group”

Determines the initial temperature assigned to data loaded or inserted into tables of this type.

Storage

“XML_Memory Limit”

Specifies the maximum amount of memory available for operations involving the XML data type.

General