[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 980
  • Last Modified:

How can one minimize logging performed by the DB2?

What DB2 settings do I need to say to literally minimize to its minimum possible level the logging functionality? In other words how can I minimize logging in DB2 if not completely turn it off? What switches are responsible and what are the right values for those?
0
mersis
Asked:
mersis
  • 4
  • 3
  • 3
  • +1
3 Solutions
 
momi_sabagCommented:
hi
there are a couple of things you can do
1) you can turn off the logging mechanism for a table if you use the statement
alter table not logged initiallit (or adding that clause in the create table statement)
that puts the table in a no logging mode until you commit, so you can completly cancel logging this way but be aware of the reprecautions.
2) make sure none of the tables have data capture turned on (unless you replicate them)
3) on some platforms, column placement within the table influence the amount of log data written. it is advise to put all the columns that you might update in the end of the table (that is, last in the column sequence in the create table command)

you can also set the no log flags on large maintenance processes such as load and reorg
0
 
mersisAuthor Commented:
Thanks momi_sabag,
what are the actual statements to say NO LOGGING when creating a table and NO DATA CAPTURE? Or where do I check the NO DATA CAPTURE parameter, what is its actual name?
0
 
momi_sabagCommented:
for no logging
not logged initially

for no data capute
data capture none

you can look up the create / alter table command in the udb information center
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
mersisAuthor Commented:
thanks momi_sabaq, will try it out!
0
 
ghp7000Commented:
I suspect your question is related to a performance issue, in that you think the logging is impacting performance too much. If this is the case, then a more detailed response is necessary, or is your question simply I want to turn logging off because I dont think I need it?
0
 
mersisAuthor Commented:
ghp7000, you got me exactly right! All I am after is improving performance, which is pretty low at the moment. I have tried the by momi_sabaq suggested keywords but it had 0 impact on performance. Would hugely appreciate if you could advice me on that. (whatever I do, I would still like to be able to call rollback and commit if necessary).
0
 
momi_sabagCommented:
what seems to be the most resource consuming process in your system ?
pleasee provide input about the processes whos performance you need to improve
0
 
ghp7000Commented:
the first place to look for performance improvements with respect to logging is the disk subsystem. If you are 'I/O Bound', it means no matter what improvements you make, db2 cant go any faster because it is waiting for disk read/write to complete.
So, if you have your db and your log files on the same disk, and your disk is somewhat slow, or, if you have archiving turned on (means online backup possible) and your archive logs are being sent to a network location that has poor performance (or a slow network card in the server), these are easy places to look and easy things to fix.
If none of the above is true, then you have to start looking at tuning the db. Of course, tuning is an art and the main reason why dba's exist, but for the novice or developer, it can be frustrating and time consuming. I will try and keep it simple
first, log on to db and issue:
db2 get db cfg or
dont log in and issue
db2 get db cfg for <dbname>
if you have grep, you can make your life easier by doing
db2 get db cfg for <dbname> | grep LOG
First, look at the LOGFILSZ. It is the number of pages allocated per log file * 4KB. So if LOGFILSIZ=5000, each log file is 20MB in size.
Next, look at LOGPRIMARY. This value tells db2 how many log files to create upon first connection to the db, if the db is operating in NO ARCHIVE mode (only offline backup possible). If it is set to high a value, that first connection will wait quite some time before work can continue. In this case, first connection means first connection after a db2start, or first connection after ACTIVATE DATABASE command.
Next, look at LOGSECOND. If LOGPRIMARY=1 and LOGSECOND=180, db2 will begin to immediately create new log files once that first log file fills up. It will continue to create log files (as needed) until a maximum of 180 have been created. Then, once the last application disconnects, all those log files are destroyed and the whole process starts all over again with the next first connection.
Next, confirm where the log files are being written to with the Path to log files setting. If the data files for the db and the log files are on the same disk, you will get disk contention and disk wait.
Next, look at the LOGBUFSZ parameter. These determine the size of the memory buffers that hold all the transactions until commit time, at which time the committed changes are written to the log files. If LOGBUFSZ is too small, it forces db2 to write to the log files more often, creating more I/O. Note that this value is in 4KB pages, and the memory allocated is taken out of the DBHEAP parameter, so if DBHEAP is too small, then you wont be able to increase LOGBUFSZ.
I think that will keep you busy for now, please post what you have found out.

0
 
tliottaCommented:
Note that depending on what platform is involved (1) logging might be _required_ in order for COMMIT/ROLLBACK even to be possible and (2) such logging might _increase_ performance.

Tom
0
 
ghp7000Commented:
you cannot turn logging off in db2, it is required for crash recovery
0
 
tliottaCommented:
Minor note... you can turn logging off in DB2 if you are running on a platform that supports it, e.g., i5/OS (or OS/400). But, the part about "...it is required [for crash recovery]" is correct -- if you don't want crash recovery, logging is not needed. Many (short-sighted) sites do exactly that.

Tom
0
 
ghp7000Commented:
thanks for the clarification, as you can see, I have no experience working with AS400, but I sure wouldnt mind getting some!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now