Skip to content

Utilities and Tools

  • TD14+: Linux processes run as teradata user instead of root
  • Use tdtrusted group to control Teradata services on Linux
    • Protected mode UDF are run by ID that is member of tdatudf group

Administrative Tools

  • Teradata Manager: Alert viewer, PMON, Administrator, Remote Console, Locking Logger, Session Information, Resource History,
  • Teradata Workload Analyzer: generates workload definitions based on DBQL or existing Priority Scheduler settings
  • Teradata Analyst Pack: VisExpl, TSET, Index Wizard, Stats Wizard
  • Index Wiz:
  • suggest SIs, STJI and partitioning.
  • Import workload from DBQL, QCD, sql files, DUMP EXPL stmt
  • Validation allows to check explain without creating suggested indexes
  • Creation phase allows auto creating recommendations except partitioning
  • Teradata Visual Explain:
  • Normal: View/delete only own plan/workload. Power: view all/delete self. Admin: All
  • INSERT EXPLAIN puts plan info into QCD database. Can optionally collect STATISTICS and DEMOGRAPHICS (QCD form not the optimizer form)
  • DUMP EXPLAIN generates INSERT statements that INSERT EXPLAIN would produce
  • Stats Wiz:Recommendations based on workload or table based
  • TSET: based on Database, query or workload

Archive/Restore/Copy

  • Archive types: data, dictionary, no fallback, journal. Can archive one type at a time
  • JI, HI, Macros, Triggers, UDT, VIews, GLOP: only dictionary info is processed
  • Archive levels: ALL AMP (default), AMP (used for no fallback tables after AMP recovery)
  • cluster archive: Groups AMPs by cluster. Better than single archive, worse than multi-stream. No journal tables, DBC database or dictionary
  • Multi-stream: ARC handles dividing data for parallelism. DBS sees it as only one All AMP backup
  • BAR solution provide Multi-stream archives: AMP-local archives, no ByNET traffic
  • Archival of selected partitions only supported for all-AMP archives
  • partition-level arc, specify cond as: PARTITIONS WHERE (!cond!)
  • Multi-stream: Master stream communicates with other streams via TARA server
  • Master server gets database Job ID that will be shared by child streams
  • Master archives DD and data. Child streams archive data only
  • Arc user ID must have MONITOR privilege to use local AMP backup
  • Non-hashed and partially loaded tables: only table header is backed up. are empty on restore
  • Partition level backups
  • restores do not restore dictionary tables, so collect stats after restores
  • Partitions with LOBs can be archived, but must restore full table
  • Cluster archive: Backs up AMP clusters
  • Must backup/restore dictionary first for specific objects before data
  • Need multiple user-ids for restore
  • Online archive: captures before images, restore point is archive start, uses only transaction locks.
  • No DBC, PJ, Temp tables, partition level backups, indexes
  • Yes: Compatible with FLOAD and MLOAD
  • Uses table-level read lock at the beginning as consistency point.
  • TD13.10+: No access lock on DB, but currently archive table can’t be altered.
  • Any table created after consistency point is archived using offline mode
  • consistency point is established using either ONLINE keyword or LOGGING ON stmt
  • NOSYNC option causes each table to have it’s own consistency point.
  • NOSYNC option is single threaded. If many more tables, split and run parallel jobs
  • If any error, puts a warning and continues (unlike no NOSYNC which fails)
  • DB having mix, tables without NOSYNC should come before ones using NOSYNC
  • TARA Script specified value for SESSIONS is per stream
  • Journal table restore area is physically different from current portion, so can it can be restored while changes are being written to current area.
  • # of SESSIONS should be divisible by # of COP entries on that BAR server (OB Local AMP Bkp)
  • DBC Restore: SYSINIT is required, then DIPMIG, then
DBC DELETE DATABASE (DBC) ALL, EXCLUDE DBC;
RESTORE DBC;
RESTORE (DBC) ALL, EXCLUDE DBC, TD_SYSFNLIB;
post_data_restore`, `DIPALL
  • To copy only DDLs from one system to another.
ARCHIVE DICTIONARY TABLES (DBC) ALL…;
COPY DICTIONARY TABLE (DB.TB) (FROM(DB.TB))...;
BUILD DATA TABLES (DB.TB)...;
  • Single AMP restore: only allowed for non-fallback tables.
  • If table header exist then it must match backup
  • If no header exists, use table rebuild utility to build header before restore
  • Will invalidate USIs
  • After restoring PPI, arc auto revalidates which is considered structure change => can’t roll-forward/rollback after restore
  • BAR will use BYNET to access AMPs on remote nodes if there are no COP entries defined.
  • RTO: Recovery Time Objective => how long recovery should run
  • RPO: Recovery Point Objective => How much data can one afford lose (backup freq)
  • ROT: set up one stream per output device.
  • ROT: Define as many sessions as many AMPs can be accessed as local.
  • ROT: Use same # of sessions / stream as # of AMPs / node
  • ROT: If multiple BAR servers, do not add COP entries for all TD nodes to all BAR servers. Maybe have two BAR servers access to 1 TD node for flexibility, otherwise network congestion increases at switch
  • BAR Config: node:server:storage
  • Eg 4:1:4 => 4 nodes/bar server and 4 streams/bar server)
  • if there are only 2 streams (tape drives) per node, use half as many sessions as AMPs / node
  • Use # of sessions that divides # of AMPs per stream evenly (eg for 36 AMPs choose 12 not 8)
  • All jobs have two phases:
  • Dictionary: check access, place lock, get event # (DBC.Next)
  • Data: INS into RCEVENT+RCCONFIGURATION, perform op, update RCEVENT

  • SYSUDTLIB is linked to DBC and is backed up/restored with DBC.

  • If archive includes DBC, SYSUDTLIB, they are backed up in order then alphabetically

  • ROT: backup 1 session/AMP, restore 2 sessions/AMP for small systems (<40 AMPS)

  • Default: 4 sessions + 2 control sessions = 6 sessions

  • DB level restore will restore data dictionary and thus delete data from the table that was excluded when archived. To prevent that, restore at table level
  • ARC doesn’t rebuild USI, use BUILD DATA TABLES
  • You cannot archive/restore individual tables in DBC.
  • Restore v/s Copy: triggers cannot be copied, only restored. SP copy only if at DB level
  • NO BUILD during restore prevents build of SI and fallback table rows
  • Tables with unresolved RI cannot be archived
  • Parameters evaluation: ARCENVX > command line > ARCENV > ARCDFLT
  • Use ANALYZE to view the contents of an existing archive file.
  • Use LONG option to view partitions’ bounding conditions for partition level backups
  • ANALYZE doesn’t require LOGON/LOGOFF
  • After PJ: Backup AMP (h/w failure protection), Before PJ: Primary AMP (s/w failures)
  • If there are >1 named checkpoints, RollForward uses oldest, RollBack uses latest
  • Rollforward can optionally act only on primary data and then use BUILD later
  • AMP specific RollForward must be to end of the journal (no TO CHECKPOINT)
  • ARC reblocks data when sending to backup server (thus losing BLC). DSA, however, sends the same FileSystem block to backup server.
  • If a larger system is restored to a small system, it might lead to DBC having negative PERM space. => System is unusable. Reduce MAXPERM of child databases to rectify.
  • Restore roughly takes about 2.5 times the backup time

System Utilities

  • Use dip utility to create system views. 3 ways to run, START DIP, /tpasw/bin/dip, bteq
  • dbw (operator console):
  • {enable,disable} [all] logons
  • abort session, get config, query state, restart tpa
  • get/set logtable <rss table> on/off
  • get/set activelogtable <rss table> (only log rows for active periods)
  • get/set resource (rss collection/logging rates)
  • grant/revoke
  • checktable: check tables for inconsistencies.
  • Levels: PendingOp(fload,mload,restore etc),1(counts),2(rowids),3(rows)
  • vprocmanager: display/modify vproc states. Initiate database restarts.
  • vprocs have two states. VPROCSTATE/configstate (teradata db state)
  • BOOT: init and start AMP with states FATAL/Down
  • INITVDISK: init TD FS for AMP in FATAL or NEWPROC states
  • RESTART, SET RESTART COLD/COLDWAIT (defer/wait tran recovery)
  • SET <vproclist> = <vprocstate>, manually set state
  • Only utility that allows TD startup to wait until recovery is finished
  • dumplocklog: logs, in a table, lock contentions and global deadlocks (including HUT)
  • Must set LockLogger in DBS to true
  • ferret: Use scope subcmd to set scope of other commands
  • ShowSpace - Sys level space; perm, spool and temp
  • ShowBlocks: space at sub-table level
    • * next to Cyl => large. # next to Blocks => blocks are compressed
  • packdisk: packs cylinders (db size doesn’t change),
    • force overrides FSP specified at table level
    • Use showfsp to estimate savings
  • defrag: defagments cylinders in scope
  • scandisk: validate MI, CI, WAL structure, ROWIDs (not userdata)
  • priority: 0(low), 1(med), 2(hi), 3(rush)
  • showwhere: shows the temperatures of blocks of <table|vproc|WAL Log>
  • showcylalloc: shows how much storage used/available and at what temp <vproc>
  • force: force data to a specific temperature
  • compress/uncompress do not update space usage. Must run UPDATESPACE
  • gtwglobal: old tool to manage sessions on a gateway (display/kill/disconnect)
  • lokdisp: displays tran locks for AMP, group of AMP or all AMPs. No HUT locks
  • BLOCKERS: blocked and blocking transactions
  • DB/TABLE/ROWHASH: database/table/row level locks
  • TRAN: vprocid(PE) uniq1 uniq2 (proc+uniq1+uniq2 is tran ID)
  • qrysessn: Displays all sessions including tran, arc, fload, mload, fexp
  • states: active,idle,delayed,sesdelayed (TASM util limit),response,parsing...
  • parent/child sessions, when running fload,mload,arc. 1. child sessions are active only loading (fload) or acquisition(mload) phase
  • rcvmanager: contains journals for transaction recovery and down amp recovery
  • Can only run when in Logon, Logoff, Startup or Lonon/quiet or logoff/quiet mode
  • AMP catchup journal counts: OJ (ordered sys chg journal: DDL), CJ (chg journal: DML)
  • LIST STATUS, LIST LOCKS, CANCEL ROLLBACK ON TABLE
  • Only single base table LOCK READ OVERRIDE SELECT are allowed
  • Only DELETE ALL can be used on rollback canceled tables.
  • RECOVERY/REBUILD PRIORITY HIGH|MEDIUM|LOW
  • Alter table or NUSI rollback doesn’t show up in "TJ Rows left/done counts"
  • rebuild: rebuild specific tables, only fallback tables or all tables on an AMP. Restartable.
  • Only needed when data corruption is suspected.
  • fallback tables from fallback or builds just the table header for non-fallback tables
  • must use ALTER TABLE … RESET DOWN to turn them online after rebuild
  • rebuild priority is set with rcvmanager
  • schmon (scheduler monitor) parms:
  • -b n: create RP, -a n: create AG, -p n <PG>: create PG
  • -d: display, -m: monitor current node, -M: mon all, -s: disp specific session
  • showlocks: displays HUT locks held by ARC utility v/s rcvmanager v/s lokdisp
  • tdlocaldef: maintains SDF (Spec for Data Fmt) file
  • tpareset: -x exit (no restart). -stop (restart except this node), -dump. Needs reason
  • updatespace: updates current perm,spool and temp space
  • use it when there is orphan or phantom spool assigned to a user, or ferret (un)compress
  • sel * from dbc.diskspace where databasename not in (sel username from dbc.sessioninfo)
  • updatedbc: updates max space, unlike updatespace which updates current space
  • /etc/init.d/dcs restart, twice to clear up Viewpoint unknown system error.
  • pdeconf: Update Linux user/groups used by PDE software and protected UDF

Restarts

  • tpareset or restart command. Options: -f force, -x shutdown, -d dump, -l delay secs,
  • cold/coldwait: Allow logins before/after AMP recovery.
  • exec allrestarts(date - 60) shows restarts in last 60 days, tpatrace, dbc.Software_Event_LogV
  • PDE states: : DOWN/START/RUN/RESET and STOP
  • disk failures and recovery
  • Single drive failure: system continues to run, replace damaged drive.
  • both drive fail: AMP fails. tpa reset, AMP offline. fallback is fine. replace drive. table rebuild for fallback, restore other tables
  • two amp in cluster fail: machine halts. restore dbc and user tables
  • Bynet failure: No tpa reset if one of two fails. If both fail, machine halts
  • AMP/PE failure: tpa reset, two AMPs fail in the single cluster, halt
  • Node failure: vprocs migrate except PE serving channel connections
  • SWS failure: no monitoring capability but no restart. Reboot AWS to recover
  • Unscheduled restarts generate crashdumps.