17.10 - DISPLAY - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Configuration
Publication ID
B035-1102-171K
Language
English (United States)

Displays the current values for all DBS Control fields or for a specified field group.

Syntax

{ DISPLAY | D } [ GroupName ]

Syntax Elements

GroupName
Which of the following groups the DISPLAY command is directed towards:
  • GENERAL (for general fields)
  • FILESYS (for file system fields)
  • PERFORMANCE (for performance fields)
  • CHECKSUM (for checksum fields)
  • COMPRESSION (for bock-level compression fields)
  • STATISTICS (for optimizer statistics fields)
  • STORAGE
  • NATIVE OBJECT STORE (for Native Object Store fields)
Rather than type in the full GroupName, you can type the first few characters, as many as required to uniquely identify the group.

Usage Notes

If GroupName is provided, only the fields associated with the specified group are displayed. If GroupName is omitted, the values of all fields are displayed.

Example: DBS Control DISPLAY output

Actual field numbers and values may be different on your system from those shown in this example.
Enter a command, HELP, or QUIT:
DISPLAY
DBS Control Record - General Fields:

  1. Version                          = 10
  2. SysInit                          = TRUE        (2009-01-20 10:09)
  3. DeadLockTimeout                  = 240         (Seconds)
  4. (Reserved for future use)
  5. HashFuncDBC                      = 6           (Universal)
  6. (Reserved for future use)
  7. (Reserved for future use)
  8. SessionMode                       = 0           (Teradata mode)
  9. LockLogger                        = FALSE       (Disabled)
 10. RollbackPriority                  = FALSE       (Disabled)
 11. MaxLoadTasks                      = 5
 12. RollForwardLock                   = FALSE       (Disabled)
 13. MaxDecimal                        = 15
 14. Century Break                     = 0
 15. DateForm                          = 0           (IntegerDate)
 16. System TimeZone Hour              = 0
 17. System TimeZone Minute            = 0
 18. System TimeZone String            = America Pacific
 19. (Reserved for future use)                
 20. RoundHalfwayMagUp                 = FALSE
 21. (Reserved for future use)
 22. Target Level Emulation            = FALSE
 23. Export Width Table ID             = 0           (Expected Defaults)
 24. DBQLLogLastResp                   = FALSE
 25. DBQL Options                      = 0           (No options)
 26. ExternalAuthentication            = 0           (On) 
 27. IdCol Batch Size                  = 100000      (Default value)
 28. LockLogger Delay Filter           = FALSE
 29. LockLogger Delay Filter Time      = 0           (Seconds; Default value)
 30. ObjectUseCountCollectRate         = 0           (Minutes; Disabled)
 31. LockLogSegmentSize                = 64          (KB; Minimum value)
 32. CostProfileId                     = 0
 33. DBQLFlushRate                     = 600         (seconds)
 34. Memory Limit Per Transaction      = 2           (pages)
 35. Client Reset Timeout              = 300         (seconds)
 36. Temporary Storage Page Size       = 4           (KB)
 37. Spill File Path                   = /var/tdrsg
 38. (Available for future use)        
 39. ChecktableTableLockRetryLimit     = 0           (Retry Forever)
 40. EnableCostProfileTLE              = FALSE 
 41. EnableSetCostProfile              = 0           (Disabled)
 42. (Reserved for future use)
 43. DisableUDTImplCastForSysFuncOp    = FALSE
 44. CurHashBucketSize                 = 20          (Bits)
 45. NewHashBucketSize                 = 20          (Bits)
 46. MaxLoadAWT                        = 0
 47. MaxRowHashBlocksPercent           = 50          (Percent; Default value)
 48. MonSesCPUNormalization            = FALSE
 49. TempLargePageSize                 = 64          (KB; Minimum value)
 50. RepCacheSegSize                   = 512         (KB; Minimum value)
 51. MaxDownRegions                    = 6           (Default value)
 52. MPS_IncludePEOnlyNodes            = FALSE
 53. PrimaryIndexDefault               = D           (First Column is NUPI)
 54. AccessLockForUncomRead            = FALSE       (Default value)
 55. EnabNonTempoOp       = FALSE       (Disabled)
 56. InclNTforGrntOrRevokAll           = FALSE
 57. TimeDateWZControl                 = 0           (Enabled; Teradata Default)
 58. (Reserved for future use)
 59. SysInitRelease                    = 13010000
 60. DefaultCaseSpec                   = FALSE
 61. PMPC_TimeoutSecs                  = 60          (Seconds; Default value)
 62. ExportOrderBySegmentLimit         = 500
 63. MLoadDiscardDupRowUPI             = FALSE
 64. DBQL CPU/IO Collection            = 0  (Algorithm 3, with step adjustments, default)
 65. NoDot0Backdown                    = TRUE
 66. SnapBypassAggrCache               = TRUE
 67. RedriveProtection                 = 1            (Redrive memory-based)
 68. RedriveDefaultParticipation       = 1            (Redrive Participation is ON)
 69. PMPC_SessionRateThreshold         = 60           (Seconds)
 70. RoundNumberAsDec                  = TRUE
 71. DMLStatementShipping              = 0            (Disabled, default value)
 72. SHOW IN XML Formatted Output      = FALSE        (Disabled, default value)
 73. XML_MemoryLimit                   = 4            (MB; Default value)
 74. Obsolete (DisplacementOnOverlap)  
 75. NameValidationRule                = 0            (Default name validation)
 76. EnableEON                         = TRUE
 77. DisableRecoverableNetProtocol     = FALSE        (RNP enabled, default)
 78. Default Character Set             = 1            (LATIN)
 79. EnableLogonMsg                    = FALSE
 80. COUNT_mode                        = 0
 81. AutoProvision                     = FALSE
 82. EnableJSON                        = TRUE
 83. JSON_MaxDepth                     = 512 (Default 512)
 84. JSON_AttributeSize                = 4096 (Default 4096)
 85. (Reserved for future use)
 86. DisableJSONValidation             = FALSE
 87. FixSRandomRange                   = TRUE
 88. Temporal Behavior                 = 1 (ANSITemporal Behavior)
 89. (Reserved for future use) 
 90. (Reserved for future use)
 91. (Reserved for future use)
 92. MLOADXUtilityLimits               = FALSE
 93. MaxMLOADXTasks                    = 30 (Default 30)
 94. MaxMLOADXAWT                      = 48
 95. CPUpdate                          = 3 (0-4, Default 3)
 96. EnableDATASET                     = TRUE
 97. DATASETAttributeSize              = 4096 (Default 4096)
 98. DisableDATASETValidation          = FALSE
 99. DotNotationOnErrorCondition       = 0 (Default 0)
100. Obsolete (XMLWarnThreshold)
101. DefaultPermSkewLimitPercent       = 0 (0-10000%, default 0%)
102. DefaultSpoolSkewLimitPercent      = 0 (0-10000%, default 0%)
103. DefaultTempSkewLimitPercent       = 0 (0-10000%, default 0%)
104. GlobalSpaceSoftLimitPercent       = 0.00 (0.0-99.99%,   default 0%)
105. ProratedSpaceDistPercent          = 0 (0-100%,   default 0%)
106. MaxSetQueryBandSize               = 3 (2-4 KB, Default 3)
107. LegacySpaceAcctg                  = FALSE (Default FALSE)
108. CDCProtocol                       = 0 (Default, Legacy Change Data Capture)
109. MetaDataCapture                   = 0 (Default, No MetaData)
110. ClientResetAction                 = 0 (Default, continue)
111. TruncRoundReturnTimestamp         = TRUE (Default FALSE)
112. ShowAllUserNames                  = FALSE (Default FALSE)

113. FastAlterEnable                   = FALSE (default FALSE)
114. FastAlterDefault                  = FALSE (default FALSE)
115. EnableDATASETCSV                  = TRUE
116 - 118 (Reserved for future use)
119. LegacyUnAuthBehavior              = FALSE (default FALSE)
120. (Reserved for future use)

DBS Control Record - File System Fields:

 1. FreeSpacePercent                   = 0           (Percent; Default value)
 2. MiniCylPackLowCylProd              = 10          (free cylinders; Default value)
 3. PermDBSize                         = 254         (Sectors; Default value)
 4. JournalDBSize                      = 254         (Sectors; Default value)
 5. DefragLowCylProd                   = 100         (Free cylinders; Default value)
 6. PermDBAllocUnit                    = 1           (Sectors; Default value)
 7. Cylinders Saved for PERM           = 10          (Cylinders; Default value)
 8. DisableWALforDBs                   = FALSE       (Enabled)
 9. DisableWAL                         = FALSE       (Enabled)
10. WAL Buffers                        = 20          (WAL log buffers; Default value)
11. MaxSyncWALWrites                   = 5           (MaxSyncWalWrites; Default value)
12. SmallDepotCylsPerPdisk             = 2           (Cylinders; Default value)
13. LargeDepotCylsPerPdisk             = 1           (Cylinders; Default value)
14. WAL Checkpoint Interval            = 60          (Seconds; Default value)
15. Free Cylinder Cache Size           = 100         (Number of cylinders;Default value)
16. Bkgrnd Age Cycle Interval          = 60          (Seconds; Default value)
17. DisableAutoCylpack                 = FALSE       (Enabled)
18. AutoCylPackColdData                = FALSE       (Disabled)
19. AutoCylPackFSP                     = 10          (Percent; Default value)
20. AutoCylPackStyle                   = 2           (PACK / UNPACK)
21. AutoCylPackThresh                  = 5           (Percent; Default value)
22. AutoCylPackFreecylThresh           = 20          (Cylinders; Default value)
23. AutoCylPackInterval                = 60          (Seconds; Default value)
24. AutoCylPackIOThresh                = 1           (Number; Default value)
25. (Reserved for future use)
26. (Reserved for future use)
27. MergeBlockRatio                    = 60          (Percent; Default value)
28. DisableMergeBlocks                 = FALSE       (Enabled)
29. DisableRestartableBLC              = FALSE       (Enabled)
30. SpoolBigDBAllocUnit                = 1           (Sectors; Default value)

DBS Control Record - Performance Fields:

 1. DictionaryCacheSize                = 3072        (KB; Default value)
 2. DBSCacheCtrl                       = TRUE        (Enabled)
 3. DBSCacheThr                        = 10          (Percent; Default value)
 4. MaxParseTreeSegs                   = 3000
 5. ReadAhead                          = TRUE        (Enabled)
 6. StepsSegmentSize                   = 1024        (KB; Default value)
 7. RedistBufSize                      = 3832        (Bytes; Default value))
 8. DisableSyncScan                    = FALSE       (Enabled)
 9. SyncScanCacheThr                   = 10          (Percent; Default value)
10. HTMemAlloc                         = 10          (Percent; Default value)
11. SkewAllowance                      = 75          (Percent; Default value)
12. Read Ahead Count                   = 1           (Data block; Default value)
13. PPICacheThrP                       = 10         (PPI cache per AMP = 1% of 100MB: 1MB)
14. ReadLockOnly                       = FALSE
15. IAMaxWorkloadCache                 = 32          (MB; Default value)
16. MaxRequestsSaved                   = 600         (Default value)
17. UtilityReadAheadCount              = 10          (Blocks; Default value)
18. StandAloneReadAheadCount           = 20          (Blocks; Default value)
19. DisablePeekUsing                   = FALSE       (Enabled)
20. IVMaxWorkloadCache                 = 1           (MB; Default value)
21. RevertJoinPlanning                 = FALSE       (Default value)
22. MaxJoinTables                      = 128
23. DBQLXMLPlanMemLimit                = 8192        (KB; Default value)
24. LimitInlistCVal                    = 1048576
25. NumStatisticsCacheSegs             = 4
26. SHOW IN XML Memory Limit           = 64000       (KB; Default value)
27. DictionaryCacheSegmentSize         = 16          ( * 64 KBs; Default value)
28. PRPDMaxSkewedVals                  = 50          (Default value)
29. PRPDSkewPct                        = 50          (Percent)
30. DBQLLOBCacheSize                   = 2           (MB; Default:2, Min:2, Max:16)
31. HJ2IMHJ                            = FALSE
32. InListRewriteThreshold             = 512         (Default:512, Min:1, Max:32767)
33. MaxNumInListRewrite                = 2           (Default:2, Min:0, Max:100)
34. InListRewriteOption                = 1           (Default:1, Min:0, Max:7)
35. DBQLDefCacheSize                   = 2           (Default:2, Min:0, Max:16)

DBS Control Record - Disk I/O Integrity Fields:

1.  System Tables            = OFF
2.  System Journal Tables    = OFF
3.  System Logging Tables    = OFF
4.  User Tables              = OFF
5.  Permanent Journal Tables = OFF
6.  Temporary Tables         = OFF

CHECKSUM LEVEL DEFINITIONS

    OFF   -  Do not calculate or validate checksums.
    ON    -  Calculate and validate checksums using the entire disk block.

DBS Control Record - Compression (Block Level) Fields:
 1.  BlockLevelCompression              = ON
 2.  (Reserved for future use)
 3.  CompressSpoolDBs                   = NEVER       (ALWAYS, NEVER, IFNOTCACHED)
 4.  (Reserved for future use)
 5.  CompressMloadWorkDBs               = NEVER       (ALWAYS, UNLESSQBNO, NEVER, ONLYIFQBYES)
 6.  CompressPJDBs                      = NEVER       (ALWAYS, NEVER)
 7.  MinDBSectsToCompress               = 16
 8.  MinPercentCompReduction            = 20%
 9.  CompressionAlgorithm               = ZLIB        (ZLIB, ELZS-H, ELZS-S)
10.  CompressionLevel                   = 1           (1-9; Default value = 1)
11.  (Reserved for future use)
12.  UncompressReservedSpace            = 5%
13.  (Reserved for future use)
14.  CompressPermPrimaryDBs             = ONLYIFQBYES (ALWAYS, UNLESSQBNO, NEVER, ONLYIFQBYES)
15.  CompressPermFallbackDBs            = ONLYIFQBYES (ALWAYS, UNLESSQBNO, NEVER, ONLYIFQBYES)
16.  CompressPermPrimaryCLOBDBs         = NEVER       (ALWAYS, UNLESSQBNO, NEVER, ONLYIFQBYES)
17.  CompressPermFallbackCLOBDBs        = NEVER       (ALWAYS, UNLESSQBNO, NEVER, ONLYIFQBYES)
18.  CompressGlobalTempPrimaryDBs       = NEVER       (ALWAYS, UNLESSQBNO, NEVER, ONLYIFQBYES)
19.  CompressGlobalTempFallbackDBs      = NEVER       (ALWAYS, UNLESSQBNO, NEVER, ONLYIFQBYES)
20.  CompressGlobalTempPrimaryCLOBDBs   = NEVER       (ALWAYS, UNLESSQBNO, NEVER, ONLYIFQBYES)
21.  CompressGlobalTempFallbackCLOBDBs  = NEVER       (ALWAYS, UNLESSQBNO, NEVER, ONLYIFQBYES)
22 - 31  (Reserved for future use)
32.  EnableTempBLC                      = TRUE        (TRUE, FALSE)
33.  DefaultTableMode                   = MANUAL      (AUTOTEMP, MANUAL, ALWAYS, NEVER)
34.  TempBLCThresh                      = COLD        (COLD, WARM, HOT, 0-100 )
35.  TempBLCSpread                      = 5%          (1-25%)
36.  TempBLCInterval                    = 10 minute(s) (1-120)
37.  TempBLCIOThresh                    = 1           (1-1000)
38.  TempBLCPriority       = MEDIUM/DEFAULT (LOW/BOTTOM, MEDIUM/DEFAULT, HIGH, RUSH/TOP)
39.  TempBLCRescanPeriod                = 7 day(s)    (1-90)
40.  CompressionZLIBMethoc              = IPPZLIB     (ZLIB, IPPZLIB)


DBS Control Record - Storage Fields:

 1.  (Reserved for future use)
 2.  SPOOL Temperature                        = HOT          (HOT, WARM, COLD, DEFAULT)
 3.  (Reserved for future use)
 4.  PERMANENT JOURNAL Temperature            = HOT          (HOT, WARM, COLD, DEFAULT)
 5.  WAL Temperature                          = HOT          (HOT, WARM, COLD, DEFAULT)
 6.  DEPOT Temperature                        = HOT          (HOT, WARM, COLD, DEFAULT)
 7.  All Other Temperature                    = WARM         (HOT, WARM, COLD, DEFAULT)
 8.  PERM Primary Temperature                 = WARM         (HOT, WARM, COLD, DEFAULT)
 9.  PERM Primary CLOB Temperature            = WARM         (HOT, WARM, COLD, DEFAULT)
10.  PERM Fallback Temperature                = WARM         (HOT, WARM, COLD, DEFAULT)
11.  PERM Fallback CLOB Temperature           = WARM         (HOT, WARM, COLD, DEFAULT)
12.  GLOBAL TEMP Primary Temperature          = WARM         (HOT, WARM, COLD, DEFAULT)
13.  GLOBAL TEMP Primary CLOB Temperature     = WARM         (HOT, WARM, COLD, DEFAULT)
14.  GLOBAL TEMP Fallback Temperature         = WARM         (HOT, WARM, COLD, DEFAULT)
15.  GLOBAL TEMP Fallback CLOB Temperature    = WARM         (HOT, WARM, COLD, DEFAULT)
16.  TIM Cache Load Disabled                  = FALSE        (TRUE, FALSE; Default value)
17.  TIM Cache Load Throttle                  = FALSE       (TRUE, FALSE; Default value)

DBS Control Record - Optimizer Statistics Fields:

 1.  DefaultTimeThreshold                    = 0 (Disabled; Default value)
 2.  DefaultUserChangeThreshold              = 0 (Disabled; Default value)
 3.  SysChangeThresholdOption          = 0 (Enabled with DBQL ObjectUsage;Default value)
 4.  SysSampleOption                         = 0 (Enabled; Default value)
 5.  BLCStats                                = 0 (Enabled; Default value)

DBS Control Record - Native Object Store Fields:


 1. EnableNOS                                = TRUE (Enabled; Default value)
 2. ConnectionBufferSize                     = 16  (MB; Default value)
 3. ConnectionTimeout                        = 40  (Seconds; Default value)
 4. RequestTimeout                           = 60  (Seconds; Default value)
 5. RequestDuration                          = 300 (Seconds; Default value)
 6. UserRetryLimit                           = 1   (Default value)
 7. DefaultRowGroupSize                      = 16  (MB; Default value)
11. ForeignTableErrorsData                   = 0 (Log errors and data; Default value)
12. ForeignTableErrorsLimit                  = 0 (Limit = 10; Default value)