EncartaLabs

Teradata Database Administration

( Duration: 3 Days )

Teradata Database Administration training course is designed to provide attendees with in-depth knowledge of system administration for the Teradata database. They will learn to run a Teradata warehouse on a daily basis from the ground up.

By attending Teradata Database Administration workshop, attendees will learn to:

  • Set up the Teradata database
  • Create databases and users
  • Allocate perm and spool space
  • Apply data protection including Transient Journal, Fallback and RAID
  • Grant and revoke security privileges
  • Load, archive and restore data
  • Implement Teradata protection features

  • IT Professionals

COURSE AGENDA

1

The Rules of Data Warehousing

  • Teradata facts and certification
  • Teradata: brilliant by design
  • The Teradata parallel architecture
  • A logical view of the Teradata architecture
  • The Parsing Engine (PE)
  • The Parsing Engine in detail
  • The request and respond parcel
  • The Parsing Engine knows all
  • The Access Module Processors (AMPs)
  • The BYNET
  • A visual for data layout
  • Teradata is a shared nothing architecture
  • Teradata has linear scalability
  • How Teradata handles data access
  • The PE uses statistics to come up with the plan
  • When there are no statistics collected on a table
  • Teradata cabinets, nodes, VPROCs, and disks
  • A Node and its memory allocations
  • Each PE has a plan library called RTS cache
  • LAN connection for network attached clients
  • Mainframe connection to Teradata
  • Sessions and session pools
  • Teradata configuration utilities
  • Config and reconfig
2

Teradata Space

  • How permanent space is calculated
  • How permanent space is given
  • The Teradata hierarchy
  • How spool space is calculated
  • A spool space example
  • PERM, SPOOL and TEMP space
  • Space allocation review
  • AMP disks have cylinders and data blocks
  • Full cylinder read
  • Table header
  • Each table is given a table ID
  • How data blocks are dynamically built
  • Data blocks
  • How Teradata finds a row of data
  • The master index
  • The cylinder index
  • Cylinder index changes
  • How Teradata writes to an AMP
  • Writing to data blocks of equal length
  • When a data block is not big enough for a write
  • How Teradata allocates blocks
  • Block and row definitions
  • Large row versus oversized row
  • Defragmentation
3

DBC Data Dictionary Tables

  • Data dictionary directory
  • The parsing engine has data dictionary cache
  • Data dictionary directory tables
  • System views
  • Accessing restricted views
  • Selecting information about created objects
  • Children view
  • Databases view
  • DBC.Users view
  • Indices view
  • AllTempTables view
  • Finding table names using the LIKE command
  • Finding table names in a particular database
  • Using the keyword USER on DBC views
  • Using DBC.AMPUsage
  • Using DBC.TableSize
  • Keeping track of logons and logoffs
4

Access Rights, Roles and Profiles

  • Access rights
  • Automatic, implicit, and explicit rights
  • Tools for finding access rights
  • The REVOKE statement
  • Roles
  • Creating roles
  • Setting and modifying roles
  • DBC.RoleInfo and DBC.RoleInfoX
  • Profiles
  • Creating profiles
  • Modifying profiles
  • DBC.ProfileInfo and DBC.ProfileInfoX
5

DBS Control

  • DBS control record – general fields
  • DBS control record – file system fields
  • DBS control record – performance fields
  • DBS control records you should know about
  • DBSControl – performance columns
6

Query Analysis and Tools

  • Database Query Log (DBQL)
  • DBQL collection options
  • DBQL tables and views
  • How to begin logging for DBQM
  • Access logging
  • Statistics wizard
  • Index wizard
  • TSET
  • Teradata visual explain utility
7

Teradata Protection Features

  • Transaction concept & Transient Journal
  • How the Transient Journal works
  • FALLBACK protection
  • How Fallback works
  • Fallback clusters
  • Down AMP Recovery Journal (DARJ)
  • Redundant Array of Independent Disks (RAID)
  • Cliques
  • Cliques – a two node example
  • Cliques – a four node example
  • Permanent Journal
  • Table create with Fallback and Permanent Journaling
  • TDQM
8

Starting and Stopping Teradata

  • Restarts of the Teradata database
  • Automatic restarts
  • Hardware failures
  • Critical database errors
  • UNIX operating system restarts
  • DBA forced restarts
  • Restarting in UNIX
  • Restarting the DB Window (UNIX Only)
  • Restarting in Windows 2000
  • Startup and recovery
  • Transaction recovery
  • Performing online and offline catch-up
9

Databases, Users and Accounting

  • Creating a database
  • Creating a user
  • Modifying and deleting a user
  • Specifying account priorities
  • System accounting
  • System accounting views
  • DBC.AcountInfo[x] view
  • DBC.AMPUsage view
10

Views and Macros

  • Creating views
  • Creating simple views and views that join tables
  • How to change a view using REPLACE
  • How to drop a view
  • View aggregation
  • Using “Locking for Access” in views
  • You can update tables through views
  • Restricting update rows with check option
  • Creating macros
  • Why the PE loves the macro
  • Creating a macro
  • Macros that use parameters
  • Changing a macro using REPLACE
  • How to execute a macro
  • How to drop a macro
11

System Access Control Levels

  • Teradata password encryption
  • Password security features
  • Host logon processing
  • GRANT/REVOKE LOGON statement
  • Session related views
  • DBC.SessionInfo view
  • DBC
  • Data access information views
  • AccLogRules views
  • AccessLog views
12

Priority Scheduler

  • Priority scheduler partition hierarchy
  • Priority scheduler hierarchy definitions
  • Resource partition example
  • Multiple resource partitions example
  • Scheduling policies
  • Performance periods
13

Teradata Manager

  • Teradata manager applications
  • Teradata dashboard – new feature
  • Workload trend analysis/data collector – new feature
  • Priority scheduler administrator – new feature
  • Teradata manager service
  • Starting teradata manager
  • Enable data collection
14

Monitoring Tools

  • Performance monitor – overview
  • Performance monitor
  • Performance monitor – continued
  • PMON main window
  • PMON sessions screen
  • Viewing session status
  • Monitoring session status
  • Session status report descriptions
  • Teradata administrator (WinDDI)
  • Teradata manager dynamic utilization charting
  • The alert facility and viewer
  • The alert viewer
  • The alert policy editor
  • Locking logger
  • Locking logger functions
  • Xperfstat
  • Teradata manager remote console
15

Teradata Remote Console Utilities

  • Starting the database window (DBW)
  • QRYCONFIG
  • QRYSESSN
  • RCVMANAGER (recovery manager)
  • SHOWLOCKS
  • VPROCMANAGER
  • FERRET UTILITY (file reconfiguration tool)
  • SHOWSPACE
  • SHOWBLOCKS
  • PACKDISK
  • SCANDISK
16

Resource Usage Data

  • ResUsage collection and logging
  • Setting resource logging – DBW
  • Collection costs
  • Resource usage tables
  • ResNode macros
  • ResNode macros – continued
  • RSSMon
17

Loading the Data

  • Fastload
  • FastLoad has some limits
  • Three key requirements for FastLoad to run
  • Maximum of 15 loads
  • FastLoad has two phases
  • PHASE 1: acquisition
  • PHASE 2: application
  • Fastload example
  • Restarting FastLoad
  • How the Checkpoint option works
  • Restarting with Checkpoint
  • MultiLoad
  • Two MultiLoad modes: IMPORT and DELETE
  • Block and tackle approach
  • MultiLoad imposes limits
  • RESTARTing Multiload
  • RELEASE MLOAD: when you don’t want to restart MultiLoad
  • TPump
  • Why it is called “TPump”
  • TPump has many unbelievable abilities
  • TPump has some limits
  • LOAD parameters in common with MultiLoad
  • .BEGIN LOAD parameters unique to TPump
  • A simple TPump script — a look at the basics
  • TPump script with error treatment options
  • RESTARTing TPump
  • TPump and MultiLoad comparison chart
  • Fastexport
  • How FastExport works
  • FastExport fundamentals
  • FastExport supported operating systems
  • Maximum of 15 loads
18

Archiving Data

  • Archive and recovery statements
  • Recovery vs. FastLoad
  • Invoking archive
  • Invoking archive – continued
  • Restart log
  • ANALYZE statement
  • Archive
  • Database DBC archive
  • Indexes option
  • Database DBC archive
  • Archive and Recovery (ARC) examples
19

Restoring Data

  • The RESTORE statement
  • COPY statement
  • Copying tables
  • BUILD statement
  • RELEASE LOCK statement

Encarta Labs Advantage

  • One Stop Corporate Training Solution Providers for over 4,000 Modules on a variety of subjects
  • All courses are delivered by Industry Veterans
  • Get jumpstarted from newbie to production ready in a matter of few days
  • Trained more than 50,000 Corporate executives across the Globe
  • All our trainings are conducted in workshop mode with more focus on hands-on sessions

View our other course offerings by visiting http://encartalabs.com/course-catalogue-all.php

Contact us for delivering this course as a public/open-house workshop/online training for a group of 10+ candidates.

Top