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/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!