Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: UR with sub-query ??

    Friends,

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

    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.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  5. #5
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Use an event monitor. It will tell you the isolation level of the statements that were executed.

    Andy

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by DBFinder View Post
    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.

  8. #8
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Thanks,

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

    DBFinder

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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)

  10. #10
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •