Delete info from DBC

Database
Enthusiast

Delete info from DBC

i created a database "jehan", have enough free space but i cant create index or table any one, the error which i get is "no more room in DBC", remember query log is not enabled

when ever we do any DDL like index creation or table creation, it is recorded in DBC, database, how to delete it, to free space.
4 REPLIES
Junior Contributor

Re: Delete info from DBC

Hi Jehan,
your other post sowed that dbc is way over it's limit. This might happen, because some system tables are owned by dbc but are not counted for a "no more room in database". I know of three tables (there might be others):
- dbc.eventlog/dbc.sessioninfotbl, the session history: you should be glad about that, because otherwise you could't logon anymore ;-) But this is probably not the root of your problem.
- It's probably dbc.transientjournal, the transaction log:
Check the size of that table and commit any open transactions. If there are no open transactions, restart Teradata (as it's just the demo version you're probably the only user). There was a problem with an ever-growing TJ, but i don't think it was V2R5.1.
If that table is small or your problem is still noit solved, post the output of following query:
SELECT tablename, SUM(currentperm), SUM(peakperm)
FROM dbc.tablesize
WHERE databasename = 'dbc'
GROUP BY 1
QUALIFY RANK() OVER (ORDER BY SUM(currentperm) DESC) <= 20

Dieter
Enthusiast

Re: Delete info from DBC

i tried to format the output well for understanding, but while posting the spaces are automatically removed, kindly interpret the following code as seperated by "="

TableName =Sum(CurrentPerm)= Sum(PeakPerm)

---------------------------------------------------------------------

CHANGEDROWJOURNAL= 126,072,320 = 126,072,320

TVM = 2,232,320 = 2,330,112

TVFIELDS= 1,879,040 = 2,047,488

ACCESSRIGHTS = 374,272 = 472,064

TEXTTBL = 343,552 = 343,552

ERRORMSGS = 163,328 = 163,328

DBCASSOCIATION = 105,984 = 107,008

INDEXES = 52,224 = 69,120

DATABASESPACE = 45,568 = 84,992

DBASE = 17,920 = 18,432

TRANSLATION= 15,360 = 15,360

RESUSAGESVPR = 8,704 = 8,704

SYSRCVSTATJOURNAL= 8,192 = 8,192

COLLATIONTBL = 5,632 = 5,632

RESUSAGESPMA = 5,632 = 5,632

RCEVENT = 4,608 = 4,608

RESUSAGEIVPR = 4,608 = 4,608

DBQLOGTBL = 4,608 = 4,608

CONSTRAINTNAMES= 4,096 = 4,096

DBQLSQLTBL = 4,096 = 4,096

I connected via DBC username and password
when i want to execute

"delete from dbc.CHANGEDROWJOURNAL";
error message: A system journal table cannot be accessed via teraData SQL

and when i want to execute
delete from dbc.TVM;
error: the user "DBC" does not have delete Access to DBC.TVM

and when i want to grant permission to DBC
same error "no more room in DBC"
Junior Contributor

Re: Delete info from DBC

Hi Jehan,
the CHANGEDROWJOURNAL is used when an AMP is out of service to capture changes for Fallback tables.

The conclusion is: an AMP is FATAL.
Start the Database Window, click on the Suprv window and check the output of "get config".
Please also check the windows eventlog for Teradata errors why it was failing.

Then you have to use the "VprocManager" and "Table Rebuild" utilities, check the Utilities manuals for details, especially:
Utilities - Volume 3, T-Z
Chapter 1: Table Rebuild (rebuild)
REBUILD AMP
Before Rebuilding All Tables

If you can't fix it you might re-install the demo version as a last resort.

Dieter

Dieter
rgs
Enthusiast

Re: Delete info from DBC

Sometimes the AMP could be down because of a hardware/software glitch, meaning the data for the AMP is just fine. If that is what it is, it would be simpler to bring it back online from vprocmanager. As Dieter said check the status of the AMPs with vprocmanger.

From Supvr window the command is “start vprocmanager”. A vprocmanger window opens up type the command “status”. It should list the status of the all AMPs (2 I think in the demo) and the PE. One of them is going to say “FATAL”. If that is the case enter the command “set n = online” where n is the number of the AMP that is fatal (vproc number). If you give it a minute or so and enter the “status” command again in vprocmanager it should have a new status of “CATCHUP”. That means it is bringing the AMP back to the state of the online AMP. It is processing the CHANGEROWJOURNAL. The AMP will remain in this mode catching up forever. You can start rcvmanager and check the status of its progress. If that does not work then a REBUILD of the AMP might be required.

If you want to bring the AMP online after it has caught up you have to restart the database. While the AMP is catching up you can use the system. But if you do updates it will generate more change rows which the database has to apply. The point is somewhere down the road it will be caught up at which point you “restart” the database to bring the AMP back online. If you just want to bring the AMP online with all the changes applied and wait while it is doing that you can restart the system with what is called a COLDWAIT restart. You do that by typing “restart COLDWAIT” in vprocmanger after you brought the AMP to online status as indicated above.