Archives of the TeradataForum

Message Posted: Mon, 12 Mar 2001 @ 17:38:20 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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)

Regards,

Thomas F. Stanek
TFS Consulting

	<Prev	Next>		<<First	<Prev	Next>	Last>>