 |
 |
Archives of the TeradataForum
Message Posted: Wed, 04 Apr 2007 @ 16:23:58 GMT
| Subj: | | Re: Summarizing dbc.qrylog table |
| |
| From: | | Diehl, Robert |
Shankar,
I would also suggest that you add compression to your table. If I remember correctly, we were able to save about 60% of the space by adding
compression. You probably should also evaluate it you are keeping enough of the original columns that may be needed for the future.
Here is the base table we use. You will have to alter the compression for your site differences. Below that is one of the summaries we use.
We are altering it to add the new TASM columns so we can report at WORKLOAD levels.
CREATE MULTISET TABLE STATS_HISTORY.DBQLogTbl ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
ProcID DECIMAL(5,0) FORMAT '-(5)9' NOT NULL,
CollectTimeStamp TIMESTAMP(2) FORMAT 'YYYY-MM-DDBHH:MI:SS' NOT NULL,
QueryID INTEGER FORMAT '--,---,---,--9' NOT NULL,
UserID BYTE(4) NOT NULL COMPRESS ('00000A19'XB, '00000F09'XB,
'00001109'XB, '00001309'XB, '00001C0A'XB, '00001E17'XB, '00002E09'XB,
'00003405'XB, '00003A14'XB, '00004104'XB, '00004612'XB, '00004905'XB,
'00005316'XB, '00006212'XB, '00006A17'XB, '00006B12'XB, '00007E18'XB,
'00008208'XB, '00008411'XB, '00008611'XB, '00008F11'XB, '00009111'XB,
'00009211'XB, '00009816'XB, '0000A614'XB, '0000A805'XB, '0000AB07'XB,
'0000BE08'XB, '0000D313'XB, '0000E313'XB, '0000ED17'XB),
AcctString CHAR(30) CHARACTER SET UNICODE NOT CASESPECIFIC
COMPRESS ('$LL$&D&HADMIN ','$LL$&D&HTCY_LOADS
','$MV$&D&HCONSUMER_MKT ','$MX$&D&HCRM
','$QL$&D&HSITEQUALITY ','$QV$&D&HCHARGEBACK
','$QV$&D&HCRUISE ','$QV$&D&HDB_DEV
','$QV$&D&HMKTG ','$QX$&D&HCOGNOS
','$QX$&D&HCONSUMER_MKT ','$QX$&D&HCRM_DEV
','$QX$&D&HREV_MGMT ','$TL$&D&HTCY_LFA
','$TV$&D&HCUSTOMER_SVC '),
ExpandAcctString CHAR(30) CHARACTER SET UNICODE NOT CASESPECIFIC,
SessionID INTEGER FORMAT '--,---,---,--9' NOT NULL,
LogicalHostID SMALLINT FORMAT 'ZZZ9' NOT NULL COMPRESS 1 ,
RequestNum INTEGER FORMAT '--,---,---,--9' NOT NULL COMPRESS (1 ,2,3 ,4 ,5 ,6 ,7 ),
LogonDateTime TIMESTAMP(2) FORMAT 'YYYY-MM-DDBHH:MI:SS' NOT NULL,
AcctStringTime FLOAT FORMAT '99:99:99' COMPRESS ,
AcctStringHour SMALLINT FORMAT '--9' COMPRESS (1 ,2 ,3 ,4 ,5 ,6 ,7 ,8
,9 ,10 ,11 ,12 ,13 ,14 ,15 ,16 ,17 ,18 ,19 ,20 ,21 ,22 ,23 ),
AcctStringDate DATE FORMAT 'YY/MM/DD',
AppID CHAR(30) CHARACTER SET UNICODE NOT CASESPECIFIC
COMPRESS ('01 ','BTEQ
','COMMONMTIER ','CPS
','EXEC_PROC.PL ','JDBC03.02.00.00;1.4.2_04
','JDBC03.02.00.01;1.5.0_05 ','JDBC03.02.00.03;1.4.2_05
','JDBC03.02.00.03;1.5.0_02 ','MSACCESS
','QUERYMAN ','SAS
','TASKBKR ','TPUMPEXE
','TRNSFRMR '),
ClientID CHAR(30) CHARACTER SET UNICODE NOT CASESPECIFIC
COMPRESS ('BIBUSTKSERVERMAIN ','BULKY
','COGNOS_ADMIN ','COMMONMTIER ','CPS
','CRMADMIN ','CRM_ADMIN
','DBCM ','DSS
','IMPUSER ','MSACCESS
','NDAVE ','QUERYMAN
','ROOT ','SG0173139
','SG0174600 ','SG0326533
','SG0383271 ','SG0620336
','SG0893452 ','SG0894769
','SG0897094 ','SG0898709
','SG0899162 ','TCYREPORT
','TRNSFRMR ','TT126571
','TVLY ','TY170364
','TY897049 ','WINDDI '),
ClientAddr CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC
COMPRESS ('10.12.146.222 ','10.12.146.252
','10.136.117.149 ','10.16.35.210
','10.16.40.59 ','10.16.40.75
','10.19.84.36 ','10.19.84.37
','10.19.84.39 ','10.19.84.42
','10.19.84.95 ','10.8.2.14
','10.8.2.184 ','10.8.2.201
','10.8.2.214 ','10.8.2.215
','10.8.2.216 ','10.8.2.25
','10.8.3.24 ','10.8.3.25
','172.30.188.101 ','172.30.188.104
','172.30.188.108 ','172.30.188.109
','172.30.188.111 ','172.30.188.112
','172.30.188.113 ','172.30.188.115
','172.30.188.118 ','172.30.188.121
','172.30.188.123 ','172.30.188.129
','172.30.188.136 ','172.30.188.137
','172.30.188.138 ','172.30.188.139
','172.30.188.140 ','172.30.188.142
','172.30.188.143 ','172.30.188.145
','172.30.188.146 ','172.30.188.149
','172.30.188.150 ','172.30.188.153
','172.30.188.154 ','172.30.188.155
','172.30.188.156 ','172.30.188.168
','172.30.188.169 ','172.30.188.173
','172.30.188.214 ','172.30.188.219
','172.30.188.227 ','172.30.188.232
','172.30.188.62 ','172.30.188.63
','172.30.188.89 ','172.30.188.95
','172.30.66.103 ','172.30.66.185
','172.30.66.34 ','172.30.67.142
','172.30.67.143 '),
QueryBand CHAR(255) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,
ProfileID BYTE(4)
COMPRESS ('00004314'XB,'00007314'XB,'00007414'XB,'0000CF13'XB),
StartTime TIMESTAMP(2) NOT NULL,
FirstStepTime TIMESTAMP(2) NOT NULL,
FirstRespTime TIMESTAMP(2) COMPRESS ,
LastRespTime TIMESTAMP(2),
NumSteps SMALLINT FORMAT '---,--9' NOT NULL
COMPRESS (0 ,1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,15 ,17 ,20 ,22 ,23 ),
NumStepswPar SMALLINT FORMAT '---,--9' COMPRESS (0 ,1 ,2 ,3 ,4 ,5 ,19 ),
MaxStepsInPar SMALLINT FORMAT '---,--9' COMPRESS (0 ,2 ,3 ),
NumResultRows FLOAT FORMAT '----,---,---,---,--9'
COMPRESS (0.00000000000000E 000 ,3.00000000000000E 000
,1.00000000000000E 000 ,2.19000000000000E 002
,7.50000000000000E 001 ,2.38000000000000E 002
,6.55350000000000E 004 ),
TotalIOCount FLOAT FORMAT '----,---,---,---,--9'
COMPRESS (0.00000000000000E 000 ,2.00000000000000E 000
,5.00000000000000E 000,6.00000000000000E 000
,1.30000000000000E 001 ,1.40000000000000E 001
,1.72000000000000E 002 ),
TotalCPUTime FLOAT FORMAT '-----,---,---,--9.99'
COMPRESS (0.00000000000000E 000 ,1.00000000000000E-002
,2.00000000000000E-002 ),
ErrorCode INTEGER FORMAT '--,---,---,--9'
COMPRESS (0 ,3604 ,3110 ,3624 ,2621 ,3706 ,3707 ,3510
,3514 ,3807 ,3810 ,2801 ,2802 ,2803 ,5628),
ErrorText VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,
WarningOnly CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC
COMPRESS (' ','T'),
DelayTime INTEGER FORMAT '--,---,---,--9' COMPRESS ,
AbortFlag CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC
COMPRESS ('','T'),
CacheFlag CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC
COMPRESS ('','T'),
QueryText VARCHAR(10000) CHARACTER SET UNICODE NOT CASESPECIFIC,
NumOfActiveAMPs INTEGER FORMAT '--,---,---,--9' COMPRESS (0 ,1 ,86),
HotAmp1CPU FLOAT FORMAT '-----,---,---,--9.99'
COMPRESS(0.00000000000000E 000 ,1.00000000000000E-002
,2.00000000000000E-002,4.00000000000000E-002
,5.00000000000000E-002 ,3.00000000000000E-002
,6.00000000000000E-002 ),
HotCPUAmpNumber SMALLINT FORMAT '---,--9' COMPRESS ,
LowAmp1CPU FLOAT FORMAT '-----,---,---,--9.99'
COMPRESS 0.00000000000000E 000 ,
HotAmp1IO FLOAT FORMAT '----,---,---,---,--9'
COMPRESS(0.00000000000000E 000 ,2.00000000000000E 000
,5.00000000000000E 000,6.00000000000000E 000
,7.00000000000000E 000 ,1.30000000000000E 001
,1.40000000000000E 001 ),
HotIOAmpNumber SMALLINT FORMAT '---,--9' COMPRESS (7 ,19 ,50 ),
LowAmp1IO FLOAT FORMAT '----,---,---,---,--9'
COMPRESS 0.00000000000000E 000 ,
SpoolUsage FLOAT FORMAT '----,---,---,---,--9'
COMPRESS (0.00000000000000E 000 ,1.02400000000000E 003
,2.56000000000000E 003 ),
WDID INTEGER FORMAT '--,---,---,--9' COMPRESS ,
WDPeriodID INTEGER FORMAT '--,---,---,--9' COMPRESS ,
LSN INTEGER FORMAT '--,---,---,--9' COMPRESS ,
NoClassification CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,
WDOverride CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,
SLGMet CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,
ExceptionValue INTEGER FORMAT '--,---,---,--9' COMPRESS ,
FinalWDID INTEGER FORMAT '--,---,---,--9' COMPRESS ,
TDWMEstMaxRows FLOAT FORMAT '----,---,---,---,--9' COMPRESS ,
TDWMEstLastRows FLOAT FORMAT '----,---,---,---,--9' COMPRESS ,
TDWMEstTotalTime FLOAT FORMAT '----,---,---,---,--9' COMPRESS ,
TDWMAllAmpFlag CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,
TDWMConfLevelUsed CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,
UserName CHAR(30) CHARACTER SET UNICODE NOT CASESPECIFIC
COMPRESS ('AROGERS ','BAUDIKESAVAN
','BULKEMAIL ','CJOHNSON
','COGNOS_ADMIN2 ','COGNOS_WEB2
','COGNOS_ZUJI ','CRM421
','CRM_ADMIN ','CRM_BATCH
','CRM_CPS ','CRM_CPS_DEV
','CRM_TBR ','DBCM
','PSHR_REPORT_USER ','SKLINE
','TCY_CHARGEBACK_USER ','TCY_HOTEL_ADMIN
','TCY_LFA ','TCY_MONITORING_USER
','TCY_SITE_QUALITY_USER ','TCY_TPUMP
','WNS912526 ','WNS912574
','WNS912583 ','WNS913343
','WNS913646 ','WNS914331
','WNS934231 ','WNS943313
','WNS990885 '),
DefaultDatabase CHAR(30) CHARACTER SET UNICODE NOT CASESPECIFIC
COMPRESS ('BAUDIKESAVAN ','CJOHNSON
','COGNOS_WEB2 ','COGNOS_ZUJI
','CRM_ADMIN ','CRM_BATCH
','CRM_CPS ','DALVIREZ
','DBCM ','DEBIT_MEMO_TABLES
','DMA_SCORING ','INFOCOMMAND_VIEWS
','MGREEN ','PASS_IOS
','PASS_IOS_PROD ','PROD_VIEWS
','PROMOTIONS_WORK ','PSHR_VIEW
','REVMGMT ','REVMGMT_FCST
','RSHIYEKA ','TCY_CHARGEBACK_USER
','TCY_FRONTEND_MSP ','TCY_HOTEL_ADMIN
','TCY_LFA ','TCY_MAIN_VIEW
','TCY_MONITORING_USER ','TCY_MSP
','TCY_SITE_QUALITY_USER ','TCY_TPUMP
','WKOCHAN ','ZUJI_VIEWS '),
ExtraField1 INTEGER FORMAT '--,---,---,--9' COMPRESS ,
ExtraField2 FLOAT FORMAT '----,---,---,---,--9' COMPRESS ,
ExtraField3 SMALLINT FORMAT '---,--9' COMPRESS ,
ExtraField4 TIMESTAMP(2) COMPRESS ,
ExtraField5 CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS
)
PRIMARY INDEX ( ProcID ,CollectTimeStamp );
CREATE SET TABLE STATS_HISTORY.DbqlQueryHistory ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
UserName CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
AcctString CHAR(30) CHARACTER SET UNICODE NOT CASESPECIFIC
COMPRESS ('$L&D&TCRM ','$LL$&D&HTCY_LOADS
','$MV$&D&HCONSUMER_MKT ','$MX$&D&HCRM
','$QV$&D&HCHARGEBACK ','$QV$&D&HCORE_SRVCS
','$QV$&D&HCRUISE ','$QV$&D&HDB_DEV
','$QV$&D&HPKG ','$QV$&D&HREV_MGMT
','$QV$&D&HZUJI_COGNOS_WEB ','$QX$&D&HCOGNOS
','$QX$&D&HCRM_DEV ','$TL$&D&HDPARKER
','$TL$&D&HTCY_LFA '),
FinalWDID INTEGER,
StartDt DATE FORMAT 'yyyy-mm-dd',
StartHr INTEGER
COMPRESS (0 ,1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ,11 ,12 ,13 ,14
,15 ,16 ,17 ,18 ,19 ,20 ,21 ,22 ,23 ),
MinTotalTime INTERVAL HOUR(4) TO SECOND(2),
AvgTotalTime INTERVAL HOUR(4) TO SECOND(2),
MaxTotalTime INTERVAL HOUR(4) TO SECOND(2),
QryCnt INTEGER COMPRESS (1 ,2 ),
TotalIOCount FLOAT,
TotalCPUTime FLOAT,
ErrorCodeCnt INTEGER COMPRESS 0 ,
AbortFlagCnt INTEGER COMPRESS 0 ,
CacheFlagCnt INTEGER COMPRESS 0 ,
MedHigaFactorCnt INTEGER COMPRESS 0 ,
HighHigaFactorCnt INTEGER COMPRESS 0 ,
MedHigaFactorInverseCnt INTEGER COMPRESS 0 ,
HighHigaFactorInverseCnt INTEGER COMPRESS 0 ,
QryCnt_Under_1sec INTEGER COMPRESS 0 ,
QryCnt_1_to_9sec INTEGER COMPRESS 0 ,
QryCnt_10sec_to_59sec INTEGER COMPRESS 0 ,
QryCnt_1_to_4min INTEGER COMPRESS 0 ,
QryCnt_5_to_9min SMALLINT COMPRESS 0 ,
QryCnt_10_to_19min SMALLINT COMPRESS 0 ,
QryCnt_20_to_29min SMALLINT COMPRESS 0 ,
QryCnt_30_to_44min SMALLINT COMPRESS 0 ,
QryCnt_45_to_59min SMALLINT COMPRESS 0 ,
QryCnt_1_to_2hr BYTEINT COMPRESS 0 ,
QryCnt_gt_2hr BYTEINT COMPRESS 0 )
PRIMARY INDEX ( UserName ,StartDt );
Thanks,
Bob Diehl
| |
