Skip to content

Archive for

How to aborted the rollback process in Teradata?

If for some reason you have ran a huge delete and the process was terminated then that huge table rollback will take lots of time. Generally 3-4 times the time it ran before being killed.
Below are the steps as how you can kill a rollback process but do note that if you do this step then the table will become unusable and you have to drop and recreate.

Steps to follow:
1) Connect to your viewpoint server
2) Go to remote Console port let
3) Select your Teradata database server and then select Recovery Manager Utility. You will find this option on the left top just under port let name)
4) Now, run below queries to find the tables which are being rolled back

Enter command, “QUIT;” or “HELP;” :
LIST ROLLBACK TABLES;
TABLES BEING ROLLED BACK AT 14:35:29 15/10/06

ONLINE USER ROLLBACK TABLE LIST

Host Session User ID Workload Definition AMP W/Count
—- ——– ——— —————————— ———–
1 12252 0000:03B2 NoWD-PG1054 125

TJ Rows Left TJ Rows Done Time Est.
————- ————- ———
190950034 122512 1904:52:18

Table ID Name
——— ——————————————————————
0000:1AC1 “DATA_TEST”.”MY_TABLE”

SYSTEM RECOVERY ROLLBACK TABLE LIST

Host Session TJ Row Count
—- ——– ————-

Table ID Name
——— ——————————————————————

5) Run below queries to actually kill rollback. Note we will use Table ID from the output of above command

Enter command, “QUIT;” or “HELP;” :
CANCEL ROLLBACK ON TABLE 00001AC1;
Type the password for user DBC or press the Enter key to return:

Rollback will be cancelled for:
0000:1AC1 “DATA_TEST”.”MY_TABLE”

Confirm y/n ?

Y

6) Check again to make sure that rollback is killed

Enter command, “QUIT;” or “HELP;” :
list rollback tables; 
TABLES BEING ROLLED BACK AT 14:39:25 15/10/06

ONLINE USER ROLLBACK TABLE LIST

Host Session User ID Performance Group AMP W/Count
—- ——– ——— —————————— ———–

TJ Rows Left TJ Rows Done Time Est.
————- ————- ———

Table ID Name
——— ——————————————————————

SYSTEM RECOVERY ROLLBACK TABLE LIST

Host Session TJ Row Count
—- ——– ————-

Table ID Name
——— ——————————————————————

Enter command, “QUIT;” or “HELP;” :

Netezza – Get table and view count per database

Here is how you can find the total number of tables per database and number of columns associated to these tables as well as number of views per database and number of columns associated to the views.

Just save this script and execute:

for tab in `nzsql -At -c “select database from _v_database;”`
do

nzsql -At -d ${tab} -c “select database,’VIEW’,count(distinct name) from _V_RELATION_COLUMN where type=’VIEW’ group by database;”
nzsql -At -d ${tab} -c “select database,’TABLE’,count(distinct name) from _V_RELATION_COLUMN where type=’TABLE’ group by database;”
nzsql -At -d ${tab} -c “select database,’TABLE COLUMNS’,count(*) from _V_RELATION_COLUMN where type=’TABLE’ group by database;”
nzsql -At -d ${tab} -c “select database,’VIEW COLUMNS’,count(*) from _V_RELATION_COLUMN where type=’VIEW’ group by database;”

done