Archives of the TeradataForum

Message Posted: Thu, 23 Sep 2004 @ 19:16:08 GMT

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

Thanks to some help from Chris Platt and Jimmy Johnson, I have a solution in a stored procedure.

     REPLACE PROCEDURE  DBC.CLEARUSERPEAKSPOOL
      ( IN USERNAME VARCHAR(30)
        , INOUT ERR=5FCD INTEGER
      )
      /**************************************************/
      /*  NAME:  CLEAR USER PEAK SPOOL                          */
      /* Desc ? REsets PEAK SPOOL FOR A GIVEN USER.   */
      /* Inputs:                                        */
      /* USERNAME                                   */
      /* ERR=5FCD     Error Code 0 = Good.             */
      /* History:                                       */
      /* 9/23/2004            Created.                 */
      /**************************************************/


     RESET:      BEGIN
     DECLARE SQLERR INTEGER;
     DECLARE SQLROWS INTEGER;
     DECLARE  COMTXT VARCHAR(3200);
     DECLARE HANDLER=5FERROR INTEGER;
     DECLARE DATABASEID BYTE(6)   ;
     DECLARE CONTINUE HANDLER
        FOR SQLEXCEPTION
        BEGIN
           SET HANDLER=5FERROR = 1;
           SET SQLERR = SQLCODE;
           SET SQLROWS=ACTIVITY=5FCOUNT;
        END;

     SET ERR=5FCD = 0;
     SET SQLERR = 0;
     SET SQLROWS = 0;
     SET HANDLER=5FERROR = 0;

     SELECT DATABASEID INTO :DATABASEID FROM DBC.DBASE WHERE DATABASENAMEi = :USERNAME;
     IF HANDLER=5FERROR = 0 AND ACTIVITY=5FCOUNT = 1
     THEN
     UPDATE DBC.DATABASESPACE
     SET PEAKSPOOLSPACE = 0
     WHERE DBC.DATABASESPACE.DATABASEID = :DATABASEID;
     END IF;
     IF HANDLER=5FERROR <> 0
     THEN
     SET COMTXT = 'ERROR - CANNOT RESET PEAK SPOOL FOR ' || TRIM(USERNAME) ;
     CALL SYSSTP.LOGIT('NA','A - CLEAR PEAK
     SPOOL','SPOOL',:COMTXT,:SQLROWS,:SQLERR,'PAGE','Y');
     SET ERR=5FCD = 1;
     LEAVE RESET;
     END IF;
     SET COMTXT = 'RESET PEAK SPOOL FOR ' || TRIM(USERNAME) ;
     CALL SYSSTP.LOGIT('NA','A - CLEAR PEAK
     SPOOL','SPOOL',:COMTXT,:SQLROWS,:SQLERR,'NONE','Y');
     END RESET;

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