Appendix D.1 Definition SQLs
(1) ALLOCATE MEMORY TABLE
For ALLOCATE MEMORY TABLE, determine the value if you will be using a memory-resident database.
(a) For HiRDB/Single Server
9 + number of table columns + number of indexes defined for the table
2 + number of tables subject to memory database processing + number of indexes having the same name as the indexes defined for the table + number of indexes stored in the same database area as for the specified index database area
- Add the following if there are routines that become invalid:
- + number of routines in which the object becomes invalid
3 + 2
- Add the following if there are triggers that become invalid:
- + number of triggers for which the object becomes invalid
2 + 2 + number of trigger event columns for which the object becomes invalid + 1 + number of parameters used in the triggers for which the object becomes invalid + 1
(b) For HiRDB/Parallel Server (front-end server)
1
- Add the following if there are routines that become invalid:
- + number of routines in which the object becomes invalid
(c) For HiRDB/Parallel Server (dictionary server)
8 + number of table columns + number of indexes defined for the table
2 + number of tables subject to memory database processing that is stored in the same XDS + number of indexes having the same name as the indexes defined for the table + number of indexes stored in the same database area as for the specified index database area
- Add the following if there are routines that become invalid:
- + number of routines in which the object becomes invalid
2 + 2
- Add the following if there are triggers that become invalid:
- + number of triggers for which the object becomes invalid
2 + 2 + number of trigger event columns for which the object becomes invalid + 1 + number of parameters used in the triggers for which the object becomes invalid + 1
(2) ALTER INDEX
(3) ALTER PROCEDURE
(a) For HiRDB/Single Server
5 + number of tables accessed in SQL statement preprocessing + number of view tables accessed in SQL statement preprocessing + number of base tables that serve as the base for view tables accessed in SQL statement preprocessing
- Add the following if the executer is not the owner:
- + 2
- Add the following if a procedure name is specified:
- + 2
- Add the following if AUTHORIZATION is specified:
- + number of invalid routines owned by a target person + number of invalid PUBLIC routines defined by a target person + 2
- Also add the following if ALL is specified:
- + total number of routines owned by a target person + total number of PUBLIC routines defined by a target person + 1
- Also add the following if INDEX USING is specified:
- + number of routines that use the specified table as a resource
2 + 3
- Add the following if AUTHORIZATION is not specified:
- + total number of invalid routines in the system
- Also add the following if ALL is specified:
- + total number of routines in the system
- Also add the following if INDEX USING is specified:
- + number of routines that use the specified table as a resource
2 + 3
- Add the following if there are resources used in routines:
- + number of routines used
2 + 1
- Add the following if there are tables in the resources used:
- + number of tables used + 1
- Add the following if there are view tables in the resources used:
- + number of view tables used + 1 + number of utilized resources used in view tables used + 1
- Add the following if there are indexes in the resources to be used:
- + number of indexes to be used + 1
- Add the following if there are routines in the resources to be used:
- + number of routines used + 1
- Add the following if there are user-defined types in the resources to be used:
- + user-defined types to be used + 1
- Add the following if parameters are specified:
- + number of parameters + 1
(b) For HiRDB/Parallel Server (front-end server)
1 + number of tables accessed in SQL statement preprocessing + number of view tables accessed in SQL statement preprocessing + number of base tables that serve as the base for view tables accessed in SQL statement preprocessing
(c) For HiRDB/Parallel Server (dictionary server)
5
- Add the following if the executer is not the owner:
- + 2
- Add the following if a procedure name is specified:
- + 2
- Add the following if AUTHORIZATION is specified:
- + number of invalid routines owned by a target person + number of invalid PUBLIC routines defined by a target person
- Also add the following if ALL is specified:
- + total number of routines owned by a target person + total number of PUBLIC routines defined by a target person
- Also add the following if INDEX USING is specified:
- + number of routines that use the specified table as a resource
2 + 3
- Add the following if AUTHORIZATION is not specified:
- + total number of invalid routines in system
- Also add the following if ALL is specified:
- + total number of routines in system
- Also add the following if INDEX USING is specified:
- + number of routines that use the specified table as a resource
2 + 3
- Add the following if there are resources used in routines:
- + number of routines used
2 + 1
- Add the following if there are tables in the resources used:
- + number of tables used + 1
- Add the following if there are view tables in the resources used:
- + number of view tables used + 1 + number of utilized resources used in view tables used + 1
- Add the following if there are indexes in the resources to be used:
- + number of indexes to be used + 1
- Add the following if there are routines in the resources to be used:
- + number of routines used + 1
- Add the following if there are user-defined types in the resources to be used:
- + user-defined types to be used + 1
- Add the following if parameters are specified:
- + number of parameters + 1
(d) For HiRDB/Parallel Server (back-end server)
1
(4) ALTER ROUTINE
(5) ALTER TABLE
(6) ALTER TRIGGER
(7) COMMENT
(a) For HiRDB/Single Server
3
- Add the following if the comment is a column:
- + 2
(b) For HiRDB/Parallel Server (dictionary server)
3
- Add the following if the comment is a column:
- + 2
(8) CREATE AUDIT
(a) For HiRDB/Single Server
7
- Add the following if objects are specified:
- + number of definitions of the objects in question
- Add the following if objects are not specified:
- + number of definitions with no object specified
(b) For HiRDB/Parallel Server (dictionary server)
7
- Add the following if objects are specified:
- + number of definitions of the objects in question
- Add the following if objects are not specified:
- + number of definitions with no object specified
(9) CREATE CONNECTION SECURITY
(a) For HiRDB/Single Server
7 + number-of-users-registered-in-dictionary-table-SQL_USERS
(b) For HiRDB/Parallel Server (front-end server)
1
(c) For HiRDB/Parallel Server (dictionary server)
6 + number-of-users-registered-in-dictionary-table-SQL_USERS
(10) CREATE [PUBLIC] FUNCTION
(11) CREATE INDEX (not a plug-in index)
(12) CREATE INDEX (plug-in index)
(13) CREATE [PUBLIC] PROCEDURE
(14) CREATE SCHEMA
(a) For HiRDB/Single Server
5
(b) For HiRDB/Parallel Server (dictionary server)
5
(15) CREATE SEQUENCE
(a) For HiRDB/Single Server
- 9 + 2
(b) For HiRDB/Parallel Server (dictionary server)
- 9
(c) For HiRDB/Parallel Server (back-end server)
- 2
(16) CREATE TABLE
(17) CREATE TRIGGER
(18) CREATE TYPE
(19) CREATE [PUBLIC] VIEW
(20) DEALLOCATE MEMORY TABLE
For DEALLOCATE MEMORY TABLE, determine the value if you will be using a memory-resident database.
(a) For HiRDB/Single Server
4 + number of indexes defined for the table
(b) For HiRDB/Parallel Server (dictionary server)
4 + number of indexes defined for the table
(21) DROP AUDIT
(a) For HiRDB/Single Server
7
- Add the following if objects are specified:
- + number of definitions of the objects in question
- Add the following if objects are not specified:
- + number of definitions with no object specified
(b) For HiRDB/Parallel Server (dictionary server)
7
- Add the following if objects are specified:
- + number of definitions of the objects in question
- Add the following if objects are not specified:
- + number of definitions with no object specified
(22) DROP CONNECTION SECURITY
(a) For HiRDB/Single Server
7 + number-of-users-registered-in-dictionary-table-SQL_USERS
(b) For HiRDB/Parallel Server (front-end server)
1
(c) For HiRDB/Parallel Server (dictionary server)
6 + number-of-users-registered-in-dictionary-table-SQL_USERS
(23) DROP DATA TYPE
(24) DROP [PUBLIC] FUNCTION
(25) DROP INDEX (not a plug-in index)
(26) DROP INDEX (plug-in index)
(27) DROP [PUBLIC] PROCEDURE
(28) DROP SCHEMA
(a) For HiRDB/Single Server
11 + number of tables inside the schema + number of view tables inside the schema + number of routines inside the schema + number of view tables in other schemas that use tables or view tables inside the schema as base tables + number of indexes in other schemas that reference the tables, view tables, routines, or data types to be deleted
- Add the number of tables inside the schema that are to be deleted (when they are not matrix-partitioned tables).
- +
{number of RDAREAs storing the tables
3 + number of specified partitioning conditions
9 + number of columns
4 + (number of LOB columns
4)
number of RDAREAs storing the tables + 1 + 8} + 2 - +
(number of RDAREAs for table
10 - + 10
- + number of table data segments being used)
- Add the number of tables inside the schema that are to be deleted (when they are matrix-partitioned tables).
- +
{number of RDAREAs storing the tables
3 + number of specified RDAREAs storing the tables (including overlapped ones)
12 + number of columns
4 + (number of LOB columns
4)
number of RDAREAs storing the tables + 1 + 8 + number of privilege definitions for the tables to be deleted + number of partitioning keys
2 + (number of specified storage conditions + 2)
2 + 2} + 3 - +
(number of RDAREAs for table
10 - + 10
- + number of table data segments being used)
- Add the number of indexes inside the schema that are to be deleted.
- + 3 +
(number of RDAREAs for indexes
7 + number of member columns
3 + number of index member substructure paths
3#1) +
(number of RDAREAs for the indexes
8 + 10 + number of index segments being used) + 8
- Add the following if optimization information is collected:
- + number of tables for which optimization information is collected
2 + number of columns for which optimization information is collected
3 + number of indexes for which optimization information is collected
3
- Add the number of view tables that are deleted along with the tables being deleted:
- + 2 +
(12 + number of base tables for view tables
4 + number of columns
4)
- Add the number of routines that use tables, view tables, routines, or data types inside the schema:
- + 3 +
(5 + number of resources in procedures
5 + number of defined parameters
3)
- Add the following if there are procedures that become invalid:
- + number of invalid procedures +
(2 + number of resources
5) + number of data types defined inside the schema#2
- Add the following if there are trigger action procedures that reference tables to be deleted:
- + number of trigger action procedures that reference tables to be deleted
5
- Add the following if there are tables for which triggers are defined:
- + number of triggers defined inside the schema
8 + total number of columns specified in the UPDATE trigger definition inside the schema
3 + number of triggers that have trigger action conditions inside the schema
3 + number of resource types referenced in the trigger action conditions inside the schema
5
- Add the following if a referential constraint is defined:
- + number of referenced tables + number of foreign keys
10
- Add the following if a check constraint is defined:
- + number of check constraints
9
- Add the following if using the facility for predicting reorganization time:
- +
{(number of RDAREAs storing the tables + (number of LOB columns + number of LOB attributes)
number of table-storage RDAREAs)
62} +
{(number of RDAREAs for indexes + (number of LOB columns + number of LOB attributes)
number of table-storage RDAREAs)
62} + 1
- Add the following if there are sequence generators inside the schema:
- + 2 + number of sequence generators inside the schema
3 + number of sequence generators inside the schema
2
(b) For HiRDB/Parallel Server (front-end server)
3 + number of tables inside the schema + number of view tables inside the schema + number of routines inside the schema + number of sequence generators inside the schema + number of view tables in other schemas that use tables or view tables inside the schema as base tables + number of routines in other schemas that reference the tables, view tables, routines, or data types to be deleted
(c) For HiRDB/Parallel Server (dictionary server)
11 + number of indexes in other schemas
- Add the number of tables inside the schema that are to be deleted (when they are not matrix-partitioned tables).
- +
{number of RDAREAs storing the tables
3 + number of specified partitioning conditions
9 + number of columns
4 + (number of LOB columns
4)
number of RDAREAs storing the tables + number of privilege definitions for the tables to be deleted
3 + 6} + 11
- Add the number of tables inside the schema that are to be deleted (when they are matrix-partitioned tables).
- +
{number of RDAREAs storing the tables
3 + number of specified RDAREAs storing the tables (including overlapped ones)
12 + number of columns
4 + (number of LOB columns
4)
number of RDAREAs storing the tables + number of privilege definitions for the tables to be deleted
3 + 1 + 6 + number of partitioning keys
2 + (number of specified storage conditions + 2)
2 + 2} + 3
- Add the number of indexes inside the schema that are to be deleted.
- + 4 +
(number of RDAREAs for the indexes
7 + number of member columns
7 + number of index member substructure paths
3#1) + number of indexes
5
- Add the following if optimization information is collected:
- + number of tables for which optimization information is collected
2 + number of columns for which table optimization information is collected
3 + number of indexes for which optimization information is collected
3
- Add the number of view tables that are deleted along with the tables being deleted:
- + 2 +
(18 + number of base tables for the view tables
4 + number of columns
4 + number of privilege definitions for the view tables
3)
- Add the number of routines that use tables, view tables, routines, or data types inside the schema:
- + 3 +
(5 + number of resources inside the procedure
10 + number of defined parameters
3)
- Add the following if there are procedures that become invalid:
- + number of invalid procedures +
(5 + number of resources
5) + number of for data types defined inside the schema#2
- Add the following if there are trigger action procedures that reference tables to be deleted:
- + number of trigger action procedures that reference tables to be deleted
5
- Add the following if there are tables for which triggers are defined:
- + number of triggers defined inside the schema
8 + total number of columns specified in the UPDATE trigger definition inside the schema
3 + number of triggers that have trigger action conditions inside the schema
3 + number of resource types referenced in the trigger action conditions inside the schema
5
- Add the following if a referential constraint is defined:
- + number of referenced tables + number of foreign keys
10
- Add the following if a check constraint is defined:
- + number of check constraints
9
- Add the following if using the facility for predicting reorganization time:
- +
((number of RDAREAs storing the tables + (number of LOB columns + number of LOB attributes)
number of table-storage RDAREAs)
62) +
((number of RDAREAs for indexes + (number of LOB columns + number of LOB attributes)
number of table-storage RDAREAs)
62) + 1
- Add the following if there are sequence generators inside the schema:
- + 2 + number of sequence generators inside the schema
2
(d) For HiRDB/Parallel Server (back-end server)
8 + number of routines whose objects or index objects become invalid
- Add the following for non-temporary tables or non-temporary table indexes:
- +
(number of RDAREAs storing tables
10 + 10 + number of table data segments being used) - +
(number of RDAREAs for indexes
8 + 10 + number of index segments being used)
- Add the following if there is a sequence generator inside the schema:
- + number of sequence generators inside the schema
2
(e) For HiRDB/Parallel Server (reference-only back-end server)
number of tables + number of RDAREAs for indexes
#1: Add iteratively if the index is a substructure index.
#2: See the number of lock requests for user-defined deletion.
(29) DROP SEQUENCE
(a) For HiRDB/Single Server
9 + 2
- Add the following if there are routines that become invalid:
- + number of routines whose objects become invalid
3 + 2
- Add the following if triggers are defined:
- + number of trigger definitions
2 + 2 + number of defined trigger event columns + 1 + number of parameters used by defined triggers + 1
(b) For HiRDB/Parallel Server (front-end server)
1
- Add the following if there are routines that become invalid:
- + number of routines whose objects become invalid
(c) For HiRDB/Parallel Server (dictionary server)
8
- Add the following if there are routines that become invalid:
- + number of routines whose objects become invalid
2 + 2
- Add the following if triggers are defined:
- + number of trigger definitions
2 + 2 + number of defined trigger event columns + 1 + number of parameters used by defined triggers + 1
(d) For HiRDB/Parallel Server (back-end server)
2
(30) DROP TABLE
(31) DROP TRIGGER
(32) DROP [PUBLIC] VIEW
(33) GRANT AUDIT
(a) For HiRDB/Single Server
3
- Add the following if using the password character restriction facility:
- + 2
(b) For HiRDB/Parallel Server (dictionary server)
- 3
- Add the following if using the password character restriction facility:
- + 2
(34) GRANT CONNECT
(a) For HiRDB/Single Server
3 + number of specified users
- Add the following if using the password character restriction facility:
- + 2
(b) For HiRDB/Parallel Server (dictionary server)
3 + number of specified users
- Add the following if using the password character restriction facility:
- + 2
(35) GRANT DBA
(a) For HiRDB/Single Server
3 + number of specified users
- Add the following if using the password character restriction facility:
- + 2
(b) For HiRDB/Parallel Server (dictionary server)
3 + number of specified users
- Add the following if using the password character restriction facility:
- + 2
(36) GRANT RDAREA
(a) For HiRDB/Single Server
5 + number of specified users
- Add the following if PUBLIC is specified:
- + number of specified RDAREAs
- Add the following if there is a user specification:
- + number of specified RDAREAs
number of specified users
(b) For HiRDB/Parallel Server (dictionary server)
5 + number of specified users
- Add the following if PUBLIC is specified:
- + number of specified RDAREAs
- Add the following if there is a user specification:
- + number of specified RDAREAs
number of specified users
(37) GRANT SCHEMA
(a) For HiRDB/Single Server
3 + number of specified users
(b) For HiRDB/Parallel Server (dictionary server)
3 + number of specified users
(38) GRANT access privileges
(a) For HiRDB/Single Server
4 + number of specified users
(b) For HiRDB/Parallel Server (dictionary server)
4 + number of specified users
(39) REVOKE CONNECT
(a) For HiRDB/Single Server
3 + number of specified users
(b) For HiRDB/Parallel Server (dictionary server)
3 + number of specified users
(40) REVOKE DBA
(a) For HiRDB/Single Server
3 + number of specified users
(b) For HiRDB/Parallel Server (dictionary server)
3 + number of specified users
(41) REVOKE RDAREA
(a) For HiRDB/Single Server
10
- Add the following if PUBLIC is specified:
- + number of specified RDAREAs
- Add the following if there is a user specification:
- + number of specified RDAREAs
number of specified users
(b) For HiRDB/Parallel Server (dictionary server)
10
- Add the following if PUBLIC is specified:
- + number of specified RDAREAs
- Add the following if there is a user specification:
- + number of specified RDAREAs
number of specified users
(42) REVOKE SCHEMA
(a) For HiRDB/Single Server
5 + number of specified users
(b) For HiRDB/Parallel Server (dictionary server)
5 + number of specified users
(43) REVOKE access privileges
(a) For HiRDB/Single Server
7 + number of specified users
- Add the following if there are routines that become invalid:
- + number of routines that become invalid
2 + 1
- Add the following if there are view tables defined based on tables:
- + number of single server locked resources of DROP [PUBLIC ]VIEW
(b) For HiRDB/Parallel Server (front-end server)
1
- Add the following if there are routines that become invalid:
- + number of routines that become invalid
- Add the following if there are view tables defined based on tables:
- + number of front-end server locked resources of DROP [PUBLIC ]VIEW
(c) For HiRDB/Parallel Server (dictionary server)
6 + number of specified users
- Add the following if there are routines that become invalid:
- + number of routines that become invalid
2 + 1
- Add the following if there are view tables defined based on tables:
- + number of dictionary server locked resources of DROP [PUBLIC ]VIEW
(d) For HiRDB/Parallel Server (back-end server)
0
- Add the following if there are routines that become invalid:
- + number of routines that become invalid
- Add the following if there are view tables defined based on tables:
- + number of back-end server locked resources of DROP [PUBLIC ]VIEW