 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / July 2005SET CURRENT LOCK TIMEOUT
The DB2 8.2 SQL Reference says SET CURRENT LOCK TIMEOUT is not under transaction control. I assume that means that changing it via the SQL command affects all connections to the database in the same way the db cfg parm LOCKTIMEOUT does? Anyone know if IBM is considering NO WAIT option (LOCKTIMEOUT =0) for an individual transaction or SQL statement? > The DB2 8.2 SQL Reference says SET CURRENT LOCK TIMEOUT is not under > transaction control. I assume that means that changing it via the SQL > command affects all connections to the database in the same way the db cfg > parm LOCKTIMEOUT does? I believe you are correct. > Anyone know if IBM is considering NO WAIT option (LOCKTIMEOUT =0) for an > individual transaction or SQL statement? If you don't want to wait for locks, you might as well skip the locking entirely. You'd be interested in "SELECT blah FROM foo WITH UR". -- Matt Emmerton This is my understanding -- When this command is issued by a user from command line, it affects only that specific session (all stmts until next set lock mode command, if any)- not all connections to the db. So, for a specific SQL, one can do the following, for instance: Db2 Set lock mode 0 Db2 "whatever" Db2 Set lock mode null (default back to locktimeout db cfg parm) When Set lock mode is issued from an app, then it's only good for the duration of the app. (Well, this is how Informix does it - does not mean db2 will) Keith Ponnapalli IBM Certified Advanced Database Administrator - DB2 UDB V8.1 for Linux, UNIX, and Windows INFORMIX Certified Database Administrator > > The DB2 8.2 SQL Reference says SET CURRENT LOCK TIMEOUT is not under > > transaction control. I assume that means that changing it via the SQL [quoted text clipped - 12 lines] > -- > Matt Emmerton > This is my understanding -- When this command is issued by a user from > command line, it affects only that specific session (all stmts until [quoted text clipped - 14 lines] > UNIX, and Windows > INFORMIX Certified Database Administrator So Keith and Matt seem to disagree. Anyone else have an opinion? I don't have a db2 to try this out but you can give the following a try... window #1 db2 set current lock timeout 300 db2 -c- lock table tb1 don't commit and wait window #2 db2 set current lock timeout 20 db2 -c- lock table tb1 don't commit and wait I think window #2 will timeout first... >>This is my understanding -- When this command is issued by a user from >>command line, it affects only that specific session (all stmts until [quoted text clipped - 16 lines] > > So Keith and Matt seem to disagree. Anyone else have an opinion? See my note, this is an Informix compatibility feature. I think Matt is confusing the statement with the matching LOCKTIMEOUT DB CFG parameter which provides the x-session default. Cheers Serge  SignatureSerge Rielau DB2 SQL Compiler Development IBM Toronto Lab > The DB2 8.2 SQL Reference says SET CURRENT LOCK TIMEOUT is not under > transaction control. I assume that means that changing it via the SQL > command affects all connections to the database in the same way the db cfg > parm LOCKTIMEOUT does? That not what it means. It means that Example: DECLARE a INT; SET a = 5; SET CURRENT LOCK TIMEOUT = 5; COMMIT; SET a = 4; SET CURRENT LOCK TIMEOUT = 4; ROLLBACK; Lock timeout and teh variable a both are 4 after the rollback. I.e. they are not under transaction control. This is nothing special. It's the same for all special registers (SET CURRENT PATH, SET CURRENT SCHEMA, optimization level, ....) > Anyone know if IBM is considering NO WAIT option (LOCKTIMEOUT =0) for an > individual transaction or SQL statement? Is is being considered, given my explanation above to you agree that it isn't critical? You can achieve the desired result with SET LOCKTIMEOUT Cheers Serge SignatureSerge Rielau DB2 SQL Compiler Development IBM Toronto Lab > Is is being considered, given my explanation above to you agree that it > isn't critical? You can achieve the desired result with SET LOCKTIMEOUT > > Cheers > Serge Perhaps not critical, but does save two calls to DB2 if you have to turn it on beforehand, and turn it back off afterward, instead of including it with the SQL statement in question. Obviously, this would not be much of a factor for a SP running on the server, but would be important for a remote client. >>Is is being considered, given my explanation above to you agree that it >>isn't critical? You can achieve the desired result with SET LOCKTIMEOUT [quoted text clipped - 6 lines] > the SQL statement in question. Obviously, this would not be much of a factor > for a SP running on the server, but would be important for a remote client. Agreed. Cheers Serge SignatureSerge Rielau DB2 SQL Compiler Development IBM Toronto Lab |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.