Archives of the TeradataForum

Message Posted: Mon, 02 Apr 2001 @ 14:40:20 GMT

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

I don't think that you're going to be able to accomplish what your looking for. The problem lies with the fact that you're dealing with a non-hashed table (ie- DBC.DATABASESPACE). Unlike a normal table where the data is distributed across all the AMPs based on the hash value calculated from the PI, the data in a non-hashed table is not distributed by hash code. Instead, data is written into a non-hashed table that is specific to the AMP on which the data is being written.

So as a job is running and consuming perm, temp and spool space (on a per-AMP basis), the rows for the tables being affected by that job are updated in DBC.DATABASESPACE on that same AMP. So as the PERM, TEMP and SPOOL values change on AMP 1-0, the rows in DBC.DATABASESPACE on AMP 1-0 are updated. What is important here is that if these rows were hashed, then keeping track of system space would generated a tremendous volume of inter-AMP traffic.

Other non-hashed tables include: DBC.ACCTG, DBC.CHANGEDROWJOURNAL, DBC.LOCALSESSIONSTATUSTABLE, DBC.LOCALTRANSACTIONSTATUSTABLE, DBC.ORDSYSCHNGTABLE, DBC.RECOVERYLOCKTABLE, DBC.RECOVERYPJTABLE, DBC.SAVEDTRANSACTIONSTATUSTABLE, DBC.SYSRCVSTATJOURNAL, DBC.TRANSIENTJOURNAL & DBC.UTILITYLOCKJOURNALTABLE. Of these tables, only DBC.ACCTG and DBC.DATABASESPACE can be accessed with SQL. Looking at the other non-hashed tables, you can imagine the amount of inter-AMP traffic if these were hashed tables.

Since the contents of a non-hashed table are not distributed by hash value, you can't use a join to access those rows. Actually, it's good that message 3719 has been added - it looks like it was added with V2R4. Prior to this error message, the system would happily execute a join to a non-hashed table. But if you double checked the results of an update, you would find that some rows were affected and other weren't. The rows affected would be those that just so happen to be on an AMP where they would have been placed if the table was hashed.

So to change rows in a non-hashed table, you have to use SQL that will result in a full-file scan. That's why DBC.CLEARPEAKDISK works:

     UPDATE DATABASESPACE
          SET PEAKPERMSPACE = 0
            , PEAKSPOOLSPACE = 0
            , PEAKTEMPSPACE = 0
          ALL;

In the past, to accomplish what you're trying to do, I wrote a SELECT that would export UPDATE statements (like the ones provided by Rudel and Ralf) and then using BTEQ, EXPORTed the results into a file. After the EXPORT, I then .RUN that file. It's really awkward, but I don't think that you're going to find anything more graceful.

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