Teradata DBA: Teradata DBA

Table of Contents

1 Disk Space

1.1 Total disk space

SELECT SUM (MaxPerm) FROM DBC.Diskpace;

1.2 Percent of disk space in use

SELECT ((SUM(CURRENTPERM) / NULLIFZERO(SUM(MAXPERM)) * 100)) (TITLE '%PERM', FORMAT 'ZZ9.99') FROM DBC.DISKSPACE;

1.3 Percent of disk space free

SELECT (((SUM(MAXPERM) - (SUM(CURRENTPERM)) - SUM(CURRENTSPOOL)) / NULLIFZERO(SUM(MAXPERM))) * 100)(TITLE'% FREE', FORMAT'ZZ9.99') FROM DBC.DISKSPACE;

1.4 Percent of disk avail for spool

SELECT (((SUM(MAXPERM) - SUM(CURRENTPERM)) / NULLIFZERO(SUM(MAXPERM))) * 100) (TITLE'% AVAIL FOR SPOOL', FORMAT'ZZ9.99') FROM DBC.DISKSPACE;

1.5 Disk space for a given database

SELECT SUM(MAXPERM)

FROM DBC.DISKSPACE

WHERE DATABASENAME='XXXX';

1.6 Disk space for a given table

SELECT SUM(CURRENTPERM)

FROM DBC.TABLESIZE

WHERE DATABASENAME='XXX'

AND TABLENAME = 'XXXX';

1.7 What is the table distribution per amp

SELECT TABLENAME (TITLE 'TABLE') ,CURRENTPERM (TITLE 'CURPERM') ,VPROC (TITLE 'AMP')

FROM DBC.TABLESIZE

WHERE DATABASENAME='XXX'

AND TABLENAME = 'XXXX'

ORDER BY 2 DESC;

1.8 Amount of MaxPerm, currentperm & free perm for a given db

SELECT DATABASENAME (FORMAT 'X(12)') ,SUM(MAXPERM) ,SUM(CURRENTPERM) ,(SUM(CURRENTPERM) / NULLIFZERO(SUM(MAXPERM))*100)(FORMAT 'ZZ9.99%', TITLE 'PERCENT // USED')

FROM DBC.DISKSPACE

WHERE DATABASENAME = 'DATABASENAME'

GROUP BY 1;

1.9 Whose Running out of Perm Space

SELECT DATABASENAME (FORMAT 'X(12)')

,SUM(MAXPERM)

,SUM(CURRENTPERM)

,((SUM(CURRENTPERM))/

NULLIFZERO (SUM(MAXPERM)) * 100)

(FORMAT 'ZZ9.99%', TITLE 'PERCENT // USED')

FROM DBC.DISKSPACE

GROUP BY 1

HAVING (SUM(CURRENTPERM) / NULLIFZERO(SUM(MAXPERM))) > 0.9

ORDER BY 4 DESC;

1.10 Who's got lots of perm

SELECT DATABASENAME (FORMAT 'X(12)')

,SUM(MAXPERM)

,SUM(CURRENTPERM)

,((SUM(CURRENTPERM))/

NULLIFZERO (SUM(MAXPERM)) * 100)

(FORMAT 'ZZ9.99%', TITLE 'PERCENT // USED')

FROM DBC.DISKSPACE

GROUP BY 1 HAVING (SUM(CURRENTPERM) / NULLIFZERO(SUM(MAXPERM))) < 0.5

ORDER BY 4;

1.11 Who’s using a lot of spool

SELECT DATABASENAME

,SUM(PEAKSPOOL)

FROM DBC.DISKSPACE

GROUP BY 1

HAVING SUM(PEAKSPOOL) > 5000000000

ORDER BY 2 DESC;

2 Access Rights

2.1 Rights Held By a User

Create this macro, execute the macro, and pass the username as argument to this macro. It will retrieve all sorts of access rights held by this user, either through role or direct.

REPLACE MACRO SYSDBA.ALLUSERRIGHTS (USERNAME CHAR(30))

AS (

/* LIST RIGHTS HELD BY A USER AS DIRECT GRANTS OR THROUGH ROLES */

LOCKING ROW FOR ACCESS

SELECT

USERNAME (VARCHAR(30))

,ACCESSTYPE (VARCHAR(30))

,ROLENAME (VARCHAR(30))

,DATABASENAME (VARCHAR(30))

,TABLENAME (VARCHAR(30))

,COLUMNNAME (VARCHAR(30))

,ACCESSRIGHT

,CASE

WHEN ACCESSRIGHT='AE' THEN 'ALTER EXTERNALPROCEDURE'

WHEN ACCESSRIGHT='AF' THEN 'ALTER FUNCTION'

WHEN ACCESSRIGHT='AP' THEN 'ALTER PROCEDURE'

WHEN ACCESSRIGHT='AS' THEN 'ABORT SESSION'

WHEN ACCESSRIGHT='CA' THEN 'CREATE AUTHORIZATION'

WHEN ACCESSRIGHT='CD' THEN 'CREATE DATABASE'

WHEN ACCESSRIGHT='CE' THEN 'CREATE EXTERNAL PROCEDURE'

WHEN ACCESSRIGHT='CF' THEN 'CREATE FUNCTION'

WHEN ACCESSRIGHT='CG' THEN 'CREATE TRIGGER'

WHEN ACCESSRIGHT='CM' THEN 'CREATE MACRO'

WHEN ACCESSRIGHT='CO' THEN 'CREATE PROFILE'

WHEN ACCESSRIGHT='CP' THEN 'CHECKPOINT'

WHEN ACCESSRIGHT='CR' THEN 'CREATE ROLE'

WHEN ACCESSRIGHT='CT' THEN 'CREATE TABLE'

WHEN ACCESSRIGHT='CU' THEN 'CREATE USER'

WHEN ACCESSRIGHT='CV' THEN 'CREATE VIEW'

WHEN ACCESSRIGHT='D' THEN 'DELETE'

WHEN ACCESSRIGHT='DA' THEN 'DROP AUTHORIZATION'

WHEN ACCESSRIGHT='DD' THEN 'DROP DATABASE'

WHEN ACCESSRIGHT='DF' THEN 'DROP FUNCTION'

WHEN ACCESSRIGHT='DG' THEN 'DROP TRIGGER'

WHEN ACCESSRIGHT='DM' THEN 'DROP MACRO'

WHEN ACCESSRIGHT='DO' THEN 'DROP PROFILE'

WHEN ACCESSRIGHT='DP' THEN 'DUMP'

WHEN ACCESSRIGHT='DR' THEN 'DROP ROLE'

WHEN ACCESSRIGHT='DT' THEN 'DROP TABLE'

WHEN ACCESSRIGHT='DU' THEN 'DROP USER'

WHEN ACCESSRIGHT='DV' THEN 'DROP VIEW'

WHEN ACCESSRIGHT='E' THEN 'EXECUTE'

WHEN ACCESSRIGHT='EF' THEN 'EXECUTE FUNCTION'

WHEN ACCESSRIGHT='GC' THEN 'CREATE GLOP'

WHEN ACCESSRIGHT='GD' THEN 'DROP GLOP'

WHEN ACCESSRIGHT='GM' THEN 'GLOP MEMBER'

WHEN ACCESSRIGHT='I' THEN 'INSERT'

WHEN ACCESSRIGHT='IX' THEN 'INDEX'

WHEN ACCESSRIGHT='MR' THEN 'MONITOR RESOURCE'

WHEN ACCESSRIGHT='MS' THEN 'MONITOR SESSION'

WHEN ACCESSRIGHT='NT' THEN 'NONTEMPORAL'

WHEN ACCESSRIGHT='OD' THEN 'OVERRIDE DELETE POLICY'

WHEN ACCESSRIGHT='OI' THEN 'OVERRIDE INSERT POLICY'

WHEN ACCESSRIGHT='OP' THEN 'CREATE OWNER PROCEDURE'

WHEN ACCESSRIGHT='OS' THEN 'OVERRIDE SELECT POLICY'

WHEN ACCESSRIGHT='OU' THEN 'OVERRIDE UPDATE POLICY'

WHEN ACCESSRIGHT='PC' THEN 'CREATE PROCEDURE'

WHEN ACCESSRIGHT='PD' THEN 'DROP PROCEDURE'

WHEN ACCESSRIGHT='PE' THEN 'EXECUTE PROCEDURE'

WHEN ACCESSRIGHT='RO' THEN 'REPLICATION OVERRIDE'

WHEN ACCESSRIGHT='R' THEN 'RETRIEVE/SELECT'

WHEN ACCESSRIGHT='RF' THEN 'REFERENCE'

WHEN ACCESSRIGHT='RS' THEN 'RESTORE'

WHEN ACCESSRIGHT='SA' THEN 'SECURITY CONSTRAINT ASSIGNMENT'

WHEN ACCESSRIGHT='SD' THEN 'SECURITY CONSTRAINT DEFINITION'

WHEN ACCESSRIGHT='ST' THEN 'STATISTICS'

WHEN ACCESSRIGHT='SS' THEN 'SET SESSION RATE'

WHEN ACCESSRIGHT='SR' THEN 'SET RESOURCE RATE'

WHEN ACCESSRIGHT='TH' THEN 'CTCONTROL'

WHEN ACCESSRIGHT='U' THEN 'UPDATE'

WHEN ACCESSRIGHT='UU' THEN 'UDT USAGE'

WHEN ACCESSRIGHT='UT' THEN 'UDT TYPE'

WHEN ACCESSRIGHT='UM' THEN 'UDT METHOD'

ELSE''

END (VARCHAR(26)) AS ACCESSRIGHTDESC

,GRANTAUTHORITY

,GRANTORNAME (VARCHAR(30))

,ALLNESSFLAG

,CREATORNAME (VARCHAR(30))

,CREATETIMESTAMP

FROM

(

SELECT -- GET DIRECT USER RIGHTS

USERNAME

,'USER' (VARCHAR(30)) AS ACCESSTYPE

,'' (VARCHAR(30)) AS ROLENAME

,DATABASENAME

,TABLENAME

,COLUMNNAME

,ACCESSRIGHT

,GRANTAUTHORITY

,GRANTORNAME

,ALLNESSFLAG

,CREATORNAME

,CREATETIMESTAMP

FROM DBC.ALLRIGHTS

WHERE USERNAME = :USERNAME

AND CREATORNAME NOT = :USERNAME -- EXCLUDE OBJECTS CREATED BY USER

UNION ALL

SELECT -- GET RIGHTS HELD THROUGH A ROLE

GRANTEE AS USERNAME

,'MEMBER' AS UR

,R.ROLENAME

,DATABASENAME

,TABLENAME

,COLUMNNAME

,ACCESSRIGHT

,NULL (CHAR(1)) AS GRANTAUTHORITY

,GRANTORNAME

,NULL (CHAR(1)) AS ALLNESSFLAG

,NULL (CHAR(1)) AS CREATORNAME

,CREATETIMESTAMP

FROM DBC.ALLROLERIGHTS R

JOIN DBC.ROLEMEMBERS M

ON M.ROLENAME = R.ROLENAME

WHERE USERNAME = :USERNAME

UNION ALL

SELECT -- GET RIGHTS HELD THROUGH A SUBROLE

USER AS USERNAME

,M.GRANTEE AS UR

,R.ROLENAME

,DATABASENAME

,TABLENAME

,COLUMNNAME

,ACCESSRIGHT

,NULL (CHAR(1)) AS GRANTAUTHORITY

,GRANTORNAME

,NULL (CHAR(1)) AS ALLNESSFLAG

,NULL (CHAR(1)) AS CREATORNAME

,CREATETIMESTAMP

FROM DBC.ALLROLERIGHTS R

JOIN DBC.ROLEMEMBERS M

ON M.ROLENAME = R.ROLENAME

WHERE M.GRANTEE IN (SELECT ROLENAME FROM DBC.ROLEMEMBERS WHERE

GRANTEE = :USERNAME)

) ALLRIGHTS

-- WHERE DATABASENAME NOT = 'DBC' -- UNCOMMENT TO EXCLUDE DBC OBJECTS

ORDER BY 4,5,6,7;

);

You can also use SQLs define in the macro to obtain direct rights or rights through roles.

2.2 Rights that could be considered for revocation

The purpose of the view is to find explicitly-granted rights that could be considered for revocation because the user has the same right via a role. I think it's been tested pretty thoroughly, but there are no guarantees so be sure you archive your DBC.AccessRights table before revoking rights.

SELECT DISTINCT

SR.ROLENAME AS ROLENAME

,UDB.DATABASENAME AS USERNAME

,DDB.DATABASENAME AS DATABASENAME

,TVM.TVMNAME AS OBJECTNAME

,CASE

WHEN TVM.TVMID = '00'XB

THEN 'N/A'

ELSE TVM.TABLEKIND

END AS OBJECTKIND

,UR.ACCESSRIGHT AS ACCESSRIGHT

FROM DBC.ACCESSRIGHTS RR

,DBC.ACCESSRIGHTS UR

,DBC.ROLEGRANTS RRG

,DBC.ROLEGRANTS URG

,DBC.ROLES SR

,DBC.DBASE UDB

,DBC.DBASE DDB

,DBC.TVM TVM

WHERE RR.DATABASEID = UR.DATABASEID

AND RR.TVMID = UR.TVMID

AND RR.FIELDID = UR.FIELDID

AND RR.ACCESSRIGHT = UR.ACCESSRIGHT

AND RR.USERID = RRG.ROLEID

AND UR.USERID = UDB.DATABASEID

AND UR.USERID = URG.GRANTEEID

AND SR.ROLEID = URG.ROLEID

AND RRG.GRANTEEID = URG.ROLEID

AND UR.DATABASEID = DDB.DATABASEID

AND UR.TVMID = TVM.TVMID

AND URG.GRANTEEKIND = 'U'

AND RRG.GRANTEEKIND = 'R'

AND UR.WITHGRANT = 'N'

AND UR.ALLNESSFLAG = 'N'

AND RR.WITHGRANT = 'N'

AND RR.ALLNESSFLAG = 'N'

UNION

SEL DISTINCT

R.ROLENAME AS ROLENAME

,UDB.DATABASENAME AS USERNAME

,DDB.DATABASENAME AS DATABASENAME

,TVM.TVMNAME AS OBJECTNAME

,CASE

WHEN TVM.TVMID = '00'XB

THEN 'N/A'

ELSE TVM.TABLEKIND

END AS OBJECTKIND

,UR.ACCESSRIGHT AS ACCESSRIGHT

FROM DBC.ACCESSRIGHTS RR

,DBC.ACCESSRIGHTS UR

,DBC.ROLEGRANTS RG

,DBC.ROLES R

,DBC.DBASE UDB

,DBC.DBASE DDB

,DBC.TVM TVM

WHERE RR.DATABASEID = UR.DATABASEID

AND RR.TVMID = UR.TVMID

AND RR.FIELDID = UR.FIELDID

AND RR.ACCESSRIGHT = UR.ACCESSRIGHT

AND RR.USERID = RG.ROLEID

AND UR.USERID = UDB.DATABASEID

AND UR.USERID = RG.GRANTEEID

AND R.ROLEID = RG.ROLEID

AND UR.DATABASEID = DDB.DATABASEID

AND UR.TVMID = TVM.TVMID

AND RG.GRANTEEKIND = 'U'

AND UR.WITHGRANT = 'N'

AND UR.ALLNESSFLAG = 'N'

AND RR.WITHGRANT = 'N'

AND RR.ALLNESSFLAG = 'N'

;

2.3 Role held by User

SELECT ROLENAME

FROM DBC.ROLEMEMBERS

WHERE GRANTEE ='USER_NAME'

3 Logon-Logoff

3.1 Users logged in for certain interval

Please see the argument you need to give to execute the macro.

REPLACE MACRO USERCOUNT

(D1 DATE DEFAULT DATE

,T1 FLOAT DEFAULT 120000

,D2 DATE DEFAULT DATE

,T2 FLOAT DEFAULT 120000)

(

SEL COUNT( A.SESSIONNO)(TITLE'NUMBER OF LOGONS/LOGOFFS')(FORMAT'ZZZZZZ9')

FROM DBC.LOGONOFF A, DBC.LOGONOFF B

WHERE A.EVENT='LOGON'

AND B.EVENT<>'LOGON'

AND A.SESSIONNO=B.SESSIONNO

AND A.IFPNO=B.IFPNO

AND A.LOGONDATE=B.LOGONDATE

AND A.LOGONTIME=B.LOGONTIME

AND ((A.LOGDATE=:D2

AND A.LOGTIME<=:T2)

OR A.LOGDATE<:D2)

AND ((B.LOGDATE=:D1

AND B.LOGTIME>=:T1)

OR B.LOGDATE>:D1)

ORDER BY 1;

);

3.2 Users which were logged on for less then a minute

SELECT A.USERNAME(TITLE'USER')(FORMAT'X(12)')

,A.SESSIONNO(TITLE'SESSION')(FORMAT'ZZZZZZ9')

,A.LOGTIME (NAMED LOGEND)(TITLE'')

,A.LOGDATE (TITLE'LOGON')

,A.LOGTIME (NAMED LOGEND)(TITLE'')

,B.LOGDATE(TITLE'LOGOFF')

,B.LOGTIME (NAMED LOGBEGIN)(TITLE'')

FROM DBC.LOGONOFF A, DBC.LOGONOFF B

WHERE A.EVENT='LOGON'

AND B.EVENT<>'LOGON'

AND A.SESSIONNO=B.SESSIONNO

AND A.LOGONDATE=B.LOGONDATE

AND A.LOGONTIME=B.LOGONTIME

/* ENDING DATE & TIME */

AND ((A.LOGDATE= DATE

AND A.LOGTIME <= TIME)

OR A.LOGDATE < DATE)

/* BEGINNING DATE & TIME 0 = MIDNIGHT, 120000 = NOON */

AND ((B.LOGDATE = DATE-1

AND B.LOGTIME >= 0)

OR B.LOGDATE > DATE-1)

AND (LOGEND-LOGBEGIN > -1.0)

ORDER BY A.LOGONDATE,A.LOGONTIME;

3.3 Logon Count for Certain Time Period

Following is a query to display how many times each user logged into your Teradata System in a given time period. By default the query returns information for the current date. For different dates, substitute date with the actual date in single quotes (i.e. '2001/07/31' for July 31, 2001). You can also substitute date with expressions such as date-1 (yesterday), date-2 (two days ago), etc.

SELECT D.DATABASENAME (TITLE 'USERNAME'),

COUNT(*) (TITLE '# OF//LOGINS', FORMAT 'ZZZZZ9')

FROM DBC.LOGONOFF L , DBC.DATABASES D

WHERE L.USERNAME = D.DATABASENAME

AND LOGONDATE=(DATE)

AND EVENT = 'LOGON'

GROUP BY 1

WITH SUM (1) (TITLE 'TOTAL', FORMAT 'ZZZZZ9')

ORDER BY 2 DESC;

3.4 Failed logon attempts during the last seven days

SELECT LOGDATE,LOGTIME,USERNAME (FORMAT ‘X(10)’),EVENT

FROM DBC.LOGONOFF

WHERE EVENT NOT LIKE (‘%LOGO%’)

AND LOGDATE GT DATE - 7

ORDER BY LOGDATE, LOGTIME ;

4 PPI

4.1 PPI Tables

SELECT DATABASENAME, TABLENAME (TITLE 'TABLE/JOIN INDEX NAME'), CONSTRAINTTEXT

FROM DBC.INDEXCONSTRAINTS

WHERE CONSTRAINTTYPE = 'Q'

ORDER BY DATABASENAME, TABLENAME;

4.2 Single Level PPI

SELECT DATABASENAME, TABLENAME (TITLE 'TABLE/JOIN INDEX NAME')

FROM DBC.INDEXCONSTRAINTS

WHERE CONSTRAINTTYPE = 'Q'

AND ( SUBSTRING(CONSTRAINTTEXT

FROM 1

FOR 13) < 'CHECK (/*02*/'

OR SUBSTRING(CONSTRAINTTEXT

FROM 1

FOR 13) > 'CHECK (/*15*/' )

ORDER BY DATABASENAME, TABLENAME;

4.3 MultiLevel PPI

SELECT DATABASENAME, TABLENAME (TITLE 'TABLE/JOIN INDEX NAME')

FROM DBC.INDEXCONSTRAINTS

WHERE CONSTRAINTTYPE = 'Q'

AND SUBSTRING(CONSTRAINTTEXT

FROM 1

FOR 13) >= 'CHECK (/*02*/'

AND SUBSTRING(CONSTRAINTTEXT

FROM 1

FOR 13) <= 'CHECK (/*15*/'

ORDER BY DATABASENAME, TABLENAME;

5 CPU

5.1 ResNode Macro

/* Parallel efficiency of total pct of time CPUs were busy */

(AVG(CPUBusy)*100)/NULLIFZERO(MAX(CPUBusy))

(FORMAT 'ZZ9', TITLE 'CPU//Eff// %'),

/* Parallel efficiency of the logical device IOs */

AVG(LogicalDeviceIO)*100 / NULLIFZERO(MAX(LogicalDeviceIO))

(FORMAT 'ZZ9', TITLE 'Ldv//Eff// %'),

5.2 CPU and I/O for a Specific user

This SQL statement requests totals for CPU time and I/O for user DBA01. The totals are aggregates of all resources.

SELECT UserName (FORMAT 'X (16)')

,AccountName (FORMAT 'X (12)')

,SUM (CpuTime)

,SUM (DiskIO)

FROM DBC.AMPUsage

WHERE UserName = 'DBA01'

GROUP BY 1, 2

ORDER BY 3 DESC ;

5.3 HOT AMP Identification

SELECT VPROC, SUM(CPUTIME), SUM(DISKIO)

FROM DBC.AMPUSAGE

ORDER BY 2,3,1 DESC

GROUP BY 1;

5.4 Skewed Query

SELECT HotAmp1CPU / NULLIFZERO(LowAmp1CPU) AS CPU_Skew ,qrylog.*

FROM DBC.qrylog

WHERE UserName = 'USER_NAME'

AND HotAmp1CPU / NULLIFZERO(LowAmp1CPU) > .9

AND NumOfActiveAMPs = 84

AND CollectTimeStamp > TIMESTAMP '2007-06-15 11:32:00'

ORDER BY CPU_Skew DESC;

5.5 CPU and I/O Consumed – Group Load

GroupLoad - groups the active sessions by user name and gives the accumulation of CPU seconds and Disk IO.

SELECT DT.UN (FORMAT 'X(10)', TITLE 'USERNAME')

,COUNT(DT.SN)(FORMAT 'Z(2)9', TITLE 'SESSION//COUNT')

,SUM(DT.CPUT)

(FORMAT 'ZZ,ZZZ,ZZZ,ZZ9.99', TITLE 'CPU//SECONDS')

,SUM(DT.DIO)

(FORMAT 'ZZZ,ZZZ,ZZZ,ZZ9', TITLE 'DISK IO//ACCESSES')

FROM

(

SELECT ST.USERNAME

,ST.SESSIONNO

,SUM(AC.CPU)

,SUM(AC.IO)

FROM DBC.SESSIONTBL ST, DBC.ACCTG AC

WHERE ST.USERNAME = AC.USERNAME

GROUP BY 1,2) DT(UN, SN, CPUT, DIO)

GROUP BY 1

ORDER BY 3 DESC;

5.6 Active workload by UserName and associated AccountName

CheckLoad - shows the active workload by user name and associated account name followed by a display of the current accumulation of CPU and Disk IO by Performance Group/Allocation Group.

SELECT 'RUNNING SESSIONS:' (TITLE '')

,DATE (FORMAT 'MM/DD/YYYY', TITLE '')

,TIME (TITLE '') ;

----------------------------------------------------------------------------------------

SELECT

ST.USERNAME (FORMAT 'X(10)', TITLE 'USERNAME')

,ST.ACCOUNTNAME (FORMAT 'X(10)', TITLE 'ACCOUNTNAME')

,CASE

WHEN ST.ACCOUNTNAME LIKE '$%$%'

THEN SUBSTRING(ST.ACCOUNTNAME

FROM (POSITION('$' IN ST.ACCOUNTNAME)+1)

FOR (POSITION('$' IN SUBSTRING (ST.ACCOUNTNAME

FROM (POSITION('$' IN ST.ACCOUNTNAME)+1)))-1))

WHEN ST.ACCOUNTNAME LIKE '$%'

THEN SUBSTRING (ST.ACCOUNTNAME FROM 2 FOR 1)

ELSE 'M'

END (NAMED PRIORITY, FORMAT 'X(8)',TITLE 'PRIORITY')

,SUM(AC.CPU)

(NAMED CPUT, FORMAT 'ZZZ,ZZZ,ZZZ,ZZ9.99', TITLE 'CPU SECONDS')

,SUM(AC.IO)

(NAMED IOT, FORMAT 'ZZZ,ZZZ,ZZZ,ZZ9', TITLE 'DISK IO//ACCESSES')

FROM DBC.SESSIONTBL ST, DBC.ACCTG AC

WHERE ST.USERNAME = AC.USERNAME

GROUP BY 1,2,3

ORDER BY 3,4 ASC;

----------------------------------------------------------------------------

SELECT 'RUNNING SESSIONS:' (TITLE '')

,DATE (FORMAT 'MM/DD/YYYY', TITLE '')

,TIME (TITLE '') ;

---------------------------------------------------------------------------------

SELECT

CASE

WHEN ST.ACCOUNTNAME LIKE '$%$%'

THEN SUBSTRING(ST.ACCOUNTNAME

FROM (POSITION('$' IN ST.ACCOUNTNAME)+1)

FOR (POSITION('$' IN SUBSTRING (ST.ACCOUNTNAME

FROM (POSITION('$' IN ST.ACCOUNTNAME)+1)))-1))

WHEN ST.ACCOUNTNAME LIKE '$%'

THEN SUBSTRING (ST.ACCOUNTNAME

FROM 2

FOR 1)

ELSE 'M'

END (NAMED PRIORITY, FORMAT 'X(8)',TITLE 'PRIORITY')

,SUM(AC.CPU)

(NAMED CPUT, FORMAT 'ZZZ,ZZZ,ZZZ,ZZ9.99', TITLE 'CPU SECONDS')

,SUM(AC.IO)

(NAMED IOT, FORMAT 'ZZZ,ZZZ,ZZZ,ZZ9', TITLE 'DISK IO//ACCESSES')

FROM DBC.SESSIONTBL ST, DBC.ACCTG AC

WHERE ST.USERNAME = AC.USERNAME

GROUP BY 1

ORDER BY 1,2 ASC;

6 DBQL

6.1 Table Accessed

SELECT OBJECTTABLENAME

FROM DBC.QRYLOGOBJECTS

WHERE CAST (COLLECTTIMESTAMP AS DATE) > DATE-7

AND OBJECTTYPE <> ‘D’

6.2 Top CPU Consuming Statements from DBQL

SELECT QueryText, TotalCPUTime, rank () over (

order by TotalCPUTime DESC) as ranking

from DBC.QryLog

where cast(CollectTimeStamp as date) = date

qualify ranking <=10

6.3 Query executed more then certain Time Period

SELECT QUERYTEXT, TOTALCPUTIME, RANK () OVER (

ORDER BY TOTALCPUTIME DESC) AS RANKING

FROM DBC.QRYLOG

WHERE CAST(COLLECTTIMESTAMP AS DATE) = DATE

QUALIFY RANKING <=10

6.4 Number of Queries by Users

SELECT

USERNAME UNAME,

COUNT(*)

FROM

DBC.QRYLOG

WHERE CAST(COLLECTTIMESTAMP AS DATE) >DATE

AND UNAME NOT IN

(

'DBCMANAGER'

,'P_LOAD_H01'

,'P_BRIO_USER'

,'DBC'

)

GROUP BY 1

6.5 Top Applications accessing Teradata Node

SELECT TOP 10 APPID ,COUNT(*)

FROM DBC.QRYLOG

GROUP BY 1

Teradata DBA

Friday, February 10, 2017

Teradata DBA – Useful SQLs

2 comments: