Where do dynamic logs get created? Speculation on DYNAMIC_LOGS parameter

This is something I may have missed trying to read all that now doc in a short time. Question: When you set your server to dynamically create logs (rather than risking having them fill), in which dbspace do they get created? I don't recall seeing any control over where the log would be created if the server is left to its own devices. My bet is, of course, on the rootdbs. My concern: This conflicts with my preferred way of maintaining logs: Whenever possible, on all servers I have configured in the past 13 years, I have always put the logs in a separate dbspace. (In one case, I was able to configure 2 log dbspaces, on two distinct platters, creating the logs on the alternate dbspaces. That way, one full log could be backed up while the other is being written, with no disk-head contention. OK, so mine is not always the last word. :-| ) FWIW, my preferred option is to set DYNAMIC_LOGS to 1; I prefer to decide where the logs would go. If it were set to 2, I would also be concerned that a rogue transaction could cause the indiscriminate addition of new logs. Fly in the ointment: If my ALARMPROGRAM action would be to run a script that intelligently adds a new log (in the dbspace that I designate), I could run into this same problem even I set DYNAMIC_LOGS to 1. Solution: Have my ALARMPROGRAM script just send an e-mail to all the DBAs. This train of though has led me to speculate on the actual benefit of DYNAMIC_LOGS. This should be a GREAT feature. What am I missing? Discussion fodder? (In the interest of brevity ;=^| I have not plumbed all possible solutions.) -- Jacob

On 24 Nov, 18:29, Jacob Salomon <SpamnTr...@yahoo.com> wrote: > This is something I may have missed trying to read all that now doc in a > short time. > > Question: > When you set your server to dynamically create logs (rather than risking > having them fill), in which dbspace do they get created?  I don't recall >   seeing any control over where the log would be created if the server > is left to its own devices.  My bet is, of course, on the rootdbs. > > My concern: This conflicts with my preferred way of maintaining logs: > Whenever possible, on all servers I have configured in the past 13 > years, I have always put the logs in a separate dbspace.  (In one case, > I was able to configure 2 log dbspaces, on two distinct platters, > creating the logs on the alternate dbspaces.  That way, one full log > could be backed up while the other is being written, with no disk-head > contention.  OK, so mine is not always the last word. :-|  ) > > FWIW, my preferred option is to set DYNAMIC_LOGS to 1;  I prefer to > decide where the logs would go.  If it were set to 2, I would also be > concerned that a rogue transaction could cause the indiscriminate > addition of new logs.  Fly in the ointment: If my ALARMPROGRAM action > would be to run a script that intelligently adds a new log (in the > dbspace that I designate), I could run into this same problem even I set > DYNAMIC_LOGS to 1.  Solution: Have my ALARMPROGRAM script just send an > e-mail to all the DBAs. > > This train of though has led me to speculate on the actual benefit of > DYNAMIC_LOGS.  This should be a GREAT feature. What am I missing? > > Discussion fodder?  (In the interest of brevity ;=^| I have not plumbed > all possible solutions.) > > -- Jacob You did not say which version and you clearly did not read the manuals (as usual): For version 11.50 http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.admin.doc/ids_admin_0739.htm "The database server allocates log files in dbspaces, in the following search order. A dbspace becomes critical if it contains logical-log files or the physical log. Pass Allocate Log File In 1 The dbspace that contains the newest log files (If this dbspace is full, the database server searches other dbspaces.) 2 Mirrored dbspace that contains log files (but excluding the root dbspace) 3 All dbspaces that already contain log files (excluding the root dbspace) 4 The dbspace that contains the physical log 5 The root dbspace 6 Any mirrored dbspace 7 Any dbspace" For version 10 http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.admin.doc/admin531.htm "Location of Dynamically Added Log Files The database server allocates log files in dbspaces, in the following search order. A dbspace becomes critical if it contains logical-log files or the physical log. Pass Allocate Log File In 1 The dbspace that contains the newest log files (If this dbspace is full, the database server searches other dbspaces.) 2 Mirrored dbspace that contains log files (but excluding the root dbspace) 3 All dbspaces that already contain log files (excluding the root dbspace) 4 The dbspace that contains the physical log 5 The root dbspace 6 Any mirrored dbspace 7 Any dbspace " Version 9.4 http://publib.boulder.ibm.com/epubs/pdf/ct1ucna.pdf (Admin Guide 14-16) "The database server allocates log files in dbspaces, in the following search order.Adbspace becomes critical if it contains logical-log files or the physical log. Pass Allocate Log File In 1 The dbspace that contains the newest log files (If this dbspace is full, the database server searches other dbspaces.) 2 Mirrored dbspace that contains log files (but excluding the root dbspace) 3 All dbspaces that already contain log files (excluding the root dbspace) 4 The dbspace that contains the physical log 5 The root dbspace 6 Any mirrored dbspace 7 Any dbspace " Version 9.3 http://publib.boulder.ibm.com/epubs/pdf/8324.pdf (Admin Guide Page 14-16/14-17) "The database server allocates log files in dbspaces, in the following search order.Adbspace becomes critical if it contains logical-log files or the physical log. Pass Allocate Log File In 1 The dbspace that contains the newest log files (If this dbspace is full, the database server searches other dbspaces.) 2 Mirrored dbspace that contains log files (but excluding the root dbspace) 3 All dbspaces that already contain log files (excluding the root dbspace) 4 The dbspace that contains the physical log 5 The root dbspace 6 Any mirrored dbspace 7 Any dbspace"

david@smooth1.co.uk wrote: > On 24 Nov, 18:29, Jacob Salomon <SpamnTr...@yahoo.com> wrote: >> This is something I may have missed trying to read all that now doc in a >> short time. >> >> Question: >> When you set your server to dynamically create logs (rather than risking >> having them fill), in which dbspace do they get created? I don't recall >> seeing any control over where the log would be created if the server >> is left to its own devices. My bet is, of course, on the rootdbs. >> >> My concern: This conflicts with my preferred way of maintaining logs: >> Whenever possible, on all servers I have configured in the past 13 >> years, I have always put the logs in a separate dbspace. (In one case, >> I was able to configure 2 log dbspaces, on two distinct platters, >> creating the logs on the alternate dbspaces. That way, one full log >> could be backed up while the other is being written, with no disk-head >> contention. OK, so mine is not always the last word. :-| ) >> >> FWIW, my preferred option is to set DYNAMIC_LOGS to 1; I prefer to >> decide where the logs would go. If it were set to 2, I would also be >> concerned that a rogue transaction could cause the indiscriminate >> addition of new logs. Fly in the ointment: If my ALARMPROGRAM action >> would be to run a script that intelligently adds a new log (in the >> dbspace that I designate), I could run into this same problem even I set >> DYNAMIC_LOGS to 1. Solution: Have my ALARMPROGRAM script just send an >> e-mail to all the DBAs. >> >> This train of though has led me to speculate on the actual benefit of >> DYNAMIC_LOGS. This should be a GREAT feature. What am I missing? >> >> Discussion fodder? (In the interest of brevity ;=^| I have not plumbed >> all possible solutions.) >> >> -- Jacob > > You did not say which version and you clearly did not read the manuals > (as usual): > > For version 11.50 > > http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.admin.doc/ids_admin_0739.htm > > "The database server allocates log files in dbspaces, in the following > search order. A dbspace becomes critical if it contains logical-log > files or the physical log. > > Pass > Allocate Log File In > 1 > The dbspace that contains the newest log files > > (If this dbspace is full, the database server searches other > dbspaces.) > 2 > Mirrored dbspace that contains log files (but excluding the root > dbspace) > 3 > All dbspaces that already contain log files (excluding the root > dbspace) > 4 > The dbspace that contains the physical log > 5 > The root dbspace > 6 > Any mirrored dbspace > 7 > Any dbspace" > > For version 10 http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.admin.doc/admin531.htm > > "Location of Dynamically Added Log Files > > The database server allocates log files in dbspaces, in the following > search order. A dbspace becomes critical if it contains logical-log > files or the physical log. > > Pass > Allocate Log File In > 1 > The dbspace that contains the newest log files > > (If this dbspace is full, the database server searches other > dbspaces.) > 2 > Mirrored dbspace that contains log files (but excluding the root > dbspace) > 3 > All dbspaces that already contain log files (excluding the root > dbspace) > 4 > The dbspace that contains the physical log > 5 > The root dbspace > 6 > Any mirrored dbspace > 7 > Any dbspace " > > > Version 9.4 http://publib.boulder.ibm.com/epubs/pdf/ct1ucna.pdf (Admin > Guide 14-16) > > "The database server allocates log files in dbspaces, in the following > search > order.Adbspace becomes critical if it contains logical-log files or > the physical > log. > Pass Allocate Log File In > 1 The dbspace that contains the newest log files > (If this dbspace is full, the database server searches other > dbspaces.) > 2 Mirrored dbspace that contains log files (but excluding the root > dbspace) > 3 All dbspaces that already contain log files (excluding the root > dbspace) > 4 The dbspace that contains the physical log > 5 The root dbspace > 6 Any mirrored dbspace > 7 Any dbspace > " > > > Version 9.3 http://publib.boulder.ibm.com/epubs/pdf/8324.pdf (Admin > Guide Page 14-16/14-17) > > "The database server allocates log files in dbspaces, in the following > search > order.Adbspace becomes critical if it contains logical-log files or > the physical > log. > Pass Allocate Log File In > 1 The dbspace that contains the newest log files > (If this dbspace is full, the database server searches other > dbspaces.) > 2 Mirrored dbspace that contains log files (but excluding the > root dbspace) > 3 All dbspaces that already contain log files (excluding the root > dbspace) > 4 The dbspace that contains the physical log > 5 The root dbspace > 6 Any mirrored dbspace > 7 Any dbspace" Thank you David, for being helpful in your usual way. This confirms a few things for me, one of them being that I would indeed be more comfortable being alerted via an alarm mechanism and manually creating additional logs IF I see fit. Indeed, one sentence you did not quote states: >- If you do not want to use this search order to allocate the new log >- file, you must set the DYNAMIC_LOGS parameter to 1 and execute >- onparams -a -i with the location you want to use for the new log. However orderly this hierarchy is (and it does make intuitive sense), a rogue transaction that would have been rolled back would now (with DYNAMIC_LOGS = 2) fill these dbspaces in succession. That can't be good. I freely admit to a bias about how things should be done. Again, David, thank you for pointing out the location of the information. -- Jacob

Jacob Salomon wrote: > > > david@smooth1.co.uk wrote: >> On 24 Nov, 18:29, Jacob Salomon <SpamnTr...@yahoo.com> wrote: >>> This is something I may have missed trying to read all that now doc in a >>> short time. >>> >>> Question: >>> When you set your server to dynamically create logs (rather than risking >>> having them fill), in which dbspace do they get created? I don't recall >>> seeing any control over where the log would be created if the server >>> is left to its own devices. My bet is, of course, on the rootdbs. >>> >>> My concern: This conflicts with my preferred way of maintaining logs: >>> Whenever possible, on all servers I have configured in the past 13 >>> years, I have always put the logs in a separate dbspace. (In one case, >>> I was able to configure 2 log dbspaces, on two distinct platters, >>> creating the logs on the alternate dbspaces. That way, one full log >>> could be backed up while the other is being written, with no disk-head >>> contention. OK, so mine is not always the last word. :-| ) >>> >>> FWIW, my preferred option is to set DYNAMIC_LOGS to 1; I prefer to >>> decide where the logs would go. If it were set to 2, I would also be >>> concerned that a rogue transaction could cause the indiscriminate >>> addition of new logs. Fly in the ointment: If my ALARMPROGRAM action >>> would be to run a script that intelligently adds a new log (in the >>> dbspace that I designate), I could run into this same problem even I set >>> DYNAMIC_LOGS to 1. Solution: Have my ALARMPROGRAM script just send an >>> e-mail to all the DBAs. >>> >>> This train of though has led me to speculate on the actual benefit of >>> DYNAMIC_LOGS. This should be a GREAT feature. What am I missing? >>> >>> Discussion fodder? (In the interest of brevity ;=^| I have not plumbed >>> all possible solutions.) >>> Just to follow on with this : 1. DYNAMIC_LOGS only help in the situation of a "long transaction" being rolled back, not just "your logical logs are full" - so a down storage manager will cause the instance to hang if the logical logs are full. From the manual : If DYNAMIC_LOGS is 2, the database server automatically allocates a new log file when the next active log file contains an open transaction. Dynamic-log allocation prevents long transaction rollbacks from hanging the system. 2. An overzealous Developer was let loose on one of my test instances, and kept getting long transactions, as I had set DYNAMIC_LOGS to 2, and LTXHWM / LTXEHWM set to the "defaults" - he just kept return down to keep executing his "update loads of rows" sql ... after the addition of 230 logical logs (and a load of "long transaction aborted messages) all over my instance, he gave up :O. This resulted in just about every dbspace having a logical log added :-/

On 25 Nov, 13:15, theBP <th...@Usenet-News.Net> wrote: > Jacob Salomon wrote: > > > da...@smooth1.co.uk wrote: > >> On 24 Nov, 18:29, Jacob Salomon <SpamnTr...@yahoo.com> wrote: > >>> This is something I may have missed trying to read all that now doc in a > >>> short time. > > >>> Question: > >>> When you set your server to dynamically create logs (rather than risking > >>> having them fill), in which dbspace do they get created?  I don't recall > >>>   seeing any control over where the log would be created if the server > >>> is left to its own devices.  My bet is, of course, on the rootdbs. > > >>> My concern: This conflicts with my preferred way of maintaining logs: > >>> Whenever possible, on all servers I have configured in the past 13 > >>> years, I have always put the logs in a separate dbspace.  (In one case, > >>> I was able to configure 2 log dbspaces, on two distinct platters, > >>> creating the logs on the alternate dbspaces.  That way, one full log > >>> could be backed up while the other is being written, with no disk-head > >>> contention.  OK, so mine is not always the last word. :-|  ) > > >>> FWIW, my preferred option is to set DYNAMIC_LOGS to 1;  I prefer to > >>> decide where the logs would go.  If it were set to 2, I would also be > >>> concerned that a rogue transaction could cause the indiscriminate > >>> addition of new logs.  Fly in the ointment: If my ALARMPROGRAM action > >>> would be to run a script that intelligently adds a new log (in the > >>> dbspace that I designate), I could run into this same problem even I set > >>> DYNAMIC_LOGS to 1.  Solution: Have my ALARMPROGRAM script just send an > >>> e-mail to all the DBAs. > > >>> This train of though has led me to speculate on the actual benefit of > >>> DYNAMIC_LOGS.  This should be a GREAT feature. What am I missing? > > >>> Discussion fodder?  (In the interest of brevity ;=^| I have not plumbed > >>> all possible solutions.) > > Just to follow on with this : > > 1. DYNAMIC_LOGS only help in the situation of a "long transaction" being rolled back, not just "your logical logs are full" - so a > down storage manager will cause the instance to hang if the logical logs are full. > >  From the manual : > > If DYNAMIC_LOGS is 2, the database server automatically allocates a new log file when the next active log file contains an open > transaction. Dynamic-log allocation prevents long transaction rollbacks from hanging the system. > > 2. An overzealous Developer was let loose on one of my test instances, and kept getting long transactions, as I had set DYNAMIC_LOGS > to 2, and LTXHWM / LTXEHWM set to the "defaults" - he just kept return down to keep executing his "update loads of rows" sql ... > after the addition of 230 logical logs (and a load of "long transaction aborted messages) all over my instance, he gave up :O. This > resulted in just about every dbspace having a logical log added :-/ Well you should set LTXHWM / LTXEHWM to 30/40 (not the defaults) to try and ensure all transactions can rollback, disk space is cheap these days :->> Don't forget as well that you can easily delete logical logs afterwards with use of onmode -c,onmode -l, log backups and onparams.