Examples of SQL statements that retrieve data dictionary tables are shown as follows: For details about the SQL statements, see the HiRDB Version 8 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 8 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