Archives of the TeradataForum

Message Posted: Thu, 20 May 2004 @ 12:01:59 GMT

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

My biggest reco is to set up a log table. Build a single routine to write to that log table and liberally sprinkle logging statements in the code. I set mine up with a "Log everything" option. Think of being back in the days (when Men were Men and the Dinsoaurs ruled the earth) before fancy debugging tools.

This log table can also be used for alerting you to problems and finding out when (and by whom) the stored procedure is being used. For instance, I am very interested in finding out who is using my password reset procedure. I found it to be invaluable when an application called complaining that my generic aggregation procedure was faulty. Turned out they were aggregating the wrong copy of their marts.

Glen

Log table

     CREATE SET TABLE DSYSLOGT.T_APPL_LOG ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
          (
           REC_UPDT_DT TIMESTAMP(6) FORMAT 'yyyy-mm-ddbhh:mi:ss.s(6)' NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
           APPL_CD CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           EVNT_NM VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           PGM_ID VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
           REC_UPDT_UID VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT USER ,
           REC_TXT VARCHAR(3200) CHARACTER SET LATIN NOT CASESPECIFIC,
           ACTV_CNT INTEGER,
           SQL_CD SMALLINT,
           PRBLM_SVTY_CD CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC,
           SESS_NBR INTEGER NOT NULL DEFAULT SESSION ,
           LAST_BPR_TM TIMESTAMP(6) FORMAT 'yyyy-mm-ddbhh:mi:ss.s(6)' DEFAULT CURRENT_TIMESTAMP(6),
           BPR_TRY_QTY INTEGER DEFAULT 0 )
     UNIQUE PRIMARY INDEX ( REC_UPDT_DT ,APPL_CD ,EVNT_NM ,REC_UPDT_UID ,SESS_NBR );

Log Procedure

     REPLACE PROCEDURE "SYSSTP"."LOGIT"
       (IN APPL_CD CHAR(2)
        , IN EVNT_NM VARCHAR(30)
        , IN PGM_ID VARCHAR(30)
        , REC_TXT VARCHAR(3200)
        , IN ACTV_CNT INTEGER
        , IN SQL_CD SMALLINT
        , IN PRBLM_SVTY_CD CHAR(4)
        , IN LOG_FLG CHAR(1))
       BEGIN
       IF LOG_FLG = 'Y'   THEN
         INSERT INTO DSYSLOGT.T_APPL_LOG
         (APPL_CD, EVNT_NM, PGM_ID, REC_TXT, ACTV_CNT, SQL_CD, PRBLM_SVTY_CD)
         VALUES
         (:APPL_CD, :EVNT_NM, :PGM_ID, :REC_TXT, :ACTV_CNT, :SQL_CD, :PRBLM_SVTY_CD);
       END IF;
       END;

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