| Author |
Message
|
| nik_iway |
 Posted: Wed Oct 20, 2010 8:18 am Post subject: ESQL Select Statements Causinbg db2 locks |
 |
|
Centurion
Joined: 21 Jul 2005
Posts: 115
|
Hi,
We are using db2 version 8 fix pack 18 as Database. There are several SQL statements within ESQL in prodcution accessing the database wchich is causing the database locks.
In DB2 if we are using SELECT with UR statement it wouldnt cause any deadlock issue.
Is there any way through ESQL programming using SELECT statement with UR or any methods not to cause DB2 deadlocks
regards |
|
| Back to top |
|
 |
| rajmq |
| Posted: Wed Oct 20, 2010 8:58 am Post subject: |
|
|
Partisan
Joined: 29 Sep 2002
Posts: 331
Location: USA
|
nik,
There are many ways we can optimize this issue
There is no specific Uncommitted Read option using ESQL but lots of other way we can optimize broker and db2 connectivity.
Determine the number of database connections required by a broker.Are you using single broker or multiple instance of flows etc ?
_________________
IBM Certified System Administrator - WebSphere MQ V6.0
IBM Certified System Administrator - WebSphere Business Integration Message Broker V6.0 |
|
| Back to top |
|
|
| zpat |
| Posted: Wed Oct 20, 2010 10:01 am Post subject: |
|
|
Jedi Council
Joined: 19 May 2001
Posts: 5866
Location: UK
|
This is standard good design to avoid deadlocks.
Always acquire locks in the same order (this avoids deadlocks).
Consider setting the database to row level locking rather than table level.
Ensure a deadlock timeout value is set. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Wed Oct 20, 2010 10:48 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Consider using Stored Procs for the DB access.
This way you can use the Native SQL commands to enforce reads with NO LOCKS or READONLY if you are just doing lookups with no intention of updating etc any of the data selected.
Is it AFAIK, more efficient than ESQL Selects especially if you can pre-compile the SP (as you can on Oracle).
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| philip.baker |
| Posted: Wed Oct 20, 2010 11:09 am Post subject: |
|
|
Voyager
Joined: 21 Mar 2002
Posts: 77
Location: Baker Systems Consulting, Inc. - Tampa
|
Hi nik_iway,
If you are able to utilize the ESQL PASSTHRU statement, you can run the query with Uncommited Read (UR) isolation level by using something like the following code:
sample code START...........
DECLARE dataBase3 external CHAR 'WBIMB';
DECLARE tableSchema3 external CHAR 'MQADMIN';
CREATE COMPUTE MODULE ReadDB2_WithNoLocks
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
DECLARE rEnvVar REFERENCE TO Environment.Variables;
DECLARE MySQL CHAR 'SELECT A.COLUMN_NAME1, A.COLUMN_NAME2 FROM ' || tableSchema3 ||'.QUERY_TABLE_NAME A WHERE A.KEY_ID1 = ? AND A.KEY_ID2 = ? WITH UR';
SET rEnvVar.ResultSet.Query1[] = PASSTHRU(MySQL TO Database.{dataBase3} VALUES(keyvalue1, keyvalue2));
RETURN TRUE;
END;
END Sample code...........
_________________
Regards,
Phil |
|
| Back to top |
|
|
| nik_iway |
| Posted: Thu Oct 21, 2010 1:07 am Post subject: |
|
|
Centurion
Joined: 21 Jul 2005
Posts: 115
|
| Thanks for reply and suggestions |
|
| Back to top |
|
|
|
|
