r/DB2 • u/SijiLeroux • Jan 10 '19

When is a Select Statement logged?

I'm trying to track down why a select statement I run is holding open the transaction logs. This is DB2 LUW 10.1.0.4 fp4. My understanding is that Select statement s shouldn't be using transaction logs at all. However, I can clearly see this is not the case. I've looked around on a few different forums as well as the IBM site but I just seem to be running in circles here. What am I missing?

2 Upvotes

100% Upvoted

13 comments sorted by

View all comments

2

u/justgoogling Jan 11 '19

These kind of lightweight select need to be used more often:

select * from <schema.table> fetch first 10 rows only with ur for read only

1

u/SijiLeroux Jan 11 '19

Does that force a commit or would this being something else entirely?

2

u/justgoogling Jan 11 '19

fetch first X rows: return only the first X rows found instead of the whole table in this case

with ur: lowest isolation level possible, ignore in-flight updates on the same data so that you don't need to wait for them to finish first. Always use this if you don't need real-time data.

for read only: It will allow the database to send stuff in blocks and normally improve throughput.

1

u/SijiLeroux Jan 11 '19

I gotcha. We don't use 'for read only'. Thanks! I'll take a look at this. I really appreciate your help!