Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.
DB2 log setup and configuration
hi,
as MySQL configuration only allow one db log for ALL user DB. how about DB2 ? it should has one or more log file for EACH user database inside, right?
as MySQL configuration only allow one db log for ALL user DB. how about DB2 ? it should has one or more log file for EACH user database inside, right?
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
There are many options though (DB2 Version 9.7 for Linux, UNIX, and Windows):
Configuring database logging options:
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.admin.ha.doc/doc/t0021274.html
Configuration parameters for database logging
Configuring database logging options:
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.admin.ha.doc/doc/t0021274.html
Configuration parameters for database logging
You can use database logs to record transaction information, synchronize primary and secondary or standby databases, and rollforward a secondary database that has taken over for a failed primary database. To configure these database logging activities according to your needs, you must set a variety of database configuration parameters.https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.ha.doc/doc/r0006082.html
Hi!
Note that even though I said that there is one transaction log per database in any DBMS (including DB2 ) then this transaction-log (logtream) is often divided physically in to multiple files for speed and "easy" log-management.
Configurations of the size of each physical logfile and how many logfiles should be created physically on disk by default and in continuous fashion should be configured and sized according to how big the workload will be and should be big enough to handle multiple simultaneous UOW of any size.
In DB2 you can have up to 256 primary logfiles allocated on disk and infinate secondary logfiles ( locsecond = -1 ). However that needs some configuration on archiving methods and overflow storage configurations.
Otherwise logprimary+logsecond <= 256 logfiles.
See here for further info and those links that NerdsOfTech provided.
Logfilz is the size of each logfile and should be big enough as I said to store multiple logrecords and multiple simultaneous UOW.
Regards,
Tomas Helgi
Note that even though I said that there is one transaction log per database in any DBMS (including DB2 ) then this transaction-log (logtream) is often divided physically in to multiple files for speed and "easy" log-management.
Configurations of the size of each physical logfile and how many logfiles should be created physically on disk by default and in continuous fashion should be configured and sized according to how big the workload will be and should be big enough to handle multiple simultaneous UOW of any size.
In DB2 you can have up to 256 primary logfiles allocated on disk and infinate secondary logfiles ( locsecond = -1 ). However that needs some configuration on archiving methods and overflow storage configurations.
Otherwise logprimary+logsecond <= 256 logfiles.
See here for further info and those links that NerdsOfTech provided.
Logfilz is the size of each logfile and should be big enough as I said to store multiple logrecords and multiple simultaneous UOW.
Regards,
Tomas Helgi
"Note that even though I said that there is one transaction log per database in any DBMS (including DB2 ) then this transaction-log (logtream) is often divided physically in to multiple files for speed and "easy" log-management. "
in MySQL it is not . please correct me if I am wrong.
"In DB2 you can have up to 256 primary logfiles allocated on disk and infinate secondary logfiles ( locsecond = -1 ). However that needs some configuration on archiving methods and overflow storage configurations. "
in DB2, more than one log file can also be use at the same time to off load the loading of log writing?
in MySQL it is not . please correct me if I am wrong.
"In DB2 you can have up to 256 primary logfiles allocated on disk and infinate secondary logfiles ( locsecond = -1 ). However that needs some configuration on archiving methods and overflow storage configurations. "
in DB2, more than one log file can also be use at the same time to off load the loading of log writing?
Hi!
By design then MySQL/MariaDB has one transaction-log (the bin-log ) per mysql instance. That is every database under that instance relies on that bin-log.
This design is also on DB2 on z/OS where there is one transaction-log per subsystem (instance). This design, that is one transaction log per instance, as is on DB2 z/OS, MySQL and MariaDB, puts that transaction log in the highest critical category of the whole system and needs to be protected against any loss or corruption to be able to do recovery to point in time for any database and/or table in that particular database in the instance.
However DB2 on LUW is designed in such way that every database has it's own transaction log so if one transaction log corrupts by missing log-files or corrupt physical files then that will not affect other databases under that particular DB2 instance.
Regards,
Tomas Helgi
By design then MySQL/MariaDB has one transaction-log (the bin-log ) per mysql instance. That is every database under that instance relies on that bin-log.
This design is also on DB2 on z/OS where there is one transaction-log per subsystem (instance). This design, that is one transaction log per instance, as is on DB2 z/OS, MySQL and MariaDB, puts that transaction log in the highest critical category of the whole system and needs to be protected against any loss or corruption to be able to do recovery to point in time for any database and/or table in that particular database in the instance.
However DB2 on LUW is designed in such way that every database has it's own transaction log so if one transaction log corrupts by missing log-files or corrupt physical files then that will not affect other databases under that particular DB2 instance.
Regards,
Tomas Helgi
"By design then MySQL/MariaDB has one transaction-log (the bin-log ) per mysql instance. That is every database under that instance relies on that bin-log. "
this is what I meant !
" However DB2 on LUW is designed in such way that every database has it's own transaction log so if one transaction log corrupts by missing log-files or corrupt physical files then that will not affect other databases under that particular DB2 instance.
"
so DB2 still better and we need additional configuration for it?
this is what I meant !
" However DB2 on LUW is designed in such way that every database has it's own transaction log so if one transaction log corrupts by missing log-files or corrupt physical files then that will not affect other databases under that particular DB2 instance.
"
so DB2 still better and we need additional configuration for it?
Hi!
>so DB2 still better and we need additional configuration for it?
It depends on what your requirements are for the database system at whole. :)
The DB2 database logs are put by default in the same path as the database itself with a default size of 1000 (4k pages) but you can easilly configure numbers of primary/secondary logfiles, logfilesize and logpath using the db2 commands.
The command (in *nix)
Regards,
Tomas Helgi
>so DB2 still better and we need additional configuration for it?
It depends on what your requirements are for the database system at whole. :)
The DB2 database logs are put by default in the same path as the database itself with a default size of 1000 (4k pages) but you can easilly configure numbers of primary/secondary logfiles, logfilesize and logpath using the db2 commands.
The command (in *nix)
db2 get db cfg for databasename | grep LOG
Regards,
Tomas Helgi
"The DB2 database logs are put by default in the same path as the database itself with a default size of 1000 (4k pages)"
tks.
"but you can easilly configure numbers of primary/secondary logfiles, logfilesize and logpath using the db2 commands.
The command (in *nix)"
tks. it should be easily configurable .
tks.
"but you can easilly configure numbers of primary/secondary logfiles, logfilesize and logpath using the db2 commands.
The command (in *nix)"
tks. it should be easily configurable .
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Linux
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
There is as in any other database system one transaction log associated with each database on any given host/instance.
Regards,
Tomas Helgi