0

Base on the following link:

https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.regvars.doc/doc/r0005657.html

I turn on the the DB2_CAPTURE_LOCKTIMEOUT=ON, and then I check whether I successful change it or not by db2set -all, and I can see that its already turn to ON.

After that, I successfully simulate the locktimeout by follow this link:

https://db2forum.wordpress.com/2011/10/17/new-options-for-analyzing-lock-timeouts-in-db2-9-5/

I successfully get Reason code "68". SQLSTATE=40001.

However, I still cant get any db2locktimeout log at /home/db2inst1/sqllib/db2dump.

I only can see db2diag.log in this path but not the db2locktimeout log.

May I know what is the mistake I make?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Panadol Chong
  • 1,793
  • 13
  • 54
  • 119

1 Answers1

0

As you quoted a V10.5 page, I assume your Db2-server is at least at that version. Always write your Db2-version and fixpack when asking for help.

This deprecated (but still working) variable is only effective for newly compiled SQL statements. This means that if your package-cache already has the SQL statements cached (the ones that you use to recreate the -911 reason code 68) then no file(s) will appear for those SQLs if they are involved in the -911.

If your recreation scenario can work on a development or a test database then you can either flush the dynamic statement cache (do not do this on production), or you can bounce the Db2-instance (which has the side effect of clearing the package cache).

If your recreation scenario uses only static-SQL the either those packages will need to be rebound (do not do this on production), or you can bounce the Db2-instance.

If you can only recreate the issue on production, and flushing or bouncing or rebinding is too invasive on that environment, then the alternate approach of using an event monitor for locking is recommended, although it requires a lot more effort.

To learn more about how Db2 compiles SQL, and how dynamic-SQL statements get stored along with their access-plan in memory (called the package-cache), study the Db2-Knowledge Centre or consult any Db2 reference book.

mao
  • 11,321
  • 2
  • 13
  • 29
  • Noted. I will mention my db2 version when asking for help. Sorry I am still not understand what is means by `your package-cache already has the SQL statements cached` , can try explain in more detail or layman terms? As I just running a very simple update statement. `update table1 set name = 'newName' where id=1;` – Panadol Chong May 17 '18 at 08:19
  • Repeating what is documented in the Db2-knowledge center for these topics (package cache, package , compilation etc.) would make the answer too long. Any good book will explain these concepts which many RDBMSs use. Avoid asking follow up questions in comments. Try to spend time with the Db2 Knowledge-Center to help with your learning of these topics. – mao May 17 '18 at 09:14