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 !

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).

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

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.

Thanks,

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

Thanks for your responses