8.9.3 Information output to the SQL runtime warning information file

Organization of this subsection
(1) Viewing the SQL runtime warning information file
(2) Output format of SQL runtime warning information

(1) Viewing the SQL runtime warning information file

You can view SQL runtime warning information with any text editor or software capable of opening a text file.

Note that, in a HiRDB/Parallel Server environment, the warning information is output to the server machine running the front-end server to which the UAP that issued the offending SQL is connected.

Remarks
  • To determine which SQL runtime warning information file is the current file, use an OS command (ls command, for instance) to check the update dates and times of the files. The file with the most recent update date and time is the one that is being used currently.
  • The output target file after HiRDB starts is the one with the most recent update date and time.
  • SQL runtime warning information is written to the file starting at the previous ending position, which means that warning information is displayed in the file chronologically.
  • Because the current SQL runtime warning information file is closed when an SQL statement has executed, you can use OS commands to back up or view the file while SQL code is not executing, without worrying about interfering with the file while it is being written to. Even when SQL code is being executed, you can manipulate the other file (the one not being used) without having to worry about destroying the file that is being written to.

(2) Output format of SQL runtime warning information

The following shows the output format of the SQL runtime warning information (when an unsigned integer is used to specify a percentage of the PDCWAITTIME operand):

** SQL CWAITTIME WARNING INFORMATION 07-01    2002/07/04 14:32:22 **
 REASON(01)
 CWAITIME(600) CWAITTIME_WRN_PNT(70) CWAITTIME_WRN_TIME(420)

* UAP INFORMATION *
 UAP_NAME(userprog1) CLTPID(408)
 IP_ADDR(196.12.42.146) SERVICE_NAME(service1)
 USERID(hiuser01) START_TIME(2002/07/03 20:24:42)

* SERVER INFORMATION *
 HOST(host03) PORT(1146)
 SVRNAME(fes1) SVRPID(905)

* SQL INFORMATION *
 OPTIMIZE_LEVEL(132768) ADDITIONAL_OPTIMIZE_LEVEL(3)
 ISOLATION_LEVEL(2)
 SQLOBJ_SIZE(2608) SQLCOUNT(1)

CNCTNO     SQL-       OP   SEC  SQL   SQL   START-TIME          END-TIME EXEC-
          COUNTER    CODE NO   CODE  WARN                               TIME
---------- ---------- ---- ---- ----- ----- ------------------- -------- -----
       10         10 AUI2 1890     0 -0000 2002/07/04 14:32:22 14:39:30   428

* SQL MESSAGE *
 "*" [*]

* SQL STATEMENT *
 DELETE FROM ZAIKO WHERE ZNO=1

The following shows the output format of the SQL runtime warning information (when an unsigned decimal number is used to specify a percentage of the PDCWAITTIME operand or an amount of elapsed time that is to constitute a trigger for output of warning information to a file is specified):

** SQL CWAITTIME WARNING INFORMATION 07-01    2002/07/04 14:32:22.100000 **
 REASON(01)
 CWAITIME(600) CWAITTIME_WRN_PNT(70.001000) CWAITTIME_WRN_TIME(420.006000)

* UAP INFORMATION *
 UAP_NAME(userprog1) CLTPID(408)
 IP_ADDR(196.12.42.146) SERVICE_NAME(service1)
 USERID(hiuser01) START_TIME(2002/07/03 20:24:42)

* SERVER INFORMATION *
 HOST(host03) PORT(1146)
 SVRNAME(fes1) SVRPID(905)

* SQL INFORMATION *
 OPTIMIZE_LEVEL(132768) ADDITIONAL_OPTIMIZE_LEVEL(3)
 ISOLATION_LEVEL(2)
 SQLOBJ_SIZE(2608) SQLCOUNT(1)

CNCTNO     SQL-       OP   SEC  SQL   SQL  
          COUNTER    CODE NO   CODE  WARN  
---------- ---------- ---- ---- ----- -----
       10         10 AUI2 1890     0 -0000

START-TIME                 END-TIME        EXEC-TIME

-------------------------- --------------- ------------
2002/07/04 14:32:22.122222 14:39:30.822223   428.700001

* SQL MESSAGE *
 "*" [*]

* SQL STATEMENT *
 DELETE FROM ZAIKO WHERE ZNO=1

Table 8-8 explains the SQL runtime warning information that is output.

Table 8-8 Description of SQL runtime warning information that is output

Output informationHeader nameDescriptionMaximum number of characters (bytes)Output?
Cond. 1Cond. 2
HiRDB versionSQL CWAITTIME WARNING INFORMATIONHiRDB version, in the format VV-RR-ZZ. If there is no ZZ, it is not output.8YY
Output timeDisplays the time at which the warning was written into the SQL runtime warning information file, in one of the following formats:
  • YYYY/MM/DD hh:mm:ss
  • YYYY/MM/DD hh:mm:ss.uuuuuu (where uuuuuu is microseconds)
19 or 26YY
Reason codeREASONReason the warning was output to the SQL runtime warning information file:
  • 00: The server process was terminated forcibly because the UAP was terminated forcibly.
  • 01: The SQL execution time exceeded the set time.
2YY
Value of PDCWAITTIME operandCWAITIMEValue (in seconds) set for the PDCWAITTIME operand in the client environment definitions.5YY
Percentage specified in operand, or amount of elapsed timeCWAITTIME_
WRN_PNT
Displays the percentage (%) specified in the PDCWAITTIMEWRNPNT operand or the pd_cwaittime_wrn_pnt operand, or amount of time (seconds). The value that is output is the one that was applied, in accordance with the precedence level of the operands.2 or 12YY
Elapsed time basis for output of warning informationCWAITTIME_
WRN_TIME
Amount of time (seconds) on the basis of which warning information was output.15 or 12YY
Name of UAPUAP_NAMEUAP name specified in the PDCLTAPNAME operand of the client environment definitions.30YY
Process numberCLTPIDClient process number.10YY
IP addressIP_ADDRIP address of the client that executed the UAP.15YY
Service nameSERVICE_
NAME
Depending on the type of UAP, the service name is indicated as follows:
  • For an OpenTP1 UAP
    In the case of a service in which an OpenTP1 SUP (Service Utilization Program) has issued a request to an SPP (Service Provider Program), or a service in which TP1/Message Control is issuing a request to an MHP (Message Handling Program), the name of the service is output. In any other case, an asterisk (*) is output.
  • For other than an OpenTP1 UAP
    An asterisk (*) is output.
31SS
Authorization identifierUSERIDName of the connected user.8YY
UAP start timeSTART_TIMETime execution of the UAP started, in the format YYYY/MM/DD hh:mm:ss.19YY
Host nameHOSTName of the host on which the server process is running.30YY
Port numberPORTCommunications port number used by the server process.5YS
Server nameSVRNAMEName of the server. For a HiRDB/Single Server, the name of the single server is output; for a HiRDB/Parallel Server, the name of the front-end server is output.8YY
Process numberSVRPIDProcess number of the server process.10YY
SQL optimization optionOPTIMIZE_
LEVEL
Value (in decimal) of the SQL optimization option. If this value cannot be obtained, an asterisk (*) is output.10YY
SQL extension optimizing optionADDITIONAL_
OPTIMIZE_LEVEL
Value (in decimal) of the SQL extension optimizing option. If this value cannot be obtained, an asterisk (*) is output.10YY
Data guarantee levelISOLATION_
LEVEL
Value set as the data guarantee level. If this value cannot be obtained, an asterisk (*) is output.10YY
Size of SQL objectSQLOBJ_SIZESize (in bytes) of the SQL object. If this value cannot be obtained, an asterisk (*) is output.10SN
SQL processed lines countSQLCOUNTNumber of lines processed by the SQL code (the number of lines read by a SELECT statement, for example). If this value cannot be obtained, an asterisk (*) is output. For details about what is output, see the manual HiRDB Version 8 SQL Reference.10SN
Connection sequence numberCNCTNOSequence number that increments each time a server receives a CONNECT request.10YY
SQL counterSQL-COUNTERSequence number that increments each time an SQL statement is received. If this value cannot be obtained, an asterisk (*) is output.10YN
Operation codeOP CODEOperation code that corresponds to the SQL code. If this value cannot be obtained, an asterisk (*) is output.4YN
Section numberSEC NOSection number that corresponds to the SQL code. If this value cannot be obtained, an asterisk (*) is output.4SN
SQLCODESQL CODESQLCODE resulting from execution of the SQL statement. If this value cannot be obtained, an asterisk (*) is output.5YN
Warning informationSQL WARNWarning information (in hexadecimal).2 If this value cannot be obtained, an asterisk (*) is output.5YN
SQL start timeSTART-TIMEDisplays the date and time the SQL execution request was received from the client, in one of the following formats:
  • YYYY/MM/DD hh:mm:ss YYYY/MM/DD
  • hh:mm:ss.uuuuuu (where uuuuuu is microseconds)
If this value cannot be obtained, an asterisk (*) is output.
19 or 26YS
SQL end timeEND-TIMEDisplays the end time of processing in response to the request received from the client, in one of the following formats:
  • hh:mm:ss
  • hh:mm:ss.uuuuuu (where uuuuuu is microseconds)
If the server process was terminated forcibly, this value indicates the time at which processing ended.
8 or 15YY
SQL execution timeEXEC-TIMEAmount of time required to process the request received from the client (in seconds). If the server process was terminated forcibly, this value indicates the amount of time until processing ended. If this value cannot be obtained, an asterisk (*) is output.5 or 12YS
SQL messageSQL MESSAGEMessage output while the SQL code was executing. If this value cannot be obtained, an asterisk (*) is output.254SN
Information in square brackets is system maintenance information. If this value cannot be obtained, an asterisk (*) is output.21YS
SQL statementSQL STATEMENTSQL statement. If a comment has been inserted in the SQL statement, or if an SQL optimization option has been specified, this information is also included in the output. If this value cannot be obtained, an asterisk (*) is output.2,000,000SS
Legend:
Cond. 1: The SQL execution time reached the set time.
Cond. 2: The server process was terminated forcibly before SQL execution reached the set time.
Y: Always output
S: Sometimes output
N: Never output
1 The following formula is used to obtain the time basis for output of warning information:
  • When an unsigned integer is used to specify a percentage of the PDCWAITTIME operand:
elapsed-time-basis-for-output-of-warning-information = MAX([Figure] (a[Figure] b) [Figure] 100 [Figure], 1)
a: Value of PDCWAITTIME operand
b: Value of PDCWAITTIMEWRNPNT operand or pd_cwaittime_wrn_pnt operand (whichever was applied, in accordance with the precedence level of these operands)
  • When an unsigned decimal number is used to specify a percentage of the PDCWAITTIME operand:
a: Value of the PDCWAITTIME operand
b: Value of the PDCWAITTIMEWRNPNT operand or pd_cwaittime_wrn_pnt operand (whichever was applied, in accordance with the precedence level of these operands)
Note that up to six decimal places are valid for the elapsed time basis for output of warning information. The seventh and subsequent decimal places are truncated.
  • When the amount of elapsed time that becomes a trigger for output of warning information to a file is specified:
elapsed-time-basis-for-output-of-warning-information = absolute-value-of-time-that-becomes-trigger-for-output-of-warning-information-to-file
2 Starting from the left side, one bit is allocated for each of 16 warning items (SQLWARN0 to SQLWARNF). A 1 is set for a warning item whose warning flag is set, and a 0 is set for a warning item whose warning flag is not set. From this, a 16-bit hexadecimal number is obtained. This 16-bit hexadecimal value is then output as a 4-digit hexadecimal number. If one or more warning flags are set, W is prefixed to the 4-digit hexadecimal number. If no warning flags are set, a hyphen (-) is prefixed to the hexadecimal number.
Example 1: If the warning information is as shown in the following, WC040 is output:
[Figure]
Example 2: If the warning information is a shown in the following, -0000 is output:
[Figure]