Results 1 to 5 of 5

Thread: Making views

  1. #1
    Join Date
    May 2010
    Posts
    5

    Unanswered: Making views

    Hi all,

    I have got the following (beginner) question regarding views:

    I have a machine, that inserts data into a db2 table, using the same user, that queries the table. So, sometimes the query can take a while, as the insert is still ongoing. (2-3 minutes sometimes)

    The table has now ~3mil rows.

    I thought that I build a View for the table, and redirect the query to the view, as the insert remains on the original table.

    Q1: Would it solve my problem with the sometimes long waiting times?
    Q2: Is the view always up-to-date with the original table, or how long does it "lag" ?
    Q3: Is it a huge workload for the db, or like nothing? (db itself is like 30Gb in size)

    Hope i was clear with the situation, and unfortunately i cannot change the users, so this is not really an option

    Thank you very much for your responses and time in advance !

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It makes no difference if you create a view. The view doesn't actually exist as a separate physical table in the database, and will not eliminate contention (if there is any).

    To solve the contention problem, there are two options.

    1. On all the select statements, put "WITH UR" at the end of the statement to eliminate locking.

    2. Issue the following commands and restart the instance:
    db2set DB2_SKIPINSERTED=ON
    db2set DB2_SKIPDELETED=ON (optional, but recommended if you ever delete rows).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    May 2010
    Posts
    5
    Hi,

    Thanks for your reply, I start experimenting with the UR option.
    In the last days I read a lot about views and locks, but I still do not really understand.

    If I create a view, based only on one table, which consists only the last 1000 inserted rows (based on timestamp), would it make the queries faster? Plus if I could query this view with another user , not the one that inserts the table, then the insert and the query could happen parallel. Am I right?

    If i am not mistaken and correctly informed, than DB2 lockouts are row-based, so logic dictates me it should work.

    Still havent found what happens if 2 separate users are querying/inserting the same table.

    Regards

  4. #4
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    having a view even on selecting the 1000 rows, is not going to help in terms of avoiding locks. Instead try to have good indexes ( e.g. on timestamp in your case) based on which the SELECT queries are fired. Having the right index for your queries are definitely going to reduce contention with other concurrent queries on the same table and also with faster response. WITH UR too is going to help avoid lock escalation.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  5. #5
    Join Date
    May 2010
    Posts
    5
    Thanks,

    I am getting closer to understand and - through this - solving my problem.

    Thanks for your responses

Posting Permissions

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