Nonstop Database, HiRDB Version 9 System Operation Guide

[Contents][Index][Back][Next]

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 configuration 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 SQL runtime warning information (when the timing for output to a file of warnings is specified by an unsigned integer as a percentage of PDCWAITTIME or by auto):

** 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 STOCK WHERE SNO=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 STOCK WHERE SNO=1

The following table explains the SQL runtime warning information that is output.

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

Output information Header name Description Maximum number of characters (bytes) Output?
Cond. 1 Cond. 2
HiRDB version SQL CWAITTIME WARNING INFORMATION HiRDB version, in the format VV-RR-ZZ. If there is no ZZ, it is not output. 8 Y Y
Output time Displays 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 26 Y Y
Reason code REASON Reason 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.
 2 Y Y
Value of PDCWAITTIME operand CWAITIME Value (in seconds) set for the PDCWAITTIME operand in the client environment definitions. 5 Y Y
Percentage specified in operand, or amount of elapsed time CWAITTIME_
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 12 Y Y
Elapsed time basis for output of warning information CWAITTIME_
WRN_TIME		 Amount of time (seconds) on the basis of which warning information was output.#1 5 or 12 Y Y
Name of UAP UAP_NAME UAP name specified in the PDCLTAPNAME operand of the client environment definitions. 30 Y Y
Process number CLTPID Client process number. If the connection was made from a Type 4 JDBC driver, 0 is displayed. 10 Y Y
IP address IP_ADDR IP address of the client that executed the UAP. 15 Y Y
Service name SERVICE_
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.
 31 S S
Authorization identifier USERID Name of the connected user. 30 Y Y
UAP start time START_TIME Time execution of the UAP started, in the format YYYY/MM/DD hh:mm:ss. 19 Y Y
Host name HOST Name of the host on which the server process is running. 30 Y Y
Port number PORT Communications port number used by the server process. 5 Y S
Server name SVRNAME Name of the server. For a HiRDB single server configuration, the name of the single server is output; for a HiRDB parallel server configuration, the name of the front-end server is output. 8 Y Y
Process number SVRPID Process number of the server process. 10 Y Y
SQL optimization option OPTIMIZE_
LEVEL		 Value (in decimal) of the SQL optimization option. If this value cannot be obtained, an asterisk (*) is output. 10 Y Y
SQL extension optimizing option ADDITIONAL_
OPTIMIZE_LEVEL		 Value (in decimal) of the SQL extension optimizing option. If this value cannot be obtained, an asterisk (*) is output. 10 Y Y
Data guarantee level ISOLATION_
LEVEL		 Value set as the data guarantee level. If this value cannot be obtained, an asterisk (*) is output. 10 Y Y
Size of SQL object SQLOBJ_SIZE Size (in bytes) of the SQL object. If this value cannot be obtained, an asterisk (*) is output. 10 S N
SQL processed lines count SQLCOUNT Number 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 9 SQL Reference. 10 S N
Connection sequence number CNCTNO Sequence number that increments each time a server receives a CONNECT request. 10 Y Y
SQL counter SQL-COUNTER Sequence number that increments each time an SQL statement is received. If this value cannot be obtained, an asterisk (*) is output. 10 Y N
Operation code OP CODE Operation code that corresponds to the SQL code. If this value cannot be obtained, an asterisk (*) is output. 4 Y N
Section number SEC NO Section number that corresponds to the SQL code. If this value cannot be obtained, an asterisk (*) is output. 4 S N
SQLCODE SQL CODE SQLCODE resulting from execution of the SQL statement. If this value cannot be obtained, an asterisk (*) is output. 5 Y N
Warning information SQL WARN Warning information (in hexadecimal).#2 If this value cannot be obtained, an asterisk (*) is output. 5 Y N
SQL start time START-TIME Displays 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 26 Y S
SQL end time END-TIME Displays 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 15 Y Y
SQL execution time EXEC-TIME Amount 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 12 Y S
SQL message SQL MESSAGE Message output while the SQL code was executing. If this value cannot be obtained, an asterisk (*) is output. 254 S N
Information in square brackets is system maintenance information. If this value cannot be obtained, an asterisk (*) is output. 21 Y S
SQL statement SQL STATEMENT SQL 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,000 S S

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]

[Contents][Back][Next]

[Trademarks]

All Rights Reserved. Copyright (C) 2011, 2015, Hitachi, Ltd.