Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2007
    Posts
    13

    Unanswered: How do I "Disable" a Table?

    Hello,

    I am adding a new feature to a legacy application. A user can display a "selection criteria" panel where he enters data into one or more fields and then presses the OK button. A SQL SELECT query is built based on the data he entered, and then a cursor is opened and the qualifying rows are fetched.

    The code uses the Call Level Interface library. The functions used are SQLExecDirect and SQLExtendedFetch. The new feature I added was a call to set the SQL_QUERY_TIMEOUT attribute using SQLSetStmtOption.

    The problem I'm having is that our development DB tables are small. I was able to get it to time out only once. I need to be able to get it to time out predictably.

    I tried entering data into the panel, and then connect to the database and lock one of the tables in exclusive mode and then press the OK button. It didn't work.

    I'm not a DB2 expert. How can I "disable" a table before I press the OK button so that the query times out? Thank you very much!

    AIX v5.3.0
    DB2 v7.1.0

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What exactly dio you mean with "It didn't work"?

    The locking approach will work with an appropriately high lock timeout value being set in the database configuration.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Sep 2007
    Posts
    13
    What I mean is that the code was able to read the database table. Afterwords I read the documentation and apparently even though the table is "locked" in exclusive mode other threads may read the table. True?

    I need to prevent other threads from reading the data. Thank you very much!

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Even if a table is locked in exclusive mode, it can be read with isolation level UR (uncommited read).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Marcus,

    I just double checked in the manual and as long as he quisce tablespace in EXCLUSIVE mode he should be a ok. Reason for it is he will be placing a Z lock on it, which is a Super exclisive lock.

    Here is what it says:"Since the table spaces are held in super exclusive mode, no other access to the table spaces is allowed."
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Cougar8000
    Marcus,

    I just double checked in the manual and as long as he quisce tablespace in EXCLUSIVE mode he should be a ok. Reason for it is he will be placing a Z lock on it, which is a Super exclisive lock.

    Here is what it says:"Since the table spaces are held in super exclusive mode, no other access to the table spaces is allowed."
    When retierney and I both mentioned "lock in exclusive mode", we meant meant X lock on the table, not a Z super exclusive lock caused by a QUIESCE.

    To the best of my knowledge a query will not incur a lock time out if it is locked by a QUIESCE with a super exclusive Z lock. If you read the original post, you can see that retierney is trying to force a lock time out error (-911, reason code 68). I believe that accessing a QUIESCE'd object would yield a different error.

    In addition, QUIESCE may not be practical if you have more than one table in a tablespace and you only want to lock one table. Also, as I am sure you know, QUIESCE does not have any effect on the instance owner id.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    @retierney: It is correct that locking a table in exclusive mode by one SQL session/connection prevents access to that table from all other SQL sessions/connections. So in your case, you must do the locking through a different connection for this to work. Additionally, you have to make sure your query does not run in isolation level UR - as Markus_A explained. If those conditions are all applicable to your scenario, please describe to us exactly what you are doing and which results/messages you are getting at the various steps.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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