Archives of the TeradataForum

Message Posted: Fri, 30 Aug 2002 @ 23:19:30 GMT

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

There are a number of ways to do this. One method recently used simply performs the update on DBC.DataBaseSpace using the internal databaseid as shown below:

create macro reset_pk_spool as (
UPDATE dbc.DataBaseSpace
   SET PeakSpoolSpace  = 0
where databaseid       = '0000841D'XB  /* Userid NBD7UZX */
  and tableid          = '000000000000'XB
;
);

The reason to use the databaseid and tableid is that it becomes a UPI update which is most efficient and minimizes locking on this critical system table. To find the internal databaseid for the desired userid use the following SQL:

locking table dbc.DBase for access
select * from dbc.DBase
where DatabaseNameI = 'NBD7UZX';

The tableid of low values ('000000000000'XB) is for the userid. Be careful who has create/drop macro on this macro as it can then be modified and therefore used to clear anyone's peakspool. You might want to grant only EXECUTE on this macro to the person performing the testing.

Regards,

Doug Drake

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