10.10.4 Using the SQL statement statistical information
In the SQL statement statistical information, you can check the operational status of each SQL statement.
For example, if you want to change the value of an operand specified in the server definition, or if you want to change the SQL statement to execute, or change a table or index, you can use the SQL statement statistical information.
- Note
-
You can determine the operational status of SQL statements by checking the SQL trace information that has been output to SQL trace files, as well as by checking the SQL statement statistical information that has been output by the adbstat command. For details about the SQL trace information, see 10.11 Running SQL tracing.
- Organization of this subsection
(1) Outputting the SQL statement statistical information and acquiring related information
When an SQL statement is finished executing, SQL statement statistical information is output to the statistics log file. By executing the adbstat command, you can display the SQL statement statistical information that was output to the statistics log file. Note that SQL statement statistical information might not be output for SQL statements that result in an error.
We recommend that you output the SQL statement statistical information on a regular basis.
- ■ Outputting the SQL statement statistical information
-
See Checking the SQL statement statistical information in a specific date and time range in Examples under adbstat (Perform Statistical Analysis of the HADB Server) in the manual HADB Command Reference.
We also recommend that you acquire the following types of related information when you output the SQL statement statistical information:
-
Server message log files
Use the OS's cp command or a similar method to copy and acquire these files. For the storage destination of server message log files, see 10.4.2 Viewing the message logs (message log output destination).
- Tip
-
When Y is specified for the adb_sql_text_out operand in the server definition, all SQL statements that were accepted by the HADB server are output to the server message log file. To make it easier to identify a certain SQL statement when you check the SQL statement statistical information and server message log files, consider specifying Y for the adb_sql_text_out operand in the server definition. For details about the adb_sql_text_out operand in the server definition, see 7.2.5 Operands related to SQL statements (set format).
-
Application identifier
Use the adbls -d cnct command to check the application identifier that is set for the application or command being executed. In the case of an application, the application identifier that was specified in the adb_clt_ap_name operand in the client definition is output.
For details about the adbls -d cnct command, see adbls -d cnct (Display the Connection Status) in the manual HADB Command Reference.
For details about the adb_clt_ap_name operand in the client definition, see Operands related to application program status monitoring in the HADB Application Development Guide.
- Note
-
If there are multiple applications, we recommend that you specify for each application a unique application identifier in the adb_clt_ap_name operand in the client definition.
-
Application connection information
Use the adbls -d cnct command to check the application connection information.
For details about the adbls -d cnct command, see adbls -d cnct (Display the Connection Status) in the manual HADB Command Reference.
-
SQL trace files
Use a command such as the OS's cp command to copy SQL trace files. For the storage destination of SQL trace files, see 10.11.1 About SQL tracing.
- Note
-
For details about how to output SQL trace information to SQL trace files, see 10.11.5 Preparations for outputting SQL trace information.
(2) Using the SQL statement statistical information and related information
By acquiring the SQL statement statistical information and related information on a regular basis, you can check the operational status of each SQL statement. You can also use the acquired SQL statement statistical information and related information to perform tuning.
■ Using the SQL statement statistical information and related information
-
Check the SQL statement statistical information.
In the SQL statement statistical information that is output, check the following types of information:
-
Timestamp (output date and time of statistical log file information)
-
AP_name (application identifier)
-
Connection_information (connection information)
-
SQL_serial_number (sequential number assigned to SQL statements starting from when a connection is established)
-
-
Compare the information with the server message log file.
Compare the information that you checked in step 1 with the information in the server message log file.
-
Timestamp
Compare the Timestamp that you checked in step 1 with the output date and time of the message in the server message log file. By comparing the Timestamp with the output date and time, you can determine what type of processing the target SQL statement was performing.
-
AP_name
Compare AP_name, which you checked in step 1, with the application identifier in message KFAA81000-I in the server message log file. By comparing AP_name with the application identifier, you can check the target application and command.
-
Connection_information
Compare Connection_information, which you checked in step 1, with the connection information in message KFAA81000-I in the server message log file. By comparing Connection_information with the connection information, you can check the target application's connection.
-
SQL_serial_number
When Y is specified for the adb_sql_text_out operand in the server definition, compare SQL_serial_number, which you checked in step 1, with the SQL statements' sequential numbers assigned starting from when the connection was established, which are indicated in message KFAA51000-I in the server message log file. By comparing SQL_serial_number with the SQL statements' sequential numbers assigned starting from when the connection was established, you can check the target SQL statement.
For an SQL statement that terminated in an error, compare SQL_serial_number, which you checked in step 1, with the SQL statement's sequential number assigned starting from when the connection was established, which is indicated in message KFAA51005-I in the server message log file.
-
To obtain information about executed SQL statements, check the information in server message log files against the SQL trace information.
The following figure shows the concept of using the SQL statement statistical information and related information.
|
|
![[Figure]](GRAPHICS/F0000814.GIF)
You can also use the SQL statement statistical information and related information to perform the following types of tuning:
-
(2) Using the SQL statement statistical information to reduce SQL statement execution time in 13.2.2 Reducing the SQL statement execution time.
-
(2) Using the SQL statement statistical information to reduce the execution time of an SQL statement that creates a global work table in 13.2.3 Reducing the execution time of an SQL statement that creates a global work table.
-
(2) Using the SQL statement statistical information to reduce the execution time of an SQL statement that creates a local work table in 13.2.4 Reducing the execution time of an SQL statement that creates a local work table.
-
(2) Using the SQL statement statistical information to reduce the execution time of an SQL statement that performs table scans in 13.2.5 Reducing the execution time of SQL statements that perform table scans.