What queries are used by the CA UIM db2 probe?

Document ID : KB000012720
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

This Article includes a list of the underlying DB2 database queries used to monitor DB2 databases/instances. This list may be helpful for UIM administrators and/or DB2 DBAs in understanding how the probe is querying the DB2 database to collect the data.

Question:

What queries are used by the CA UIM db2 probe?

Environment:
- UIM 8.x or higher- db2 prove v4.10 or higher
Answer:

/*000*/ {"generic_query","select snapshot_timestamp, server_platform from sysibmadm.snapdb"},

10/*001*/ {"app_acc_curs_blk","select appl_name, appl_id, acc_curs_blk from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

11/*002*/ {"app_agents_stolen","select appl_name, appl_id, agents_stolen from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

12/*003*/ {"app_appl_idle_time","select appl_name, appl_id, appl_idle_time from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

13/*004*/ {"app_assoc_agents_top","select appl_name, appl_id, associated_agents_top from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

14/*005*/ {"app_avg_direct_read_time","select appl_name, appl_id, direct_read_time, direct_reads from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

15/*006*/ {"app_avg_direct_write_time","select appl_name, appl_id, direct_write_time, direct_writes from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

16/*007*/ {"app_avg_sort_time","select appl_name, appl_id, total_sort_time, total_sorts from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

17/*008*/ {"app_binds_precompiles","select appl_name, appl_id, binds_precompiles from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

18/*009*/ {"app_cat_cache_heap_full","select appl_name, appl_id, cat_cache_overflows as cat_cache_heap_full from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

19/*010*/ {"app_cat_cache_hit_rto","select appl_name, appl_id, cat_cache_inserts, cat_cache_lookups from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

20/*011*/ {"app_cat_cache_inserts","select appl_name, appl_id, cat_cache_inserts from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

21/*012*/ {"app_cat_cache_lookups","select appl_name, appl_id, cat_cache_lookups from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

22/*013*/ {"app_cat_cache_overflows","select appl_name, appl_id, cat_cache_overflows from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

23/*014*/ {"app_commit_sql_stmts","select appl_name, appl_id, commit_sql_stmts from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

24/*015*/ {"app_ddl_sql_stmts","select appl_name, appl_id, ddl_sql_stmts from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

25/*016*/ {"app_deadlocks","select appl_name, appl_id, deadlocks from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

26/*017*/ {"app_direct_read_reqs","select appl_name, appl_id, direct_read_reqs from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

27/*018*/ {"app_direct_read_time","select appl_name, appl_id, direct_read_time from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

28/*019*/ {"app_direct_reads","select appl_name, appl_id, direct_reads from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

29/*020*/ {"app_direct_write_reqs","select appl_name, appl_id, direct_write_reqs from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

30/*021*/ {"app_direct_write_time","select appl_name, appl_id, direct_write_time from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

31/*022*/ {"app_direct_writes","select appl_name, appl_id, direct_writes from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

32/*023*/ {"app_dynamic_sql_stmts","select appl_name, appl_id, dynamic_sql_stmts from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

33/*024*/ {"app_failed_sql_stmts","select appl_name, appl_id, failed_sql_stmts from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

34/*025*/ {"app_hash_join_overflows","select appl_name, appl_id, hash_join_overflows from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

35/*026*/ {"app_hash_join_small_overflows","select appl_name, appl_id, hash_join_small_overflows from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

36/*027*/ {"app_int_auto_rebinds","select appl_name, appl_id, int_auto_rebinds from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

37/*028*/ {"app_int_commits","select appl_name, appl_id, int_commits from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

38/*029*/ {"app_int_deadlock_rollbacks","select appl_name, appl_id, int_deadlock_rollbacks from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

39/*030*/ {"app_int_rollbacks","select appl_name, appl_id, int_rollbacks from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

40/*031*/ {"app_int_rows_deleted","select appl_name, appl_id, int_rows_deleted from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

41/*032*/ {"app_int_rows_inserted","select appl_name, appl_id, int_rows_inserted from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

42/*033*/ {"app_int_rows_updated","select appl_name, appl_id, int_rows_updated from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

43/*034*/ {"app_lock_escals","select appl_name, appl_id, lock_escals from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

44/*035*/ {"app_lock_timeouts","select appl_name, appl_id, lock_timeouts from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

45/*036*/ {"app_lock_wait_time","select appl_name, appl_id, lock_wait_time from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

46/*037*/ {"app_lock_waits","select appl_name, appl_id, lock_waits from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

47/*038*/ {"app_locklist_util","select appl_name, appl_id, locks_held, locks_held as lock_list from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

48/*039*/ {"app_locks_held","select appl_name, appl_id, locks_held from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

49/*040*/ {"app_num_agents","select appl_name, appl_id, num_agents from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

50/*041*/ {"app_num_assoc_agents","select appl_name, appl_id, num_assoc_agents from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

51/*042*/ {"app_open_loc_curs","select appl_name, appl_id, open_loc_curs from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

52/*043*/ {"app_open_loc_curs_blk","select appl_name, appl_id, open_loc_curs_blk from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

53/*044*/ {"app_open_rem_curs","select appl_name, appl_id, open_rem_curs from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

54/*045*/ {"app_open_rem_curs_blk","select appl_name, appl_id, open_rem_curs_blk from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

55/*046*/ {"app_pct_sort_overflows","select appl_name, appl_id, sort_overflows, total_sorts from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

56/*047*/ {"app_pkg_cache_hit_rto","select appl_name, appl_id, pkg_cache_inserts, pkg_cache_lookups from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

57/*048*/ {"app_pkg_cache_inserts","select appl_name, appl_id, pkg_cache_inserts from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

58/*049*/ {"app_pkg_cache_lookups","select appl_name, appl_id, pkg_cache_lookups from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

59/*050*/ {"app_pool_data_l_reads","select appl_name, appl_id, pool_data_l_reads from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

60/*051*/ {"app_pool_data_p_reads","select appl_name, appl_id, pool_data_p_reads from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

61/*052*/ {"app_pool_data_writes","select appl_name, appl_id, pool_data_writes from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

62/*053*/ {"app_pool_hit_ratio","select appl_name, appl_id, pool_data_p_reads, pool_index_p_reads, pool_data_l_reads, pool_index_l_reads from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

63/*054*/ {"app_pool_index_l_reads","select appl_name, appl_id, pool_index_l_reads from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

64/*055*/ {"app_pool_index_p_reads","select appl_name, appl_id, pool_index_p_reads from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

65/*056*/ {"app_pool_index_writes","select appl_name, appl_id, pool_index_writes from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

66/*057*/ {"app_pool_read_time","select appl_name, appl_id, pool_read_time from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

67/*058*/ {"app_pool_write_time","select appl_name, appl_id, pool_write_time from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

68/*059*/ {"app_rej_curs_blk","select appl_name, appl_id, rej_curs_blk from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

69/*060*/ {"app_rollback_sql_stmts","select appl_name, appl_id, rollback_sql_stmts from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

70/*061*/ {"app_rows_deleted","select appl_name, appl_id, rows_deleted from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

71/*062*/ {"app_rows_inserted","select appl_name, appl_id, rows_inserted from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

72/*063*/ {"app_rows_read","select appl_name, appl_id, rows_read from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

73/*064*/ {"app_rows_selected","select appl_name, appl_id, rows_selected from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

74/*065*/ {"app_rows_updated","select appl_name, appl_id, rows_updated from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

75/*066*/ {"app_rows_written","select appl_name, appl_id, rows_written from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

76/*067*/ {"app_select_sql_stmts","select appl_name, appl_id, select_sql_stmts from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

77/*068*/ {"app_sort_overflows","select appl_name, appl_id, sort_overflows from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

78/*069*/ {"app_static_sql_stmts","select appl_name, appl_id, static_sql_stmts from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

79/*070*/ {"app_sys_cpu_time","select appl_name, appl_id, agent_sys_cpu_time_s, agent_sys_cpu_time_ms from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

80/*071*/ {"app_total_hash_joins","select appl_name, appl_id, total_hash_joins from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

81/*072*/ {"app_total_hash_loops","select appl_name, appl_id, total_hash_loops from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

82/*073*/ {"app_total_sort_time","select appl_name, appl_id, total_sort_time from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

83/*074*/ {"app_total_sorts","select appl_name, appl_id, total_sorts from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

84/*075*/ {"app_uid_sql_stmts","select appl_name, appl_id, uid_sql_stmts from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

85/*076*/ {"app_uow_elapsed_time","select appl_name, appl_id, uow_elapsed_time_s as seconds_uow_elapsed_time, uow_elapsed_time_ms as microsec_uow_elapsed_time from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

86/*077*/ {"app_uow_lock_wait_time","select appl_name, appl_id, uow_lock_wait_time from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

87/*078*/ {"app_uow_log_space_used","select appl_name, appl_id, uow_log_space_used from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

88/*079*/ {"app_usr_cpu_time","select appl_name, appl_id, agent_usr_cpu_time_s as seconds_agent_user_cpu, agent_usr_cpu_time_ms as microsec_agent_user_cpu from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

89/*080*/ {"app_x_lock_escals","select appl_name, appl_id, x_lock_escals from sysibmadm.snapappl inner join sysibmadm.snapappl_info on sysibmadm.snapappl_info.agent_id = sysibmadm.snapappl.agent_id"},

90/*081*/ {"bp_avg_direct_read_time","select bp_name, trim(db_name) as db_name, direct_read_time, direct_reads from sysibmadm.snapbp"},

91/*082*/ {"bp_avg_direct_write_time","select bp_name, trim(db_name) as db_name, direct_write_time, direct_writes from sysibmadm.snapbp"},

92/*083*/ {"bp_direct_read_reqs","select bp_name, trim(db_name) as db_name, direct_read_reqs from sysibmadm.snapbp"},

93/*084*/ {"bp_direct_read_time","select bp_name, trim(db_name) as db_name, direct_read_time from sysibmadm.snapbp"},

94/*085*/ {"bp_direct_reads","select bp_name, trim(db_name) as db_name, direct_reads from sysibmadm.snapbp"},

95/*086*/ {"bp_direct_write_reqs","select bp_name, trim(db_name) as db_name, direct_write_reqs from sysibmadm.snapbp"},

96/*087*/ {"bp_direct_write_time","select bp_name, trim(db_name) as db_name, direct_write_time from sysibmadm.snapbp"},

97/*088*/ {"bp_direct_writes","select bp_name, trim(db_name) as db_name, direct_writes from sysibmadm.snapbp"},

98/*089*/ {"bp_files_closed","select bp_name, trim(db_name) as db_name, files_closed from sysibmadm.snapbp"},

99/*090*/ {"bp_pool_async_data_read_reqs","select bp_name, trim(db_name) as db_name, pool_async_data_read_reqs from sysibmadm.snapbp"},

100/*091*/ {"bp_pool_async_data_reads","select bp_name, trim(db_name) as db_name, pool_async_data_reads from sysibmadm.snapbp"},

101/*092*/ {"bp_pool_async_data_writes","select bp_name, trim(db_name) as db_name, pool_async_data_writes from sysibmadm.snapbp"},

102/*093*/ {"bp_pool_async_index_reads","select bp_name, trim(db_name) as db_name, pool_async_index_reads from sysibmadm.snapbp"},

103/*094*/ {"bp_pool_async_index_writes","select bp_name, trim(db_name) as db_name, pool_async_index_writes from sysibmadm.snapbp"},

104/*095*/ {"bp_pool_async_read_time","select bp_name, trim(db_name) as db_name, pool_async_read_time from sysibmadm.snapbp"},

105/*096*/ {"bp_pool_async_write_time","select bp_name, trim(db_name) as db_name, pool_async_write_time from sysibmadm.snapbp"},

106/*097*/ {"bp_pool_avg_async_read_time","select bp_name, trim(db_name) as db_name, pool_async_read_time, pool_async_data_reads from sysibmadm.snapbp"},

107/*098*/ {"bp_pool_avg_async_write_time","select bp_name, trim(db_name) as db_name, pool_async_write_time, pool_async_data_writes, pool_async_index_writes from sysibmadm.snapbp"},

108/*099*/ {"bp_pool_avg_write_time","select bp_name, trim(db_name) as db_name, pool_async_write_time, pool_async_data_writes, pool_async_index_writes from sysibmadm.snapbp"},

109/*100*/ {"bp_pool_data_from_estore","select bp_name, trim(db_name) as db_name, 0 as pool_data_from_estore from sysibmadm.snapbp"},

110/*101*/ {"bp_pool_data_l_reads","select bp_name, trim(db_name) as db_name, pool_data_l_reads from sysibmadm.snapbp"},

111/*102*/ {"bp_pool_data_p_reads","select bp_name, trim(db_name) as db_name, pool_data_p_reads from sysibmadm.snapbp"},

112/*103*/ {"bp_pool_data_to_estore","select bp_name, trim(db_name) as db_name, 0 as pool_data_to_estore from sysibmadm.snapbp"},

113/*104*/ {"bp_pool_data_writes","select bp_name, trim(db_name) as db_name, pool_data_writes from sysibmadm.snapbp"},

114/*105*/ {"bp_pool_hit_ratio","select bp_name, trim(db_name) as db_name, pool_data_p_reads, pool_index_p_reads, pool_data_l_reads, pool_index_l_reads from sysibmadm.snapbp"},

115/*106*/ {"bp_pool_index_from_estore","select bp_name, trim(db_name) as db_name, 0 as pool_index_from_estore from sysibmadm.snapbp"},

116/*107*/ {"bp_pool_index_l_reads","select bp_name, trim(db_name) as db_name, pool_index_l_reads from sysibmadm.snapbp"},

117/*108*/ {"bp_pool_index_p_reads","select bp_name, trim(db_name) as db_name, pool_index_p_reads from sysibmadm.snapbp"},

118/*109*/ {"bp_pool_index_to_estore","select bp_name, trim(db_name) as db_name, 0 as pool_index_to_estore from sysibmadm.snapbp"},

119/*110*/ {"bp_pool_index_writes","select bp_name, trim(db_name) as db_name, pool_index_writes from sysibmadm.snapbp"},

120/*111*/ {"bp_pool_read_time","select bp_name, trim(db_name) as db_name, pool_read_time from sysibmadm.snapbp"},

121/*112*/ {"bp_pool_sync_idx_reads","select bp_name, trim(db_name) as db_name, pool_index_p_reads, pool_async_index_reads from sysibmadm.snapbp"},

122/*113*/ {"bp_pool_sync_idx_writes","select bp_name, trim(db_name) as db_name, pool_index_writes, pool_async_index_writes from sysibmadm.snapbp"},

123/*114*/ {"bp_pool_sync_reads","select bp_name, trim(db_name) as db_name, pool_data_p_reads, pool_async_data_reads from sysibmadm.snapbp"},

124/*115*/ {"bp_pool_sync_write_time","select bp_name, trim(db_name) as db_name, pool_write_time, pool_async_write_time from sysibmadm.snapbp"},

125/*116*/ {"bp_pool_sync_writes","select bp_name, trim(db_name) as db_name, pool_data_writes, pool_async_data_writes from sysibmadm.snapbp"},

126/*117*/ {"bp_pool_write_time","select bp_name, trim(db_name) as db_name, pool_write_time from sysibmadm.snapbp"},

127/*118*/ {"db_active_sorts","select trim(db_name) as db_name, active_sorts from sysibmadm.snapdb"},

128/*119*/ {"db_agents_top","select trim(db_name) as db_name, agents_top from sysibmadm.snapdb"},

129/*120*/ {"db_appl_section_inserts","select trim(db_name) as db_name, appl_section_inserts from sysibmadm.snapdb"},

130/*121*/ {"db_appl_section_lookups","select trim(db_name) as db_name, appl_section_lookups from sysibmadm.snapdb"},

131/*122*/ {"db_appls_cur_cons","select trim(db_name) as db_name, appls_cur_cons from sysibmadm.snapdb"},

132/*123*/ {"db_appls_in_db2","select trim(db_name) as db_name, appls_in_db2 from sysibmadm.snapdb"},

133/*124*/ {"db_avg_direct_read_time","select trim(db_name) as db_name, direct_read_time, direct_reads from sysibmadm.snapdb"},

134/*125*/ {"db_avg_direct_write_time","select trim(db_name) as db_name, direct_write_time, direct_writes from sysibmadm.snapdb"},

135/*126*/ {"db_avg_sort_heap","select trim(db_name) as db_name, sort_heap_allocated, active_sorts from sysibmadm.snapdb"},

136/*127*/ {"db_avg_sort_time","select trim(db_name) as db_name, total_sort_time, total_sorts from sysibmadm.snapdb"},

137/*128*/ {"db_binds_precompiles","select trim(db_name) as db_name, binds_precompiles from sysibmadm.snapdb"},

138/*129*/ {"db_cat_cache_heap_full","select trim(db_name) as db_name, cat_cache_overflows as cat_cache_heap_full from sysibmadm.snapdb"},

139/*130*/ {"db_cat_cache_hit_rto","select trim(db_name) as db_name, cat_cache_inserts, cat_cache_lookups from sysibmadm.snapdb"},

140/*131*/ {"db_cat_cache_inserts","select trim(db_name) as db_name, cat_cache_inserts from sysibmadm.snapdb"},

141/*132*/ {"db_cat_cache_lookups","select trim(db_name) as db_name, cat_cache_lookups from sysibmadm.snapdb"},

142/*133*/ {"db_cat_cache_overflows","select trim(db_name) as db_name, cat_cache_overflows from sysibmadm.snapdb"},

143/*134*/ {"db_commit_sql_stmts","select trim(db_name) as db_name, commit_sql_stmts from sysibmadm.snapdb"},

144/*135*/ {"db_connect_time","select trim(db_name) as db_name, db_conn_time from sysibmadm.snapdb"},

145/*136*/ {"db_connections_top","select trim(db_name) as db_name, connections_top from sysibmadm.snapdb"},

146/*137*/ {"db_coord_agents_top","select trim(db_name) as db_name, coord_agents_top from sysibmadm.snapdb"},

147/*138*/ {"db_ddl_sql_stmts","select trim(db_name) as db_name, ddl_sql_stmts from sysibmadm.snapdb"},

148/*139*/ {"db_deadlocks","select trim(db_name) as db_name, deadlocks from sysibmadm.snapdb"},

149/*140*/ {"db_direct_read_reqs","select trim(db_name) as db_name, direct_read_reqs from sysibmadm.snapdb"},

150/*141*/ {"db_direct_read_time","select trim(db_name) as db_name, direct_read_time from sysibmadm.snapdb"},

151/*142*/ {"db_direct_reads","select trim(db_name) as db_name, direct_reads from sysibmadm.snapdb"},

152/*143*/ {"db_direct_write_reqs","select trim(db_name) as db_name, direct_write_reqs from sysibmadm.snapdb"},

153/*144*/ {"db_direct_write_time","select trim(db_name) as db_name, direct_write_time from sysibmadm.snapdb"},

154/*145*/ {"db_direct_writes","select trim(db_name) as db_name, direct_writes from sysibmadm.snapdb"},

155/*146*/ {"db_dynamic_sql_stmts","select trim(db_name) as db_name, dynamic_sql_stmts from sysibmadm.snapdb"},

156/*147*/ {"db_failed_sql_stmts","select trim(db_name) as db_name, failed_sql_stmts from sysibmadm.snapdb"},

157/*148*/ {"db_files_closed","select trim(db_name) as db_name, files_closed from sysibmadm.snapdb"},

158/*149*/ {"db_hash_join_overflows","select trim(db_name) as db_name, hash_join_overflows from sysibmadm.snapdb"},

159/*150*/ {"db_hash_join_small_overflows","select trim(db_name) as db_name, hash_join_small_overflows from sysibmadm.snapdb"},

160/*151*/ {"db_heap_top","select trim(db_name) as db_name, pool_watermark as heap_top from sysibmadm.snapdb_memory_pool where pool_id = upper('database')"},

161/*152*/ {"db_int_auto_rebinds","select trim(db_name) as db_name, int_auto_rebinds from sysibmadm.snapdb"},

162/*153*/ {"db_int_commits","select trim(db_name) as db_name, int_commits from sysibmadm.snapdb"},

163/*154*/ {"db_int_deadlock_rollbacks","select trim(db_name) as db_name, int_deadlock_rollbacks from sysibmadm.snapdb"},

164/*155*/ {"db_int_rollbacks","select trim(db_name) as db_name, int_rollbacks from sysibmadm.snapdb"},

165/*156*/ {"db_int_rows_deleted","select trim(db_name) as db_name, int_rows_deleted from sysibmadm.snapdb"},

166/*157*/ {"db_int_rows_inserted","select trim(db_name) as db_name, int_rows_inserted from sysibmadm.snapdb"},

167/*158*/ {"db_int_rows_updated","select trim(db_name) as db_name, int_rows_updated from sysibmadm.snapdb"},

168/*159*/ {"db_lock_escals","select trim(db_name) as db_name, lock_escals from sysibmadm.snapdb"},

169/*160*/ {"db_lock_list_in_use","select trim(db_name) as db_name, lock_list_in_use from sysibmadm.snapdb"},

170/*161*/ {"db_lock_timeouts","select trim(db_name) as db_name, lock_timeouts from sysibmadm.snapdb"},

171/*162*/ {"db_lock_wait_time","select trim(db_name) as db_name, lock_wait_time from sysibmadm.snapdb"},

172/*163*/ {"db_lock_waits","select trim(db_name) as db_name, lock_waits from sysibmadm.snapdb"},

173/*164*/ {"db_locks_held","select trim(db_name) as db_name, locks_held from sysibmadm.snapdb"},

174/*165*/ {"db_locks_waiting","select trim(db_name) as db_name, locks_waiting from sysibmadm.snapdb"},

175/*166*/ {"db_log_reads","select trim(db_name) as db_name, log_reads from sysibmadm.snapdb"},

176/*167*/ {"db_log_util_rto","select trim(db_name) as db_name, total_log_used, total_log_available from sysibmadm.snapdb"},

177/*168*/ {"db_log_writes","select trim(db_name) as db_name, log_writes from sysibmadm.snapdb"},

178/*169*/ {"db_num_assoc_agents","select trim(db_name) as db_name, num_assoc_agents from sysibmadm.snapdb"},

179/*170*/ {"db_pct_hjs_overflows","select trim(db_name) as db_name, hash_join_small_overflows, hash_join_overflows from sysibmadm.snapdb"},

180/*171*/ {"db_pct_sort_overflows","select trim(db_name) as db_name, sort_overflows, total_sorts from sysibmadm.snapdb"},

181/*172*/ {"db_pkg_cache_inserts","select trim(db_name) as db_name, pkg_cache_inserts from sysibmadm.snapdb"},

182/*173*/ {"db_pkg_cache_lookups","select trim(db_name) as db_name, pkg_cache_lookups from sysibmadm.snapdb"},

183/*174*/ {"db_pool_async_data_read_reqs","select trim(db_name) as db_name, pool_async_data_read_reqs from sysibmadm.snapdb"},

184/*175*/ {"db_pool_async_data_reads","select trim(db_name) as db_name, pool_async_data_reads from sysibmadm.snapdb"},

185/*176*/ {"db_pool_async_data_writes","select trim(db_name) as db_name, pool_async_data_writes from sysibmadm.snapdb"},

186/*177*/ {"db_pool_async_index_reads","select trim(db_name) as db_name, pool_async_index_reads from sysibmadm.snapdb"},

187/*178*/ {"db_pool_async_index_writes","select trim(db_name) as db_name, pool_async_index_writes from sysibmadm.snapdb"},

188/*179*/ {"db_pool_async_read_time","select trim(db_name) as db_name, pool_async_read_time from sysibmadm.snapdb"},

189/*180*/ {"db_pool_async_write_time","select trim(db_name) as db_name, pool_async_write_time from sysibmadm.snapdb"},

190/*181*/ {"db_pool_avg_async_read_time","select trim(db_name) as db_name, pool_async_read_time, pool_async_data_reads from sysibmadm.snapdb"},

191/*182*/ {"db_pool_avg_async_write_time","select trim(db_name) as db_name, pool_async_write_time,pool_async_data_writes,pool_async_index_writes from sysibmadm.snapdb"},

192/*183*/ {"db_pool_avg_write_time","select trim(db_name) as db_name, pool_async_write_time,pool_async_data_writes,pool_async_index_writes from sysibmadm.snapdb"},

193/*184*/ {"db_pool_data_from_estore","select trim(db_name) as db_name, 0 as pool_data_from_estore from sysibmadm.snapdb"},

194/*185*/ {"db_pool_data_l_reads","select trim(db_name) as db_name, pool_data_l_reads from sysibmadm.snapdb"},

195/*186*/ {"db_pool_data_p_reads","select trim(db_name) as db_name, pool_data_p_reads from sysibmadm.snapdb"},

196/*187*/ {"db_pool_data_to_estore","select trim(db_name) as db_name, 0 as pool_data_to_estore from sysibmadm.snapdb"},

197/*188*/ {"db_pool_data_writes","select trim(db_name) as db_name, pool_data_writes from sysibmadm.snapdb"},

198/*189*/ {"db_pool_drty_pg_steal_clns","select trim(db_name) as db_name, pool_drty_pg_steal_clns from sysibmadm.snapdb"},

199/*190*/ {"db_pool_drty_pg_thrsh_clns","select trim(db_name) as db_name, pool_drty_pg_thrsh_clns from sysibmadm.snapdb"},

200/*191*/ {"db_pool_hit_ratio","select trim(db_name) as db_name, pool_data_p_reads,pool_index_p_reads,pool_data_l_reads,pool_index_l_reads from sysibmadm.snapdb"},

201/*192*/ {"db_pool_index_from_estore","select trim(db_name) as db_name, 0 as pool_index_from_estore from sysibmadm.snapdb"},

202/*193*/ {"db_pool_index_l_reads","select trim(db_name) as db_name, pool_index_l_reads from sysibmadm.snapdb"},

203/*194*/ {"db_pool_index_p_reads","select trim(db_name) as db_name, pool_index_p_reads from sysibmadm.snapdb"},

204/*195*/ {"db_pool_index_to_estore","select trim(db_name) as db_name, 0 as pool_index_to_estore from sysibmadm.snapdb"},

205/*196*/ {"db_pool_index_writes","select trim(db_name) as db_name, pool_index_writes from sysibmadm.snapdb"},

206/*197*/ {"db_pool_lsn_gap_clns","select trim(db_name) as db_name, pool_lsn_gap_clns from sysibmadm.snapdb"},

207/*198*/ {"db_pool_read_time","select trim(db_name) as db_name, pool_read_time from sysibmadm.snapdb"},

208/*199*/ {"db_pool_sync_idx_reads","select trim(db_name) as db_name, pool_index_p_reads, pool_async_index_reads from sysibmadm.snapdb"},

209/*200*/ {"db_pool_sync_idx_writes","select trim(db_name) as db_name, pool_index_writes, pool_async_index_writes from sysibmadm.snapdb"},

210/*201*/ {"db_pool_sync_reads","select trim(db_name) as db_name, pool_data_p_reads, pool_async_data_reads from sysibmadm.snapdb"},

211/*202*/ {"db_pool_sync_write_time","select trim(db_name) as db_name, pool_write_time, pool_async_write_time from sysibmadm.snapdb"},

212/*203*/ {"db_pool_sync_writes","select trim(db_name) as db_name, pool_data_writes, pool_async_data_writes from sysibmadm.snapdb"},

213/*204*/ {"db_pool_write_time","select trim(db_name) as db_name, pool_write_time from sysibmadm.snapdb"},

214/*205*/ {"db_prefetch_wait_time","select trim(db_name) as db_name, prefetch_wait_time from sysibmadm.snapdb"},

215/*206*/ {"db_rollback_sql_stmts","select trim(db_name) as db_name, rollback_sql_stmts from sysibmadm.snapdb"},

216/*207*/ {"db_rows_deleted","select trim(db_name) as db_name, rows_deleted from sysibmadm.snapdb"},

217/*208*/ {"db_rows_inserted","select trim(db_name) as db_name, rows_inserted from sysibmadm.snapdb"},

218/*209*/ {"db_rows_selected","select trim(db_name) as db_name, rows_selected from sysibmadm.snapdb"},

219/*210*/ {"db_rows_updated","select trim(db_name) as db_name, rows_updated from sysibmadm.snapdb"},

220/*211*/ {"db_sec_log_used_top","select trim(db_name) as db_name, sec_log_used_top from sysibmadm.snapdb"},

221/*212*/ {"db_sec_logs_allocated","select trim(db_name) as db_name, sec_logs_allocated from sysibmadm.snapdb"},

222/*213*/ {"db_select_sql_stmts","select trim(db_name) as db_name, select_sql_stmts from sysibmadm.snapdb"},

223/*214*/ {"db_since_last_backup","select trim(db_name) as db_name, last_backup, elapsed_exec_time_s as seconds_last_backup, elapsed_exec_time_ms as microsec_last_backup from sysibmadm.snapdb"},

224/*215*/ {"db_sort_heap_allocated","select trim(db_name) as db_name, sort_heap_allocated from sysibmadm.snapdb"},

225/*216*/ {"db_sort_overflows","select trim(db_name) as db_name, sort_overflows from sysibmadm.snapdb"},

226/*217*/ {"db_static_sql_stmts","select trim(db_name) as db_name, static_sql_stmts from sysibmadm.snapdb"},

227/*218*/ {"db_status","select trim(db_name) as db_name, db_status from sysibmadm.snapdb"},

228/*219*/ {"db_tot_log_used_top","select trim(db_name) as db_name, tot_log_used_top from sysibmadm.snapdb"},

229/*220*/ {"db_total_cons","select trim(db_name) as db_name, total_cons from sysibmadm.snapdb"},

230/*221*/ {"db_total_hash_joins","select trim(db_name) as db_name, total_hash_joins from sysibmadm.snapdb"},

231/*222*/ {"db_total_hash_loops","select trim(db_name) as db_name, total_hash_loops from sysibmadm.snapdb"},

232/*223*/ {"db_total_sec_cons","select trim(db_name) as db_name, total_sec_cons from sysibmadm.snapdb"},

233/*224*/ {"db_total_sort_time","select trim(db_name) as db_name, total_sort_time from sysibmadm.snapdb"},

234/*225*/ {"db_total_sorts","select trim(db_name) as db_name, total_sorts from sysibmadm.snapdb"},

235/*226*/ {"db_uid_sql_stmts","select trim(db_name) as db_name, uid_sql_stmts from sysibmadm.snapdb"},

236/*227*/ {"db_x_lock_escals","select trim(db_name) as db_name, x_lock_escals from sysibmadm.snapdb"},

237/*228*/ {"i_agents_created_empty_pool","select agents_created_empty_pool from sysibmadm.snapdbm"},

238/*229*/ {"i_agents_created_ratio","select agents_created_empty_pool, agents_from_pool from sysibmadm.snapdbm"},

239/*230*/ {"i_agents_from_pool","select agents_from_pool from sysibmadm.snapdbm"},

240/*231*/ {"i_agents_registered","select agents_registered from sysibmadm.snapdbm"},

241/*232*/ {"i_agents_registered_top","select agents_registered_top from sysibmadm.snapdbm"},

242/*233*/ {"i_agents_stolen","select agents_stolen from sysibmadm.snapdbm"},

243/*234*/ {"i_agents_waiting_on_token","select agents_waiting_on_token from sysibmadm.snapdbm"},

244/*235*/ {"i_agents_waiting_top","select agents_waiting_top from sysibmadm.snapdbm"},

245/*236*/ {"i_check_dbalive","select trim(db_name) as db_name, db_status as status from sysibmadm.snapdb"},

246/*237*/ {"i_comm_private_mem","select comm_private_mem from sysibmadm.snapdbm"},

247/*238*/ {"i_con_local_dbases","select con_local_dbases from sysibmadm.snapdbm"},

248/*239*/ {"i_coord_agents_top","select coord_agents_top from sysibmadm.snapdbm"},

249/*240*/ {"i_gw_cons_wait_client","select gw_cons_wait_client from sysibmadm.snapdbm"},

250/*241*/ {"i_gw_cons_wait_host","select gw_cons_wait_host from sysibmadm.snapdbm"},

251/*242*/ {"i_gw_cur_cons","select gw_cur_cons from sysibmadm.snapdbm"},

252/*243*/ {"i_gw_total_cons","select gw_total_cons from sysibmadm.snapdbm"},

253/*244*/ {"i_idle_agents","select idle_agents from sysibmadm.snapdbm"},

254/*245*/ {"i_local_cons","select local_cons from sysibmadm.snapdbm"},

255/*246*/ {"i_local_cons_in_exec","select local_cons_in_exec from sysibmadm.snapdbm"},

256/*247*/ {"i_max_agent_overflows","select max_agent_overflows from sysibmadm.snapdbm"},

257/*248*/ {"i_pct_active_connections","select connections_top,rem_cons_in,rem_cons_in_exec,local_cons,local_cons_in_exec from sysibmadm.snapdb,sysibmadm.snapdbm"},

258/*249*/ {"i_piped_sorts_accepted","select piped_sorts_accepted from sysibmadm.snapdbm"},

259/*250*/ {"i_piped_sorts_rejected","select piped_sorts_requested, piped_sorts_accepted from sysibmadm.snapdbm"},

260/*251*/ {"i_piped_sorts_requested","select piped_sorts_requested from sysibmadm.snapdbm"},

261/*252*/ {"i_post_threshold_hash_joins","select post_threshold_hash_joins from sysibmadm.snapdbm"},

262/*253*/ {"i_post_threshold_sorts","select post_threshold_sorts from sysibmadm.snapdbm"},

263/*254*/ {"i_rem_cons_in","select rem_cons_in from sysibmadm.snapdbm"},

264/*255*/ {"i_rem_cons_in_exec","select rem_cons_in_exec from sysibmadm.snapdbm"},

265/*256*/ {"i_sort_heap_allocated","select sort_heap_allocated from sysibmadm.snapdbm"},

266/*257*/ {"ts_free_pages","select current server db_name, tbsp_name, tbsp_type, tbsp_free_pages from sysibmadm.tbsp_utilization"},

267/*258*/ {"ts_free_pages_pct","select current server db_name, tbsp_name, tbsp_type, tbsp_free_pages, tbsp_total_pages from sysibmadm.tbsp_utilization"},

268/*259*/ {"ts_max_used_pages","select current server db_name, tbsp_name, tbsp_type, tbsp_used_pages from sysibmadm.tbsp_utilization"},

269/*260*/ {"ts_max_used_pages_pct","select current server db_name, tbsp_name, tbsp_type, tbsp_used_pages, tbsp_total_pages from sysibmadm.tbsp_utilization"},

270/*261*/ {"ts_status","select current server db_name, tbsp_name, tbsp_type, tbsp_state from sysibmadm.tbsp_utilization"},

271/*262*/ {"ts_total_pages","select current server db_name, tbsp_name, tbsp_type, tbsp_total_pages from sysibmadm.tbsp_utilization"},

272/*263*/ {"ts_usable_pages","select current server db_name, tbsp_name, tbsp_type, tbsp_usable_pages from sysibmadm.tbsp_utilization"},

273/*264*/ {"ts_usable_pages_pct","select current server db_name, tbsp_name, tbsp_type, tbsp_total_pages, tbsp_usable_pages from sysibmadm.tbsp_utilization"},

274/*265*/ {"ts_used_pages","select current server db_name, tbsp_name, tbsp_type, tbsp_used_pages from sysibmadm.tbsp_utilization"},

275/*266*/ {"ts_used_pages_pct","select current server db_name, tbsp_name, tbsp_type, tbsp_total_pages, tbsp_used_pages from sysibmadm.tbsp_utilization"},

276/*267*/ {"ts_data_partitioning","select current server db_name, trim(substr(tbsp_name,1,20)) tbsp_name, tbsp_type, dbpartitionnum, int((tbsp_total_size_kb)/1024) as total_allocated_mb, int((tbsp_used_size_kb)/1024) as total_used_mb, int((tbsp_free_size_kb)/1024) as free_space_mb, int(100-tbsp_utilization_percent) as percent_free from sysibmadm.tbsp_utilization order by 7 asc"},

277/*268*/ {"hadr_dbrole","SELECT SUBSTR(DB_NAME, 1, 8) AS db_name, hadr_role FROM TABLE (SNAP_GET_HADR (CAST (NULL as VARCHAR(128)), 0)) as T"},

278/*269*/ {"hadr_connect_status","SELECT SUBSTR(DB_NAME, 1, 8) AS db_name, hadr_connect_status FROM TABLE (SNAP_GET_HADR (CAST (NULL as VARCHAR(128)), 0)) as T"},

279/*270*/ {"db_partition_status","select trim(db_name) as db_name, dbpartitionnum as db_partition_num, db_status as database_status from sysibmadm.snapdb"},

Additional Information:

A file with the list of queries has also been attached to this article.

db2 (DB2 Database Monitoring) Help doc:

https://docops.ca.com/ca-unified-infrastructure-management-probes/en/alphabetical-probe-articles/db2-db2-database-monitoring

File Attachments:
TEC1345801.zip