By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,963 Members | 1,749 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,963 IT Pros & Developers. It's quick & easy.

select on table T1 while importing into table T1

P: n/a
Hello everybody

I have table T1 with 10000000 rows.
I want to import 1000000 rows(takes 2 hrs ) and while import is
running
run select like

select T1.* from T1 where TA = 10 by more then 1 user

1. will import lock table T1 complitly,how to minimaze lock for
importing ?
2. Idealy I want to be able get only records prior to import, is
possible ?
3. will I be able to use select T1.* from T1 where TA = 10 WITH
UR, while import is running ?

Thank you

Alex
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
If you're running DB2 UDB V8, you can use the LOAD utility and specify ALLOW
READ ACCESS. This allows you query access to the original data.

The IMPORT utility will acquire an exclusive lock on the table when it
starts and I don't know of any way to change this behavior. You can invoke
your queries using a UR isolation level, but you will also have access to
the rows being inserted by IMPORT.
"Alex" <t2***@hotmail.com> wrote in message
news:c8*************************@posting.google.co m...
Hello everybody

I have table T1 with 10000000 rows.
I want to import 1000000 rows(takes 2 hrs ) and while import is
running
run select like

select T1.* from T1 where TA = 10 by more then 1 user

1. will import lock table T1 complitly,how to minimaze lock for
importing ?
2. Idealy I want to be able get only records prior to import, is
possible ?
3. will I be able to use select T1.* from T1 where TA = 10 WITH
UR, while import is running ?

Thank you

Alex

Nov 12 '05 #2

P: n/a
Alex,

AFAIK online IMPORT is in V8 FP4.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #3

P: n/a
Ian
Alex wrote:
Hello everybody

I have table T1 with 10000000 rows.
I want to import 1000000 rows(takes 2 hrs ) and while import is
running
run select like

select T1.* from T1 where TA = 10 by more then 1 user

1. will import lock table T1 complitly,how to minimaze lock for
importing ?
2. Idealy I want to be able get only records prior to import, is
possible ?
3. will I be able to use select T1.* from T1 where TA = 10 WITH
UR, while import is running ?


IMPORT takes an X lock on the table for the duration of the operation.
This is done in order to avoid deadlocks due to lock escalation. The
X (exclusive) lock prevents _all_ access to the table.

In V8.1.4 (Fixpack 4), IBM added online import functionality which allows
read/write access to the table during the import, but there are still some
restrictions (see the Fixpack 4 release notes for information on these
restrictions). For read-only use I think you should be OK, but realize
that your users may get an imcomplete view of the data while it's being
imported (i.e. queries may access the data being imported before the
entire import has been completed).

You may also want to consider using the online load functionality. It
will be faster, and the considerations for using it may be more compatible
with what you application is doing.
Good luck,

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Nov 12 '05 #4

P: n/a
> 2. Idealy I want to be able get only records prior to import, is
possible ?


In general, in the db2 world, when lock avoidance is used, the data you see
is
the uncomitted/new data, not the old.

Even with the new v8.1.4 weird registry values like
DB2_EVALUNCOMMITTED
DB2_SKIPDELETED
....

PM
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.