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.