Db2 database common debugging and detection database statements

Db2 database common debugging and detection database statements

Connect to [database name] user [operation user name] using [Password]

Connect to the specified database, excluding brackets

 

reorgchk on table EAS.T_User

Check whether the table EAS. T_User needs to be reorganized

reorgchk update statistics on table all

Check whether all tables need to be reorganized

 

Select tabname, colcount, status FROM syscat. tables WHERE tabschema not like 'sys % 'order by tabname

Status normal status column N = normal; C = awaiting review

Get snapshot for bufferpools on Database Name

Detect snapshot buffer pool hit rate> 95%?

Get snapshot for Locks on EASdb

Detect snapshot locks

 

Get snapshot for Locks on EASdb
Get snapshot for all on EASdb

Detect snapshot locks

Select index_hit_ratio_percent from sysibmadm. bp_hitratio

Buffer Pool hit rate

Select agent_id, rows_selected, rows_read from sysibmadm. application_performance

View System Performance

Select elapsed_time_min, appl_status, agent_id from sysibmadm. long_running_ SQL order by elapsed_time_min desc fetch first 5 rows only

View applications with the longest running time

Select * from sysibmadm. top_dynamic_ SQL order by num_executions desc

View the SQL statements with the most running times

Select tabschema, tabname from syscat. tables where stats_time is null

View tables without statistics

Select tabname from syscat. indexes where stats_time is null

View indexes without statistics

 

Reorg table EAS. T_City

Reorganizing a single table

Reorg indexes all for table Name

Only reorganize Indexes

Runstats on table EAS. t_group and detailed indexes all

Restatistical table and Index

List applications show detail

Show used applications

List tablespaces show detail

Display the tablespace used

 

Create unique index t_groupIdCODEname on eas. t_group (G_ID asc, G_CODE, G_NAME) allow reverse scans

Create a unique index that can be scanned in reverse order.

Create index t_groupIdCODEname on eas. t_group (G_ID asc) include (G_CODE, G_NAME)

Create include Index

Create table eas. t1 (c1 int, c2 double, c3 double generated always as c1 + c2, c4 generated always as (case when c1> c2 then 1 else null end ))

Create a table with computing Columns

 

There are usually a large number of deletions, modifications, the need for reorg table when adding, in the runstats on table