to check queries that have bad performance

Procedures to do when you need to know what are the queries that have bad     

   performance

An easier way to find queries that have bad performance:

• On Db2 V8 run this query:

db2 "select TOTAL_EXEC_TIME, NUM_EXECUTIONS, substr (STMT_TEXT, 1,500) \
as statement from table(SNAPSHOT_DYN_SQL('<database>', -1)) as \
snap_dyn_sql order by 1 desc,2 desc"

• On Db2 V7 run the snapshot command and then the query:

db2 get snapshot for dynamic sql on ICMNLSDB write to file
db2 "select TOTAL_EXEC_TIME_S, NUM_EXECUTIONS, substr (STMT_TEXT, 1,500) \
as statement from table(SYSFUN.SQLCACHE_SNAPSHOT()) as snap_dyn_sql \
order by 1 desc,2 desc"

After finding out the queries that are taking a long time or the ones that run very often, you can use db2advis to check if you need new indexes using it a query each time:

db2advis -d <db_name> [{-s "sql_statement" | -i <input_file>}]
[-l <disk-limit>] [-t <max_advise_time>] [-h] [-o <output_file>]

Where:

-d <db_name> specifies the name of the database to which a connection is to be established.

-s "sql_statement" specifies the text of a single SQL statement whose indexes are to be advised. The statement must be enclosed by double quotation marks.

-i <input_file> specifies the name of an input file containing one or more SQL statements. Statements must be delimited by semicolons. Comments should have two hyphens at the start of each line. The frequency at which each statement in the workload is to be executed can by changed by inserting the following line into the input file: --#SET FREQUENCY <value>

-l <disk-limit> specifies the maximum number of megabytes available for all indexes in the existing schema. The default value is 20% of the total database size.

-t <max-advise-time> specifies the maximum allowable time, in minutes, to complete the operation. If no value is specified for this option, the operation will continue until it is completed.

-h displays help information.

-o <output_file> saves the script to create the recommended objects in output_file.

Or to investigate one or more slow queries in a production environment:

db2advis -d <db_name> -i <input_file> -o <output_file>