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.

Example 1

Retrieve the server names for the RDAREAs that exist in the HiRDB system, the HiRDB filenames, and the names of the RDAREAs to which the HiRDB files belong:

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

Example 2

From the column definition information for tables owned by a user, retrieve the names of the tables that contain the columns, the column names, the data types, and the column data lengths:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH
  FROM MASTER.SQL_COLUMNS
  WHERE TABLE_SCHEMA=USER*
  ORDER BY TABLE_NAME
  WITHOUT LOCK NOWAIT

Example 3

From the index definition information for tables owned by a user, retrieve the names of the tables that contain the index, the index names, and the percentages of unused space per page:

SELECT TABLE_NAME, INDEX_NAME, FREE_AREA
  FROM MASTER.SQL_INDEXES
  WHERE TABLE_SCHEMA=USER*
  ORDER BY TABLE_NAME
  WITHOUT LOCK NOWAIT

Example 4

Retrieve the tables that a user can access and the types of access privileges to those tables (SELECT, INSERT, DELETE, and UPDATE privileges):

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

Example 5

Retrieve the number of RDAREAs that become targets for group specification by a command (RDAREAs beginning with RD1):

SELECT COUNT(*) FROM MASTER.SQL_RDAREAS
  WHERE RDAREA_TYPE='U' AND
  RDAREA_NAME LIKE 'RD1%'
  WITHOUT LOCK NOWAIT

Example 6

Retrieve the names of RDAREAs that become targets for group specification by a command (RDAREAs beginning with RD1):

SELECT RDAREA_NAME FROM MASTER.SQL_RDAREAS
  WHERE RDAREA_TYPE='U' AND
  RDAREA_NAME LIKE 'RD1%' ORDER BY RDAREA_NAME
  WITHOUT LOCK NOWAIT

Example 7

Retrieve the name of the RDAREA that stores a non-partitioning table owned by a user (table named T1):

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

* USER refers to a variable that stores a value indicating the executing user's authorization identifier. For details about authorization identifiers, see the HiRDB Version 8 SQL Reference manual.

Example 8

Retrieve the name of the RDAREA that stores objects for a stored procedure or stored function, to be used during execution to re-initialize a data dictionary LOB RDAREA.

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

Note

When a data dictionary LOB RDAREA is reinitialized, all its stored SQL objects must be re-created.

Example 9

Retrieve the name of the stored procedure or stored function that has an invalid SQL object or an invalid index:

SELECT ROUTINE_SCHEMA,ROUTINE_NAME
    FROM MASTER.SQL_ROUTINES
    WHERE ROUTINE_VALID='N'
      OR INDEX_VALID='N'
    WITHOUT LOCK NOWAIT

Example 10

Retrieve the data types of the arguments that are actually used when embedded variables are used in arguments of the user-defined function FUNC1:

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

Example 11

To reorganize all the tables owned by user USERA, retrieve the RDAREAs containing any of those tables (the RDAREAs that need to be placed in shutdown status):

Non-partitioned table:

SELECT DISTINCT(RDAREA_NAME) FROM MASTER.SQL_TABLES
     WHERE TABLE_SCHEMA=USERA AND RDAREA_NAME IS NOT NULL
     WITHOUT LOCK NOWAIT

Partitioned table:

SELECT DISTINCT(RDAREA_NAME) FROM MASTER.SQL_DIV_TABLE
     WHERE TABLE_SCHEMA=USERA
     WITHOUT LOCK NOWAIT

Eliminate any duplicated RDAREA names from the result, then place all the resulting RDAREAs in shutdown status.