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

load with "allow read access" gives -911 on UDB v8 FP 5 on AIX

P: n/a
When running LOAD with "ALLOW READ ACCESS", I get the following error
if select is running againt the table (isolation UR).

load.sql
---------
db2 load from /u02/data/dly_d040817_test.dat of asc \
modified by implieddecimal nullindchar=Y \
fastparse anyorder \
METHOD L \(01 10,11 12,13 15,16 24,25 28,29 48,49 64\) \
MESSAGES /u02/load/msg/auth_detail.msg \
tempfiles path /u02/load/tmp \
INSERT INTO MYTAB \
nonrecoverable \
data buffer 14400 \
cpu_parallelism 2 \
disk_parallelism 4 \
allow read access
Agent Type Node SQL Code Result
__________________________________________________ ____________________________
LOAD 003 -00000911 Init error. Table unchanged.
__________________________________________________ ____________________________
LOAD 004 -00000911 Init error. Table unchanged.
__________________________________________________ ____________________________
RESULTS: 0 of 2 LOADs completed successfully.
__________________________________________________ ____________________________

Summary of LOAD Agents:
Number of rows read = 0
Number of rows skipped = 0
Number of rows loaded = 0
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 0

SQL0911N The current transaction has been rolled back because of a
deadlock
or timeout. Reason code "68". SQLSTATE=40001

The following message is found in the diag.log
-----------------------------------------------
LOADID: 46164.2004-08-18-22.45.15.301714.4 (12;4)
CatlogSA: error encountered during InitSA processing. -911, 0,
Detected in file: sqlusCatalogSA.C,lin
e 859
....
....
Reply type of request discarded =
0x2FF1EC1C : 0x00000002 ....

2004-08-18-22.45.46.109422 Instance:xxxx Node:004
PID:46164(db2agent (MYDB) 4) TID:1 Appid:*N3.xxxx.040818224514
database utilities DIAG_ERROR Probe:0 Database:MYDB

LOADID: 46164.2004-08-18-22.45.15.301714.4 (12;4)
Coord: error encountered during catalog agent processing. -911, 0,
Detected in file: sqlusMPPCoordinator.C,line 1257
================================================

But, if the load is already started the select goes thru fine and the
load waits until the select is over (to obtain the Z lock). Once the
select completes load also completes successfully.

Is the behaviour documented anywhere that the LOAD requires exclusive
access on the table at the start and end of the LOAD?

Thanks,
Prince.
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
gs**@yahoo.com (Prince Kumar) wrote in message news:<62**************************@posting.google. com>...
When running LOAD with "ALLOW READ ACCESS", I get the following error
if select is running againt the table (isolation UR). <snip>
But, if the load is already started the select goes thru fine and the
load waits until the select is over (to obtain the Z lock). Once the
select completes load also completes successfully.

Is the behaviour documented anywhere that the LOAD requires exclusive
access on the table at the start and end of the LOAD?


'ALLOW READ ACCESS' allows reads during the first phases of the load
operation, but still needs super exclusive access - just for a moment
in the last phase.

I've seen this documented, though can't remember where now. Perhaps
the data movement redbook?

buck
Nov 12 '05 #2

P: n/a
The problem is not in the last phase. As I have mentioned clearly, it
fails with -911 immediatley, if there is a select access againt the
table. But, if the "select" is already in progress, the "load" waits
until the "select" completes. (It seems the load loads the data but
wait for the 'Z' lock to complete in the second case.)

If the load can not proceed, if there is select access againt the
table, I am not sure how useful this parameter is!

Thanks.

bu*********@yahoo.com (Buck Nuggets) wrote in message news:<66**************************@posting.google. com>...
gs**@yahoo.com (Prince Kumar) wrote in message news:<62**************************@posting.google. com>...
When running LOAD with "ALLOW READ ACCESS", I get the following error
if select is running againt the table (isolation UR).

<snip>

But, if the load is already started the select goes thru fine and the
load waits until the select is over (to obtain the Z lock). Once the
select completes load also completes successfully.

Is the behaviour documented anywhere that the LOAD requires exclusive
access on the table at the start and end of the LOAD?


'ALLOW READ ACCESS' allows reads during the first phases of the load
operation, but still needs super exclusive access - just for a moment
in the last phase.

I've seen this documented, though can't remember where now. Perhaps
the data movement redbook?

buck

Nov 12 '05 #3

P: n/a
Any updates on this?

Thanks,
Prince.
gs**@yahoo.com (Prince Kumar) wrote in message news:<62**************************@posting.google. com>...
The problem is not in the last phase. As I have mentioned clearly, it
fails with -911 immediatley, if there is a select access againt the
table. But, if the "select" is already in progress, the "load" waits
until the "select" completes. (It seems the load loads the data but
wait for the 'Z' lock to complete in the second case.)

If the load can not proceed, if there is select access againt the
table, I am not sure how useful this parameter is!

Thanks.

bu*********@yahoo.com (Buck Nuggets) wrote in message news:<66**************************@posting.google. com>...
gs**@yahoo.com (Prince Kumar) wrote in message news:<62**************************@posting.google. com>...
When running LOAD with "ALLOW READ ACCESS", I get the following error
if select is running againt the table (isolation UR). <snip>
But, if the load is already started the select goes thru fine and the
load waits until the select is over (to obtain the Z lock). Once the
select completes load also completes successfully.

Is the behaviour documented anywhere that the LOAD requires exclusive
access on the table at the start and end of the LOAD?


'ALLOW READ ACCESS' allows reads during the first phases of the load
operation, but still needs super exclusive access - just for a moment
in the last phase.

I've seen this documented, though can't remember where now. Perhaps
the data movement redbook?

buck

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.