UR with sub-query ??

DBFinder · #1 (**permalink**) 06-18-10, 12:53

Friends,

Is there a way to use UR in sub-query ?

Quote:

SELECT msg_id, create_date, admin_id, casino_id, active_start, active_end,

msg_type, msg_content, segment

from Broadcast_Message b

Where CURRENT TIMESTAMP between active_start and active_end and coalesce(cancel,'N') != 'Y'

and 0 = (select count(*) from delivered_message d where acc_num=? and d.msg_id = b.msg_id with UR)

and (casino_id = ? or segment in (? ,?))

order by active_start
with UR

As seen practically Main with UR does not work for subquery.

Help appreciated.

dav1mo · #2 (**permalink**) 06-18-10, 12:58

In a word, NO. Though, why would you need the multiple WITH UR statements? The one at the end of the statement, where it IS allowed, makes the entire statement WITH UR.
Dave

DBFinder · #3 (**permalink**) 06-18-10, 13:01

Does it ?? with whole.

My experience says NO. that's why. We had locking problem because of that.

Can I query DB2 to find if a select on a table was at what isolation level .
I want to perform a test.

Regards

tonkuma · #4 (**permalink**) 06-18-10, 14:34

You can specify isolation-clause in a subselect on DB2 for LUW 9.7,
by referring to "IBM DB2 9.7 for Linux, UNIX, and Windows SQL Reference, Volume 1".

Here is an example:

Code:

------------------------------ Commands Entered ------------------------------
SELECT empno , workdept
  FROM (SELECT deptno
          FROM department
         WHERE admrdept = 'A00'
        WITH UR
       )
     , employee
 WHERE workdept = deptno
;
------------------------------------------------------------------------------

EMPNO  WORKDEPT
------ --------
000010 A00     
000020 B01     
000030 C01     
000050 E01     
000110 A00     
000120 A00     
000130 C01     
000140 C01     

  8 record(s) selected.

DBFinder · #5 (**permalink**) 06-18-10, 14:41

Thanks,

I am at DB2V9.5.5 on Win 2K3

Is there a way to find isolation level of a select on a table ?
I want to see whether main with UR also affect subquery or not.

I would like to run the query and would like to know at what isolation level main query ran and same for su-query.

What do I need to set up - event or turn on auditing. I had never needed this befor. Someone might have tried this.

Regards

ARWinner · #6 (**permalink**) 06-18-10, 15:06

Use an event monitor. It will tell you the isolation level of the statements that were executed.

Andy

n_i · #7 (**permalink**) 06-18-10, 16:04

Quote:

Originally Posted by DBFinder

I would like to run the query and would like to know at what isolation level main query ran and same for su-query.

Explain plan output usually shows that.

DBFinder · #8 (**permalink**) 06-18-10, 16:25

Thanks,

Accept my regards, I will do test both ways today.

DBFinder

db2girl · #9 (**permalink**) 06-18-10, 17:59

I also think that the one at the end of the statement applies to the entire statement (unless you specify something else at the subquery level)

DBFinder · #10 (**permalink**) 06-19-10, 01:07

That will make my job easy. However, I was told that it is not working as we all had expected. Our java developers do not usually get satisfied until I do a prctical test on the issue.

I did believe same way ( that it should be applicable to whole query). I will do the test and will post back.

Thanks Bella for your time. Have nice weekend ! How is traffic this weekend !

Regards
DBFinder

Internet Services

Web Development

Digital Marketing

Consumer Tech