Find Invalid views

The below SQL can be used to identify invalid views .. (extract from the Unix script ..)

select 'select 1 from ${DATABASENAME}.'||TRIM(TableName)||' where 1 = 2; //
.IF ERRORCODE <> 0 THEN INSERT INTO temp_db.invalid_views values(''${DATABASENAME}'','''||TRIM(TableName)||''');' (TITLE '') from dbc.tables
where databasename = '${DATABASENAME}';

TPERF per user

Below script helps to check the amount of TPERF being used by a user. The eg. below uses 246 as the system TPERF, you have to change this to reflect your systems value.

SELECT dlt.UserName

,dlt.LogDate

/* Identify total CPU time used by queries executed by user for logdate */

,SUM(dlt.AMPCPUTime+ dlt.ParserCPUTime) AS Sum_CPUTime

/* Identify impact CPU time (ie to account for skew imbalance) used by queries executed by user for logdate */

,SUM((dlt.MaxAMPCPUTime*(HASHAMP()+1))+ dlt.ParserCPUTime) AS Sum_ImpactCPUTime

/* Identify total TPerfhours used by queries executed by user for logdate where system TPerf is 246 in this eg. */

,(Sum_ImpactCPUTime * 246.00) / (64.00 * 3600.00) AS Sum_ImpactTPerfHour

/* Remove 10% of avail TPerf for OS overhead */

,246.00 * 24 * 0.90 AS Sum_AvailTPerfHour


FROM dbqlogtbl_hst dlt


WHERE dlt.LogDate > CURRENT_DATE -95

AND dlt.UserName LIKE ANY ( 'UserName' )

GROUP BY 1,2

;