CREATE SET TABLE perfstat.acctgDetail ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, FREESPACE = 20 PERCENT, CHECKSUM = DEFAULT ( AccountName CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, AccountPriority CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ('H1','M1','M2'), AccountType CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ('AUDB','CLCB','CMPB','CPSB','CSSB','DOMB','FARB','MSTR','STAT','TACA','TACB','TPSB','USRB','USRD','USRE'), AccountSession INTEGER, AccountYYMMDD CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ('070830','070831','070901','070902','070903','070904','070905','070906','070907','070908','070909','070910','070911','070912','070913','070914','070915','070916','070917','070918','070919','070920','070921','070922','070923','070924','070925','070926','070927','070928','070929','070930','071001','071002','071003','071004','071005','071006','071007','071008','071009','071010','071011','071012','071013','071014','071015','071016','071017','071018','071019','071020','071021','071022','071023','071024','071025','071026','071027','071028','071029','071030','071031','071101','071102','071103','071104','071105','071106','071107','071108'), AccountHH CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ('00','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23'), UserName CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, CPU FLOAT FORMAT '---,---,---,--9.99' NOT NULL COMPRESS (0.00000000000000E 000 ,2.50000000000000E-001 ,5.00000000000000E-001 ,9.00000000000000E-002 ,1.80000000000000E-001 ,3.60000000000000E-001 ,4.70000000000000E-001 ,3.30000000000000E-001 ,1.10000000000000E-001 ,2.20000000000000E-001 ,4.40000000000000E-001 ,6.30000000000000E-001 ,1.50000000000000E-001 ,3.00000000000000E-001 ,6.00000000000000E-001 ,4.10000000000000E-001 ,2.70000000000000E-001 ,5.40000000000000E-001 ,1.90000000000000E-001 ,3.80000000000000E-001 ,5.10000000000000E-001 ,4.90000000000000E-001 ,3.50000000000000E-001 ,2.30000000000000E-001 ,4.60000000000000E-001 ,1.00000000000000E-002 ,2.00000000000000E-002 ,4.00000000000000E-002 ,8.00000000000000E-002 ,1.60000000000000E-001 ,3.20000000000000E-001 ,6.40000000000000E-001 ,4.30000000000000E-001 ,6.10000000000000E-001 ,2.90000000000000E-001 ,5.00000000000000E-002 ,1.00000000000000E-001 ,2.00000000000000E-001 ,4.00000000000000E-001 ,5.50000000000000E-001 ,1.30000000000000E-001 ,2.60000000000000E-001 ,5.20000000000000E-001 ,3.70000000000000E-001 ,3.00000000000000E-002 ,6.00000000000000E-002 ,1.20000000000000E-001 ,2.40000000000000E-001 ,4.80000000000000E-001 ,1.70000000000000E-001 ,3.40000000000000E-001 ,4.50000000000000E-001 ,3.10000000000000E-001 ,6.20000000000000E-001 ,2.10000000000000E-001 ,4.20000000000000E-001 ,5.90000000000000E-001 ,7.00000000000000E-002 ,1.40000000000000E-001 ,2.80000000000000E-001 ,5.60000000000000E-001 ,3.90000000000000E-001 ,5.30000000000000E-001 ), IO FLOAT FORMAT '--,---,---,--9' NOT NULL COMPRESS (0.00000000000000E 000 ,2.00000000000000E 000 ,3.00000000000000E 000 ,4.00000000000000E 000 ,5.00000000000000E 000 ,6.00000000000000E 000 ,7.00000000000000E 000 ,8.00000000000000E 000 ,9.00000000000000E 000 ,1.00000000000000E 001 ,1.10000000000000E 001 ,1.20000000000000E 001 ,1.30000000000000E 001 ,1.40000000000000E 001 ,1.50000000000000E 001 ,1.60000000000000E 001 ,1.70000000000000E 001 ,1.80000000000000E 001 ,1.90000000000000E 001 ,2.00000000000000E 001 ,2.10000000000000E 001 ,2.20000000000000E 001 ,2.30000000000000E 001 ,2.40000000000000E 001 ,2.50000000000000E 001 ,2.60000000000000E 001 ,2.70000000000000E 001 ,2.80000000000000E 001 ,2.90000000000000E 001 ,3.00000000000000E 001 ,3.10000000000000E 001 ,3.20000000000000E 001 ,3.40000000000000E 001 ,3.60000000000000E 001 ,3.80000000000000E 001 ,4.00000000000000E 001 ,4.20000000000000E 001 ,4.40000000000000E 001 ,4.60000000000000E 001 ,4.80000000000000E 001 ,5.00000000000000E 001 ,5.20000000000000E 001 ,5.40000000000000E 001 ,5.60000000000000E 001 ,5.80000000000000E 001 ,6.00000000000000E 001 ,6.20000000000000E 001 ,6.40000000000000E 001 ,6.80000000000000E 001 ,7.20000000000000E 001 ,7.60000000000000E 001 ,8.00000000000000E 001 ,8.40000000000000E 001 ,8.80000000000000E 001 ,9.20000000000000E 001 ,9.60000000000000E 001 ,1.00000000000000E 002 ,1.04000000000000E 002 ,1.08000000000000E 002 ,1.12000000000000E 002 ,1.24000000000000E 002 ,1.28000000000000E 002 ,2.32000000000000E 002 ,1.00000000000000E 000 ,2.33000000000000E 002 ,6.50000000000000E 001 ,6.90000000000000E 001 ,8.10000000000000E 001 ,8.50000000000000E 001 ,9.30000000000000E 001 ,9.70000000000000E 001 ,2.02000000000000E 002 ,3.30000000000000E 001 ,3.50000000000000E 001 ,3.70000000000000E 001 ,3.90000000000000E 001 ,4.10000000000000E 001 ,4.30000000000000E 001 ,4.50000000000000E 001 ,4.70000000000000E 001 ,4.90000000000000E 001 ,5.10000000000000E 001 ,5.30000000000000E 001 ,5.50000000000000E 001 ,5.70000000000000E 001 ,5.90000000000000E 001 ,6.10000000000000E 001 ,6.30000000000000E 001 ,6.60000000000000E 001 ,7.00000000000000E 001 ,7.40000000000000E 001 ,7.80000000000000E 001 ,8.20000000000000E 001 ,8.60000000000000E 001 ,9.00000000000000E 001 ,9.80000000000000E 001 ,1.02000000000000E 002 ,1.06000000000000E 002 ,1.10000000000000E 002 ,1.22000000000000E 002 ,2.04000000000000E 002 ,6.70000000000000E 001 ,7.10000000000000E 001 ,7.90000000000000E 001 ,8.30000000000000E 001 ,8.70000000000000E 001 ,9.50000000000000E 001 ,9.90000000000000E 001 ,1.03000000000000E 002 ,2.30000000000000E 002 ), Vproc SMALLINT NOT NULL, VprocType CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS 'AMP ', Model CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS '5400') UNIQUE PRIMARY INDEX PK_acctgDetail ( AccountName ,UserName , Vproc ) INDEX IE_1acctgDetail ( AccountYYMMDD ); -- This table is optional. Just for debugging my process if something would fail. It's still work in progress. CREATE SET TABLE perfstat.Application_Log ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, FREESPACE = 20 PERCENT, CHECKSUM = DEFAULT ( LOG_DTM TIMESTAMP(6) FORMAT 'yyyy-mm-ddbhh:mi:ss.s(6)' NOT NULL DEFAULT CURRENT_TIMESTAMP(6), APPLICATION_NM CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, EVENT_NM VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, PROGRAM_NM VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC, OWNER_NAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT USER , LOG_TEXT VARCHAR(3200) CHARACTER SET LATIN NOT CASESPECIFIC, ACTIVITY_CNT INTEGER, SQL_STATE CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC, SQL_CODE INTEGER, PROBLEM_SEVERITY_CD INTEGER DEFAULT 0 , SESSION_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 ( LOG_DTM ,APPLICATION_NM ,EVENT_NM ,OWNER_NAME , SESSION_NBR ); CREATE VIEW perfstat.dbc_AcctgDetail AS LOCKING TABLE perfstat.AcctgDetail FOR ACCESS SELECT * FROM perfstat.AcctgDetail; -- Procedure that you can use to refresh data in to the target acctg table for analysis -- This is scheduled at 9:20 am and doesn't take more than 10 minutes. We have a 3TB DW. CREATE PROCEDURE "perfstat"."acctgDetail_Refresh" () BEGIN DECLARE currentCentury CHAR(2); DECLARE maxAccountYYMMDD CHAR(6); call PERFSTAT.APPLICATION_LOG_P('QueryLogging', 'Start', 'acctgDetail_Refresh', 'Start Process', 1, '00', 0, 0, 'Y'); -- This isn't used but was done in an effort to find out the correct account strings -- from acctg as there was some non standard data in our table SELECT SUBSTR(TRIM(EXTRACT(YEAR FROM DATE)), 1, 2) INTO :currentCentury; -- Find out the Max Date in acctgDetail table that needs to be refreshed with new data. -- Reruns of the process makes sure that you reload current data SELECT MAX(AccountYYMMDD) INTO :maxAccountYYMMDD FROM perfstat.acctgDetail; DELETE FROM "perfstat"."acctgDetail" WHERE AccountYYMMDD = :maxAccountYYMMDD; -- Insert data from Max Date till run date to keep acctgDetail current. INSERT INTO perfstat.acctgDetail SELECT AccountName , SUBSTR(AccountName, 2, 2) AccountPriority , SUBSTR(AccountName, 5, 4) AccountType , CAST(SUBSTR(AccountName, 9, 9) AS INTEGER) AccountSession , SUBSTR(AccountName, 18, 6) AccountYYMMDD , SUBSTR(AccountName, 24, 2) AccountHH , UserName , CPU , IO , Vproc , VprocType , Model FROM dbc.acctg WHERE SUBSTR(AccountName, 18, 6) IN (SELECT calendar_date(FORMAT 'yymmdd')(CHAR(6)) FROM sys_calendar.calendar WHERE calendar_date BETWEEN CAST('20'||:maxAccountYYMMDD AS DATE FORMAT 'yyyymmdd') AND DATE); -- WHERE calendar_date BETWEEN CAST('20070830' AS DATE FORMAT 'yyyymmdd') AND DATE); call PERFSTAT.APPLICATION_LOG_P('QueryLogging', 'Informational', 'acctgDetail_Refresh', 'Insert into perfstat.acctgDetail completed', 2, '00', 0, 0, 'Y'); -- Some housekeeping DELETE FROM PERFSTAT.APPLICATION_LOG WHERE LOG_DTM <= DATE -15 (timestamp(0)) AND APPLICATION_NM = 'QueryLogging' AND PROGRAM_NM = 'acctgDetail_Refresh'; call PERFSTAT.APPLICATION_LOG_P('QueryLogging', 'Informational', 'acctgDetail_Refresh', 'Purge APPLICATION_LOG for acctgDetail_Refresh Completed', 3, '00', 0, 0, 'Y'); call PERFSTAT.APPLICATION_LOG_P('QueryLogging', 'End', 'acctgDetail_Refresh', 'End Process', 4, '00', 0, 0, 'Y'); END; -- This procedure is optional. Just for debugging my process if something would fail. It's still work in progress. -- If you don't need this, then delete calls to this proc from the procedure above. CREATE PROCEDURE "PERFSTAT"."Application_Log_P" ( IN APPLICATION_NM CHAR(30) , IN EVENT_NM VARCHAR(30) , IN PROGRAM_NM VARCHAR(30) , LOG_TEXT VARCHAR(3200) , IN ACTIVITY_CNT INTEGER , IN SQL_STATE CHAR(2) , IN SQL_CODE INTEGER , IN PROBLEM_SEVERITY_CD INTEGER , IN LOG_FLG CHAR(1)) BEGIN /* * PROBLEM_SEVERITY_CD * 0 - Informational Only * 1 - High Severity, cannot proceed * 2 - Medium Seviriy, significant, process does continue * 3 - Low Severity, non impacting, process does continue * * EVENT_NM * Start * End * Error * Informational * * ACTIVITY_COUNT, SQLSTATE, SQLCODE are for future enhancement to trap and handle exceptions. * Example available on page 173 of http://www.teradataforum.com/teradata_pdf/b035-2414-062a.pdf * */ IF LOG_FLG = 'Y' THEN INSERT INTO PERFSTAT.APPLICATION_LOG ( APPLICATION_NM, EVENT_NM, PROGRAM_NM, LOG_TEXT, ACTIVITY_CNT, SQL_STATE, SQL_CODE, PROBLEM_SEVERITY_CD) VALUES ( :APPLICATION_NM, :EVENT_NM, :PROGRAM_NM, :LOG_TEXT, :ACTIVITY_CNT, :SQL_STATE, :SQL_CODE, :PROBLEM_SEVERITY_CD); END IF; END; -- Here are a few sample queries -- CPU Utilization of batch processes last 30 days. SELECT AccountYYMMDD theDate, username, SUM(CPU) / 24 TotalCPU FROM perfstat.dbc_AcctgDetail WHERE AccountYYMMDD IN (SELECT calendar_date(FORMAT 'yymmdd')(CHAR(8)) FROM sys_calendar.calendar WHERE calendar_date BETWEEN (ADD_MONTHS(DATE, -1)) AND DATE) AND username LIKE '%BATCH%' GROUP BY AccountYYMMDD, username; -- CPU Utilization for all users for last 30 days SELECT AccountYYMMDD theDate, username, SUM(CPU) / 24 TotalCPU FROM perfstat.dbc_AcctgDetail WHERE AccountYYMMDD IN (SELECT calendar_date(FORMAT 'yymmdd')(CHAR(8)) FROM sys_calendar.calendar WHERE calendar_date BETWEEN (DATE - 30) AND DATE) GROUP BY AccountYYMMDD, username; -- CPU Utilization of non-batch processes last 30 days. SELECT AccountYYMMDD DateID, username, SUM(CPU) / 24 TotalCPU FROM perfstat.dbc_AcctgDetail WHERE AccountYYMMDD IN (SELECT calendar_date(FORMAT 'yymmdd')(CHAR(8)) FROM sys_calendar.calendar WHERE calendar_date BETWEEN (DATE - 30) AND DATE) AND username NOT LIKE '%BATCH%' GROUP BY AccountYYMMDD, username; -- CPU Utilization for all users since yesterday SELECT AccountHH RunTime, username username, SUM(CPU) CPUUSED FROM perfstat.dbc_AcctgDetail WHERE AccountYYMMDD IN (SELECT calendar_date(FORMAT 'yymmdd')(CHAR(8)) FROM sys_calendar.calendar WHERE calendar_date >= (DATE - 1)) GROUP BY RunTime, username ORDER BY RunTime, username; -- This uses resusage views SELECT CPUBusy AS CPUBUSY, cpuwaitio AS CPUWAITIO, (CAST(thedate || ' ' || thetime AS CHAR(20))) AS datetime FROM dbc.Resgeneralinfoview WHERE thedate = DATE ORDER BY datetime;