SQL Page 1

Move Space : 

CREATE DATABASE SPACEMOVE  FROM DBC AS PERM = 100000000000;
GIVE SPACEMOVE TO SERVICEDB_DV1;  
DROP DATABASE SPACEMOVE ;

Object  Count:

SELECT C.DatabaseName
     , CASE WHEN RowType = 'u' THEN 'User'
            WHEN RowType = 'D' THEN 'Database'
            ELSE NULL
       END DatabaseType
     , DatabaseAvailablePerm
     , SUM(TableSize) AS DatabaseSize
     , AVG(TableSize) AS AvgTableSize
     , AVG(TableRows) AS AvgTableRows
     , SUM(CASE WHEN TableMissingStats = 'Y' THEN 1 ELSE 0 END) TablesMissingStats
, SUM(CASE WHEN TableMissingStats = 'N' THEN 1 ELSE 0 END) TablesWithStats
     , TablesWithStats/(NULLIFZERO(TableCount) * 1.000) AS PercentTableWithStats
     , COUNT(TableName) AS ObjectCount
     , SUM(CASE WHEN TableKind = 'T' THEN 1 ELSE 0 END) AS TableCount
     , SUM(CASE WHEN TableKind = 'V' THEN 1 ELSE 0 END) AS ViewCount
     , SUM(CASE WHEN TableKind = 'M' THEN 1 ELSE 0 END) AS MacroCount
     , SUM(CASE WHEN TableKind = 'P' THEN 1 ELSE 0 END) AS ProcedureCount
     , SUM(CASE WHEN TableKind = 'E' THEN 1 ELSE 0 END) AS ExternalCount
     , SUM(CASE WHEN TableKind = 'F' THEN 1 ELSE 0 END) AS FunctionCount
     , SUM(CASE WHEN TableKind = 'I' THEN 1 ELSE 0 END) AS JoinIndexCount
     , SUM(CASE WHEN TableKind = 'U' THEN 1 ELSE 0 END) AS UserDefinedTypeCount
     , SUM(CASE WHEN TableKind = 'S' THEN 1 ELSE 0 END) AS OLAPFunctionCount
     , SUM(CASE WHEN TableKind = 'R' THEN 1 ELSE 0 END) AS TableFunctionCount
     , SUM(CASE WHEN TableKind = 'G' THEN 1 ELSE 0 END) AS TriggerCount
     , SUM(CASE WHEN TableKind = 'D' THEN 1 ELSE 0 END) AS JARCount
     , SUM(CASE WHEN TableKind = 'Q' THEN 1 ELSE 0 END) AS QueueTableCount
     , SUM(CASE WHEN TableKind = 'N' THEN 1 ELSE 0 END) AS HashIndexCount
     , SUM(CASE WHEN TableKind = 'J' THEN 1 ELSE 0 END) AS JournalCount
     , SUM(CASE WHEN TableKind = 'H' THEN 1 ELSE 0 END) AS InstanceOrMethodCount
     , SUM(CASE WHEN TableKind = 'B' THEN 1 ELSE 0 END) AS CombinedAggOLAPCount
     , SUM(CASE WHEN TableKind = 'A' THEN 1 ELSE 0 END) AS AggregateFunctionCount
     , SUM(CASE WHEN TableKind = 'X' THEN 1 ELSE 0 END) AS AuthorizationCount
FROM SysDBA.Object_Catalog C
   , DBC.Dbase D
   , (SELECT DatabaseName
           , SUM(MaxPerm) AS DatabaseAvailablePerm
      FROM DBC.DiskSpace
      GROUP BY 1
     ) S
WHERE C.DatabaseName = D.DatabaseName
  AND S.DatabaseName = D.DatabaseName
GROUP BY 1,2,3
;

Check the Space With Skew

LOCKING ROW FOR ACCESS
SEL TRIM(DatabaseName) AS DatabaseName,
                  SUM(MaxPerm)/1048576/1024 AS MaxPermGB, SUM(CurrentPerm)/1048576/1024 AS CurPermGB,
(MAX(currentperm)*(HASHAMP()+1))/1024/1024/1024 "Skew",
                         SUM(MAXPERM)/1048576/1024 - (MAX(currentperm)*(HASHAMP()+1))/1024/1024/1024 AS FreePermGB
                  FROM DBC.DiskSpace
                  WHERE DatabaseName <> 'ALL'
                  GROUP BY 1                
                  ORDER BY 4 DESC;



Access Rights abbreviations displayed in the Data Dictionary.

The script below maintains the full descriptions for the access rights and can be joined to DBC.ALLRIGHTS/ DBC.ALLROLERIGHTS.


/************************Set up Volatile Table**************************/

DROP TABLE AccessRightsAbbv;

CREATE VOLATILE SET TABLE
AccessRightsAbbv(
Description VARCHAR(50),
AccessRight CHAR(2))
PRIMARY INDEX(AccessRight)
ON COMMIT PRESERVE ROWS;

ins AccessRightsAbbv('CHECKPOINT','CP');
ins AccessRightsAbbv('CREATE AUTHORIZATION','CA');
ins AccessRightsAbbv('CREATE MACRO','CM');
ins AccessRightsAbbv('CREATE PROCEDURE','PC');
ins AccessRightsAbbv('CREATE TABLE','CT');
 ins AccessRightsAbbv('CREATE TRIGGER','CG');
 ins AccessRightsAbbv('CREATE VIEW','CV');
 ins AccessRightsAbbv('DELETE','D');
 ins AccessRightsAbbv('DROP AUTHORIZATION','DA');
 ins AccessRightsAbbv('DROP FUNCTION ','DF');
 ins AccessRightsAbbv('DROP MACRO','DM');
 ins AccessRightsAbbv('DROP PROCEDURE','PD');
 ins AccessRightsAbbv('DROP TRIGGER','DG');
 ins AccessRightsAbbv('DROP VIEW','DV');
 ins AccessRightsAbbv('DUMP','DP');
 ins AccessRightsAbbv('INSERT','I');
 ins AccessRightsAbbv('RESTORE','RS');
 ins AccessRightsAbbv('SELECT','R');
 ins AccessRightsAbbv('UPDATE','U');
 ins AccessRightsAbbv('CREATE DATABASE','CD');
 ins AccessRightsAbbv('CREATE USER','CU');
 ins AccessRightsAbbv('DROP DATABASE','DD');
 ins AccessRightsAbbv('DROP USER','DU');
 ins AccessRightsAbbv('ALTER EXTERNAL PROCEDURE','AE');
 ins AccessRightsAbbv('ALTER FUNCTION','AF');
 ins AccessRightsAbbv('ALTER PROCEDURE','AP');
 ins AccessRightsAbbv('CREATE EXTERNAL PROCEDURE','CE');
 ins AccessRightsAbbv('CREATE FUNCTION','CF');
 ins AccessRightsAbbv('EXECUTE FUNCTION','EF');
 ins AccessRightsAbbv('EXECUTE PROCEDURE','PE');
 ins AccessRightsAbbv('EXECUTE','E');
 ins AccessRightsAbbv('DROP TABLE','DT');
 ins AccessRightsAbbv('INDEX','IX');
 ins AccessRightsAbbv('REFERENCES','RF');
 ins AccessRightsAbbv('ABORT SESSION','AS');
 ins AccessRightsAbbv('MONRESOURCE','MR');
 ins AccessRightsAbbv('MONSESSION','MS');
 ins AccessRightsAbbv('SETRESRATE','SR');
 ins AccessRightsAbbv('SETSESSRATE','SS');
 ins AccessRightsAbbv('CREATE PROFILE','CO');
 ins AccessRightsAbbv('CREATE ROLE','CR');
 ins AccessRightsAbbv('DROP PROFILE','DO');
 ins AccessRightsAbbv('DROP ROLE','DR');
 ins AccessRightsAbbv('REPLCONTROL','RO');
ins AccessRightsAbbv('SHOW','SH');
ins AccessRightsAbbv('CREATE GLOP','GC');
ins AccessRightsAbbv('DROP GLOP','GD');
ins AccessRightsAbbv('GLOP MEMBER','GM');
ins AccessRightsAbbv('CREATE OWNER PROCEDURE','OP');

 / ***************SQL for AccessRights held by a user***********************/


SELECT UserName, DatabaseName, TableName,ColumnName,
 CASE
WHEN Abbv.AccessRight IS NOT NULL THEN Abbv.Description
ELSE ALRTS.AccessRight
END AS AccessRight,
 GrantAuthority, GrantorName, AllnessFlag, CreatorName, CreateTimeStamp
 FROM DBC.ALLRIGHTS ALRTS LEFT OUTER JOIN AccessRightsAbbv AbbvON ALRTS.AccessRight = Abbv.AccessRight
 WHERE UserName='Josephm1'
AND DatabaseName='E_COMPANYDB'
Order By 2,3,4,5;

 /*************SQL for AccessRights held by a ROLE************************/

SELECT RoleName, DatabaseName, TableName,ColumnName,
CASE WHEN Abbv.AccessRight IS NOT NULL THEN Abbv.Description
ELSE ALRTS.AccessRight
END AS AccessRight, GrantorName, CreateTimeStampFROM DBC.ALLROLERIGHTS ALRTS LEFT OUTER JOIN AccessRightsAbbv AbbvON ALRTS.AccessRight = Abbv.AccessRight WHERE RoleName='E_COMPANYDB_DEVELOPER'
AND DatabaseName='E_COMPANYDB'
Order By 2,3,4,5;