[Tech Topic] DB2 Performance (Row Lock & Table Lock)

 db2set -info DB2_EVALUNCOMMITTED

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0002025.html

Db2 registry and environment variables
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.regvars.doc/doc/c0007340.html

Setting registry and environment variables
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.regvars.doc/doc/t0004954.html

db2top -d PGW
http://www.bigdatalyn.com/2016/02/24/DB2-db2top/
https://datageek.blog/2014/08/26/db2-basics-db2top/

SELECT s.coord_member,s.application_handle,s.application_name,s.session_auth_id,s.client_applname,s.elapsed_time_sec,agent.event_state, agent.event_object || ':' || agent.event_type event_type, s.activity_state, s.activity_type, s.total_cpu_time,(s.total_cpu_time/1000) total_cpu_time_ml, s.rows_read, s.rows_returned, s.query_cost_estimate, s.direct_reads, s.direct_writes, substr(s.stmt_text,1,5000) stmt_text
FROM sysibmadm.mon_current_sql s,
TABLE(WLM_GET_SERVICE_CLASS_AGENTS('', '',CAST(NULL AS BIGINT),-2)) AS agent
WHERE s.application_handle = agent.application_handle


TRANSACTION ISOLATION LEVEL
https://ithelp.ithome.com.tw/articles/10194749

TRANSACTION ISOLATION LEVEL 大致上可以分成四種：

READ UNCOMMITTED
寬鬆級別：A 交易更新但未確認資料，B交易不能更新只能讀取(直到A交易提交後)，確保交易更新資料不會有問題。

READ COMMITTED
比較嚴格一些：A 交易更新並確認資料前，其他交易不能讀取該資料

官網說明：READ COMMITTED 隨著 READ_COMMITTED_SNAPSHOT 設定而異，請參考最下方餐可考資料

REPEATABLE READ
更嚴格一點的限制：讀取中資料會被鎖定，確保同一筆交易中的讀取資料必須相同

SERIALIZABLE
最嚴謹的限制：A 交易讀取時，B交易更新要排隊；A交易更新時，B交易讀取與更新都需要排隊


Determine the effective isolation level in DB2 for Linux, UNIX, and Windows
https://www.ibm.com/developerworks/data/library/techarticle/dm-1107db2isolationlevel/index.html

==============

SQL Trace
https://www.ibm.com/developerworks/data/library/techarticle/dm-1401sqltrace/index.html