Clean up the DBC.AccessRights

/* -------------------------------------------------------------*/
/* Check for "orphan" accessright entries, i.e. accessrights that reference */
/* non-existent tables. Orphan access rights will not cause a problem unless you */
/* get so many that they impact the access performance. */
/* -------------------------------------------------------------*/

SELECT count(*) FROM DBC.AccessRights
WHERE TVMID <> '000001000000'xb
AND TVMID NOT IN (SEL TVMId FROM DBC.TVM);

/* -------------------------------------------------------------*/
/* Check for accessrights that reference non-existent databases. */
/* -------------------------------------------------------------*/

SELECT count(*) FROM DBC.AccessRights
WHERE DatabaseId NOT IN (SEL DatabaseId from DBC.DBase);

/* -------------------------------------------------------------*/
/* Check for access rights granted to non-existent users. */
/* -------------------------------------------------------------*/

SELECT count(*) FROM DBC.AccessRights
WHERE UserID NOT IN (SEL DatabaseID FROM DBC.DBase)
AND UserID NOT IN (SEL RoleID from DBC.Roles);




/* -------------------------------------------------------------*/
/* Check for accessrights that extend Reference or Index access at the database */
/* level (this is only valid at the table level). Reference DR 43044 */
/* If these exist it is a security hole. */
/* All must be removed or any subordinate databases will inherit the problem. */
/* -------------------------------------------------------------*/

SELECT count(*) FROM DBC.AccessRights
WHERE AccessRight IN ('IX','RF')
AND Databaseid <> '00'xb
AND TVMId = '000000000000'xb;

/* -------------------------------------------------------------*/
/* Check for accessrights entries that are not properly manipulated by access */
/* logging (see DR 48757) */
/* -------------------------------------------------------------*/

SELECT count(*) FROM DBC.AccessRights WHERE AccessRight = 'RO'
and userid = '00000100'xb AND databaseid = '00000100'xb;

Get collect stats details from Dieter Noeth Collect Views

Here is useful Query to get detailed report and make it a script from Dieter Noeth Collect Views:

First you have to install the "Dieter Noeth Collect Views", you can find it by search in T@YS

What is this query doing?

This query just do the following:

• Translate the flags in the views to its meaning (ex. C -> Column , I -> Index) and so on.
• Consider if the column or index is having full stat or sample.
• You can specify the collect duration or since what this collect done.
• Build a collect stat statement based on the above points.
  

/*Determine which collect stats take a long time or had a stale stats */

select
-- databasename, tablename, columnname, collectdate,collectduration,samplesize
'COLLECT STATISTICS' || case when samplesize is not null then ' USING SAMPLE' when samplesize is null then '' end || ' ON ' || databasename || '.' || trim(tablename) || ' '|| case when statstype='c' then 'COLUMN' when statstype='i' then 'INDEX' when statstype='m' then 'COLUMN' end || ' (' || trim(columnname) || ');'
--*
from {DB Contains Dieter Noeth Collect Views}.stats_details
where
collectduration > '0:30:00.00' -- For collect takes longer than 30 minutes
databasename = '{Database you need to inquire on}'
and tablename = '{Table Name}' -- Tables that you need to inquire, you can hash it if you need to see all tables
and columnname = '{Column Name}' -- COlumn that you need to inquire, you can hash it if you need to see all Column
and tablename not like '%temp%' -- If you need to exclude some tables, you can hash it if you need to see all tables
and cast(collectdate as date) < current_date -5 -- This means get all collect stat that run older than 5 days
order by collectduration ;