Archives of the TeradataForum
Message Posted: Mon, 12 Mar 2001 @ 17:38:20 GMT
I have used the following SQL in the past to monitor a rollback. I execute the query at regular intervals to get a snapshot of how a rollback is progressing. As Dave Wellman mentioned, you really need to have an understanding of the transaction involved, but this query has helped us better understand what the system is doing. We have also used it to determine whether it's better to ABORT a query that we know is wrong and let the rollback process, or let the query complete and correct the problem afterwards. You'd hate to ABORT a 10 hour transaction that is 10 minutes away from completion if you don't have to.
If you drop the results into a spreadsheet and add a few calculations, you can compute the rate of rollback and make some projections as to the completion time. Keep in mind that TransientJournal keeps track of all maintenance-type activity which means the the JOURNALSIZE column may not be overly useful if there are other updates, inserts, or deletes occurring while you are monitoring. Also, you'll notice that the row counts may change while the spool and TransientJournal sizes don't for several intervals. That's because the rows in these tables aren't deleted until all of the rows are processed for an AMP. The result is that you will typically see the TransientJournal, for example, decrease in size in steps, as eah AMP completes the rollback for it's process.
Hope this is useful.
Locking target_table FOR ACCESS SELECT DATE ,TIME ,SPOOLSIZE ,SPOOLAMPCNT ,JOURNALSIZE ,TABLESIZE ,ROWCNT FROM (SELECT SUM(CURRENTSPOOL) FROM DBC.ALLSPACE WHERE DATABASENAME EQ 'userid') AS S (SPOOLSIZE) ,(SELECT SUM(CASE WHEN CURRENTSPOOL EQ 0 THEN 0 ELSE 1 END) FROM DBC.ALLSPACE WHERE DATABASENAME EQ 'userid') AS A (SPOOLAMPCNT) ,(SELECT SUM(CURRENTPERM) FROM DBC.ALLSPACE WHERE DATABASENAME EQ 'DBC' AND TABLENAME EQ 'TransientJournal') AS J (JOURNALSIZE) ,(SELECT SUM(CURRENTPERM) FROM DBC.ALLSPACE WHERE DATABASENAME EQ 'target_database' AND TABLENAME EQ 'target_table') AS T (TABLESIZE) ,(SELECT COUNT(*) FROM taret_table) AS R (ROWCNT)
Thomas F. Stanek
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|