17.4 Rules and notes

Organization of this section
(1) Rules
(2) Notes

(1) Rules

  1. The access path display utility can be executed at any time, whether or not HiRDB is active.
  2. The access path display utility must be executed at the server machine containing the single server or front-end server that is accessed by the UAP subject to utility processing.

(2) Notes

(a) Notes about return code

For the pdvwopt utility, return code 0 indicates normal termination, and return code 8 indicates abnormal termination.

(b) Notes about collecting an access path information file
  1. Specifying a value of 1 in the PDVWOPTMODE client environment definition variable increases the server's workload for creating an access path information file. Therefore, this value should be specified only when it is necessary to collect access path information. If output of access path information is specified with the UAP report facility, the server's workload may increase. For details about the UAP report facility, see the HiRDB Version 8UAP Development Guide.
  2. If you specify a value of 1 in the PDVWOPTMODE client environment definition variable and if the SQL object being checked results in a cache hit, the utility uses the previously created SQL object because SQL optimization is not executed. Therefore, the utility does not obtain the access path information.
  3. If you specify a value of 2 in the PDVWOPTMODE client environment definition variable, the utility obtains the access path information even when the SQL object being checked results in a cache hit. In this case, the utility creates SQL objects by executing SQL optimization regardless of the cache hit; therefore, the server's workload may be greater than when a value of 1 is specified in PDVWOPTMODE.
  4. For an SQL statement specified in a stored procedure, executing the CALL statement does not obtain the access path information. In this case, one of the following actions must be taken in order to obtain access path information (a different access path might be obtained because the SQL object is re-created on the basis of current cost information):
    • Execute ALTER PROCEDURE to re-create the SQL object.
    • Delete the procedure with DROP PROCEDURE, then re-create the SQL object with CREATE PROCEDURE.
  5. For a triggered SQL statement specified in a trigger, the utility does not obtain the access path information even if the trigger is executed. To obtain access path information for a triggered SQL statement, use the following method (note that the access path may be different from the previous path because the utility re-creates the SQL object on the basis of the current cost information):
    • Execute the ALTER TRIGGER statement to re-create the SQL object.
  6. HiRDB does not delete access path information files. Any unneeded access path information files must be deleted by the user, taking care not to delete the SQL information directory ($PDDIR/spool/pdsqldump); if this directory is deleted, access path information files can no longer be created.
  7. When you define in CREATE TABLE a referencing table for which CASCADE is specified as the referential constraint action, an internal trigger is created; therefore, access path information is acquired when such a referencing table is defined.
  8. For the trigger created when a referencing table is defined, no access path information is acquired even when the trigger is executed. In this case, execute ALTER ROUTINE to re-create the SQL object and then acquire access path information. Note that the acquired access path may not match the existing access path because the SQL object is re-created on the basis of the current cost information.
  9. If you execute the INSERT statement with VALUES specified, the utility acquires the access path information only when one of the following functions is executed:
    • Trigger
    • Referential constraint
    • Scalar subquery
(c) Notes about executing the access path display utility

The same version of the access path display utility and access path information file must be used; if their versions do not match, an error occurs.

(d) Notes on the output information in the output format

Internal system information in the execution results output by the access path display utility is enclosed in braces ({ }).