Archives of the TeradataForum

Message Posted: Wed, 04 Apr 2007 @ 14:02:41 GMT

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

Hi All,

We are maintaining below specified table for daily copying dbc.qrylog records and cleaning the dbc.qrylog. Now this table has more than 3 years of data and consumed huge space, so we are planning to still summarize this table further such way that it should used for future analysis. I request you all to provide me solution at what level we can go ahead summarize this table such way it should useful for future analysis.

Please let me know is there any best practices for summarizing this.

     CREATE MULTISET TABLE dbadmin.dba_query_log ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
          (
           StartDate DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE ,
           UserName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT '',
           SessionID INTEGER NOT NULL DEFAULT 0 ,
           QueryID INTEGER NOT NULL DEFAULT 0 ,
           RequestNum INTEGER NOT NULL DEFAULT 0 ,
           StartTime TIMESTAMP(2) NOT NULL,
           FirstStepTime TIMESTAMP(2) NOT NULL,
           FirstRespTime TIMESTAMP(2),
           NumSteps SMALLINT FORMAT '---,--9' NOT NULL,
           NumStepswPar SMALLINT FORMAT '---,--9',
           MaxStepsInPar SMALLINT FORMAT '---,--9',
           ErrorCode INTEGER FORMAT '--,---,---,--9',
           ErrorText VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,
           TDQMFlag CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
           AbortFlag CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
           CacheFlag CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
           QueryText VARCHAR(10000) CHARACTER SET UNICODE NOT CASESPECIFIC,
           LastRespTime TIMESTAMP(2))
     PRIMARY INDEX ( UserName ,StartTime )

Thanks,

Shankar

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