DB2/UDB stage causing table deadlock
Posted: Mon Nov 10, 2008 10:19 pm
Dear all,
How is everyone? =)
I was hoping to be able to obtain some clarification here. I am currently facing a predicament not encountered previously.
I have a simple job that does this:
Dataset-> Surrogatekey generator-> Db2/UDB stage.
In my Db2/UDB stage I am selecting 'Write: Append' as the insert option.
My volume of data is just 800,000 records. Normally this would be dealted with swiftly in the development environment, but in production, it is inserting in a crawl... at around few hundred records per 2-3 minute interval. That is, if the records are still being inserted.
Worse case scenario is that the table will go into a deadlock after few records. <-- this being the frequent one.
I checked with the DBA, and he noticed that for that job alone, which was assigned a specific session id, it is generating many many lock threads...
1) Is this the cause of the deadlock/job crawling?
2) Is this normal for write/append option?
3) Any other hints to where this might have gone wrong?
I've searched and thus far no one encountered this problem. Slow maybe, but not to a grinding halt.
Any ideas guys? Right now the DBA and developers are throwing punches at each other. hahaha. Funny situation really...
How is everyone? =)
I was hoping to be able to obtain some clarification here. I am currently facing a predicament not encountered previously.
I have a simple job that does this:
Dataset-> Surrogatekey generator-> Db2/UDB stage.
In my Db2/UDB stage I am selecting 'Write: Append' as the insert option.
My volume of data is just 800,000 records. Normally this would be dealted with swiftly in the development environment, but in production, it is inserting in a crawl... at around few hundred records per 2-3 minute interval. That is, if the records are still being inserted.
Worse case scenario is that the table will go into a deadlock after few records. <-- this being the frequent one.
I checked with the DBA, and he noticed that for that job alone, which was assigned a specific session id, it is generating many many lock threads...
Question:
Example lock snapshot:
Application handle = 354
Application ID = *N0.db2inst1.081111031741
Sequence number = 0001
Application name = osh
CONNECT Authorization ID = ETLOPER
Application status = UOW Executing
Status change time = Not Collected
Application code page = 874
Locks held = 1024
Total wait time (ms) = Not Collected
List Of Locks
Database partition = 0
Lock Name = 0x003B010701DD72010000000052
Lock Attributes = 0x00000008
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 31289857
Object Type = Row
Tablespace Name = STG_DATA1_TS
Table Schema = CTRLSTG
Table Name = FNC_STG_DATA_001
Mode = X
Database partition = 0
Lock Name = 0x003B010701DD72000000000052
Lock Attributes = 0x00000008
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 31289856
Object Type = Row
Tablespace Name = STG_DATA1_TS
Table Schema = CTRLSTG
Table Name = FNC_STG_DATA_001
Mode = X
1) Is this the cause of the deadlock/job crawling?
2) Is this normal for write/append option?
3) Any other hints to where this might have gone wrong?
I've searched and thus far no one encountered this problem. Slow maybe, but not to a grinding halt.
Any ideas guys? Right now the DBA and developers are throwing punches at each other. hahaha. Funny situation really...