1 . How to find effective Space consumed by table
SELECT
DATABASENAME
,TABLENAME
, (MAX (CURRENTPERM)*(HASHAMP()+1)) /(1024*1024*1024) AS EFFECTIVE_PERM_INCLUDING_SKEW_GB
FROM DBC.TABLESIZE TS
WHERE DATABASENAME ='DATABASE_NAME' AND TABLENAME='TABLE_NAME'
GROUP BY 1,2 ;
DATABASENAME
,TABLENAME
, (MAX (CURRENTPERM)*(HASHAMP()+1)) /(1024*1024*1024) AS EFFECTIVE_PERM_INCLUDING_SKEW_GB
FROM DBC.TABLESIZE TS
WHERE DATABASENAME ='DATABASE_NAME' AND TABLENAME='TABLE_NAME'
GROUP BY 1,2 ;
SELECT TSIZE.DatabaseName,TSIZE.TableName,tdef.creatorname AS userid ,u.commentstring AS username,TDEF.CreateTimeStamp
AS Created ,TDEF.LastAlterTimeStamp AS LastAltered ,
TDEF.AccessCount,TDEF.LastAccessTimeStamp AS LastAccess
,SUM(TSIZE.CurrentPerm)/1024/1024/1024 AS CurrentPerm_GB
,SUM(TSIZE.PeakPerm) AS PeakPerm, (100 -
(AVG(TSIZE.CurrentPerm)/MAX(TSIZE.CurrentPerm)*100)) AS SkewFactor
FROM DBC.TableSizeV TSIZE
JOIN DBC.TablesV TDEF
ON TSIZE.DatabaseName = TDEF.DatabaseName
AND TSIZE.TableName = TDEF.TableName
JOIN dbc.users u
ON tdef.creatorname=u.username
AND TSIZE.TableName = TDEF.TableName
AND TSIZE.DatabaseName = 'DATABASE_NAME'
GROUP BY 1,2,3,4,5,6,7,8;
SELECT
TS.DATABASENAME
, TS.TABLENAME
, T.CREATORNAME
, T.CREATETIMESTAMP
, T.LASTACCESSTIMESTAMP
, SUM(TS.CURRENTPERM)/1024/1024/1024 AS CURRENT_PERM
, CAST (MAX ((TS.CURRENTPERM)*(HASHAMP()+1)) / (1024*1024*1024)
AS DECIMAL(18,
2)) AS EFFECTIVE_PERM_INCLUDING_SKEW
, EFFECTIVE_PERM_INCLUDING_SKEW - CURRENT_PERM AS
SPACE_WASTED_DUE_TO_SKEW
, (100 - (AVG (TS.CURRENTPERM) / NULLIFZERO(MAX
(TS.CURRENTPERM))*100)) AS SKEW_PERCENT
FROM DBC.TABLESIZEV TS INNER JOIN DBC.TABLESV T
ON TS.DATABASENAME || '.' || TS.TABLENAME =
T.DATABASENAME || '.' || T.TABLENAME
WHERE
T.LASTACCESSTIMESTAMP < CURRENT_DATE - 180
GROUP BY 1,2,3,4,5
ORDER BY 5 DESC;
SELECT TS.DATABASENAME , TS.TABLENAME , T.CREATORNAME ,
SUM ( TS.CURRENTPERM ) / 1024 / 1024 / 1024 AS CURRENT_PERM ,
CAST( MAXIMUM ( ( TS.CURRENTPERM ) * ( HASHAMP ( ) + 1 ) ) / ( 1024 * 1024 * 1024 )
AS DEC ( 18 ,2 ) ) AS
EFFECTIVE_PERM_INCLUDING_SKEW ,
EFFECTIVE_PERM_INCLUDING_SKEW - CURRENT_PERM
AS SPACE_WASTED_DUE_TO_SKEW ,
100 - ( AVG (
TS.CURRENTPERM ) / NULLIFZERO ( MAXIMUM ( TS.CURRENTPERM ) ) * 100 ) )
AS SKEW_PERCENT
FROM DBC.TABLESIZEV TS
INNER JOIN DBC.TABLESV T
ON TS.DATABASENAME ||
'.' || TS.TABLENAME = T.DATABASENAME || '.' || T.TABLENAME
WHERE TS.DATABASENAME =
'DB'
GROUP BY 1 , 2 , 3
ORDER BY 5 DESC ;
Size based on AMPS:
SELECT DatabaseName,
TableName ,
Vproc ,
CurrentPerm ,
PeakPerm
FROM DBC.TableSize
WHERE
DatabaseName= 'DB_NAME'
AND
TableName='PUT_TABLE_NAME_HERE'
ORDER BY Vproc
;
2. How to find effective space consumed at database level
SELECT
DatabaseName
,SUM(CurrentPerm)/1024/1024/1024 AS USEDSPACE_IN_GB
,SUM(MaxPerm)/1024/1024/1024 AS MAXSPACE_IN_GB
,SUM(CurrentPerm)/ NULLIFZERO (SUM(MaxPerm)) *100 (FORMAT 'zz9.99%') AS Percentage_Used
,MAXSPACE_IN_GB- USEDSPACE_IN_GB AS REMAININGSPACE_IN_GB
FROM DBC.DiskSpace
WHERE DatabaseName = 'DATABAE_NAME'
GROUP BY DatabaseName;
DatabaseName
,SUM(CurrentPerm)/1024/1024/1024 AS USEDSPACE_IN_GB
,SUM(MaxPerm)/1024/1024/1024 AS MAXSPACE_IN_GB
,SUM(CurrentPerm)/ NULLIFZERO (SUM(MaxPerm)) *100 (FORMAT 'zz9.99%') AS Percentage_Used
,MAXSPACE_IN_GB- USEDSPACE_IN_GB AS REMAININGSPACE_IN_GB
FROM DBC.DiskSpace
WHERE DatabaseName = 'DATABAE_NAME'
GROUP BY DatabaseName;
3. Recursively display Databases and its child databases
with recursive descendants(DatabaseName, DBPath, Level) as
(
select DatabaseName
, cast(databasename as varchar(1024)) as dbpath
, 0
from DBC.databasesv
where DatabaseName = 'Root_Database_Name'
union all
select c.DatabaseName
, p.DBPath || '>' || c.DatabaseName
, p.Level + 1
from DBC.databasesv c
, descendants p
where p.DatabaseName = c.OwnerName
and c.DatabaseName <> 'Root_Database_Name'
)
select substring('', 1, level * 2) || d.DatabaseName as DatabaseName
from descendants d
(
select DatabaseName
, cast(databasename as varchar(1024)) as dbpath
, 0
from DBC.databasesv
where DatabaseName = 'Root_Database_Name'
union all
select c.DatabaseName
, p.DBPath || '>' || c.DatabaseName
, p.Level + 1
from DBC.databasesv c
, descendants p
where p.DatabaseName = c.OwnerName
and c.DatabaseName <> 'Root_Database_Name'
)
select substring('', 1, level * 2) || d.DatabaseName as DatabaseName
from descendants d
4. Query for Logon event table
SELECT LogDate,LogTime,UserName (FORMAT
'X(10)'),Event
FROM DBC.LogOnOffV
WHERE Event NOT LIKE ('%Logo%')
AND LogDate GT DATE - 7
ORDER BY LogDate, LogTime ;
FROM DBC.LogOnOffV
WHERE Event NOT LIKE ('%Logo%')
AND LogDate GT DATE - 7
ORDER BY LogDate, LogTime ;
5.Query to find user last logon date
Sel Max(LogDate), UserName from dbc.LogonOff where
UserName in ('XYZ')
6.BTEQ scripts to find all invalid views ( invalid reference
views)
Create following bteq script and excut it in bteq widow
.logon system/user,passwd;
.set width 500;
create volatile table invalid_views (databasename varchar(30), tablename varchar(30)) unique primary index (databasename, tablename) on commit preserve rows;
.OS rm /yourpath/out.bteq
.export data file=/yourpath/out.bteq
.RECORDMODE OFF
.FOLDLINE ALL
select 'show select * from ' || trim(databasename) || '.' || trim(tablename) || ';',
'.if errorcode = 0 then .goto next;',
'insert into invalid_views values ('''||trim(databasename)||''',''' ||trim(tablename)||''');',
'.label next;'
from dbc.tables
where tablekind = 'V'
order by 1;
.export reset
.run file = /yourpath/out.bteq
.FOLDLINE OFF ALL
select *
from invalid_views
order by 1,2
;
.set width 500;
create volatile table invalid_views (databasename varchar(30), tablename varchar(30)) unique primary index (databasename, tablename) on commit preserve rows;
.OS rm /yourpath/out.bteq
.export data file=/yourpath/out.bteq
.RECORDMODE OFF
.FOLDLINE ALL
select 'show select * from ' || trim(databasename) || '.' || trim(tablename) || ';',
'.if errorcode = 0 then .goto next;',
'insert into invalid_views values ('''||trim(databasename)||''',''' ||trim(tablename)||''');',
'.label next;'
from dbc.tables
where tablekind = 'V'
order by 1;
.export reset
.run file = /yourpath/out.bteq
.FOLDLINE OFF ALL
select *
from invalid_views
order by 1,2
;
7.Space monitor query
SELECT A.DATABASENAME AS "Database_Name" , SUM ( A.CURRENTPERM ) / ( 1024 * 1024 * 1024 ) AS "Consumed_Space" ,
SUM ( A.MAXPERM ) / ( 1024 * 1024 * 1024 ) AS "Allocated_Space" ,
( ( ( Allocated_Space - Consumed_Space ) ) ) AS "Remaining_Space" ,
( Consumed_Space / Allocated_Space ) * 100 AS "PercConsumed" ,
( MAXIMUM ( A.currentperm ) * ( HASHAMP ( ) + 1 ) ) / ( 1024 * 1024 * 1024 ) AS "Effective_Space" ,
( Allocated_Space - Effective_Space ) AS "Remaining_EffectiveSpace" ,
( ( Effective_Space / Allocated_Space ) * 100 ) AS "PercConsumed_Effective" ,
CASE
WHEN PercConsumed_Effective <= 80 THEN 'OK'
ELSE 'Out_of_Space'
END AS "Status"
FROM DBC.DISKSPACE A
WHERE databasename IN ( 'xyz')
GROUP BY 1
HAVING Allocated_Space <> 0
ORDER BY 5 DESC ;
8. Query find Role membership
SELECT
A.ROLENAME
,A.GRANTEE
--,A.GRANTOR
--,A.DefaultRole,A.WithAdmin
,B.DATABASENAME
--,B.TABLENAME,B.COLUMNNAME,B.GRANTORNAME
,B.AccessRight,
CASE
WHEN B.AccessRight = 'AF' THEN 'Alter Function'
WHEN B.AccessRight = 'AP' THEN 'Alter Procedure'
WHEN B.AccessRight = 'AS' THEN 'Abort Session'
WHEN B.AccessRight = 'CA' THEN 'Create Authorization'
WHEN B.AccessRight = 'CD' THEN 'Create Database'
WHEN B.AccessRight = 'CF' THEN 'Create Function'
WHEN B.AccessRight = 'CG' THEN 'Create Trigger'
WHEN B.AccessRight = 'CM' THEN 'Create Macro'
WHEN B.AccessRight = 'CO' THEN 'Create Profile'
WHEN B.AccessRight = 'CP' THEN 'Checkpoint'
WHEN B.AccessRight = 'CR' THEN 'Create Role'
WHEN B.AccessRight = 'CT' THEN 'Create Table'
WHEN B.AccessRight = 'CU' THEN 'Create User'
WHEN B.AccessRight = 'CV' THEN 'Create View'
WHEN B.AccessRight = 'D' THEN 'Delete'
WHEN B.AccessRight = 'DA' THEN 'Drop Authorization'
WHEN B.AccessRight = 'DD' THEN 'Drop Database'
WHEN B.AccessRight = 'DF' THEN 'Drop Function'
WHEN B.AccessRight = 'DG' THEN 'Drop Trigger'
WHEN B.AccessRight = 'DM' THEN 'Drop Macro'
WHEN B.AccessRight = 'DO' THEN 'Drop Profile'
WHEN B.AccessRight = 'DP' THEN 'Dump'
WHEN B.AccessRight = 'DR' THEN 'Drop Role'
WHEN B.AccessRight = 'DT' THEN 'Drop Table'
WHEN B.AccessRight = 'DU' THEN 'Drop User'
WHEN B.AccessRight = 'DV' THEN 'Drop View'
WHEN B.AccessRight = 'E' THEN 'Execute'
WHEN B.AccessRight = 'EF' THEN 'Execute Function'
WHEN B.AccessRight = 'GC' THEN 'Create GLOP SET'
WHEN B.AccessRight = 'GD' THEN 'Drop GLOP SET'
WHEN B.AccessRight = 'GM' THEN 'GLOP Member'
WHEN B.AccessRight = 'I' THEN 'Insert'
WHEN B.AccessRight = 'IX' THEN 'Indexes'
WHEN B.AccessRight = 'MR' THEN 'Monitor Resource'
WHEN B.AccessRight = 'MS' THEN 'Monitor Session'
WHEN B.AccessRight = 'NT' THEN 'Non Temporal'
WHEN B.AccessRight = 'OA' THEN 'Override Archive Constraint'
WHEN B.AccessRight = 'OD' THEN 'Override Delete Constraint'
WHEN B.AccessRight = 'OI' THEN 'Override Insert Constraint'
WHEN B.AccessRight = 'OP' THEN 'Create Owner Procedure'
WHEN B.AccessRight = 'OR' THEN 'Override Restore Constraint'
WHEN B.AccessRight = 'OS' THEN 'Override Select Constraint'
WHEN B.AccessRight = 'OU' THEN 'Override Update Constraint'
WHEN B.AccessRight = 'PC' THEN 'Create Procedure'
WHEN B.AccessRight = 'PD' THEN 'Drop Procedure'
WHEN B.AccessRight = 'PE' THEN 'Execute Procedure'
WHEN B.AccessRight = 'R' THEN 'Retrieve or Select'
WHEN B.AccessRight = 'RF' THEN 'References'
WHEN B.AccessRight = 'RS' THEN 'Restore'
WHEN B.AccessRight = 'SH' THEN 'Show'
WHEN B.AccessRight = 'SA' THEN 'Constraint Assignment'
WHEN B.AccessRight = 'SD' THEN 'Constraint Definition'
WHEN B.AccessRight = 'SS' THEN 'Set Session Rate'
WHEN B.AccessRight = 'SR' THEN 'Set Resource Rate'
WHEN B.AccessRight = 'ST' THEN 'Statistics'
WHEN B.AccessRight = 'TH' THEN 'Connect Through'
WHEN B.AccessRight = 'U' THEN 'Update'
WHEN B.AccessRight = 'UM' THEN 'UDT Method'
WHEN B.AccessRight = 'UT' THEN 'UDT Type'
WHEN B.AccessRight = 'UU' THEN 'UDT Usage'
END AS Permission
A.ROLENAME
,A.GRANTEE
--,A.GRANTOR
--,A.DefaultRole,A.WithAdmin
,B.DATABASENAME
--,B.TABLENAME,B.COLUMNNAME,B.GRANTORNAME
,B.AccessRight,
CASE
WHEN B.AccessRight = 'AF' THEN 'Alter Function'
WHEN B.AccessRight = 'AP' THEN 'Alter Procedure'
WHEN B.AccessRight = 'AS' THEN 'Abort Session'
WHEN B.AccessRight = 'CA' THEN 'Create Authorization'
WHEN B.AccessRight = 'CD' THEN 'Create Database'
WHEN B.AccessRight = 'CF' THEN 'Create Function'
WHEN B.AccessRight = 'CG' THEN 'Create Trigger'
WHEN B.AccessRight = 'CM' THEN 'Create Macro'
WHEN B.AccessRight = 'CO' THEN 'Create Profile'
WHEN B.AccessRight = 'CP' THEN 'Checkpoint'
WHEN B.AccessRight = 'CR' THEN 'Create Role'
WHEN B.AccessRight = 'CT' THEN 'Create Table'
WHEN B.AccessRight = 'CU' THEN 'Create User'
WHEN B.AccessRight = 'CV' THEN 'Create View'
WHEN B.AccessRight = 'D' THEN 'Delete'
WHEN B.AccessRight = 'DA' THEN 'Drop Authorization'
WHEN B.AccessRight = 'DD' THEN 'Drop Database'
WHEN B.AccessRight = 'DF' THEN 'Drop Function'
WHEN B.AccessRight = 'DG' THEN 'Drop Trigger'
WHEN B.AccessRight = 'DM' THEN 'Drop Macro'
WHEN B.AccessRight = 'DO' THEN 'Drop Profile'
WHEN B.AccessRight = 'DP' THEN 'Dump'
WHEN B.AccessRight = 'DR' THEN 'Drop Role'
WHEN B.AccessRight = 'DT' THEN 'Drop Table'
WHEN B.AccessRight = 'DU' THEN 'Drop User'
WHEN B.AccessRight = 'DV' THEN 'Drop View'
WHEN B.AccessRight = 'E' THEN 'Execute'
WHEN B.AccessRight = 'EF' THEN 'Execute Function'
WHEN B.AccessRight = 'GC' THEN 'Create GLOP SET'
WHEN B.AccessRight = 'GD' THEN 'Drop GLOP SET'
WHEN B.AccessRight = 'GM' THEN 'GLOP Member'
WHEN B.AccessRight = 'I' THEN 'Insert'
WHEN B.AccessRight = 'IX' THEN 'Indexes'
WHEN B.AccessRight = 'MR' THEN 'Monitor Resource'
WHEN B.AccessRight = 'MS' THEN 'Monitor Session'
WHEN B.AccessRight = 'NT' THEN 'Non Temporal'
WHEN B.AccessRight = 'OA' THEN 'Override Archive Constraint'
WHEN B.AccessRight = 'OD' THEN 'Override Delete Constraint'
WHEN B.AccessRight = 'OI' THEN 'Override Insert Constraint'
WHEN B.AccessRight = 'OP' THEN 'Create Owner Procedure'
WHEN B.AccessRight = 'OR' THEN 'Override Restore Constraint'
WHEN B.AccessRight = 'OS' THEN 'Override Select Constraint'
WHEN B.AccessRight = 'OU' THEN 'Override Update Constraint'
WHEN B.AccessRight = 'PC' THEN 'Create Procedure'
WHEN B.AccessRight = 'PD' THEN 'Drop Procedure'
WHEN B.AccessRight = 'PE' THEN 'Execute Procedure'
WHEN B.AccessRight = 'R' THEN 'Retrieve or Select'
WHEN B.AccessRight = 'RF' THEN 'References'
WHEN B.AccessRight = 'RS' THEN 'Restore'
WHEN B.AccessRight = 'SH' THEN 'Show'
WHEN B.AccessRight = 'SA' THEN 'Constraint Assignment'
WHEN B.AccessRight = 'SD' THEN 'Constraint Definition'
WHEN B.AccessRight = 'SS' THEN 'Set Session Rate'
WHEN B.AccessRight = 'SR' THEN 'Set Resource Rate'
WHEN B.AccessRight = 'ST' THEN 'Statistics'
WHEN B.AccessRight = 'TH' THEN 'Connect Through'
WHEN B.AccessRight = 'U' THEN 'Update'
WHEN B.AccessRight = 'UM' THEN 'UDT Method'
WHEN B.AccessRight = 'UT' THEN 'UDT Type'
WHEN B.AccessRight = 'UU' THEN 'UDT Usage'
END AS Permission
FROM DBC.ROLEMEMBERS A
INNER JOIN DBC.ALLROLERIGHTS B
ON A.ROLENAME = B.ROLENAME
WHERE Grantee in ('ramu' )
AND B.DATABASENAME IN
(
'xyz'
)
GROUP BY 1,2,3,4,5--,6,7,8,9,10,11
ORDER BY 2,1,3,4,5;
INNER JOIN DBC.ALLROLERIGHTS B
ON A.ROLENAME = B.ROLENAME
WHERE Grantee in ('ramu' )
AND B.DATABASENAME IN
(
'xyz'
)
GROUP BY 1,2,3,4,5--,6,7,8,9,10,11
ORDER BY 2,1,3,4,5;
9. Top 10 time-consuming quires
SELECT SessionID,CollectTimeStamp,UserName, QueryText,
ElapsedTime, RANK() OVER(
ORDER BY ElapsedTime DESC) AS RNK FROM DBC.QRYLOG WHERE STARTTIME(DATE) >= CURRENT_DATE-1
QUALIFY RNK <= 10;
ORDER BY ElapsedTime DESC) AS RNK FROM DBC.QRYLOG WHERE STARTTIME(DATE) >= CURRENT_DATE-1
QUALIFY RNK <= 10;
10. Query to check child tables ( child reference tables)
select * from DBC.All_RI_Children
where ParentTable ='TABLE_NAME';
where ParentTable ='TABLE_NAME';
Query generator
sel 'ALTER TABLE '||trim(ChildDB)||'.'||trim(ChildTable)||' ADD
CONSTRAINT '||trim(IndexName)||' FOREIGN KEY ('||trim(ChildKeyColumn)||')
REFERENCES
'||trim(ParentDB)||'.'||trim(ParentTable)||'('||trim(ParentKeyColumn)||')
;'
from DBC.All_RI_Children where ChildDB='XYZ_DB_NAME' ;
from DBC.All_RI_Children where ChildDB='XYZ_DB_NAME' ;
11.Query to check response time using query band ( Teradata 15.10
version)
SELECT
GETQUERYBANDVALUE( q.queryband,0,'Universe' ) AS BOUniverse ,
GETQUERYBANDVALUE( q.queryband,0,'Document' ) AS BOReport ,
COUNT(*) AS ReportCount,
AVERAGE(q.TotalFirstRespTime) AS avg_resp,
MAX(q.TotalFirstRespTime) AS max_resp,
MIN(q.TotalFirstRespTime) AS min_resp,
AVERAGE(s.elapsedtime) AS avg_return,
MAX(s.elapsedtime) AS max_return,
MIN(s.elapsedtime) AS min_return,
AVERAGE(q.numresultrows) AS avg_rows,
MAX(q.numresultrows) AS max_rows,
MIN(q.numresultrows) AS min_rows
FROM dbc.qrylog q
LEFT OUTER JOIN dbc.QryLogStepsV s
ON q.procid = s.procid
AND q.queryid = s.queryid
AND s.STEPNAME = 'RESP'
WHERE USERNAME = 'XYZ'
AND queryband IS NOT NULL
AND starttime > '2016-02-19 00:00:00'
GROUP BY 1, 2
ORDER BY ReportCount DESC;
GETQUERYBANDVALUE( q.queryband,0,'Universe' ) AS BOUniverse ,
GETQUERYBANDVALUE( q.queryband,0,'Document' ) AS BOReport ,
COUNT(*) AS ReportCount,
AVERAGE(q.TotalFirstRespTime) AS avg_resp,
MAX(q.TotalFirstRespTime) AS max_resp,
MIN(q.TotalFirstRespTime) AS min_resp,
AVERAGE(s.elapsedtime) AS avg_return,
MAX(s.elapsedtime) AS max_return,
MIN(s.elapsedtime) AS min_return,
AVERAGE(q.numresultrows) AS avg_rows,
MAX(q.numresultrows) AS max_rows,
MIN(q.numresultrows) AS min_rows
FROM dbc.qrylog q
LEFT OUTER JOIN dbc.QryLogStepsV s
ON q.procid = s.procid
AND q.queryid = s.queryid
AND s.STEPNAME = 'RESP'
WHERE USERNAME = 'XYZ'
AND queryband IS NOT NULL
AND starttime > '2016-02-19 00:00:00'
GROUP BY 1, 2
ORDER BY ReportCount DESC;
For Old database versions
REPLACE VIEW Ramu.BMIBORepUseByUser AS
LOCKING ROW FOR ACCESS
SELECT StartDate RepDate,
BOUniverse,
BOUser,
BORep,
COUNT(*) RepCount
FROM (
SELECT starttime(DATE) StartDate,
POSITION('Universe=' IN TRIM(queryband)) + 9 BOUniStart,
POSITION(';' IN TRIM(queryband)) - BOUniStart BOUniLen,
SUBSTRING(TRIM(queryband) FROM BOUniStart + BOUniLen + 1) QB1,
POSITION('ClientUser=' IN TRIM(QB1)) + 11 BOUserStart,
POSITION(';' IN TRIM(QB1)) - BOUserStart BOUserLen,
SUBSTRING(TRIM(QB1) FROM BOUserStart + BOUserLen + 1) QB2,
POSITION('ClientUser=' IN TRIM(QB2)) + 10 BORepStart,
POSITION(';' IN TRIM(QB2)) - BORepStart BORepLen,
SUBSTRING(TRIM(queryband) FROM BOUniStart FOR BOUniLen) BOUniverse,
SUBSTRING(TRIM(QB1) FROM BOUserStart FOR BOUserLen) BOUser,
SUBSTRING(TRIM(QB2) FROM BORepStart FOR BORepLen) BORep
FROM p_admin.dbqlogtbl_hst
WHERE username = 'XYZ'
AND queryband IS NOT NULL
) BOUsage
GROUP BY 1,2,3,4
;
LOCKING ROW FOR ACCESS
SELECT StartDate RepDate,
BOUniverse,
BOUser,
BORep,
COUNT(*) RepCount
FROM (
SELECT starttime(DATE) StartDate,
POSITION('Universe=' IN TRIM(queryband)) + 9 BOUniStart,
POSITION(';' IN TRIM(queryband)) - BOUniStart BOUniLen,
SUBSTRING(TRIM(queryband) FROM BOUniStart + BOUniLen + 1) QB1,
POSITION('ClientUser=' IN TRIM(QB1)) + 11 BOUserStart,
POSITION(';' IN TRIM(QB1)) - BOUserStart BOUserLen,
SUBSTRING(TRIM(QB1) FROM BOUserStart + BOUserLen + 1) QB2,
POSITION('ClientUser=' IN TRIM(QB2)) + 10 BORepStart,
POSITION(';' IN TRIM(QB2)) - BORepStart BORepLen,
SUBSTRING(TRIM(queryband) FROM BOUniStart FOR BOUniLen) BOUniverse,
SUBSTRING(TRIM(QB1) FROM BOUserStart FOR BOUserLen) BOUser,
SUBSTRING(TRIM(QB2) FROM BORepStart FOR BORepLen) BORep
FROM p_admin.dbqlogtbl_hst
WHERE username = 'XYZ'
AND queryband IS NOT NULL
) BOUsage
GROUP BY 1,2,3,4
;
12. User statistics query
REPLACE VIEW Ramu.LogonCheck AS
LOCKING ROW FOR ACCESS
SELECT u.LockedCount AS BadAttempts,
CASE WHEN COALESCE(pi.MAXLOGONATTEMPTS,(SELECT MAXLOGONATTEMPTS FROM dbc.SecurityDefaults),0) = 0 THEN
-1
ELSE
COALESCE(pi.MAXLOGONATTEMPTS,(SELECT MAXLOGONATTEMPTS FROM dbc.SecurityDefaults)) - u.lockedcount
END AS AttemptsLeft,
CASE WHEN AttemptsLeft = 0 THEN
'Locked'
ELSE
'Open'
END Locked,
u.Databasename Username,
u.commentstring,
COALESCE(u.profilename,'<SecDef>') ProfileName,
/*+------------------------------------------------+
| LockedDate converted from Julian to YYYY-MM-DD |
+------------------------------------------------+ */
((100 * ((4 * u.LockedDate - 1) / 146097)
+ (4 * (((4 * u.LockedDate - 1) MOD 146097) / 4)
+ 3) / 1461 - 1900) + ((5 * (((4 * (((4 * u.LockedDate
- 1) MOD 146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2)
/ 12) * 10000 + (((5 * (((4 * (((4 * u.LockedDate - 1)
MOD 146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2) MOD 12
+ 1) * 100 + ((5 * (((4 * (((4 * u.LockedDate - 1) MOD
146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) MOD 153 +5) / 5
(DATE, FORMAT 'yyyy-mm-dd', NAMED MostRecentLockDate),
/*+------------------------------------------------+
| Lockedtime converted from minutes to HH:MM |
+------------------------------------------------+*/
(u.LockedTime / 60 ) * 100 +(u.LockedTime MOD 60)
(INTEGER, FORMAT '99:99', NAMED MostRecentLockTime)
FROM dbc.dbase u
LEFT JOIN dbc.profileinfo pi
ON pi.profilename = u.profilename
WHERE u.rowtype = 'U'
-- AND u.ownername IN ('UserDBA','GenDBA','DBC')
;
LOCKING ROW FOR ACCESS
SELECT u.LockedCount AS BadAttempts,
CASE WHEN COALESCE(pi.MAXLOGONATTEMPTS,(SELECT MAXLOGONATTEMPTS FROM dbc.SecurityDefaults),0) = 0 THEN
-1
ELSE
COALESCE(pi.MAXLOGONATTEMPTS,(SELECT MAXLOGONATTEMPTS FROM dbc.SecurityDefaults)) - u.lockedcount
END AS AttemptsLeft,
CASE WHEN AttemptsLeft = 0 THEN
'Locked'
ELSE
'Open'
END Locked,
u.Databasename Username,
u.commentstring,
COALESCE(u.profilename,'<SecDef>') ProfileName,
/*+------------------------------------------------+
| LockedDate converted from Julian to YYYY-MM-DD |
+------------------------------------------------+ */
((100 * ((4 * u.LockedDate - 1) / 146097)
+ (4 * (((4 * u.LockedDate - 1) MOD 146097) / 4)
+ 3) / 1461 - 1900) + ((5 * (((4 * (((4 * u.LockedDate
- 1) MOD 146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2)
/ 12) * 10000 + (((5 * (((4 * (((4 * u.LockedDate - 1)
MOD 146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2) MOD 12
+ 1) * 100 + ((5 * (((4 * (((4 * u.LockedDate - 1) MOD
146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) MOD 153 +5) / 5
(DATE, FORMAT 'yyyy-mm-dd', NAMED MostRecentLockDate),
/*+------------------------------------------------+
| Lockedtime converted from minutes to HH:MM |
+------------------------------------------------+*/
(u.LockedTime / 60 ) * 100 +(u.LockedTime MOD 60)
(INTEGER, FORMAT '99:99', NAMED MostRecentLockTime)
FROM dbc.dbase u
LEFT JOIN dbc.profileinfo pi
ON pi.profilename = u.profilename
WHERE u.rowtype = 'U'
-- AND u.ownername IN ('UserDBA','GenDBA','DBC')
;
12. How to find Roles on DB
SELECT
AR.ROLENAME
,AR.DATABASENAME
,AR.TABLENAME
,AR.ACCESSRIGHT
FROM DBC.ALLROLERIGHTSV AR
WHERE
AR.DATABASENAME IN('DB_NAME');
14. Finding Delay time.
SEL
cast(sqltextinfo as varchar(10000)) SQLFIRST10KCHARS
,cast(starttime as date) as Logdate
,queryband
,username
,sessionid,
A.firstresptime
,A.starttime
,A.firststeptime
,zeroifnull(cast(sum(cast(
extract(hour from ((A.firstresptime-A.starttime) Hour(2) TO second(6)))*3600
+
extract(minute from((A.firstresptime-A.starttime) Hour(2) TO second(6)))*60
+
extract(second from((A.firstresptime-A.starttime)Hour(2) TO second(6)))As decimal(10,2)))
As decimal(10,2))) As ElapsedTimesecs
,zeroifnull(cast(sum(cast(
extract(hour from ((A.firstresptime-A.firststeptime) Hour(2) TO second(6)))*3600
+
extract(minute from((A.firstresptime-A.firststeptime) Hour(2) TO second(6)))*60
+
extract(second from((A.firstresptime-A.firststeptime)Hour(2) TO second(6)))As decimal(10,2)))
As decimal(10,2))) As ExecutionTimesecs
,ElapsedTimesecs-ExecutionTimesecs as delay_time_secs
,Zeroifnull(cast(sum(cast(
extract(hour from ((LastRespTime-FirstRespTime) Hour(2) TO second(6)))*3600
+
extract(minute from((LastRespTime-FirstRespTime) Hour(2) TO second(6)))*60
+
extract(second from((LastRespTime-FirstRespTime)Hour(2) TO second(6)))As decimal(10,2)))
As decimal(10,2))) As total_responding_secs
from dbc.dbqlogtbl A
JOIN dbc.dbqlsqltbl B
on A.queryid=B.queryid
and A.procid=B.procid
and cast(A.collecttimestamp as date) =cast(b.collecttimestamp as date)
AND username='sysdba'
--where a.ampcputime <>0 and b.sqlrowno=1
group by 1,2,3,4,5,6,7,8
----COD ---
sel
CAST(CAST(20000000+CreateDate AS CHAR(8)) AS DATE FORMAT 'YYYYMMDD'),
CreateTime
,SystemCpuLimit,SystemIOLimit,ReservedAWT,LimitAWT from
tdwm.PsfGlobalOpEnvValues
order by
CreateDate desc,CreateTime desc ;
----QB test
---
SEL
cast(sqltextinfo as varchar(10000))
SQLFIRST10KCHARS
,A.CollectTimeStamp
,queryband
,username
,sessionid
from
pdcrinfo.dbqlogtbl_hst A
Join
pdcrinfo.dbqlsqltbl_hst B
on
A.queryid=B.queryid
and
A.procid=B.procid
and
A.logdate=B.logdate
where A.logdate >=current_date - 1
and
B.logdate>=current_date-1
and
username='XYZ'
--and
queryband is not null
SEL
cast(sqltextinfo as varchar(10000))
SQLFIRST10KCHARS
,A.CollectTimeStamp
,queryband
,username
,sessionid
from
dbc.dbqlogtbl A
JOIN
dbc.dbqlsqltbl B
on
A.queryid=B.queryid
and
A.procid=B.procid
and
cast(A.collecttimestamp as date) =cast(b.collecttimestamp as date)
and
username='XYZ'
----- know about system restart
select
TheDate,TheTime (FORMAT '99:99:99') as times,TheTime,Event_Tag,TheFunction
,Text(varchar(500)) as sometxt from DBC.Software_Event_LogV
where
times between'01:50:00' and '02:00:11'
and thedate=1170825 order by TheTime desc;
TASM:
command on node: schset
DSA jobs sql:
select i.job_name, s.system_name from bar.job_object j, bar.job_index i, bar.system_index s, bar.job_settings b
where (b.source_system_id=s.system_id or b.target_system_id=s.system_id) and b.job_id=i.job_id
and lower(j.object_name) = ‘objectName' and j.job_id=i.job_id and s.system_name=’systemName';
TASM:
command on node: schset
DSA jobs sql:
select i.job_name, s.system_name from bar.job_object j, bar.job_index i, bar.system_index s, bar.job_settings b
where (b.source_system_id=s.system_id or b.target_system_id=s.system_id) and b.job_id=i.job_id
and lower(j.object_name) = ‘objectName' and j.job_id=i.job_id and s.system_name=’systemName';
