Nonstop Database, HiRDB Version 9 UAP Development Guide
Examples of SQL statements that retrieve data dictionary tables are shown as follows: For details about the SQL statements, see the HiRDB Version 9 SQL Reference manual.
The types of information that a particular user can retrieve depend on the setting of the data dictionary referencing authorization. For details about how to set data dictionary referencing authorizations, see the HiRDB Version 9 System Operation Guide.
After a dictionary table is retrieved, immediately issue a COMMIT statement or specify WITHOUT LOCK NOWAIT as shown in the retrieval example.
SELECT X_SERVER_NAME, PHYSICAL_FILE_NAME, X.RDAREA_NAME FROM MASTER.SQL_PHYSICAL_FILES X, MASTER.SQL_RDAREAS Y WHERE X.RDAREA_NAME=Y.RDAREA_NAME ORDER BY SERVER_NAME WITHOUT LOCK NOWAIT
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH FROM MASTER.SQL_COLUMNS WHERE TABLE_SCHEMA=USER* ORDER BY TABLE_NAME WITHOUT LOCK NOWAIT
SELECT TABLE_NAME, INDEX_NAME, FREE_AREA FROM MASTER.SQL_INDEXES WHERE TABLE_SCHEMA=USER* ORDER BY TABLE_NAME WITHOUT LOCK NOWAIT
SELECT TABLE_NAME, SELECT_PRIVILEGE, INSERT_PRIVILEGE, DELETE_PRIVILEGE, UPDATE_PRIVILEGE FROM MASTER.SQL_TABLE_PRIVILEGES WHERE GRANTEE=USER* OR GRANTEE='PUBLIC' WITHOUT LOCK NOWAIT
SELECT COUNT(*) FROM MASTER.SQL_RDAREAS WHERE RDAREA_TYPE='U' AND RDAREA_NAME LIKE 'RD1%' WITHOUT LOCK NOWAIT
SELECT RDAREA_NAME FROM MASTER.SQL_RDAREAS WHERE RDAREA_TYPE='U' AND RDAREA_NAME LIKE 'RD1%' ORDER BY RDAREA_NAME WITHOUT LOCK NOWAIT
SELECT X.RDAREA_NAME FROM MASTER.SQL_RDAREAS X, MASTER.SQL_TABLES Y WHERE Y.TABLE_SCHEMA=USER* AND Y.TABLE_NAME='T1' AND X.RDAREA_NAME=Y.RDAREA_NAME
SELECT RDAREA_NAME FROM MASTER.SQL_DIV_COLUMN WHERE TABLE_SCHEMA='HiRDB' AND TABLE_NAME='SQL_ROUTINES' AND COLUMN_NAME='ROUTINE_OBJECT' WITHOUT LOCK NOWAIT
SELECT ROUTINE_SCHEMA,ROUTINE_NAME FROM MASTER.SQL_ROUTINES WHERE ROUTINE_VALID='N' OR INDEX_VALID='N' WITHOUT LOCK NOWAIT
SELECT PARAMETER_NAME,DATA_TYPE,UDT_OWNER,UDT_NAME, PARAMETER_NO FROM MASTER.SQL_ROUTINE_PARAMS WHERE ROUTINE_SCHEMA=USER AND ROUTINE_NAME='FUNC1' ORDER BY PARAMETER_NO WITHOUT LOCK NOWAIT
SELECT DISTINCT(RDAREA_NAME) FROM MASTER.SQL_TABLES WHERE TABLE_SCHEMA=USERA AND RDAREA_NAME IS NOT NULL WITHOUT LOCK NOWAIT
SELECT DISTINCT(RDAREA_NAME) FROM MASTER.SQL_DIV_TABLE WHERE TABLE_SCHEMA=USERA WITHOUT LOCK NOWAIT
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.