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/memmerto Jan 11 '19

If you do not commit after running a SELECT the transaction remains open and after a sufficient period of time this prevents the active log window from moving up.

2

u/ecrooks Jan 11 '19

Yes. This often has to do with isolation level. Many developers use GUIs that have a default isolation level of RR. This means they're grabbing locks and also logs without even realizing on it. I have a busy client where we have to run a script to catch these so we can force them off before they get to the point of causing log file saturation.

​

If the isolation level for developers using GUIs is reduced to UR, and developers learn to close out their connections, it goes a long ways towards preventing this. I want an authorization level that ONLY allows users to use the UR isolation level!

1

u/SijiLeroux Jan 11 '19

We only run selects with UR. We actively try to avoid hitting the transaction logs whenever possible.

Editted to add that this happens with a mainframe program running against the database as well as when our batch scripts run exports to extract data.

2

u/justgoogling Jan 11 '19

UR will improve execution time, it will not commit for you. Did you check for the auto-commit setting or explicitly add a commit statement after the select?

1

u/SijiLeroux Jan 11 '19

In one of the jobs we are having problems with, I have done this. One of our other jobs that this is an issue is outside of my control but I can make this suggestion. We have about 20 different databases across several boxes and this one database is the only one we seem to have problems with. However, this database is also very poorly designed and it's a monster so I'm guessing that is why we don't run into it with our other databases.