The facility for output of extended SQL error information performs the following functions:

• Outputs the affected SQL statement and parameter information to the information of the error log facility. (The information produced when the SQL statement and parameter information are added to the information of the error log facility is called SQL error information.)
• Outputs SQL error information to the server as well. (The file to which SQL error information is output is called the SQL error report file.)

• Centralized management of SQL error information
  If an SQL error occurs, SQL error information is output on the server side, as well as on the client side. Since SQL error information for multiple clients can be output to the SQL error report file of one server, centralized management of SQL error information is possible.
  
• Output of the affected SQL statement and parameter information
  The SQL statement affected by the error and the related parameter information are output. The affected SQL statement can be investigated from this information.
  

When you use the facility for output of extended SQL error information, specify the following system definitions or client environment definitions:

• Whether or not the facility for output of extended SQL error information is to be used
  Use the pd_uap_exerror_log_use operand or PDUAPEXERLOGUSE to set whether or not the facility for output of extended SQL error information is to be used. Specify the pd_uap_exerror_log_use operand to set a value for the entire HiRDB system, and specify PDUAPEXERLOGUSE to set a value for each application.
  
• Output destination directory and maximum size of the SQL error report file
  Use the pd_uap_exerror_log_dir operand to set the output directory of the SQL error report file. Use the pd_uap_exerror_log_size operand to set the maximum size of the SQL error report file.
  
• Maximum data length of the parameter information output to the error log file or the SQL error report file
  Use the pd_uap_exerror_log_param_size operand or PDUAPEXERLOGPRMSZ to set the maximum data length of the parameter information output to the error log file or the SQL error report file. Specify the pd_uap_exerror_log_param_size operand to set a value for the entire HiRDB system, and specify PDUAPEXERLOGPRMSZ to set a value for each application.
  

(a) Output format of the SQL error report file

The output format of the SQL error report file is shown below.

Output format

 
** UAP ERROR INFORMATION aa...aa bbbbbbbbbbbbbbbbbbbbbbbbbb ** [1]
 
* UAP INFORMATION * [2]
  UAP_NAME(cc...cc) USERID(dd...dd)
  IPADDR(ee...ee) CLTPID(ff...ff) THRDID(gg...gg)
  START_TIME(hhhhhhhhhhhhhhhhhhh)
 
* SERVER INFORMATION * [3]
  HOST(ii...ii) PORT(jj...jj) PLATFORM(kk...kk)
  SVRNAME(ll...ll) SVRPID(mm...mm)
 
* SQL INFORMATION * [4]
  OPTIMIZE_LEVEL(nn...nn) ADDITIONAL_OPTIMIZE_LEVEL(oo...oo)
  ISOLATION_LEVEL(pp...pp)
 
CNCTNO     SQL-       OP   SEC  SQL   SQL   OP   ERROR
           COUNTER    CODE NO   CODE  WARN  TION COUNTER
---------- ---------- ---- ---- ----- ----- ---- -----
rrrrrrrrrr     ssssssssss    tttt   uuuu  vvvvv  wwwww xxxx  yyyyy
 
START-TIME      END-TIME        EXEC-TIME
--------------- --------------- -----------------
zzzzzzzzzzzzzzz     AAAAAAAAAAAAAAA  BB...BB
 
* SQL MESSAGE * [5]
  "CC...CC" [DD...DD]
 
* SQL STATEMENT * [6]
  "EE...EE"
 
* PARAMETER * [7]
*ELM NO= FFFFF*
*GGGGG* NO=HHHHH COD=III XDIM=JJJJJ SYS=KKKKK LEN=LLLLLLLLLLL IND=MMMMMMMMMMM
          ARRAY NUM=NNNNN
        DATA=OO...OO
 

Explanation

1. Title of SQL error report file
2. UAP information
3. Server information
4. SQL information
5. SQL message
6. SQL statement
7. Parameter information

aa...aa

Displays the HiRDB version in the format shown below. (The maximum size of the displayed characters is 8 bytes.)

vv-rr-zz

If there is no -zz value, -zz is not output.

bbbbbbbbbbbbbbbbbbbbbbbbbb

Displays the date and time that the error information was output. The output format is shown below. (The maximum size of the displayed characters is 26 bytes.)

YYYY/MM/DD hh:mm:ss.uuuuuu

YYYY: Year

MM: Month

DD: Day

hh: Hour

mm: Minute

ss: Second

uuuuuu: Microsecond

cc...cc

Displays the UAP name that was specified in the PDCLTAPNAME client environment definition. (The maximum size of the displayed characters is 30 bytes.)

dd...dd

Displays the authorization identifier of the connected user. (The maximum size of the displayed characters is 8 bytes.)

ee...ee

Displays the IP address of the UAP. (The maximum size of the displayed characters is 15 bytes.)

ff...ff

Displays the UAP process number. (The maximum size of the displayed characters is 10 bytes.)

If the correct process number cannot be obtained, an invalid value may be displayed (Windows).

gg...gg

Displays the UAP thread number if the UAP is operating in multiple threads. (The maximum size of the displayed characters is 11 bytes.) If the UAP is not operating in multiple threads, 0 is displayed.

An incorrect number may be displayed if the correct thread number cannot be obtained. If the client version is 07-01 or earlier, * is displayed.

hhhhhhhhhhhhhhhhhhh

Displays the UAP execution time in the format shown below. (The maximum size of the displayed characters is 19 bytes.)

YYYY/MM/DD hh:mm:ss

YYYY: Year

MM: Month

DD: Day

hh: Hour

mm: Minute

ss: Second

ii...ii

Displays the name of the host in which the server process is operating. (The maximum size of the displayed characters is 30 bytes.)

jj...jj

Displays the communication port number of the server process. (The maximum size of the displayed characters is 5 bytes.)

kk...kk

Displays the platform supported by the client library. (The maximum size of the displayed characters is 6 bytes.)

For details about the output information, see the UAP operation platform in 10.1.1(2) Examining SQL trace information. If the client version is 07-01 or earlier, * is output.

ll...ll

Displays the server name of the single server or the front-end server. (The maximum size of the displayed characters is 8 bytes.)

mm...mm

Displays the process number of the server process. (The maximum size of the displayed characters is 10 bytes.)

nn...nn

Displays the value of the SQL optimization option in decimal format. (The maximum size of the displayed characters is 10 bytes.)

oo...oo

Display the value of the SQL extension optimizing option in decimal format. (The maximum size of the displayed characters is 10 bytes.)

pp...pp

Displays the value of the data guarantee level. (The maximum size of the displayed characters is 10 bytes.)

rrrrrrrrrr

Displays the connection sequence number each time the server accepts CONNECT. (The maximum size of the displayed characters is 10 bytes.) The displayed connection sequence number is right-justified and padded with leading single-byte space characters.

ssssssssss

Displays the incremented SQL counter value each time an SQL statement is accepted. (The maximum size of the displayed characters is 10 bytes.) The displayed SQL counter value is right-justified and padded with leading single-byte space characters.

tttt

Displays the operation code for the SQL statement. (The maximum size of the displayed characters is 4 bytes.)

uuuu

Displays the section number of the SQL statement. (The maximum size of the displayed characters is 4 bytes.) The displayed section number is right-justified and padded with leading single-byte space characters. If an error occurs during execution of a control SQL, **** is displayed.

vvvvv

Displays the SQLCODE of the SQL execution result. (The maximum size of the displayed characters is 5 bytes.) The displayed SQLCODE is right-justified and padded with leading single-byte space characters.

wwwww

Displays warning information in hexadecimal format. (The maximum size of the displayed characters is 5 bytes.) In the warning information, one bit is assigned to each of the items SQLWARN0 to SQLWARNF, starting from the left. If a warning flag is set to one of these items, the corresponding bit is set to 1. If a warning flag is not set, the bit is set to 0. All of these bits combined are output as a 4-digit hexadecimal value. If at least one warning flag is set, the 4-digit hexadecimal value is preceded by W. If no warning flag is set, the value is preceded by -. Examples are shown below.

Example:

xxxx

Displays information that the system uses. (The maximum size of the displayed characters is 4 bytes.)

If the first byte is M, it indicates that the inter-process memory communication facility is being used. The other three bytes represent maintenance information. However, if the client version is 07-01 or earlier, **** is displayed.

yyyyy

Displays the error log number. (The maximum size of the displayed characters is 5 bytes.)

The output error log number is right-justified and padded with leading single-byte space characters. However, If the client version is 07-01 or earlier, ***** is displayed.

zzzzzzzzzzzzzzz

Displays the time that the SQL execution request was received from the client. The time is displayed in the format shown below. (The maximum size of the displayed characters is 15 bytes.)

hh:mm:ss.uuuuuu

hh: Hour

mm: Minute

ss: Second

uuuuuu: Microsecond

AAAAAAAAAAAAAAA

Displays the time that processing of the client request ended. The time is displayed in the format shown below. (The maximum size of the displayed characters is 15 bytes.)

hh:mm:ss.uuuuuu

hh: Hour

mm: Minute

ss: Second

uuuuuu: Microsecond

BB...BB

Displays the processing time of the client request in the format shown below. (The maximum size of the displayed characters is 17 bytes.) The displayed seconds value is right-justified and padded with leading single-byte space characters.

ssssssssss.uuuuuu

ssssssssss: Seconds

uuuuuu: Microseconds

CC...CC

Displays the message that was output during SQL execution. (The maximum size of the displayed characters is 254 bytes.)

DD...DD

Displays information that the system uses. (The maximum size of the displayed characters is 21 bytes.)

EE...EE

Displays the SQL statement. (The maximum size of the displayed characters is 2,000,000 bytes.)

If comments or SQL optimization specifications are described in the SQL statement, those are also displayed. If an error occurred during execution of a control SQL statement, * is displayed. For details about comments and SQL optimization specifications, see the manual HiRDB Version 8 SQL Reference.

FFFFF

Displays the affected element number if an error occurs in an SQL statement that uses an array. (The maximum size of the displayed characters is 5 bytes.)

GGGGG

Displays INPRM for input parameter information or OUTRM for output parameter information. For input/output parameter information, this variable displays INPRM for input information and OUTRM for output information. (The maximum size of the displayed characters is 5 bytes.)

HHHHH

Displays the parameter number. (The maximum size of the displayed characters is 5 bytes.)

III

Displays the data-type code. (The maximum size of the displayed characters is 3 bytes.)

JJJJJ

Displays the number of array elements. (The maximum size of the displayed characters is 5 bytes.)

KKKKK

Displays the area length of one element, including gaps. (The maximum size of the displayed characters is 5 bytes.)

LLLLLLLLLLL

Displays the data length. (The maximum size of the displayed characters is 11 bytes.)

MMMMMMMMMMM

Displays the indicator variable value. (The maximum size of the displayed characters is 11 bytes.)

NNNNN

Displays the number of elements in the repetition column if the SQL statement contains a repetition column. (The maximum size of the displayed characters is 5 bytes.) If the SQL statement does not contain a repetition column, this information is not displayed.

OO...OO

Displays parameter information. (The size of the displayed characters is the value specified for the pd_uap_exerror_log_param_size operand.) The types of parameter information are input parameter information, output parameter information, and input/output parameter information. The rules pertaining to parameter information are as follows:

• If an input parameter is a BLOB-type or BINARY-type locator, the value of the BLOB-type or BINARY-type locator is displayed.
• If the indicator variable is a negative value, only the portion up to DATA= is displayed.
• If there is information for several parameters, the parameter information is displayed in the sequence that the parameters were specified.
• If similar data extends beyond one line, --- SAME x LINES --- (x is the number of lines) is displayed.
• The size of the displayed parameter information is the sum of the actual data length and the data area length.
• For a repetition column, the number of elements in the repetition column is displayed in ARRAY NUM.
• For a repetition column, DATA is preceded by an indicator variable for each repetition element.
• For a repetition column, DATA is followed by the repetition column element number enclosed in parentheses.

(b) Output format of the error log file

Shown below is the output format of the error log file when the facility for output of extended SQL error information is used.

Output format

 
>  8355     0  8393     9 2005/08/12 14:06:30 KFPZ03000-I Error information, type=CONNECT STATUS,
 inf=CLT=07-02(Aug  4 2005):WS SVR=07-02   US:WS LIBTYPE=UNIX_32
>  8355     0  8393    10 2005/08/12 14:06:30 KFPZ03000-I Error information, type=SQL STREAM,
 inf=insert into t1 values ( ? , ? ,? )
>> 8355     0  8393    11 2005/08/12 14:06:30 SQLCODE:-404    47(140630218) sqaexp0.c   :2348 AUX
 KFPA11404-E Input data too long for column or assignment target in variable 3
 UAP userprog1,hiuser01 [1]
 SVR host03,1146,sds,hp [2]
 SQLINF 1034,1,2,7,17,-0000,0000,14:06:30.216463,14:06:30.217765,0.001302 [3]
 SQL INSERT INTO T1 VALUES(?,?,?) [4]
 PRM [5]
 INPRM 1,f1,1,0,4,0
         DATA=00 00 ff ff                                       *....            *
 INPRM 2,c1,10,258,255,9
         ARRAY NUM=    9
       0 DATA(    0)=00 01 61                                          *..a             *
       0 DATA(    1)=00 02 61 62                                       *..ab            *
       0 DATA(    2)=00 03 61 62 63                                    *..abc           *
       0 DATA(    3)=00 04 61 62 63 64                                 *..abcd          *
       0 DATA(    4)=00 05 61 62 63 64 65                              *..abcde         *
       0 DATA(    5)=00 06 61 62 63 64 65 66                           *..abcdef        *
       0 DATA(    6)=00 07 61 62 63 64 65 66 67                        *..abcdefg       *
       0 DATA(    7)=00 08 61 62 63 64 65 66 67 68                     *..abcdefgh      *
      -1 DATA(    8)=
 INPRM 3,93,1,0,32002,0
         DATA=00 00 00 00 00 00 7d 02 41 41 41 41 41 41 41 41   *......}.AAAAAAAA*
              41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41   *AAAAAAAAAAAAAAAA*
              --- SAME 14 LINES ---
 

Explanation

1. UAP information

UAP name

The name of the UAP that was specified in the PDCLTAPNAME client environment definition is displayed.

Authorization identifier

The authorization identifier of the connected user is displayed.

2. Server information

Host name

The name of the host in which the server process is operating is displayed.

Port number

The communication port number of the server process is displayed.

Server name

The server name of the single server or front-end server is displayed.

Platform

The platform supported by the client library is displayed.

For details about the displayed information, see the UAP operation platform in 10.1.1(2) Examining SQL trace information. If the client version is 07-01 or earlier, * is displayed.

3. SQL information

SQL optimization option

The value of the SQL optimization option is displayed in decimal format.

SQL extension optimizing option

The value of the SQL extension optimizing option is displayed in decimal format.

Data guarantee level

The value of the data guarantee level is displayed.

Connection sequence number

The connection sequence number, which is incremented sequentially each time the server accepts CONNECT, is displayed.

Section number

The section number of the SQL statement is displayed.

SQLWARN

Warning information is displayed in hexadecimal format. In the warning information, one bit is assigned to each of the items SQLWARN0 to SQLWARNF, starting from the left. If a warning flag is set to one of these items, the corresponding bit is set to 1. If a warning flag is not set, the bit is set to 0. All of these bits combined are output as a 4-digit hexadecimal value. If at least one warning flag is set, the 4-digit hexadecimal value is preceded by W. If no warning flag is set, the value is preceded by -. (For examples, see the explanation for wwwww in (a) Output format of the SQL error report file.)

System information

Information used by the system is displayed.

If the first byte is M, it indicates that the inter-process memory communication facility is being used. The other three bytes represent maintenance information. However, if the client version is 07-01 or earlier, **** is displayed.

SQL start time

The time when the SQL execution request from the client was received is displayed in the following format:

hh:mm:ss.uuuuuu

hh: Hour

mm: Minute

ss: Second

uuuuuu: Microsecond

SQL end time

The time when the process requested by the client ended is displayed in the following format:

hh:mm:ss.uuuuuu

hh: Hour

mm: Minute

ss: Second

uuuuuu: Microsecond

SQL runtime

The processing time of the client request is output in the following format. The displayed seconds value is right-justified and padded with leading single-byte space characters.

ssssssssss.uuuuuu

ssssssssss: Second

uuuuuu: Microsecond

4. SQL statement

SQL statement

The SQL statement is displayed.

If comments or SQL optimization specifications are described in the SQL statement, those are also displayed. The size of the displayed SQL statement is the value specified for PDSQLTEXTSIZE in the client environment definitions.

If an error occurs during execution of a control SQL statement, that SQL statement cannot be obtained and * is displayed instead.

For details about comments and SQL optimization specifications, see the manual HiRDB Version 8 SQL Reference.

5. Parameter information

ELM NO

If an error occurs in an SQL statement that uses an error, the number of that element is displayed.

Parameter information type

INPRM is displayed for input parameter information and OUTRM for output parameter information. For input/output parameter information, INPRM is displayed for input information and OUTRM for output information.

NO

The parameter number is displayed.

COD

The data type code is displayed.

XDIM

The number of array elements is displayed.

SYS

The area length of one element, including gaps, is displayed.

LEN

The data length is displayed.

IND

The value of the indicator variable is displayed.

ARRAY NUM

If the SQL statement contains a repetition column, the number of elements in the repetition column is displayed. If the SQL statement does not contain a repetition column, this information is not displayed.

DATA

Parameter information is displayed.

The types of parameter information are input parameter information, output parameter information, and input/output parameter information.

The rules pertaining to parameter information are as follows:

• If an input parameter is a BLOB-type or BINARY-type locator, the value of the BLOB-type or BINARY-type locator is displayed.
• If the indicator variable is a negative value, only the portion up to DATA= is displayed.
• If there is information for several parameters, the parameter information is displayed in the sequence that the parameters were specified.
• If similar data extends beyond one line, --- SAME x LINES --- (x is the number of lines) is displayed.
• The size of the displayed parameter information is sum of the actual data length and the data area length.
• For a repetition column, the number of elements in the repetition column is displayed in ARRAY NUM.
• For a repetition column, DATA is preceded by an indicator variable for each repetition element.
• For a repetition column, DATA is followed by the repetition column element number enclosed in parentheses.

The rules pertaining to the SQL error report files are described below. To view an SQL error report file, use a text editor or similar software.

1. HiRDB executes SQL statements, and each time it detects an error, it opens the SQL error report file, writes SQL error information, and then closes the file. Since the SQL error information is appended to the final position of the SQL error report file, SQL error information accumulates in the file in chronological order.
2. Two SQL error report files are created (pduaperrlog1 and pduaperrlog2). If the size of the file to which data is currently being written exceeds the specified value of the pd_uap_exerror_log_size operand in the system definition, the output destination is switched to the other file. The system uses the two files alternately by performing this switching process on the other file as well. (After switching takes place, the contents of the previous file are deleted.) After HiRDB is started, the file that was most recently updated becomes the output destination.
3. When SQL processing ends, the SQL error report files are closed. Therefore, when an SQL statement is not being executed, you can use an OS command to back up or view the files. Even while an SQL statement is being executed, you can back up or view the file that is not the output destination.
4. To determine the SQL error report file that is being used currently, use the OS's dir command (1s -1 command in UNIX) to check the most recent update dates of the files. The SQL error report file that was updated most recently is the current file.

1. When the facility for output of extended SQL error information is used, time is required for executing a system call that retrieves the SQL start time and runtime, even if SQL error information is not output.
2. If the OS detects an error (such as a file system failure or an inappropriate file write privilege) while information is being output to an error log file or an SQL error report file, SQL error information is not output to the SQL error report file.
3. When the facility for output of extended SQL error information is used, memory space becomes necessary because parameter information is output.