When a session appears idle, it is also referred to as a hung session. Hung sessions can be a result of problems with lock contention, a transaction rollback on a job with an error, the TDP losing communication with Vantage, or some other problem. The tools listed in the following table can help troubleshoot hung sessions.
| Utility | What It Determines |
|---|---|
| Query Session (qrysessn) | The status of each session. For more information on what the different statuses mean, see “Query Session (qrysessn)” in Teradata Vantage™ - Database Utilities, B035-1102. |
| Recovery Manager (rcvmanager) | If the system is rolling back any transaction. See "Recovery Manager (lokdisp)" in Teradata Vantage™ - Database Utilities, B035-1102. Some rollbacks can take a long time (several hours) to process because every TJ on every AMP must be accessed and updated.
|
| Lock Display (lokdisp) | Transaction locks blocking the system. See “Lock Display (lokdisp)” in Teradata Vantage™ - Database Utilities, B035-1102. |
| Show Locks (showlocks) | If there are host utility locks or HUT locks. You may have determine if there is a lock on a certain table cleared by release lock override and system running normally. See “Show Locks (showlocks)” in Teradata Vantage™ - Database Utilities, B035-1102. |
If there is an error during an archive or the archive job is killed prematurely, the outstanding locks from the job may block other sessions. To resolve this problem:
- First run Query Session (qrysessn) to determine which session are blocked.
- Then run Recovery Manager to see if anything is in recovery.
- Next, check to see if there are any locks present and what kind of locks they are.
- If you notice DBC has a WRITE lock on anything, run the CheckTable utility to determine the state of the tables. If any tables are corrupted, try dropping and restoring the table.
- If there are DSA jobs that are hung, release the locks by using the RELEASE LOCK (databasename) ALL, OVERRIDE statement.
- Use QuerySession to check whether formerly blocked sessions are now active.
Sometimes, you may have to abort the session and restart the system (tpareset) to clear a hung session. However, first contact Teradata Services and determine if you should take a crashdump before restarting the system so that you can try isolating the problem causing the block.
You may run across problems with FastLoad or MultiLoad sessions. This is usually indicated by either a 2633, 2574, or 2738 error. Use the DBC.SessionInfoV view to determine the host number, username, and partition associated with the session number.
If a session experiences an SQL error and the system attempts to roll back the transaction, and other sessions become blocked during this time, you may need to issue aborts using Teradata Viewpoint. See Help in Teradata Viewpoint for information on how to issue an abort.
For information on how to handle a slow or hung job, see the sections beginning with Investigating Query Blocks and Delays.