ALTER PROCEDURE (Re-create SQL object of procedure)

Function

ALTER PROCEDURE re-creates the SQL object for procedures or modifies the compile options for a Java procedure.

Privileges

Owner of the procedure

A user can recreate an SQL object of any procedure owned by that user (including a public procedure defined by the user).

Users with the DBA privilege

A user can recreate an SQL object of any procedure owned by that user (including a public procedure defined by the user) and procedures owned by other users (including a public procedure defined by other users).

Format

ALTER PROCEDURE
  [{[authorization-identifier.]routine-identifier
   |  [AUTHORIZATION authorization-identifier]
      [ALL|INDEX USING [authorization-identifier.]
         table-identifier]}]
  [SQL-compile-option[SQL-compile-option]...
|SUBSTR LENGTH maximum-character-length ]

SQL-compile-option ::={ISOLATION data-guarantee-level [FOR UPDATE EXCLUSIVE]
                    |OPTIMIZE LEVEL SQL-optimization-option
                       [,SQL-optimization-option]
                    |ADD OPTIMIZE LEVEL SQL-extension-
                     optimizing-options
                       [,SQL-extension-optimizing-option]

Operands

Specifies a specific procedure whose SQL object is to be re-created.

The SQL object is re-created, regardless of the validity of the index information of the specified procedure or the validity of the SQL object.

This operand is used to change the SQL compile option.

authorization-identifier
Specifies the authorization identifier of the owner of the procedure whose SQL object is to be re-created.
When recreating an SQL object of a public procedure, specify PUBLIC in all caps enclosed in double quotation marks (") for the authorization identifier.
routine-identifier
Specifies the name of the procedure whose SQL object is to be re-created.

Specifies procedures that are to be re-created in terms of the authorization identifier of the owner of the procedures and the procedures' status.

[AUTHORIZATION authorization-identifier]
Specifies the authorization identifier of the owner of a procedure and recreates the SQL objects of all procedures owned by the user (including public procedures defined by the user).
When this operand is omitted, the SQL objects of all procedures in the system are re-created.
However, whether or not all the SQL objects will actually be re-created is determined by the combination of this specification and specification of the ALL or INDEX USING clause.
authorization-identifier
Specifies the authorization identifier of the owner of the procedures whose SQL objects are to be re-created.
[ALL|INDEX USING [authorization-identifier.]table-identifier]
Specifies the status of the procedures whose SQL objects are to be re-created.
If neither the ALL nor the INDEX USING clause is specified, SQL objects are re-created for only those procedures whose SQL objects are inactive.
ALL
Specifies that all the SQL objects are to be re-created, regardless of the validity of the index information of each specified procedure or the validity of each SQL object.
INDEX USING [authorization-identifier.]table-identifier
Specifies that only the SQL objects of procedures whose index information is invalid are to be re-created.
When an index is added or deleted, the index information in the procedure's SQL object becomes invalid. Therefore, specifying the base table identifier of a table in which an index was added or deleted enables re-creation of the SQL objects of all procedures that use that table and thus have invalid index information.
A procedure can still be executed when only the index information in its SQL object is invalid. However, better performance is achieved when the index information is valid.
An SQL object is re-created also for a procedure that uses the specified base table or a view table defined using the view table as the base table, if its index information is invalid.
When the INDEX USING clause is specified, the SQL object is re-created for a procedure in which only its index information is invalid, but the SQL object is not re-created for a procedure whose SQL object is inactive. If it is necessary to re-create an SQL object for a procedure whose SQL object is inactive, either the INDEX USING clause must be omitted or ALTER PROCEDURE with ALL specified must be issued.
[authorization-identifier.]table-identifier
Specifies the authorization identifier and table identifier of a table or view table that is used by the procedures whose SQL objects are to be re-created.
If the authorization identifier is omitted, the authorization identifier of the executing user is assumed.
When specifying a public view in table-identifier, in authorization-identifier specify the word PUBLIC enclosed in double quotation marks (").
[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. Specifying 0 for a set of data allows the user to reference the data even when it is being updated by another user. If the table to be referenced is a shared table, and if another use 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 by other users until the retrieval process is completed (until HiRDB finishes viewing the current page or row).
  • 2
    Guarantee 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 is completed.
When this operand is omitted, the data guarantee level specified for the most recent creation of an SQL object (execution of a CREATE PROCEDURE, ALTER PROCEDURE, or ALTER ROUTINE statement) is assumed.
For data integrity assurance levels, see the HiRDB Version 9 UAP Development Guide.
[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 PROCEDURE 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.
OPTIMIZE LEVEL SQL-optimization-option[,SQL-optimization-option]...
Specifies the optimization method for determining the most efficient access path by taking the database's status into consideration.
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 adopted during the previous SQL object creation (CREATE PROCEDURE, ALTER PROCEDURE, or ALTER ROUTINE).
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
  1. At least one identifier must be specified.
  2. When multiple identifiers are specified, separate them with the comma (,).
  3. For details about the contents that can be specified in an identifier (optimization methods), see Table 3-2 SQL optimization option specification values (ALTER PROCEDURE).
  4. 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.
  5. The identifiers are case-sensitive.
  6. 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 (optimization values listed in Table 3-2)
  • Apply the 2. Making multiple SQL objects, the 8. Suppressing use of AND multiple indexes, and the 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
  1. When HiRDB is upgraded from a version earlier than Version 06-00 to a Version 06-00 or later, the total value specification in the earlier 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.
  2. At least one integer must be specified.
  3. When multiple integers are specified, separate them with the comma (,).
  4. For details about the contents that can be specified in an unsigned integer (optimization methods), see Table 3-2 SQL optimization option specification values (ALTER PROCEDURE).
  5. 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.
  6. 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.
  7. 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.
  8. Specifying multiple optimization options by adding their values can be ambiguous as to which optimization options are actually intended, so Hitachi recommends that the option values be specified individually separated by commas. If multiple optimization option have already been specified by the addition method and another optimization option is required, you can specify the new option's value following the previous summed value by separating them with a comma.
Relationships to system definition
  1. When specified in ALTER PROCEDURE, the system-defined operand pd_optimize_level has no effect.
  2. 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.
  3. 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 PROCEDURE.
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
The following table lists the SQL optimization option specification values. For details about optimization methods, see the HiRDB Version 9 UAP Development Guide.

Table 3-2 SQL optimization option specification values (ALTER PROCEDURE)

No.Optimization optionSpecification values
IdentifierUnsigned integer
1Forced nest-loop-join"FORCE_NEST_JOIN"4
2Making multiple SQL objects"SELECT_APSL"10
3Increasing the target floatable servers (back-end servers for fetching data)#1, #2"FLTS_INC_DATA_BES"16
4Prioritized nest-loop-join"PRIOR_NEST_JOIN"32
5Increasing the number of floatable server candidates#2"FLTS_MAX_NUMBER"64
6Priority of OR multiple index use"PRIOR_OR_INDEXES"128
7Group processing, ORDER BY processing, and DISTINCT set function processing at the local back-end server#2"SORT_DATA_BES"256
8Suppressing use of AND multiple indexes"DETER_AND_INDEXES"512
9Rapid grouping facility"RAPID_GROUPING"1024
10Limiting the target floatable servers (back-end servers for fetching data)#1, #2"FLTS_ONLY_DATA_BES"2048
11Separating data collecting servers#1, #2"FLTS_SEPARATE_COLLECT_SVR"2064
12Suppressing index use (forced table scan)"FORCE_TABLE_SCAN"4096
13Forcing use of multiple indexes"FORCE_PLURAL_INDEXES"32768
14Suppressing creation of update-SQL work tables"DETER_WORK_TABLE_FOR_UPDATE"131072
15Deriving conditions for rapid searches"DERIVATIVE_COND"262144
16Applying key conditions including scalar operations"APPLY_ENHANCED_KEY_COND"524288
17Facility for batch acquisition from functions provided by plug-ins"PICKUP_MULTIPLE_ROWS_PLUGIN"1048576​
18Facility for moving search conditions into derived table"MOVE_UP_DERIVED_COND"2097152​

#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 the value that was used during the previous SQL object creation (CREATE PROCEDURE, ALTER PROCEDURE, or ALTER ROUTINE).
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
  1. At least one identifier must be specified.
  2. When multiple identifiers are specified, separate them with the comma (,).
  3. For details about the contents that can be specified in an identifier (optimization methods), see Table 3-3 SQL extension optimizing option specification values (ALTER PROCEDURE).
  4. If no extension optimizing options are to be applied, specify "NONE" as the identifier.
  5. The identifiers are case-sensitive.
  6. 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
  1. At least one integer must be specified.
  2. When multiple integers are specified, separate them with the comma (,).
  3. For details about the contents that can be specified in an unsigned integer (optimization methods), see Table 3-3 SQL extension optimizing option specification values (ALTER PROCEDURE).
  4. If no extension optimizing options are to be applied, specify 0 as the integer.
  5. If the same unsigned integer 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 unsigned integer in duplicate.
Relationship to system definition
The system-defined pd_optimize_level operand, if specified in ALTER PROCEDURE, has no effect.
Relationship to client environment definition
The specification of PDADDITIONALOPTLVL has no applicability to ALTER PROCEDURE.
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
The following table lists the SQL extension optimizing option specification values. For details about optimization methods, see the HiRDB Version 9 UAP Development Guide.

Table 3-3 SQL extension optimizing option specification values (ALTER PROCEDURE)

No.Optimizing optionSpecification values
IdentifierUnsigned integer
1Application of optimizing mode 2 based on cost"COST_BASE_2"1
2Hash join, subquery hash execution"APPLY_HASH_JOIN"2
3Facility for applying join conditions including value expression"APPLY_JOIN_COND_FOR_VALUE_EXP"32
Note
Items 2-3 take effect when Application of optimizing mode 2 based on cost is specified.
[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 for the UNIX edition); it affects the length of the result of the SUBSTR scalar function. For details about SUBSTR, see 2.16.1(20) 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 PROCEDURE, the pd_substr_length system definition operand has no effect. For details about the pd_substr_length operand, see the manual HiRDB Version 9 System Definition.
Relationship to client environmental definition
The specification of PDSUBSTREN has no applicability to ALTER PROCEDURE. For details about PDSUBSTRLEN, see the manual HiRDB Version 9 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, or ALTER ROUTINE statement) is assumed.

Common rules

  1. When the SQL compile option is specified in ALTER PROCEDURE, the length of the SQL statement that is created by incorporating the SQL compile option in the source CREATE PROCEDURE for the procedure to be re-created must not exceed the maximum allowable length for SQL statements.
  2. The ALTER PROCEDURE cannot be executed from a Java procedure when the SQL object being executed can be re-created.

Notes

  1. The ALTER PROCEDURE statement cannot be specified from an X/Open-compliant UAP running under OLTP.
  2. When SQL objects for multiple procedures are being re-created, a COMMIT or ROLLBACK statement is executed automatically for each procedure.
  3. By executing a GET DIAGNOSTICS statement immediately following execution of an ALTER PROCEDURE statement, diagnostic information can be obtained for the ALTER PROCEDURE statement. The return code for the SQL object of a procedure whose re-creation terminated normally is 0.
  4. The data guarantee level of the SQL statement in the procedure, the SQL optimization option, the SQL extension optimizing option, and the maximum character length are determined by what is specified when the routine 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 is called.
  5. Because no SQL object is created, the Java procedure and the Java function cannot re-create the SQL object; they can only update the SQL compile option.
  6. The identifier for a trigger action procedure cannot be specified in routine-identifier. When recreating a trigger SQL object, use either ALTER ROUTINE or ALTER TRIGGER.
  7. Even if you re-create an SQL object of a public procedure defined by another user, the definer of the SQL object does not change.

Examples

  1. Re-create with data guarantee level 1 a procedure (PROC1) belonging to a user (USER1):

    ALTER PROCEDURE
      USER1.PROC1 ISOLATION 1

  2. Of the active procedures of a user (USER1) that reference a table (T1) belonging to that user, re-create those procedures whose SQL objects contain invalid index information:

    ALTER PROCEDURE
      AUTHORIZATION USER1 INDEX USING USER1.T1

  3. Of all procedures, re-create those procedures that contain inactive SQL objects:

    ALTER PROCEDURE