Appendix F.1 Examples of SQL statements for retrieval

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.