set integrity for table partitions with "allow write access"
tg
users to have read and write access to the table while it is being
processed.
I have tested this out and found that I have no problems reading and
updating data on the unaffected partitions however I cannot insert
data to the unaffected partitions. Why are the inserts not allowed??
The exact command is: "set integrity for xxxxx allow write access
immediate checked incremental"
Any help would be appreciated. Thank you !!!
Naresh Chainani
Please provide what do you mean by affected partitions? Were these
partitions previously attached using the ALTER TABLE ATTACH PARTITION
command? Also, what is the exact error message you are getting back
when inserting into what you refer to as affected partition?
Naresh
tg
It is a table with 11 partitions that have been added with the ALTER
TABLE ATTACH PARTITION. There are also several indexes and constraints
on this table as well. The 12th partition is then added. During the
set integrity, I cannot insert a row in the other 11 partitions. I
get a lock-timeout. I thought that if I could update a row in any of
the other 11 partitions then I should also be able to insert a row.
Thank you for !!!
Naresh Chainani
Knowing exactly what lock the insert times out on would help
understand the problem. Are you familiar with debugging lock timeout
issues? I typically use db2pd for collecting the information, but
there is another procedure at http://www-01.ibm.com/support/docview.wss?rs=71&&uid=swg21322231.
It seems well documented, though I have never used it myself. If you
would rather use db2pd, I can help with that.
Do you have any Repeatable Read scans on the table?
Another theory is that maybe the compilation (rather than execution)
of the insert statement is getting blocked. This can happen when SET
INTEGRITY updates the partition state in the catalogs and an exclusive
lock is taken by this update. This would prevent the insert (or any
other SQL) from compiling. In this case, the lock timeout by the
insert would be reported on something called the CATCACHE lock. The
reason the update could possibly go through maybe because it was
already compiled and in the cache. Enough of theories, knowing the
exact lock conflict would be a step in the right direction.
Naresh
tg
> It seems well documented, though I have never used it myself. If you
> would rather use db2pd, I can help with that.
>
> Do you have any Repeatable Read scans on the table?
>
> Another theory is that maybe the compilation (rather than execution)
> of the insert statement is getting blocked. This can happen when SET
> INTEGRITY updates the partition state in the catalogs and an exclusive
> lock is taken by this update. This would prevent the insert (or any
> other SQL) from compiling. In this case, the lock timeout by the
> insert would be reported on something called the CATCACHE lock. The
> reason the update could possibly go through maybe because it was
> already compiled and in the cache. Enough of theories, knowing the
> exact lock conflict would be a step in the right direction.
>
>
> - Show quoted text -
Thanks so much for the info. I will try what you suggested shortly.
There was nothing else running while I was doing this test.
I had gathered the following info during the lock:
From the locktimeout report:
------------------------
Lock Information:
------------------------
Lock Name: FFFA8001FFFF00000000000054
Lock Type: Table
Lock Specifics: Tablespace ID=65530, Table ID=32769
Lock Requestor:
System Auth ID: ID1
Application Handle: [0-1365]
Application ID: *LOCAL.ifs_t9.091230192822
Application Name: db2bp
Requesting Agent ID: 2814112
Coordinator Agent ID: 2814112
Coordinator Partition: 0
Lock timeout Value: 50000 milliseconds
Lock mode requested: ..X
Application Status: (SQLM_UOWEXEC)
Current Operation: (SQLM_EXECUTE_IMMEDIATE)
Lock Escalation: No
Context of Lock Request: Not available.
Lock Owner (Representative):
System Auth ID: ID1
Application Handle: [0-1397]
Application ID: *LOCAL.ifs_t9.091230194121
Application Name: db2bp
Requesting Agent ID: 3784864
Coordinator Agent ID: 3784864
Coordinator Partition: 0
Lock mode held: .IX
List of Active SQL Statements:
Entry: #1
Identification: UOW ID (1); Activity ID (1)
Package Schema: (NULLID )
Package Name: (SQLC2F0A)
Package Version: ()
Section Entry Number: 203
SQL Type: Dynamic
Statement Type: DDL, Set Constraints
Effective Isolation: Cursor Stability
Statement Unicode Flag: No
Statement: set integrity for etludb.dtlopen_f allow
write access immediate checked incremental
List of Inactive SQL Statements from current UOW: Not available
-----------------------------------
From the lock snapshot:
-----------------------------------
Lock Name = 0xFFFA8001000000000000000074
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 12
Hold Count = 0
Lock Object Name = 32769
Object Type = Internal Table Alter Lock
Table Schema = ETLUDB
Table Name = DTLOPEN_F
Mode = X
Lock Name = 0xFFFA8001FFFF00000000000054
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 255
Hold Count = 0
Lock Object Name = 32769
Object Type = Table
Table Schema = ETLUDB
Table Name = DTLOPEN_F
Data Partition Id = -1
Mode = IX
Lock Name = 0xFFFA8001000000000000000054
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 3
Hold Count = 0
Lock Object Name = 32769
Object Type = Table Partition
Tablespace Name = FCT_TS01_DTL2008
Table Schema = ETLUDB
Table Name = DTLOPEN_F
Data Partition Id = 0
Mode = X
Thanks!!!
Naresh Chainani
This is a good start. The SET INTEGRITY statement, using application
handle [0-1397], has an intent exclusive (IX) lock on ETLUDB.DTLOPEN_F
table, an alter-table lock to prevent other utilities/DDL from running
and an exclusive lock on the attached partition which it is presumably
trying to make visible. This all is quite normal for SI.
Along comes application handle [0-1365] running as the same user as
the SET INTEGRITY statement, requesting an *exclusive* lock on the
entire partitioned table. It is not normal for a simple insert to
request an exclusive lock on the table, so there is something else
going on. Please take a closer look at this application. Look in the
db2diag.log for any clues as to what might be causing this application
to request this X lock. From the lock information you provided, this
clearly is not a case of lock escalation. We can also get the stack of
this exclusive lock requester by running:
db2pd -stack <eduid> where eduid corresponds to our lock requester and
is 2814112 in your email above, but likely to change when you run the
repro again.
It may help to set the lock timeout to a larger value so that you have
enough time to collect the diagnostic information. For help with db2pd
-stack, see http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0011729.html.
Naresh