ALTER ROUTINE (Recreate SQL objects for functions, procedures, and triggers)
Function
ALTER ROUTINE recreates the SQL objects for functions, procedures, and triggers, or modifies the compile option for a Java function or procedure.
Privileges
- Owner of the functions, procedures, and triggers
A user can recreate SQL objects for functions, procedures, and triggers owned by that user.
- Only the user's own authorization identifier can be specified in the AUTHORIZATION clause.
- If the AUTHORIZAT ION clause is omitted, an error results.
- Users with the DBA privilege
These users can recreate the SQL objects for functions, procedures, and triggers owned by them and by other users.
- Both the user's own authorization identifier and other users' authorization identifiers can be specified in the AUTHORIZATION clause.
- By omitting the AUTHORIZATION clause, SQL objects for all functions and procedures in the system are recreated.
Format
ALTER ROUTINE [[AUTHORIZATION authorization-identifier] [ALL]]
[SQL-compile-option[SQL-compile-option]...]
SQL-compile-option ::={ISOLATION data-guarantee-level [FOR UPDATE EXCLUSIVE]
|OPTIMIZE LEVEL SQL-optimization-option
[,SQL-optimization-option]...
|ADD OPTIMIZE LEVEL SQL-extension-optimizing-option
[,SQL-extension-optimizing-option]...
|SUBSTR LENGTH maximum-character-length }
Operands
- [AUTHORIZATION authorization-identifier] [ALL]
Specifies the function, procedure, and trigger to be recreated in terms of the owner's authorization identifier and the status of the function, procedure, and trigger.
- [AUTHORIZATION authorization-identifier]
- Specifies the authorization identifier of the owner of the function, procedure, and trigger to recreate the SQL objects of all the functions, procedures, and triggers owned by the user. The default for this operand is to recreate the SQL objects for all the functions, procedures, and triggers in the system. Whether an SQL object is actually recreated is determined by the particular combination of the operands with the ALL clause.
- authorization-identifier
- Specifies the authorization identifier of the owner of functions, procedures, and triggers that are to be recreated.
- [ALL]
- Specifies that the status of the SQL objects is to be taken into consideration in determining which SQL objects for functions, procedures, and triggers are to be recreated. When this operand is omitted, only functions, procedures, and triggers whose SQL object is disabled are recreated.
- ALL
- Specifies that the SQL objects for all functions, procedures, and triggers are to be recreated, regardless of whether the SQL objects are enabled or disabled.
- SQL-compile-option::= {ISOLATION data-guarantee-level [FOR UPDATE EXCLUSIVE]
|OPTIMIZE LEVEL SQL-optimization-option
[, SQL-optimization-option]...
|ADD OPTIMIZE LEVEL SQL-extension-optimizing-option
[, SQL-extension-optimizing-option]...
|SUBSTR LENGTH maximum-character-length}
ISOLATION, OPTIMIZE LEVEL, ADD OPTIMIZE LEVEL, and SUBSTR LENGTH can each be specified only once in SQL-compile-option.
- [ISOLATION data-guarantee-level [FOR UPDATE EXCLUSIVE]]
- Specifies an SQL data integrity guarantee level.
- data-guarantee-level
- A data integrity guarantee level specifies the point to which the integrity of the transaction data must be guaranteed. The following data integrity guarantee levels can be specified:
- 0
Do not guarantee data integrity. When 0 is specified for a set of data, the user can reference the data even while it is being updated by another user. If the table to be referenced is a shared table, and if another user is executing the LOCK statement, a lock release wait is required.
- 1
Guarantee the integrity of data until a retrieval process is completed. When level 1 is specified, data that has been retrieved cannot be updated until the retrieval process is completed (until HiRDB finishes viewing the current page or row).
- 2
Guarantees the integrity of data that has been retrieved until the transaction is completed. When level 2 is specified, data that has been retrieved cannot be updated by other users until the transaction has been completed.
- [FOR UPDATE EXCLUSIVE]
- Specify this option if WITH EXCLUSIVE LOCK is always assumed irrespective of the cursor in a procedure for which the FOR UPDATE clause is specified or assumed, or for the data guarantee level on a query that is specified in SQL-compile-option. If level 2 is specified in data-guarantee-level, WITH EXCLUSIVE LOCK is assumed for the cursor for which the FOR UPDATE clause is specified or assumed, or for the query, and, therefore, FOR UPDATE EXCLUSIVE need not be specified. If a data guarantee level is specified in SQL-compile-option, and FOR UPDATE EXCLUSIVE is omitted, FOR UPDATE EXCLUSIVE need not be specified.
- Relationship with client environment definition
- Any specification of PDISLLVL or PDFORUPDATEEXLOCK with respect to ALTER ROUTINE has no effect.
- Relationship with SQL statements
- If a lock option is specified in an SQL statement in a procedure, the lock option specified in the SQL statement takes precedence over any data guarantee level specified in SQL-compile-option or the lock option assumed because of FOR UPDATE EXCLUSIVE.
- When this operand is omitted, the data guarantee level specified for the most recent SQL object creation (execution of a CREATE PROCEDURE, ALTER PROCEDURE, CREATE TYPE, ALTER ROUTINE, CREATE TRIGGER, or ALTER TRIGGER statement) is assumed.
- For data guarantee levels, see the HiRDB Version 8 UAP Development Guide.
- OPTIMIZE LEVEL SQL-optimization-option[,SQL-optimization-option]...
- Specifies the optimization method for determining the most efficient access path, taking into account the database's status.
- SQL optimization options can be specified with identifiers (character strings) or numeric values. Hitachi recommends that identifiers be used.
- The default is the value that was used during the previous creation of an SQL object (CREATE PROCEDURE, ALTER PROCEDURE, CREATE TYPE, ALTER ROUTINE, CREATE TRIGGER, or ALTER TRIGGER).
- Specification with identifiers:
OPTIMIZE LEVEL "identifier"[, "identifier"] ...
- Specification examples
- Apply the Prioritized nest-loop-join and the Rapid grouping facility options:
OPTIMIZE LEVEL "PRIOR_NEST_JOIN","RAPID_GROUPING"
- Do not apply any optimization:
OPTIMIZE LEVEL "NONE"
- Rules
- At least one identifier must be specified.
- When multiple identifiers are specified, separate them with the comma (,).
- For the optimization option identifiers, see Table 3-4 SQL optimization option specification values below.
- If no optimization options are to be applied, specify "NONE" as the identifier. If "NONE" and some other identifier are both specified, the "NONE" specification is ignored.
- The identifiers are case-sensitive.
- If the same identifier is specified more than once, it is treated as if it was specified only once; however, where possible, precautions should be taken to avoid specifying a given identifier in duplicate.
- Specification with numeric values:
OPTIMIZE LEVEL unsigned-integer[, unsigned-integer] ...
- Specification examples
- Apply the 2. Making multiple SQL objects, 8. Suppressing use of AND multiple indexes, and 13. Forcing use of multiple indexes options:
Specify unsigned integers separated by commas:
OPTIMIZE LEVEL 4,10,16
- Specify a sum of unsigned integers:
OPTIMIZE LEVEL 30
- Add the new value 16 to the previously specified value 14 (4 + 10):
OPTIMIZE LEVEL 14,16
- Do not apply any optimization:
OPTIMIZE LEVEL 0
- Rules
- When HiRDB is upgraded from a version older than Version 06-00 to a Version 06-00 or later, the total value specification in the older version also remains valid. If the optimization option does not need to be modified, the specification value for this operand need not be changed when HiRDB is upgraded to a Version 06-00 or later.
- At least one integer must be specified.
- When multiple integers are specified, separate them with the comma (,).
- For the optimization option integers, see Table 3-4 SQL optimization option specification values below.
- If no optimization options are to be applied, specify 0 as the integer. If 0 and another integer are both specified, the 0 specification is ignored.
- If the same integer is specified more than once, it will be treated as a single instance of the integer. However, multiple specifications of the same integer should be avoided.
- When multiple optimization options are to be applied, you can specify the sum of the appropriate unsigned integers. However, the same optimization option value must not be added in more than once to avoid the possibility of the addition result being interpreted as a different set of optimization options.
- If multiple optimization methods are specified by adding values, which optimization methods are being specified may not be apparent. In this case, Hitachi recommends specifying values by delimiting them with commas. If multiple optimization methods are already specified by adding values, and a new optimization method must be specified, the additional value can be appended by delimiting it with a comma.
- Relationships to system definition
- The system-defined pd_optimize_level operand, if specified in ALTER ROUTINE, has no effect.
- When the pd_floatable_bes operand or the pd_non_floatable_bes operand is specified, specification of the Increasing the target floatable servers (back-end servers for fetching data) option or the Limiting the target floatable servers (back-end servers for fetching data) option, respectively, is invalid.
- When KEY is specified in the pd_indexlock_mode operand of the system definition (i.e., in the case of index key value lock), specification of the Suppressing creation of update-SQL work tables option is invalid.
- Relationship to client environment definition
- The specification of PDSQLOPTLVL has no applicability to ALTER ROUTINE.
- Relationship with SQL
- If SQL optimization is specified in an SQL statement, the SQL optimization specification takes precedence over SQL optimization options. For SQL optimization specifications, see 2.24 SQL optimization specification.
- SQL optimization option specification values
- Table 3-4 shows the SQL optimization option specification values. For details about the optimization option, see the manual HiRDB Version 8 UAP Development Guide.
Table 3-4 SQL optimization option specification values (ALTER ROUTINE)
No. | Optimization option | Specification values |
---|
Identifier | Unsigned integer |
---|
1 | Forced nest-loop-join | FORCE_NEXT_JOIN | 4 |
2 | Making multiple SQL objects | SELECT_APSL | 10 |
3 | Increasing the target floatable servers (back-end servers for fetching data)#1, #2 | FLTS_INC_DATA_BES | 16 |
4 | Prioritized nest-loop-join | PRIOR_NEST_JOIN | 32 |
5 | Increasing the number of floatable server candidates#2 | FLTS_MAX_NUMBER | 64 |
6 | Priority of OR multiple index use | PRIOR_OR_INDEXES | 128 |
7 | Group processing, ORDER BY processing, and DISTINCT set function processing at the local back-end server#2 | SORT_DATA_BES | 256 |
8 | Suppressing use of AND multiple indexes | DETER_AND-INDEXES | 512 |
9 | Rapid grouping facility | RAPID_GROUPING | 1024 |
10 | Limiting the target floatable servers (back-end servers for fetching data)#1, #2 | FLTS_ONLY_DATA_BES | 2048 |
11 | Separating data collecting servers#1, #2 | FLTS_SEPARATE_COLLECT_SVR | 2064 |
12 | Suppressing index use (forced table scan) | FORCE_TABLE_SCAN | 4096 |
13 | Forcing use of multiple indexes | FORCE_PLURAL_INDEXES | 32768 |
14 | Suppressing creation of update-SQL work tables | DETER_WORK_TABLE_FOR_UPDATE | 131072 |
15 | Deriving rapid retrieval conditions | "DERIVATIVE_COND" | 262144 |
16 | Applying key conditions including scalar operations | "APPLY_ENHANCED_KEY_COND" | 524288 |
17 | Facility for batch acquisition from functions provided by plug-ins | "PICKUP_MULTIPLE_ROWS_PLUGIN" | 1048576 |
- #1: If the 3. Increasing the target floatable servers (back-end servers for fetching data) option and the 10. Limiting the target floatable servers (back-end servers for fetching data) option are both specified, neither of these options will be applied; instead, the 11. Separating data collecting servers option will be applied.
- #2: This option is ignored if specified for a HiRDB/Single Server.
- ADD OPTIMIZE LEVEL SQL-extension-optimizing-option[, SQL-extension-optimizing-option]...
- Specifies optimizing options for determining the most efficient access path, taking into consideration the status of the database.
- SQL extension optimizing options can be specified with identifiers (character strings) or numeric values. Hitachi recommends that identifiers be used.
- The default is to use value that was used during the previous creation of an SQL object (CREATE PROCEDURE, ALTER PROCEDURE, CREATE TYPE, ALTER ROUTINE, CREATE TRIGGER, or ALTER TRIGGER).
- Specification with identifiers:
ADD OPTIMIZE LEVEL "identifier"[, "identifier"] ...
- Specification examples
- Apply the Application of optimizing mode 2 based on cost and Hash join, subquery hash execution options:
ADD OPTIMIZE LEVEL
"COST_BASE_2", "APPLY_HASH_JOIN"
- Do not apply any optimizing:
ADD OPTIMIZE LEVEL "NONE"
- Rules
- At least one identifier must be specified.
- When multiple identifiers are specified, separate them with the comma (,).
- For details of specifying optimization option identifiers, see Table 3-5 SQL extension optimizing option specification values below.
- If no extension optimizing options are to be applied, specify "NONE" as the identifier.
- The identifiers are case-sensitive.
- If the same identifier is specified more than once, it is treated as if it was specified only once; however, where possible, precautions should be taken to avoid specifying a given identifier in duplicate.
- Specification with numeric values:
ADD OPTIMIZE LEVEL unsigned-integer[, unsigned-integer] ...
- Specification examples
- Apply the Application of optimizing mode 2 based on cost and Hash join, subquery hash execution options:
ADD OPTIMIZE LEVEL 1,2
- Do not apply any optimizing:
ADD OPTIMIZE LEVEL 0
- Rules
- At least one integer must be specified.
- When multiple integers are specified, separate them with the comma (,).
- For details of specifying extension optimizing option integers, see Table 3-5 SQL extension optimizing option specification values below.
- If no extension optimizing options are to be applied, specify 0 as the integer.
- If the same integer is specified more than once, it will be treated as a single instance of the integer. However, multiple specifications of the same integer should be avoided.
- Relationship to system definition
- The system-defined pd_optimize_level operand, if specified in ALTER ROUTINE, has no effect.
- Relationship to client environment definition
- The specification of PDADDITIONALOPTLVL has no applicability to ALTER ROUTINE.
- Relationship with SQL
- If SQL optimization is specified in an SQL statement, the SQL optimization specification takes precedence over SQL optimization options. For SQL optimization specifications, see 2.24 SQL optimization specification.
- SQL extension optimizing option specification values
- Table 3-5 shows the SQL extension optimizing option specification values. For details about optimization methods, see the HiRDB Version 8 UAP Development Guide.
Table 3-5 SQL extension optimizing option specification values (ALTER ROUTINE)
No. | Optimizing option | Specification values |
---|
Identifier | Unsigned integer |
---|
1 | Application of optimizing mode 2 based on cost | "COST_BASE_2" | 1 |
2 | Hash join, subquery hash execution | "APPLY_HASH_JOIN" | 2 |
3 | Suppressing foreign servers for SQL statements including a join | "DETER_JOIN_SQL" | 67108864 |
4 | Forcing the execution of foreign servers for SQL statements including direct product | "FORCE_CROSS_JOIN_SQL" | 134217728 |
5 | Suppressing the derivation of rapid search conditions that can be executed on a foreign server and that are unconditionally generated | "DETER_FSVR_DERIVATIVE_COND" | 1073741824 |
- Note 1
- Items 2-5 take effect when Application of optimizing mode 2 based on cost is specified.
- Note 2
- Optimization items 3-5 take effect when a foreign table is retrieved; in other cases, they have no effect.
- [SUBSTR LENGTH maximum-character-length]
- Specifies the maximum number of bytes for representing a single character.
- The value specified for the maximum character length must be in the range from 3 to 6.
- This operand is valid only when utf-8 is specified for the character code type in the pdntenv command (pdsetup command in the case of the UNIX version), it affects the length of the result of the SUBSTR scalar function. For details about SUBSTR, see 2.16.1(21) SUBSTR.
- Rules
- When HiRDB is upgraded from a version earlier than version 08-00 to version 08-00 or later, 3 is assumed. If there is no need to change the maximum character length, you do not need to specify this operand when upgrading to HiRDB version 08-00 or later.
- Relationships to system definition
- When SUBSTR LENGTH is specified in ALTER ROUTINE, the pd_substr_length system definition operand has no effect. For details about the pd_substr_length operand, see the manual HiRDB Version 8 System Definition.
- Relationship to client environmental definition
- The specification of PDSUBSTREN has no applicability to ALTER ROUTINE. For details about PDSUBSTRLEN, see the manual HiRDB Version 8 UAP Development Guide.
- Relationship to the character code type specified in the pdntenv or pdsetup command
- This operand is valid only when utf-8 is specified for the character code type.
- For all other character code types, only a syntax check is performed and the specification is ignored.
- When this operand is omitted, the value specified during creation of the most recent SQL object (execution of a CREATE PROCEDURE, ALTER PROCEDURE, CREATE FUNCTION, CREATE TYPE, ALTER ROUTINE, CREATE TRIGGER, or ALTER TRIGGER statement) is assumed.
Common rules
- When an SQL compile option is specified in ALTER ROUTINE, the SQL statement created by incorporating the SQL compile option into the original CREATE PROCEDURE, CREATE FUNCTION, CREATE TYPE, or CREATE TRIGGER statement of the routine to be recreated must be of a length that does not exceed the maximum allowable length for SQL statements.
- Specification of the SQL compile option is valid only for procedures or triggers; it has no effect on functions.
- The ALTER ROUTINE cannot be executed from a Java procedure when the SQL object being executed can be re-created.
Notes
- ALTER ROUTINE cannot be specified from an X/Open-compliant UAP running under OLTP.
- When SQL objects for multiple functions, procedures, and triggers are recreated, the COMMIT or ROLLBACK statement is executed for each function, procedure, and trigger.
- By executing a GET DIAGNOSTICS statement immediately following execution of an ALTER ROUTINE statement, diagnostic information can be obtained for the ALTER ROUTINE statement. The return code for the SQL object of a function, procedure, or trigger whose re-creation terminated normally is 0.
- The data guarantee level of the SQL optimization option, the SQL extension optimizing option, and the maximum character length are determined by what is specified when the routine or trigger is being defined or re-created, and are not affected by the system definition or client environment variable definition that is in effect when the procedure or function is called or when the trigger operation is executed.
- If an index is added or deleted to or from a table (exclusive of the table in which the trigger is defined) that is used in a procedure or trigger, any index information in the SQL object for the procedure and the trigger is nullified, in which case the affected trigger cannot be executed. Because the procedure cannot be executed from another procedure or trigger, the SQL object needs to be recreated by specifying ALL.
- Because no SQL object is created, the Java procedure or Java function cannot re-create the SQL object; it can only update the SQL compile option.
- If triggers must be nested, care should be taken with the following items:
- If some or all of the nested triggers are disabled, a single execution of ALTER ROUTINE may not be able to put all the triggers in effect (ALTER ROUTINE results in a KFPA11528-E error). In this case, execute ALTER ROUTINE repeatedly until ALTER ROUTINE terminates normally.
- If there are triggers to be nested, and if a function is specified in a search condition during the operation of the triggers, care should be taken with the following item:
- If this function is deleted, a KFPA11529-E error may occur during the execution of the trigger, even when ALTER ROUTINE terminates normally. In this case, re-execute the trigger or the routine by recreating it (ALTER TRIGGER or ALTER PROCEDURE). The trigger or routine that caused the runtime error is the calling trigger, or routine or the trigger from which the function was deleted.
- If there are looping triggers, care should be taken with the following item:
- If all looping triggers are disabled, they cannot be recreated by executing ALTER ROUTINE. In this case, delete all the looping triggers and the triggers that are in the table defining the looping triggers. You need to re-define them.
Examples
- Recreate among all functions, procedures, and triggers those functions and procedures for which the SQL object has been nullified:
ALTER ROUTINE
- Recreate all the functions, procedures, and triggers belonging to a user (USER1):
ALTER ROUTINE
AUTHORIZATION USER1 ALL