Ref¶
DD¶
Non-hashed tables: Acctg, DatabaseSpace, journal, lock, transaction
| Table | Purpose |
|---|---|
| AccessLogRulesV | Access log rules Rule code (All Acc/Denied Acc/Text) xxy (x: freq F,L,E,Both) y: Text -(denials),+(all),= |
| {,Delete}AccessLogV | {Selectable,Deletable} logs older than 30 days |
| AccountInfoV | Valid accounts for each user/profile |
| All_RI_ChildrenV | RI parent/child relationship contains inconsistent RI information |
| AllSpaceV | All space usage including spool, table ‘ALL’ |
| AMPUsage | Usage at User+Acct+AMP. Inserted/updated with each logon or SQL => CPU+IO by Workload+Time dimensions |
| ArchiveLoggingObjsV | All tables which currently have logging enabled for online archive |
| ChildrenV | Database Parent/Child information |
| Database2V | Unresolved RI count count at database level |
| DBQLRulesV | DBQL rules |
| DiskSpaceV | Database size |
| Events_Configuration | Each archive /restore even that was not all AMP |
| Events/RCEvent:T/ Association |
Each archive / restore event Objected imported through COPY |
| IDCol | Next value for Identity column |
| IndexConstraints | IndexType=Q: PPI expression |
| LogOnOffV | All logon/offs including bad logons, force logoffs |
| LogonRules | results of GRANT/REVOKE LOGON (Host ID 1024=ALL) |
| ObjectUssage:T | UDI and stats usage counts TD14.10+ |
| PasswordRestrictionV | Words that cannot be used as password |
| ProfileInfoV | Profile info. Contains only first/default account name |
| QryLogV | CacheFlag: Specific/Generic/T(found cached)/Always |
| QueryBandReservedNames | Reserved query bands. Category: T/A/I |
| All{,Role}RightsV | Access granted to {ID,roles} for all |
| User{,Role}RightsV | Access granted to {ID,roles} for the current user |
| SecurityDefaultsV | Default PASSWORD ATTRIBUTES when creating a new user |
| SessionInfoV | Information about current users logged on |
| Show{Tbl,Col}Checks | {Table+named-column,Unnamed-column} level checks |
| SoftwareEventLogV | contains entries like tpareset (TheFunction column) |
| StatsTbl ColumnStatsV/IndexStatsV MultiColumnStatsV |
StatsID = 0 => Summary Stats |
| TableSizeV | Table sizes |
| TDWMSummaryLog | WD summary (queries,errors,rejected,delayed etc) |
| UsersV | Contains all users owned or created by current user |
Macros¶
| Macro | Purpose |
|---|---|
| ClearAccounting | resets accounting data in dbc.Acctg (AMPUsage) table |
| LogonRule | Exec access allows GRANT/REVOKE LOGON statements |
| AccLogRule | Exec access allows BEGIN/END LOGGING statements |
| DBQLAccessMacro | Exec access allows BEGIN/END QUERY LOGGING statements |
| ClearPeakDIsk | resets peak usage in dbc.DatabaseSpace table |
| DBC.VHCTRL (TD14.10+) | Exec allows setting QB TVSTEMPERATURE_PRIMARY to ß |
Diagnostic commands¶
| Command | For |
|---|---|
dump costs My_DBSCtrl_Values |
|
helpstats |
|
"randomampsampling=<options>" | IFP (D/L/M/N/A) |
|
"randomsampling=<percent>" | IFP |
|
"collectstats, samplesize=5" |
|
accessright |
|
NO5LAFOROJS |
|
nosattc |
|
noviewfold |
|
"maxviewvftreesize n" |
see MaxViewFoldTreeSize |
"lookahead=1" |
|
set costs |
TLE=>Fake Hardware costs for optimizer (part of TSET) |
DIAGNOSTIC qrwtext |
Show rewritten query |
AUTOPARSE |
for frequent 3710 errors (PE out of memory) |
"setcurrentdate 1000103" |
modify current date |
jintrace |
use before explain to shows all qualified JI |
DBQL_NO_EXEC [NOT] |
Doesn’t run queries, but populate DBQL |
spoilq |
Triggers DBQL to reread the rules from dbc after a restore |
verboseexplain |
|
"setcurrentdate=1100103" |
|
QRWTEXT |
show rewritten query text instead of running it |
DBSControl¶
| Option | Reason |
|---|---|
| {Cur,New}HashBucketSize | HashBucketSize {prior,after} to reconfig |
| RollbackPriority | TRUE:Rollback at user priority, FALSE=Rush |
| NoViewFold | ON: Do not fold view query for global optimization |
| ObjectUseCountCollectRate | >0 => updates AccessCount info in DD |
| MaxLoadTask, MaxLoadAWT | if MaxLoadAWT = 0 max(fexp+fload+mload) <= 15, else max(fload+mload) <= 30,fexp = 60 - (fload+mload). MaxLoadAWT <= 60% total AWT |
| DisableInListJoin | TRUE: do not materialize IN lists |
| DisablePeekUsing | TRUE: do not re-optimize parameterized query |
| CylindersSavedForPERM | # of cylinders reserved for PERM (no spool) |
| EnableExpediteExp | =1 => use AWT 09/10 for all express requests |
| Free Cylinder Cache Size | # of cylinders reserved for SPOOL (no perm) |
| SessionMode | 0 => Teradata 1=> ANSI |
| DBQLFlushRate | seconds to flush DBQL tables; default 600. DBQLogTbl may be flushed sooner according to TDWM Logging interval |
| DisableTDWMSessionRules | true => honor MINSESS / MAXSESS values for utilities |
| MonSesCPUNormalization | Use normalized CPU usage for TASM evaluation. |
| HTMemAlloc | Mem for hash joins. 0 => turn-off hash joins |
| ReadLockOnly | false => access lock for AccessRight table |
| PPICacheThrP | 1% mem blocks available for multicontext operations |
| PermDBSize | Data block size in sectors (127) Max (2047) |
| WorkDBSize (Internal) | Spool/MLOAD Work DB Size (default = Max = 2047) |
| IDColBatchSize | Block of identity column values reserved for each AMP |
| DisableWAL | TRUE=>write DB and CI to disk |
| DisableWALforDBs | TRUE=>Write CI to WAL and write DB to disk |
| Cylinder Read | LogOnly => Use Cyl Read only for WAL, no data |
| DBSCacheThr | % of FSG Cache that demarcates small table from large. Small tables are preferentially cached |
| TIMCacheLoadDisabled | Do not preload data in TIM |
| TIMCacheLoadThrottle | throttle loading of TIM |
| CompressionAlgorithm | ZLIB (software) / ELZS-H (hardware) |
| BlockLevelCompression | ON/OFF |
| EnableTempBLC/TempBLC{Thresh/Spread} | TRUE/FALSE, %/WARM, % |
| DefaultTableMode | MANUAL/AUTOTEMP/NEVER |
| Compress{Perm/GT/MLWork/PJ/Spool}DB | ONLYIFQBYES, UNLESSQBNO, NEVER |
| Perm/GT support Prim/FallBack/FBCLOB | Spool: IFNOTCACHED, PJ:YES/NO |
| TempBLCThresh | COLD|WARM|HOT|n limit in % at which TBBLC begins |
| DefaultTimeThreshold | Time in days for stats recollection |
| DefaultUserChangeThreshold | % rows changed/added for stats recollection |
| SysChangeThresholdOption | Sys determined threshold when above is set to 0 |
| FallBackSetting | 0: Default NO, 1: Default YES, 2: Forced YES |
Limits¶
| Option | Value |
|---|---|
| # of Columns in PI/SI | 64 |
| Sector size | 512 bytes |
| Hash Bucket # | 20/16 bits |
| Row-hash+Uniqueness Value | 32+32bits |
| Max SI | 32 |
| Max nesting levels of Views | 64 |
| Max # of columns in PK/FK | 16 |
| Cylinder size in sectors | 3872 (< TD 13.10) 23232 (Optional TD 13.10) |
| Cylinder Index size | 24 X 2 sectors |
| Max # of Cylinders per AMP | 700,000 (<= TD13.0) |
| Max Data Block size | 255 sectors |
| Cylinder defrag threshold | >= 25% (default) |
| Mini cylpack trigger | < 10 free cylinders (default) |
| MergeBlockRatio | 60% (default) (Merge blocks only until this limit) |
| Row header size | 72 bytes or 128 bytes (BLC) |
| Table header size | 512 if cols < 3 else 1024 if no cmprs else >=4k |
| Max # of partitions | 65535 (TD < 14) or 2^128 |
| Max # of memory segments for hash join | 50 |
| Max sessions / PE | 120 |
| Default/Max # of PE per Gateway | 2 / 10 |
| Table ID | 32 bit + 16 bit sub-table ID |
| Max # of columns in a table | 2048 |
| Max # of volatile tables | 1000 |
| Max # of materialized Temp tables | 2000 |
| Max # combined refresh stats | 512 |
| Max Utility Sessions in TASM Util Sess | 4 |
| WL Max Classification criteria | 6 |
| Deadlock detection local/global | 30sec (fixed)/4min (default) |
| Constraints | 5/table, 6 non-set+2 set/user |
| Constraint labels | 10000/non-set, 256/set |
ResUsage¶
- SAWT: Node+AMP, WorkTypeInUseXX/MailboxDepth/InUseMax:
- SVPR: MsgWorkQLenMax is better than SAWT MailBoxDepth for tracking worse case values
- SPS: Node+WDID (workload ID) (if tasm, or PG+PP)
- WorkTypeInUseXX and WorkTypeMaxXX, but no InUseMax.
- QWaitTime: Wait times for tasks that were able to get AWT by interval end
- WorkMsgReceiveDelay: Wait times for tasks that were not able to get AWT by interval end
- WorkMsgReceiveDelayMax: Max a message waited for an AWT
- Using MAX(WorkMsgReceiveDelayMax,QWaitTimeMax) gives max wait for an AWT
- WorkMsgSendDelay: if on PE, delays for WorkNew, if on AMP, delays for spawned work
- SPMA (Node): AWTInUseMax across all AMPs
Host{Read,Write}KBonly count data flowing through gateway nodes, hence DSA isn't counted
- CPU usage in SPMA accounts for non-database usage (eg. gateway or OS), but SPS does not.
- ROT: For high vol tables such as SPS, use Active Row Filter mode
- ROT: Compare QWaitTime in relation to ServiceTime and NumRequests
- For v. busy systems, logging period is doubled.
- For coexistence systems, normalized values use 5100 as base value of 1.