Queries used db2 probe in DX UIM
Article ID: 12720
Updated On:
Products
DX Unified Infrastructure Management (Nimsoft / UIM)
CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
CA Unified Infrastructure Management SaaS (Nimsoft / UIM)
Issue/Introduction
This Article includes a list of the underlying DB2 database queries used to monitor DB2 databases/instances. This list may be helpful for DX UIM administrators and/or DB2 DBAs in understanding how the probe is querying the DB2 database to collect the data.
What queries are used by the db2 probe?
DB2 probe checkpoint queries
Environment
- DX UIM 23.4.*
- db2 probe v4.31 (4.31T1)
Cause
Guidance
Resolution
/*000*/ {"generic_query", "select snapshot_timestamp, server_platform from sysibmadm.snapdb"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*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"},/*081*/ {"bp_avg_direct_read_time", "select bp_name, trim(db_name) as db_name, direct_read_time, direct_reads from sysibmadm.snapbp"},/*082*/ {"bp_avg_direct_write_time", "select bp_name, trim(db_name) as db_name, direct_write_time, direct_writes from sysibmadm.snapbp"},/*083*/ {"bp_direct_read_reqs", "select bp_name, trim(db_name) as db_name, direct_read_reqs from sysibmadm.snapbp"},/*084*/ {"bp_direct_read_time", "select bp_name, trim(db_name) as db_name, direct_read_time from sysibmadm.snapbp"},/*085*/ {"bp_direct_reads", "select bp_name, trim(db_name) as db_name, direct_reads from sysibmadm.snapbp"},/*086*/ {"bp_direct_write_reqs", "select bp_name, trim(db_name) as db_name, direct_write_reqs from sysibmadm.snapbp"},/*087*/ {"bp_direct_write_time", "select bp_name, trim(db_name) as db_name, direct_write_time from sysibmadm.snapbp"},/*088*/ {"bp_direct_writes", "select bp_name, trim(db_name) as db_name, direct_writes from sysibmadm.snapbp"},/*089*/ {"bp_files_closed", "select bp_name, trim(db_name) as db_name, files_closed from sysibmadm.snapbp"},/*090*/ {"bp_pool_async_data_read_reqs", "select bp_name, trim(db_name) as db_name, pool_async_data_read_reqs from sysibmadm.snapbp"},/*091*/ {"bp_pool_async_data_reads", "select bp_name, trim(db_name) as db_name, pool_async_data_reads from sysibmadm.snapbp"},/*092*/ {"bp_pool_async_data_writes", "select bp_name, trim(db_name) as db_name, pool_async_data_writes from sysibmadm.snapbp"},/*093*/ {"bp_pool_async_index_reads", "select bp_name, trim(db_name) as db_name, pool_async_index_reads from sysibmadm.snapbp"},/*094*/ {"bp_pool_async_index_writes", "select bp_name, trim(db_name) as db_name, pool_async_index_writes from sysibmadm.snapbp"},/*095*/ {"bp_pool_async_read_time", "select bp_name, trim(db_name) as db_name, pool_async_read_time from sysibmadm.snapbp"},/*096*/ {"bp_pool_async_write_time", "select bp_name, trim(db_name) as db_name, pool_async_write_time from sysibmadm.snapbp"},/*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"},/*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"},/*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"},/*100*/ {"bp_pool_data_from_estore", "select bp_name, trim(db_name) as db_name, 0 as pool_data_from_estore from sysibmadm.snapbp"},/*101*/ {"bp_pool_data_l_reads", "select bp_name, trim(db_name) as db_name, pool_data_l_reads from sysibmadm.snapbp"},/*102*/ {"bp_pool_data_p_reads", "select bp_name, trim(db_name) as db_name, pool_data_p_reads from sysibmadm.snapbp"},/*103*/ {"bp_pool_data_to_estore", "select bp_name, trim(db_name) as db_name, 0 as pool_data_to_estore from sysibmadm.snapbp"},/*104*/ {"bp_pool_data_writes", "select bp_name, trim(db_name) as db_name, pool_data_writes from sysibmadm.snapbp"},/*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"},/*106*/ {"bp_pool_index_from_estore", "select bp_name, trim(db_name) as db_name, 0 as pool_index_from_estore from sysibmadm.snapbp"},/*107*/ {"bp_pool_index_l_reads", "select bp_name, trim(db_name) as db_name, pool_index_l_reads from sysibmadm.snapbp"},/*108*/ {"bp_pool_index_p_reads", "select bp_name, trim(db_name) as db_name, pool_index_p_reads from sysibmadm.snapbp"},/*109*/ {"bp_pool_index_to_estore", "select bp_name, trim(db_name) as db_name, 0 as pool_index_to_estore from sysibmadm.snapbp"},/*110*/ {"bp_pool_index_writes", "select bp_name, trim(db_name) as db_name, pool_index_writes from sysibmadm.snapbp"},/*111*/ {"bp_pool_read_time", "select bp_name, trim(db_name) as db_name, pool_read_time from sysibmadm.snapbp"},/*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"},/*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"},/*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"},/*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"},/*116*/ {"bp_pool_sync_writes", "select bp_name, trim(db_name) as db_name, pool_data_writes, pool_async_data_writes from sysibmadm.snapbp"},/*117*/ {"bp_pool_write_time", "select bp_name, trim(db_name) as db_name, pool_write_time from sysibmadm.snapbp"},/*118*/ {"db_active_sorts", "select trim(db_name) as db_name, active_sorts from sysibmadm.snapdb"},/*119*/ {"db_agents_top", "select trim(db_name) as db_name, agents_top from sysibmadm.snapdb"},/*120*/ {"db_appl_section_inserts", "select trim(db_name) as db_name, appl_section_inserts from sysibmadm.snapdb"},/*121*/ {"db_appl_section_lookups", "select trim(db_name) as db_name, appl_section_lookups from sysibmadm.snapdb"},/*122*/ {"db_appls_cur_cons", "select trim(db_name) as db_name, appls_cur_cons from sysibmadm.snapdb"},/*123*/ {"db_appls_in_db2", "select trim(db_name) as db_name, appls_in_db2 from sysibmadm.snapdb"},/*124*/ {"db_avg_direct_read_time", "select trim(db_name) as db_name, direct_read_time, direct_reads from sysibmadm.snapdb"},/*125*/ {"db_avg_direct_write_time", "select trim(db_name) as db_name, direct_write_time, direct_writes from sysibmadm.snapdb"},/*126*/ {"db_avg_sort_heap", "select trim(db_name) as db_name, sort_heap_allocated, active_sorts from sysibmadm.snapdb"},/*127*/ {"db_avg_sort_time", "select trim(db_name) as db_name, total_sort_time, total_sorts from sysibmadm.snapdb"},/*128*/ {"db_binds_precompiles", "select trim(db_name) as db_name, binds_precompiles from sysibmadm.snapdb"},/*129*/ {"db_cat_cache_heap_full", "select trim(db_name) as db_name, cat_cache_overflows as cat_cache_heap_full from sysibmadm.snapdb"},/*130*/ {"db_cat_cache_hit_rto", "select trim(db_name) as db_name, cat_cache_inserts, cat_cache_lookups from sysibmadm.snapdb"},/*131*/ {"db_cat_cache_inserts", "select trim(db_name) as db_name, cat_cache_inserts from sysibmadm.snapdb"},/*132*/ {"db_cat_cache_lookups", "select trim(db_name) as db_name, cat_cache_lookups from sysibmadm.snapdb"},/*133*/ {"db_cat_cache_overflows", "select trim(db_name) as db_name, cat_cache_overflows from sysibmadm.snapdb"},/*134*/ {"db_commit_sql_stmts", "select trim(db_name) as db_name, commit_sql_stmts from sysibmadm.snapdb"},/*135*/ {"db_connect_time", "select trim(db_name) as db_name, TIMESTAMPDIFF(2, CHAR(CURRENT_TIMESTAMP - db_conn_time)) as db_conn_time from sysibmadm.snapdb"}, /* DE277790SC 00604879 | DB2: qos "QOS_DB2_DB_CONNECT_TIME" not generated *//*136*/ {"db_connections_top", "select trim(db_name) as db_name, connections_top from sysibmadm.snapdb"},/*137*/ {"db_coord_agents_top", "select trim(db_name) as db_name, coord_agents_top from sysibmadm.snapdb"},/*138*/ {"db_ddl_sql_stmts", "select trim(db_name) as db_name, ddl_sql_stmts from sysibmadm.snapdb"},/*139*/ {"db_deadlocks", "select trim(db_name) as db_name, deadlocks from sysibmadm.snapdb"},/*140*/ {"db_direct_read_reqs", "select trim(db_name) as db_name, direct_read_reqs from sysibmadm.snapdb"},/*141*/ {"db_direct_read_time", "select trim(db_name) as db_name, direct_read_time from sysibmadm.snapdb"},/*142*/ {"db_direct_reads", "select trim(db_name) as db_name, direct_reads from sysibmadm.snapdb"},/*143*/ {"db_direct_write_reqs", "select trim(db_name) as db_name, direct_write_reqs from sysibmadm.snapdb"},/*144*/ {"db_direct_write_time", "select trim(db_name) as db_name, direct_write_time from sysibmadm.snapdb"},/*145*/ {"db_direct_writes", "select trim(db_name) as db_name, direct_writes from sysibmadm.snapdb"},/*146*/ {"db_dynamic_sql_stmts", "select trim(db_name) as db_name, dynamic_sql_stmts from sysibmadm.snapdb"},/*147*/ {"db_failed_sql_stmts", "select trim(db_name) as db_name, failed_sql_stmts from sysibmadm.snapdb"},/*148*/ {"db_files_closed", "select trim(db_name) as db_name, files_closed from sysibmadm.snapdb"},/*149*/ {"db_hash_join_overflows", "select trim(db_name) as db_name, hash_join_overflows from sysibmadm.snapdb"},/*150*/ {"db_hash_join_small_overflows", "select trim(db_name) as db_name, hash_join_small_overflows from sysibmadm.snapdb"},/*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')"},/*152*/ {"db_int_auto_rebinds", "select trim(db_name) as db_name, int_auto_rebinds from sysibmadm.snapdb"},/*153*/ {"db_int_commits", "select trim(db_name) as db_name, int_commits from sysibmadm.snapdb"},/*154*/ {"db_int_deadlock_rollbacks", "select trim(db_name) as db_name, int_deadlock_rollbacks from sysibmadm.snapdb"},/*155*/ {"db_int_rollbacks", "select trim(db_name) as db_name, int_rollbacks from sysibmadm.snapdb"},/*156*/ {"db_int_rows_deleted", "select trim(db_name) as db_name, int_rows_deleted from sysibmadm.snapdb"},/*157*/ {"db_int_rows_inserted", "select trim(db_name) as db_name, int_rows_inserted from sysibmadm.snapdb"},/*158*/ {"db_int_rows_updated", "select trim(db_name) as db_name, int_rows_updated from sysibmadm.snapdb"},/*159*/ {"db_lock_escals", "select trim(db_name) as db_name, lock_escals from sysibmadm.snapdb"},/*160*/ {"db_lock_list_in_use", "select trim(db_name) as db_name, lock_list_in_use from sysibmadm.snapdb"},/*161*/ {"db_lock_timeouts", "select trim(db_name) as db_name, lock_timeouts from sysibmadm.snapdb"},/*162*/ {"db_lock_wait_time", "select trim(db_name) as db_name, lock_wait_time from sysibmadm.snapdb"},/*163*/ {"db_lock_waits", "select trim(db_name) as db_name, lock_waits from sysibmadm.snapdb"},/*164*/ {"db_locks_held", "select trim(db_name) as db_name, locks_held from sysibmadm.snapdb"},/*165*/ {"db_locks_waiting", "select trim(db_name) as db_name, locks_waiting from sysibmadm.snapdb"},/*166*/ {"db_log_reads", "select trim(db_name) as db_name, log_reads from sysibmadm.snapdb"},/*167*/ {"db_log_util_rto", "select trim(db_name) as db_name, total_log_used, total_log_available from sysibmadm.snapdb"},/*168*/ {"db_log_writes", "select trim(db_name) as db_name, log_writes from sysibmadm.snapdb"},/*169*/ {"db_num_assoc_agents", "select trim(db_name) as db_name, num_assoc_agents from sysibmadm.snapdb"},/*170*/ {"db_pct_hjs_overflows", "select trim(db_name) as db_name, hash_join_small_overflows, hash_join_overflows from sysibmadm.snapdb"},/*171*/ {"db_pct_sort_overflows", "select trim(db_name) as db_name, sort_overflows, total_sorts from sysibmadm.snapdb"},/*172*/ {"db_pkg_cache_inserts", "select trim(db_name) as db_name, pkg_cache_inserts from sysibmadm.snapdb"},/*173*/ {"db_pkg_cache_lookups", "select trim(db_name) as db_name, pkg_cache_lookups from sysibmadm.snapdb"},/*174*/ {"db_pool_async_data_read_reqs", "select trim(db_name) as db_name, pool_async_data_read_reqs from sysibmadm.snapdb"},/*175*/ {"db_pool_async_data_reads", "select trim(db_name) as db_name, pool_async_data_reads from sysibmadm.snapdb"},/*176*/ {"db_pool_async_data_writes", "select trim(db_name) as db_name, pool_async_data_writes from sysibmadm.snapdb"},/*177*/ {"db_pool_async_index_reads", "select trim(db_name) as db_name, pool_async_index_reads from sysibmadm.snapdb"},/*178*/ {"db_pool_async_index_writes", "select trim(db_name) as db_name, pool_async_index_writes from sysibmadm.snapdb"},/*179*/ {"db_pool_async_read_time", "select trim(db_name) as db_name, pool_async_read_time from sysibmadm.snapdb"},/*180*/ {"db_pool_async_write_time", "select trim(db_name) as db_name, pool_async_write_time from sysibmadm.snapdb"},/*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"},/*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"},/*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"},/*184*/ {"db_pool_data_from_estore", "select trim(db_name) as db_name, 0 as pool_data_from_estore from sysibmadm.snapdb"},/*185*/ {"db_pool_data_l_reads", "select trim(db_name) as db_name, pool_data_l_reads from sysibmadm.snapdb"},/*186*/ {"db_pool_data_p_reads", "select trim(db_name) as db_name, pool_data_p_reads from sysibmadm.snapdb"},/*187*/ {"db_pool_data_to_estore", "select trim(db_name) as db_name, 0 as pool_data_to_estore from sysibmadm.snapdb"},/*188*/ {"db_pool_data_writes", "select trim(db_name) as db_name, pool_data_writes from sysibmadm.snapdb"},/*189*/ {"db_pool_drty_pg_steal_clns", "select trim(db_name) as db_name, pool_drty_pg_steal_clns from sysibmadm.snapdb"},/*190*/ {"db_pool_drty_pg_thrsh_clns", "select trim(db_name) as db_name, pool_drty_pg_thrsh_clns from sysibmadm.snapdb"},/*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"},/*192*/ {"db_pool_index_from_estore", "select trim(db_name) as db_name, 0 as pool_index_from_estore from sysibmadm.snapdb"},/*193*/ {"db_pool_index_l_reads", "select trim(db_name) as db_name, pool_index_l_reads from sysibmadm.snapdb"},/*194*/ {"db_pool_index_p_reads", "select trim(db_name) as db_name, pool_index_p_reads from sysibmadm.snapdb"},/*195*/ {"db_pool_index_to_estore", "select trim(db_name) as db_name, 0 as pool_index_to_estore from sysibmadm.snapdb"},/*196*/ {"db_pool_index_writes", "select trim(db_name) as db_name, pool_index_writes from sysibmadm.snapdb"},/*197*/ {"db_pool_lsn_gap_clns", "select trim(db_name) as db_name, pool_lsn_gap_clns from sysibmadm.snapdb"},/*198*/ {"db_pool_read_time", "select trim(db_name) as db_name, pool_read_time from sysibmadm.snapdb"},/*199*/ {"db_pool_sync_idx_reads", "select trim(db_name) as db_name, pool_index_p_reads, pool_async_index_reads from sysibmadm.snapdb"},/*200*/ {"db_pool_sync_idx_writes", "select trim(db_name) as db_name, pool_index_writes, pool_async_index_writes from sysibmadm.snapdb"},/*201*/ {"db_pool_sync_reads", "select trim(db_name) as db_name, pool_data_p_reads, pool_async_data_reads from sysibmadm.snapdb"},/*202*/ {"db_pool_sync_write_time", "select trim(db_name) as db_name, pool_write_time, pool_async_write_time from sysibmadm.snapdb"},/*203*/ {"db_pool_sync_writes", "select trim(db_name) as db_name, pool_data_writes, pool_async_data_writes from sysibmadm.snapdb"},/*204*/ {"db_pool_write_time", "select trim(db_name) as db_name, pool_write_time from sysibmadm.snapdb"},/*205*/ {"db_prefetch_wait_time", "select trim(db_name) as db_name, prefetch_wait_time from sysibmadm.snapdb"},/*206*/ {"db_rollback_sql_stmts", "select trim(db_name) as db_name, rollback_sql_stmts from sysibmadm.snapdb"},/*207*/ {"db_rows_deleted", "select trim(db_name) as db_name, rows_deleted from sysibmadm.snapdb"},/*208*/ {"db_rows_inserted", "select trim(db_name) as db_name, rows_inserted from sysibmadm.snapdb"},/*209*/ {"db_rows_selected", "select trim(db_name) as db_name, rows_selected from sysibmadm.snapdb"},/*210*/ {"db_rows_updated", "select trim(db_name) as db_name, rows_updated from sysibmadm.snapdb"},/*211*/ {"db_sec_log_used_top", "select trim(db_name) as db_name, sec_log_used_top from sysibmadm.snapdb"},/*212*/ {"db_sec_logs_allocated", "select trim(db_name) as db_name, sec_logs_allocated from sysibmadm.snapdb"},/*213*/ {"db_select_sql_stmts", "select trim(db_name) as db_name, select_sql_stmts from sysibmadm.snapdb"},/*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 , timestampdiff(2, char(timestamp(CURRENT TIMESTAMP)- timestamp(last_backup))) as diff from sysibmadm.snapdb,sysibm.sysdummy1"},/*215*/ {"db_sort_heap_allocated", "select trim(db_name) as db_name, sort_heap_allocated from sysibmadm.snapdb"},/*216*/ {"db_sort_overflows", "select trim(db_name) as db_name, sort_overflows from sysibmadm.snapdb"},/*217*/ {"db_static_sql_stmts", "select trim(db_name) as db_name, static_sql_stmts from sysibmadm.snapdb"},/*218*/ {"db_status", "select trim(db_name) as db_name, db_status from sysibmadm.snapdb"},/*219*/ {"db_tot_log_used_top", "select trim(db_name) as db_name, tot_log_used_top from sysibmadm.snapdb"},/*220*/ {"db_total_cons", "select trim(db_name) as db_name, total_cons from sysibmadm.snapdb"},/*221*/ {"db_total_hash_joins", "select trim(db_name) as db_name, total_hash_joins from sysibmadm.snapdb"},/*222*/ {"db_total_hash_loops", "select trim(db_name) as db_name, total_hash_loops from sysibmadm.snapdb"},/*223*/ {"db_total_sec_cons", "select trim(db_name) as db_name, total_sec_cons from sysibmadm.snapdb"},/*224*/ {"db_total_sort_time", "select trim(db_name) as db_name, total_sort_time from sysibmadm.snapdb"},/*225*/ {"db_total_sorts", "select trim(db_name) as db_name, total_sorts from sysibmadm.snapdb"},/*226*/ {"db_uid_sql_stmts", "select trim(db_name) as db_name, uid_sql_stmts from sysibmadm.snapdb"},/*227*/ {"db_x_lock_escals", "select trim(db_name) as db_name, x_lock_escals from sysibmadm.snapdb"},/*228*/ {"i_agents_created_empty_pool", "select agents_created_empty_pool from sysibmadm.snapdbm"},/*229*/ {"i_agents_created_ratio", "select agents_created_empty_pool, agents_from_pool from sysibmadm.snapdbm"},/*230*/ {"i_agents_from_pool", "select agents_from_pool from sysibmadm.snapdbm"},/*231*/ {"i_agents_registered", "select agents_registered from sysibmadm.snapdbm"},/*232*/ {"i_agents_registered_top", "select agents_registered_top from sysibmadm.snapdbm"},/*233*/ {"i_agents_stolen", "select agents_stolen from sysibmadm.snapdbm"},/*234*/ {"i_agents_waiting_on_token", "select agents_waiting_on_token from sysibmadm.snapdbm"},/*235*/ {"i_agents_waiting_top", "select agents_waiting_top from sysibmadm.snapdbm"},/*236*/ {"i_check_dbalive", "select trim(db_name) as db_name, db_status as status from sysibmadm.snapdb"},/*237*/ {"i_comm_private_mem", "select comm_private_mem from sysibmadm.snapdbm"},/*238*/ {"i_con_local_dbases", "select con_local_dbases from sysibmadm.snapdbm"},/*239*/ {"i_coord_agents_top", "select coord_agents_top from sysibmadm.snapdbm"},/*240*/ {"i_gw_cons_wait_client", "select gw_cons_wait_client from sysibmadm.snapdbm"},/*241*/ {"i_gw_cons_wait_host", "select gw_cons_wait_host from sysibmadm.snapdbm"},/*242*/ {"i_gw_cur_cons", "select gw_cur_cons from sysibmadm.snapdbm"},/*243*/ {"i_gw_total_cons", "select gw_total_cons from sysibmadm.snapdbm"},/*244*/ {"i_idle_agents", "select idle_agents from sysibmadm.snapdbm"},/*245*/ {"i_local_cons", "select local_cons from sysibmadm.snapdbm"},/*246*/ {"i_local_cons_in_exec", "select local_cons_in_exec from sysibmadm.snapdbm"},/*247*/ {"i_max_agent_overflows", "select max_agent_overflows from sysibmadm.snapdbm"},/*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"},/*249*/ {"i_piped_sorts_accepted", "select piped_sorts_accepted from sysibmadm.snapdbm"},/*250*/ {"i_piped_sorts_rejected", "select piped_sorts_requested, piped_sorts_accepted from sysibmadm.snapdbm"},/*251*/ {"i_piped_sorts_requested", "select piped_sorts_requested from sysibmadm.snapdbm"},/*252*/ {"i_post_threshold_hash_joins", "select post_threshold_hash_joins from sysibmadm.snapdbm"},/*253*/ {"i_post_threshold_sorts", "select post_threshold_sorts from sysibmadm.snapdbm"},/*254*/ {"i_rem_cons_in", "select rem_cons_in from sysibmadm.snapdbm"},/*255*/ {"i_rem_cons_in_exec", "select rem_cons_in_exec from sysibmadm.snapdbm"},/*256*/ {"i_sort_heap_allocated", "select sort_heap_allocated from sysibmadm.snapdbm"},/*257*/ {"ts_free_pages", "select current server db_name, tbsp_name, tbsp_type, tbsp_free_pages from sysibmadm.tbsp_utilization"},/*258*/ {"ts_free_pages_pct", "select current server db_name, tbsp_name, tbsp_type, tbsp_free_pages, tbsp_total_pages from sysibmadm.tbsp_utilization"},/*259*/ {"ts_max_used_pages", "select current server db_name, tbsp_name, tbsp_type, tbsp_used_pages from sysibmadm.tbsp_utilization"},/*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"},/*261*/ {"ts_status", "select current server db_name, tbsp_name, tbsp_type, tbsp_state from sysibmadm.tbsp_utilization"},/*262*/ {"ts_total_pages", "select current server db_name, tbsp_name, tbsp_type, tbsp_total_pages from sysibmadm.tbsp_utilization"},/*263*/ {"ts_usable_pages", "select current server db_name, tbsp_name, tbsp_type, tbsp_usable_pages from sysibmadm.tbsp_utilization"},/*264*/ {"ts_usable_pages_pct", "select current server db_name, tbsp_name, tbsp_type, tbsp_total_pages, tbsp_usable_pages from sysibmadm.tbsp_utilization"},/*265*/ {"ts_used_pages", "select current server db_name, tbsp_name, tbsp_type, tbsp_used_pages from sysibmadm.tbsp_utilization"},/*266*/ {"ts_used_pages_pct", "select current server db_name, tbsp_name, tbsp_type, tbsp_total_pages, tbsp_used_pages from sysibmadm.tbsp_utilization"},/*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"},/*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"},/*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"},/*270*/ {"db_partition_status", "select trim(db_name) as db_name, dbpartitionnum as db_partition_num, db_status as database_status from sysibmadm.snapdb"},/**/ {"ps_gbp_hit_ratio", "SELECT varchar(BP_NAME,20) as BP_NAME, POOL_DATA_GBP_L_READS, POOL_DATA_GBP_P_READS, MEMBER, CASE WHEN POOL_DATA_GBP_L_READS > 0 THEN DEC(((FLOAT(POOL_DATA_GBP_L_READS) - FLOAT(POOL_DATA_GBP_P_READS)) / FLOAT(POOL_DATA_GBP_L_READS)) * 100,5,2) ELSE 100 END AS HIT_RATIO FROM TABLE(MON_GET_BUFFERPOOL('',-2))"}, {"ps_member_status", "SELECT ID,varchar(HOME_HOST,15) as member_host,TYPE,state from table(DB2_GET_INSTANCE_INFO(null,'','','',null)) as TBL where TBL.TYPE='MEMBER'"}, {"ps_cf_status", "SELECT ID,varchar(HOME_HOST,15) as cf_host,state from table(DB2_GET_INSTANCE_INFO(null,'','','',null)) as TBL where TBL.TYPE='CF'"}, {"ps_cf_gbp_sz", "SELECT varchar(HOST_NAME,27) AS HOST, CURRENT_CF_GBP_SIZE, CONFIGURED_CF_GBP_SIZE, DEC((FLOAT(CURRENT_CF_GBP_SIZE)/FLOAT(CONFIGURED_CF_GBP_SIZE)) * 100,5,2) AS GBP_SIZE_IN_USE FROM TABLE( MON_GET_CF(NULL) )"}, {"ps_hadr_status", "select log_stream_id,primary_member,standby_member,hadr_state from table (mon_get_hadr(-2))"}, {"ps_deadlocks", "SELECT MEMBER, SUM(DEADLOCKS) AS DEADLOCKS FROM TABLE (MON_GET_CONNECTION(cast(NULL as bigint), -2)) GROUP BY MEMBER"}, {"ps_lock_escals", "SELECT MEMBER, SUM(LOCK_ESCALS_GLOBAL) AS LOCK_ESCALS FROM TABLE(MON_GET_TABLE('','',-2)) GROUP BY MEMBER"}, {"ps_lock_waits", "SELECT MEMBER, SUM(LOCK_WAITS) AS LOCK_WAITS FROM TABLE(MON_GET_TABLE('','',-2)) GROUP BY MEMBER"}, {"ps_lock_timeouts", "SELECT MEMBER, SUM(LOCK_TIMEOUTS_GLOBAL) AS LOCK_TIMEOUTS FROM TABLE (MON_GET_CONNECTION(cast(NULL as bigint), -2)) GROUP BY MEMBER"}, {"ps_lock_wait_time", "SELECT MEMBER, SUM(LOCK_WAIT_TIME_GLOBAL) AS LOCK_WAIT_TIME FROM TABLE(MON_GET_TABLE('','',-2)) GROUP BY MEMBER"},/*271*/ {"pm_hadr_log_gap", "SELECT SUBSTR(HADR_ROLE, 1, 8) AS hadr_role, HADR_LOG_GAP from table (mon_get_hadr( NULL )) as T"},/*272*/ {"pm_hadr_replay_lag", "SELECT SUBSTR(HADR_ROLE, 1, 8) AS hadr_role, EXTRACT(SECONDS FROM standby_replay_log_time) AS replay_time_seconds FROM table (mon_get_hadr(NULL)) as T"},/*273*/ {"pm_hadr_send_rec_qu_size", "SELECT SUBSTR(HADR_ROLE, 1, 8) AS hadr_role, SUM(SOCK_RECV_BUF_ACTUAL) + SUM(SOCK_SEND_BUF_ACTUAL) AS total_queue_size FROM TABLE(MON_GET_HADR(NULL)) AS T GROUP BY SUBSTR(HADR_ROLE, 1, 8)"},/*272*/ {"pm_hadr_primary_logs_held", "SELECT SUBSTR(HADR_ROLE, 1, 8) AS hadr_role, log_hadr_wait_cur from table (mon_get_hadr(NULL)) as T"},/*273*/ {"pm_hadr_sync_mode_mon", "SELECT SUBSTR(HADR_ROLE, 1, 8) AS hadr_role, hadr_connect_status, hadr_syncmode from table (mon_get_hadr(NULL))"},/*274*/ {"pm_hadr_ntw_time", "select SUBSTR(HADR_ROLE, 1, 8) AS hadr_role, STANDBY_REPLAY_DELAY from table (mon_get_hadr((cast(NULL as bigint)))) as T"},/*275*/ {"pm_hadr_apply_time", "SELECT SUBSTR(HADR_ROLE, 1, 8) AS hadr_role, EXTRACT(SECONDS from standby_log_time) AS standby_log_time from table (mon_get_hadr(NULL)) as T"},/*272*/ {NULL, NULL}
Additional Information
- Attached to the KB are 2 files:
- DB2_Queries for db2probe_1671551105665.txt (Includes the queries for legacy probe 4.10)
- queries_4.31_t4.txt (Includes the queries up to 4.31t4) - db2 (DB2 Database Monitoring) Techdoc: DB2 Database
Attachments
Feedback
Yes
No
Powered by
