Performance issue? DB2 internal workings 1

[MarcLodge]

All,
I want to make an insert to a table based upon data in that table. Let's assume the table has 3 columns PROD_ID, PROD_TYPE and PROD_TIMESTAMP. The query I wish to run is along the following lines:

INSERT INTO TABLE_A
SELECT PROD_ID
,16 (this is the PROD_TYPE column)
,PROD_TIMESTAMP + 1 MICROSECOND
FROM TABLE_A
WHERE PROD_TYPE = 15

What I believe will happen is that DB2 will run the inner query, building an interim results table, and then perform the insert from that temporary table. In locking terms, I think that a read lock will be taken whilst the inner query is running, which is then promoted to update lock as the insert is performed.

Is anybody able to confirm whether this is correct or incorrect.

Marc

 

[sathyarams]

Marc ... IMHO ,for the SELECT part of the statement a S/NS lock is taken , resultset generated and the lock released . Following this, the INSERT part of the statement takes an X lock on the new inserted row and IX lock on the table, inserts and then releases the locks ...

I assume this is what you meant, but as the term 'promotion' was bit confusing, I thought I'll describe it again ..

For reference the Access Plan :

Data Stream 1:
| Not Piped
| Access Table Name = SATHYARAM.EMPLOYEE ID = 2,5
| | #Columns = 14
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 1
| Insert Into Temp Table ID = t1
| | #Columns = 14
End of Data Stream 1
Access Temp Table ID = t1
| #Columns = 14
| Relation Scan
| | Prefetch: Eligible
Insert: Table Name = SATHYARAM.EMPLOYEE ID = 2,5

HTH

Sathyaram



More DB2 questions answered at

http://www.dbforums.com/f8

 

[MarcLodge]

Thanks Sathyaram, that confirms what I thought/hoped.

Marc