8.9.1 Overview of the SQL runtime warning output facility

After SQL execution, HiRDB determines the SQL execution time. If this amount of time exceeds a preset elapsed time for output of warning information, the following warning information about that SQL is output; this facility is called the SQL runtime warning output facility:

Organization of this subsection
(1) Reasons for using the SQL runtime warning output facility
(2) Setting the elapsed time basis for output of warning information
(3) SQL statements monitored by the SQL runtime warning output facility
(4) Actions when warning information is output
(5) Conditions under which warning information is output
(6) Relationship between PDCWAITTIME and the SQL runtime warning output facility

(1) Reasons for using the SQL runtime warning output facility

The SQL runtime warning output facility can be used for the following purposes:

(2) Setting the elapsed time basis for output of warning information

The amount of time on the basis of which warning information is to be output is called the elapsed time basis for output of warning information. When SQL execution time exceeds the elapsed time basis for output of warning information, warning information is output. The elapsed time basis for output of warning information is determined as either of the following:

(3) SQL statements monitored by the SQL runtime warning output facility

The SQL runtime warning output facility monitors all SQL statements except for CONNECT statements.

(4) Actions when warning information is output

When information warning that a PDCWAITTIME timeout may occur is output, you should take the following actions based on the output information:

  1. Determine if lockout has occurred.
  2. Determine if a network failure has occurred.
  3. Tune the SQL code.
  4. Increase the value specified in the PDCWAITTIME operand.
  5. Check if the execution time of the SQL code has increased because of an increase in the number of data transactions.

(5) Conditions under which warning information is output

When the SQL runtime warning output facility is being used, warning information may be output, even when the execution time of SQL code is less than the set time. Warning information (message only) may also be output, even when the SQL runtime warning output facility is not being used. Table 8-7 describes the conditions under which warning information is output by the SQL runtime warning output facility.

Table 8-7 Conditions under which warning information is output by the SQL runtime warning output facility

ConditionWarning information that is output
SQL runtime warning information fileKFPA20009-W message
When the SQL runtime warning output facility is being usedThe SQL execution time meets or exceeds the set time.YesYes
Server process is terminated forcibly due to a PDCWAITTIME timeout.PartPart
Server process is terminated forcibly for some other reason.PartPart
When the SQL runtime warning output facility is not being usedThe SQL execution time meets or exceeds the set time.NoNo
Server process is terminated forcibly due to a PDCWAITTIME timeout.NoPart
Server process is terminated forcibly for some other reason.NoPart
Legend:
Yes: The indicated information is output.
Part: The indicated information is partially output.
In addition, the SQL runtime warning information file or the KFPA20009-W message may not be output depending on the timing of the forced termination.
Note, also, that you can suppress re-output of the SQL runtime information file and the KFPA20009-W message with the pd_dump_suppress_watch_time operand.
No: The indicated information is not output.
Note
When SQL execution time exceeds the value in the PDCWAITTIME operand, the server process is terminated forcibly regardless of whether the SQL runtime warning output facility is being used.

(6) Relationship between PDCWAITTIME and the SQL runtime warning output facility

Figures 8-14 and 8-15 illustrate the relationship between PDCWAITTIME and the SQL runtime warning output facility.

Figure 8-14 Relationship between PDCWAITTIME and the SQL runtime warning output facility (1 of 2)

[Figure]

Figure 8-15 Relationship between PDCWAITTIME and the SQL runtime warning output facility (2 of 2)

[Figure]

Explanation
  • The range of measuring SQL execution time is from the time a request sent from a client is received by the server until the execution result in response to the request is returned.
  • When the SQL execution result is returned to the client, HiRDB determines the SQL execution time. If the SQL execution time equals or exceeds the set time, the warning information is output.