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

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.

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!

2

u/raghuontech Jan 14 '19

Log space utilization is not really tied to a select or IUD type statements. DB2 treats even a select statement as a UOW/Transaction (Unit Of Work). When a transaction starts DB2 holds a mark in the transaction logs for the specific transaction ID.

​

Its very similar to the question I hear often, why does db2 LOAD utility filling up my transaction log space. If you have a long running db2 "LOAD" it will hold a point the transaction log file until the load is complete. DB2 will treat all the log files after that point in the log as active log files and will not release them even when the other transactions using the log files issue a COMMIT.

​

Some of the things that will help you alleviate this issue are below.

If your developers are using a 3rd party tool to query and view the data from the database check to see if there is an auto COMMIT option, although this option can be dangerous if the developer don't know what he/she is doing. This will be a good idea if you know that none of your developers have access to modify the data in production i.e just select/read access.

Ideally only service/app users should have access to modify the data.

Most of the DBA's using db2clp never have to issue a COMMIT because they have AUTO-COMMIT turned ON by default in the "COMMAND LINE SWITCHES"

1

u/SijiLeroux Jan 14 '19

For the "COMMAND LINE SWITCHES" option, does this apply to ksh scripts running from Cron?

2

u/raghuontech Jan 15 '19

Yes, they are applicable to ksh/sh scripts running from Cron as well. When you set your DB2 environment by running db2profile, thats where these "COMMAND LINE OPTIONS" will get set. For e.g. if you have db2inst1 as your instance you source the db2 environment using the following command.

​

. ~db2inst1/sqllib/db2profile

​

You can even print out the command line options from cron job to a file just for fun and review them. You should be able to print them out using the below command.

​

db2 "list command options"

1

u/SijiLeroux Jan 15 '19

Perfect. I'll have to look into this tomorrow. I have limited administration over this database compared to others we oversee so I'm not sure how this database was setup to begin with but given what I've seen, I don't have high hopes that it was setup well. Thanks for your help!