Home Page for the TeradataForum

Archives of the TeradataForum

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

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

Subj:   Re: Spool space for Teradata users
From:   Doug Drake

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.


Doug Drake

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016