The following table lists some common tools you can use to administrate your system. The table also lists tools intended for use by Teradata field engineers or Vantage system developers. If the tools are described as for use by Teradata personnel only, do not use them unless instructed by Teradata Support.
For more information on the respective tool, see the documentation referenced. For a list of the utilities by suggested function (such as maintenance, troubleshooting, or installation), see the “Alphabetical Listing of Utilities” in Teradata Vantage™ - Database Utilities, B035-1102.
What to Troubleshoot or Administrate | Tool and Description | Reference |
---|---|---|
Administration Workstation | The AWS console for an MPP installation displays the status of each physical component, including nodes, DBW consoles, and the BYNET. It provides many of the functions the System Console would for an SMP. | AWS manuals |
AMPs and AMP Worker Tasks | The ampload utility reports AMP bottlenecks due to unavailable free AWTs. The AWT Monitor (awtmon) utility quickly views how many AMP worker tasks are active (in use) and determines “hot AMPs” so you can troubleshoot performance problems. awtmon is a front-end tool to the “puma -c” command and prints AWT in-use count information in a summary format. By default, awtmon displays the AWT in-use count of the local node. With the -s option, print system-wide information and locate hot AMPs on the nodes of the entire system. When hot AMPs are identified, run awtmon on those hot nodes using pcl or psh. You can also use the ResUsageSawt table to see AMP worker tasks in-use counts by work type and determine when AMPs enter the state of flow control. To determine which workload is responsible for I/O skew, use the ResUsageSps table and then use DBQL to identify specific queries. |
|
Data corruption and integrity | Checksums check the integrity of disk I/O operations. A checksum is a numeric value computed from data. For a given set of data, the checksum value is always the same, provided the data is unchanged. Because calculating checksums requires system resources and may affect system performance, system-wide checksumming is disabled by default on most platforms. Contact Teradata Support if you suspect disk corruption. Use the CHECKSUM option of CREATE TABLE, ALTER TABLE, CREATE JOIN INDEX, and CREATE HASH INDEX statements to enable checksums for a table, hash index, or join index. |
“Checksum Fields” in Teradata Vantage™ - Database Utilities, B035-1102 |
The SCANDISK command of the Ferret utility checks the integrity of the file system. This includes the master index, cylinder index, and data block structures (including structures associated with WAL logs). Previously halted SCANDISK operations can be restarted using a Perl script. If further events occur after SCANDISK has been running for some time, restarting SCANDISK where it left off the last time will not find errors in previously scanned data that were caused by the most recent failures. Therefore, be very careful when deciding to restart a pending SCANDISK operation versus starting the entire operation again from the beginning.
|
“Ferret Utility (ferret)” in Teradata Vantage™ - Database Utilities, B035-1102 | |
Disk space | The Ferret utility allows field engineers and Teradata support people to monitor and control disk usage. The commands of this utility allow the system to combine free sectors on a cylinder, reconfigure contents on a disk leaving a specified percentage of space free for future use, report which tables qualify for packing and display disk cylinder utilization and available free cylinders. This utility is used by Vantage engineers to perform routine and special diagnostics.
|
“Ferret Utility (ferret)” in Teradata Vantage™ - Database Utilities, B035-1102 |
Update DBC utility recalculates
PermSpace, SpoolSpace, and TempSpace for user DBC in DBase table.
Then based on DBase values, Update DBC utility recalculates
MaxPermSpace and MaxSpoolSpace in DBC.DataBaseSpace for all
databases. Use Update DBC only to correct inconsistency in the DBase or DataBaseSpace tables, which might occur as a result of rare types of system failures. |
“Update DBC (updatedbc)” in Teradata Vantage™ - Database Utilities, B035-1102 | |
Update Space recalculates permanent, temporary, or spool spaces for one database or all databases. | “Update Space (updatespace)” in Teradata Vantage™ - Database Utilities, B035-1102 | |
The FixCurrentSpace SQL stored procedure performs the same functions as the Update Space utility.fixcurrentspace | FixCurrentSpace Procedure | |
The FixAllocatedSpace SQL stored procedure repairs the dynamic need-based allocations for databases that use global space accounting. This procedure is fast because it assumes actual current usage is correct. | FixAllocatedSpace Procedure | |
The DBC.DiskSpaceV view provides disk space usage information per AMP. This includes permanent and spool data by database or account for each AMP. Use this view to track large spool usage and available PERM space. |
|
|
Down AMP | If you receive an error that an AMP is down (for example from a MultiLoad or BTEQ job) use Vprocmanager to reboot a fatal AMP or bring a offline down AMP up. | “Vproc Manager (vprocmanager)” in Teradata Vantage™ - Database Utilities, B035-1102 |
Down Table | The system will mark a data or index subtable as down if an error occurs in several regions of the file system structures of the table. This error could be due to hardware or bad I/O. To address or fix a down region in a subtable, do one of the following:
After repairing a down table, use the ALTER TABLE ... RESET DOWN statement to reset a down table. In the case of hardware errors, a fallback table can be rebuilt to copy the rows from the fallback subtable, to repair the rows in the down regions of the primary subtable, and vice versa. If the down table is rebuilt or restored from an earlier back up, the down status flag for the various subtables will be reset, and the rows in them will also be moved to unmarked data blocks and cylinders from the fallback copies. To change the number of regions per AMP that must fail before a table is marked as down, use the MaxDownRegions field in DBS Control. See “Table” in this table for more information. |
|
Global parameters | DBS Control displays and modifies the tunable global parameters in the DBS Control record. | “DBS Control (dbscontrol)” in Teradata Vantage™ - Database Utilities, B035-1102 |
Gateway settings | The Gateway Control utility allows you to set Gateway settings such as how to handle encryption, number of session allowed for host group, time out values and more. You can use options in the Gateway Control utility to select things like connection time out length, external authentication settings, or whether to use the system default or customer-settable defaults after a configuration or addition of new host groups and gateway vprocs, and more. This utility is used by Vantage engineers to perform routine and special diagnostics.
|
|
The Gateway Global utility monitors and controls sessions for LAN-connected users. You can monitor traffic, control network sessions, display which users are using which sessions, or abort specific sessions. | ||
Hangs or slowdowns | See the topics “Sessions that are hung”, “AMPs and AMP Worker Tasks”, and “Resource usage” in this table. Resource Check Tools detect hangs or slowdowns as follows:
|
mboxchk, nodecheck, syscheck in man pages or pdehelp |
Loads into Large Tables | To improve the speed of a batch
INSERT/SELECT request into a large table, partition the table by
transaction date, which groups the inserts into data blocks within
the partition instead of throughout the table. This reduces the
number of I/Os. You can also use the DBQL utility job log table, DBC.DBQLUtilityTbl, to troubleshoot utility job performance. |
|
Lock contentions or held locks | There are several things that might cause lock contention: a pending lock, a very congested system, a number of transactions exceeding the machine capacity, or a conflict with DSA. Careful planning of what sessions run in which order or at what time of day can help prevent lock contention. Use the EXPLAIN. Consider running a request that needs an EXCLUSIVE lock and will take a while to process during off hours. |
Lock Contentions |
The Lock Display utility shows all real-time locks in use. This includes locks for concurrency control. You can identify which transactions are blocked and which transactions are doing the blocking. | “Lock Display (lokdisp)” in Teradata Vantage™ - Database Utilities, B035-1102 | |
You may have locking issues on DBC.AccessRights if you are running concurrent jobs with the same userid in the same database and dropping or creating objects. To reduce locking issues on DBC.AccessRights:
|
Teradata knowledge articles on reducing locking issues with specific applications | |
The DBQL Lock Log logs in XML
format in DBQLXMLLOCKTbl any lock contention longer than a
user-specified threshold. You can access the DBQL lock log through the Viewpoint lock viewer portlet or query the system table DBC.DBQLXMLLOCKTbl or the view DBC.QrylockLogXMLV. |
XML Lock Log Table: DBQLXMLLockTbl. | |
Showlocks utility identifies and displays all active host utility (HUT) locks placed on databases and tables by DSA operations. HUT locks may interfere with application processing and are normally released after the utility process is complete. If locks interfere with application processing, you can remove them by invoking the RELEASE LOCK statement. It is available through the DSA utility or as an SQL statement. Showlocks can be started from the DB Window Supervisor screen with the command start showlocks. |
|
|
Memory (insufficient memory resulting in Error 3710) | Teradata recommends that you use the default value set for the MaxParseTreeSegs field in DBS Control. If you run into this error, first try simplifying your queries and/or dropping unnecessary join indexes. If this still does not work, verify the value you have set for MaxParseTreeSegs and contact Teradata Support. The optimal value depends on your system and what release and fix level you are running. Increasing MaxParseTreeSegs to a value larger than the default may allow a previously failed 3710 error to run but could result in other problems such as a long parsing time or in extreme cases, a Teradata reset.
|
“MaxParseTreeSegs” in Teradata Vantage™ - Database Utilities, B035-1102 |
Priority of jobs | TASM assigns different priorities to different types of jobs. Higher priority work receives access to CPU and I/O more frequently. Use the ResUsageSps table to understand how CPU is being consumed by different workloads. You can also use this table to understand the pattern of database activity across the processing day. |
“ResUsageSps Table” in Teradata Vantage™ - Resource Usage Macros and Tables, B035-1099 |
Queries | Use BEGIN/REPLACE QUERY LOGGING WITH XMLPLAN or use the EXPLAIN IN XML request modifier. Alternately, use the STEPINFO option of BEGIN/REPLACE QUERY LOGGING to determine which step uses the most resources and if there are any large differences between estimated and actual CPU, I/O, and number of rows. Manage your queries using Teradata Viewpoint. You can:
|
|
Resource usage Teradata Vantage™ - Data Dictionary | Using resource usage macros and tables to obtain information about which nodes or which vprocs are heavily using which resources. This can help you identify bottlenecks. | Teradata Vantage™ - Resource Usage Macros and Tables, B035-1099 |
The DBQL view DBC.QryLogV reports things such as the AMP using the most CPU, the AMP with the most I/O, or maximum amount of spool used when processing a query. | See QryLog[V] in Teradata Vantage™ - Data Dictionary, B035-1092 | |
Sessions that are hung | Use Query Session utility to determine the state of each session and then use Lock Display utility to find lock contentions. | Troubleshooting Problems with Sessions |
Session information when logged onto multiple sessions | To find the Thread ID or Job ID of the session from which you are querying, first submit a SELECT SESSION; Then after that query returns the session number n, use that number in the following query: sel logonsource
from sessioninfovx
where sessionno = n;
Result: *** Query completed. One row found. One column returned. *** Total elapsed time was 1 second. LogonSource ------------------------------------------------------- (TCP/IP) 09AB 127.0.0.1 DBC 8804 USER BTEQ 01 LSS This helps you determine which Client process/Thread ID belongs to which session. This is especially useful if you want to find out which Thread ID belongs to which session because you are logged on to multiple sessions. |
“SessionInfo[V][X]” in Teradata Vantage™ - Data Dictionary, B035-1092 |
Query Session utility displays the state of load utility and query sessions. Details can include statuses such as Parsing, Active, Blocked, Response, whether stored procedures are being processed, and so on. | “Query Session (qrysessn)” in Teradata Vantage™ - Database Utilities, B035-1102 | |
Skew | There are many ways to detect
skew:
|
|
Slowdowns | See the topic “Hangs or slowdowns” in this table. | |
Slow queries to DBC tables | If your site uses a lot of custom applications or tools that often query the DBC tables, consider collecting statistics on these tables | About Collecting Statistics on Data Dictionary Tables |
Space Exceeded | If a space-intensive operation,
such as a load utility or restoring from an archive, exceeds
available perm space, two DBS Control utility fields can be set
temporarily to allow operations to complete and avoid out of space
errors. The settings apply to all operations and all instances of space accounting, such as table inserts, so Teradata recommends setting these fields to a non-zero value only temporarily. |
OverflowMaxPermSpace-Percentage and OverflowMaxPermSpace-KByteCount fields of the DBS Control utility in Teradata Vantage™ - Database Utilities, B035-1102 |
Alternatively, use global space accounting, which provides extra space to AMPs when needed, preventing space-intensive operations from failing due to lack of space. | Global Space Accounting | |
Spool space depletion | There are several reasons that could contribute to limited spool usage:
Limiting spool space for new users helps reduce the impact of possibly bad queries (such as product joins). With a reasonable limit, the user will get out-of-spool messages before a bad query ties up the system. Furthermore, adding spool to a bad query will allow the query to run longer but a bad query may still not complete. |
|
Space allocated to storage | Use the Teradata Virtual Allocator Manager (TVAM) utility. Allows you to display mapping or force migration of cylinders on the system. Do not use the TVAM utility unless instructed by Teradata Support.
|
Man page or online help |
System crashes or failures | Screen Debug and Screen Dump controls how and what your system records for crashdumps. Teradata recommends that default settings for all dumps be changed only when requested by a system support representative or Teradata Support. | “Control GDO Editor (ctl)” in Teradata Vantage™ - Database Utilities, B035-1102 |
DBC.Software_Event_LogV is a system
view that provides detailed information about errors or system
failures and their related node, vproc, partition, task, function,
software version, optional backtrace, diagnostic information, and so
on. The Event_Tag field of the view reports the error message number. |
|
|
DUL utility saves or restores system dump tables onto tape. | “Dump Unload/Load Utility (dul)” in Teradata Vantage™ - Database Utilities, B035-1102 | |
System recovery | Recovery Manager utility monitors and reports the progress of a system recovery after a crash or a user abort. You can use this utility to monitor transaction rollbacks, cancel rollbacks, and more. | “Recovery Manager (rcvmanager)” in Teradata Vantage™ - Database Utilities, B035-1102 |
Table | The Table Rebuild utility rebuilds tables Vantage cannot recover automatically. Table Rebuild can rebuild the following for an AMP:
|
|
Table inconsistencies or corruption | CheckTable is a diagnostic tool that checks for:
Options for CheckTable include the following:
CHECKTABLEB is a non-interactive batch mode version of CheckTable that can be run through the cnsrun utility. CHECKTABLEB is identical to CheckTable except that it is meant to run from a script. |
“CheckTable (checktable)” in Teradata Vantage™ - Database Utilities, B035-1102 |
The Table Rebuild utility allows
you to fix a primary or fallback copy of a table or only specific
corrupted parts. When the rebuild operation successfully completes
on an AMP, TableRebuild will remove the down region information from
the table header. If, however, the fallback subtable has any down
regions defined on it, the rebuild process aborts and the system
returns an error. You cannot rebuild a table with parts of its fallback down. While you can rebuild stored procedures, UDFs, and UDMS, you cannot rebuild a join index or hash index defined on a table being rebuilt. |
|
|
Vprocs | The Vproc Manager utility allows you to:
A few of the vprocs statuses include:
|
“Vproc Manager (vprocmanager)” in Teradata Vantage™ - Database Utilities, B035-1102 |
Vprocs and storage | From the Supervisor screen of the
Database Window, enter GET CONFIG to view the current system
configuration. To administrate vprocs and disks, use the Vproc Manager utility. |
|
The Query Configuration utility reports the status of vprocs managed by node or for the entire system. | “Query Configuration (qryconfig)” in Teradata Vantage™ - Database Utilities, B035-1102 | |
Vproc Manager (vprocmanager) utility | If vprocmanager exits with an error...# vprocmanager PDE is not running: Operation not permitted PDE event 10117 reported when task is not attached to PDE or PDE is down. Task should stop on this error. then you are probably attempting to run it from a non-TPA node. Run vprocmanager from a TPA node (a node running Vantage software). |
“Vproc Manager (vprocmanager)” in Teradata Vantage™ - Database Utilities, B035-1102 |