Article is a great god, and how to view a SQL statement execution time point of the DB2?
Database performance problems, have found suspicious of SQL, want to make sure whether the SQL when performance problems in execution,

CodePudding user response:

1, analyze the execution efficiency of SQL statements:
The SELECT rows_read/(num_executions + 1) as avg_rows_read,
Rows_written/(num_executions + 1) as avg_rows_written,
As avg_sorts stmt_sorts/(num_executions + 1),
As avg_exec_time total_exec_time/(num_executions + 1),
Substr (stmt_text, 1100, 0) as SQL_Stmt
The FROM SYSIBMADM. SNAPDYN_SQL ORDER BY avg_exec_time desc
The fetch first 10 rows only;
# # field explain
Num_executions statement execution times
Avg_exec_time average execution time
SQL_Stmt statement content capture

2, check a SQL: execution,
Using Linux console,
Writing SQL to be analysed in the sample. SQL file, multiple SQL using a semicolon,
Perform the db2batch - d dbname - f sample. SQL & gt; Sample. The log
To view the sample. The log can, including content, statement execution, the execution results, as well as various time,
Also can call Windows environment db2cmd should, in DB2 bin directory search,

CodePudding user response:

reference 1st floor ata15 response:

1, the analysis of the efficiency of the execution of SQL statements:
The SELECT rows_read/(num_executions + 1) as avg_rows_read,
Rows_written/(num_executions + 1) as avg_rows_written,
As avg_sorts stmt_sorts/(num_executions + 1),
As avg_exec_time total_exec_time/(num_executions + 1),
Substr (stmt_text, 1100, 0) as SQL_Stmt
The FROM SYSIBMADM. SNAPDYN_SQL ORDER BY avg_exec_time desc
The fetch first 10 rows only;
# # field explain
Num_executions statement execution times
Avg_exec_time average execution time
SQL_Stmt statement content capture

2, check a SQL: execution,
Using Linux console,
Writing SQL to be analysed in the sample. SQL file, multiple SQL using a semicolon,
Perform the db2batch - d dbname - f sample. SQL & gt; Sample. The log
To view the sample. The log can, including content, statement execution, the execution results, as well as various time,
Also can call Windows environment db2cmd should, look in DB2 bin directory,

Example:
SQL
$cat sample.Select * from sys_user;
Select * from sys_role;
Bidms $db2batch - d - f sample. SQL & gt; Sample. The log
$cat sample. The log
* Timestamp: Thu Apr 12 2018 10:24:57 CST
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

* SQL Statement Number 1:

Select * from sys_user;

The result is slightly...

* 24 row (s) fetched, 24 row (s) the output.

* Elapsed Time is: 0.000599 seconds

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

* SQL Statement Number 2:

Select * from sys_role;

The result is slightly...

* 5 row (s) fetched, 5 row (s) of the output.

* Elapsed Time is: 0.000204 seconds

* the Summary Table:

The Type Number Repetitions of Total Time (s) Min Time (s) Max Time (s) Arithmetic Mean Geometric Mean Row (s) Fetched Row (s) the Output
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
24 24 Statement 1 1 0.000599 0.000599 0.000599 0.000599 0.000599
The Statement 2 1, 0.000204 0.000204 0.000204 0.000204 0.000204 5 5

2
* Total Entries:* Total Time: 0.000803 seconds
* Minimum Time: 0.000204 seconds
* Maximum Time: 0.000599 seconds
* Arithmetic Mean Time: 0.000402 seconds
* Geometric Mean Time: 0.000350 seconds
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
* Timestamp: four on April 12, 2018 10:24:57 CST