Stupid DBA tricks

Hi All, So, I have a brand new system from my own "learning experience": a single CPU box with 2 4GB Hard drives and SUN OS 5.7 I'm running IDS 2000 9.21.UC1 in this test environment while preparing to migrate the "real" servers to IDS 2000 within the next few months. As I was copying the current database from the live servers to this test environment, I ran into a Long Transaction problem ("Load from ...." on a large table). Instead of using 'hpl' or even breaking this file into parts, I upped the LTXHWM and LTXEHWM to 70 and 80, respectively. Of course, I also forgot to set LBU-Preserve to 1. Needless to say, this time when the Long Transaction hit, it didn't bomb the load, but instead hung the DB. The online.log message read "Waiting for Next logical Log". Trouble with a Capital T! I couldn't force a checkpoint because the logs were totally full. I tried 'ontape -a' but got an error "LogBackup to /dev/null not allowed". I'm assuming that this is also because there were no free logical logs to write out the fact that an archive was being performed. Since this is "my" play server and the DB was just being built, I took the easy way out and ran an 'oninit -i' and started over from scratch. For my future knowledge and further education, should I ever decide to listen to the little voices in my head saying "why not try *this*?" and end up in this situation again, is there ANY way to bounce the database and *** BYPASS *** the Fast Recovery step? Or was my "easy way out" really my only choice anyway? Thanks, Michael Hoffman

1) Use dbload to automatically break the load file into multiple transactions. 2) Never (on currently released versions) set LTXEHWM above 60.. This probably won't be an issue with 9.3, but I can't talk about that just yet..... Michael Hoffman wrote: > Hi All, > So, I have a brand new system from my own "learning experience": a > single CPU box with 2 4GB Hard drives and SUN OS 5.7 I'm running IDS 2000 > 9.21.UC1 in this test environment while preparing to migrate the "real" > servers to IDS 2000 within the next few months. > > As I was copying the current database from the live servers to this > test environment, I ran into a Long Transaction problem ("Load from ...." on > a large table). Instead of using 'hpl' or even breaking this file into parts, > I upped the LTXHWM and LTXEHWM to 70 and 80, respectively. Of course, I also > forgot to set LBU-Preserve to 1. > > Needless to say, this time when the Long Transaction hit, it didn't > bomb the load, but instead hung the DB. The online.log message read "Waiting > for Next logical Log". Trouble with a Capital T! I couldn't force a > checkpoint because the logs were totally full. I tried 'ontape -a' but got an > error "LogBackup to /dev/null not allowed". I'm assuming that this is also > because there were no free logical logs to write out the fact that an archive > was being performed. > > Since this is "my" play server and the DB was just being built, I took > the easy way out and ran an 'oninit -i' and started over from scratch. > > For my future knowledge and further education, should I ever decide to > listen to the little voices in my head saying "why not try *this*?" and end > up in this situation again, is there ANY way to bounce the database and > *** BYPASS *** the Fast Recovery step? Or was my "easy way out" really my > only choice anyway? > > Thanks, > Michael Hoffman

In <3A75BEED.737FD43F@home.com> article, Madison Pruet mentioned that: : 1) Use dbload to automatically break the load file into multiple transactions. I want to see if 'hpl' will work first, but dbload is my last, and safest, option. As I have the time to play with this machine, I have the freedom to break it over and over again until I fully understand everything I'm doing and all the consequences. : 2) Never (on currently released versions) set LTXEHWM above 60.. Thus, the subject "Stupid DBA tricks". Sort of like the kid who puts his tongue on the frozen flagpole. Knowing it's a dumb idea doesn't always stop us from doing it. :-) : This probably won't be an issue with 9.3, but I can't talk about that just : yet..... : Michael Hoffman wrote: :> Hi All, :> So, I have a brand new system from my own "learning experience": a :> single CPU box with 2 4GB Hard drives and SUN OS 5.7 I'm running IDS 2000 :> 9.21.UC1 in this test environment while preparing to migrate the "real" :> servers to IDS 2000 within the next few months. :> :> As I was copying the current database from the live servers to this :> test environment, I ran into a Long Transaction problem ("Load from ...." on :> a large table). Instead of using 'hpl' or even breaking this file into parts, :> I upped the LTXHWM and LTXEHWM to 70 and 80, respectively. Of course, I also :> forgot to set LBU-Preserve to 1. :> :> Needless to say, this time when the Long Transaction hit, it didn't :> bomb the load, but instead hung the DB. The online.log message read "Waiting :> for Next logical Log". Trouble with a Capital T! I couldn't force a :> checkpoint because the logs were totally full. I tried 'ontape -a' but got an :> error "LogBackup to /dev/null not allowed". I'm assuming that this is also :> because there were no free logical logs to write out the fact that an archive :> was being performed. :> :> Since this is "my" play server and the DB was just being built, I took :> the easy way out and ran an 'oninit -i' and started over from scratch. :> :> For my future knowledge and further education, should I ever decide to :> listen to the little voices in my head saying "why not try *this*?" and end :> up in this situation again, is there ANY way to bounce the database and :> *** BYPASS *** the Fast Recovery step? Or was my "easy way out" really my :> only choice anyway? :> :> Thanks, :> Michael Hoffman

> : 2) Never (on currently released versions) set LTXEHWM above 60.. > > Thus, the subject "Stupid DBA tricks". Sort of like the kid who puts his > tongue on the frozen flagpole. Knowing it's a dumb idea doesn't always stop > us from doing it. :-) And from calling downed systems for help once it's done.... ;-) > > > : This probably won't be an issue with 9.3, but I can't talk about that just > : yet..... > > : Michael Hoffman wrote: > > :> Hi All, > :> So, I have a brand new system from my own "learning experience": a > :> single CPU box with 2 4GB Hard drives and SUN OS 5.7 I'm running IDS 2000 > :> 9.21.UC1 in this test environment while preparing to migrate the "real" > :> servers to IDS 2000 within the next few months. > :> > :> As I was copying the current database from the live servers to this > :> test environment, I ran into a Long Transaction problem ("Load from ...." on > :> a large table). Instead of using 'hpl' or even breaking this file into parts, > :> I upped the LTXHWM and LTXEHWM to 70 and 80, respectively. Of course, I also > :> forgot to set LBU-Preserve to 1. > :> > :> Needless to say, this time when the Long Transaction hit, it didn't > :> bomb the load, but instead hung the DB. The online.log message read "Waiting > :> for Next logical Log". Trouble with a Capital T! I couldn't force a > :> checkpoint because the logs were totally full. I tried 'ontape -a' but got an > :> error "LogBackup to /dev/null not allowed". I'm assuming that this is also > :> because there were no free logical logs to write out the fact that an archive > :> was being performed. > :> > :> Since this is "my" play server and the DB was just being built, I took > :> the easy way out and ran an 'oninit -i' and started over from scratch. > :> > :> For my future knowledge and further education, should I ever decide to > :> listen to the little voices in my head saying "why not try *this*?" and end > :> up in this situation again, is there ANY way to bounce the database and > :> *** BYPASS *** the Fast Recovery step? Or was my "easy way out" really my > :> only choice anyway? > :> > :> Thanks, > :> Michael Hoffman

Hi Michael, it`s a good idea to look into the server and to play with it. But before you make a change to a parameter you should read the manuals. LTXEHWM shouldn't be a parameter or in other words, there shouldn't be the possibility, to increase the percentage value. If you want to play with the system: create table t1 ( whateveryouwant char(1000), f1 serial ); create unique index t1idx01 on t1( f1 ); update systables set flags=8 where tabname = "t1"; Shutdown the IDS and bring it up again !!! load from "averylargefile" insert into t1; update systables set flags=0 where tabname = "t1"; Shutdown the IDS and bring it up again !!! Don't tell it anybody if it works ;-))) FOR EVERYBODY, DON'T TRY IT IF YOU HAVE 7.30 OR 9.20 SERVERS. Regards -- Stefan Weideneder Phone: +49 89/3565478-2 --------------- --- Fax: +49 89/3565478-3 ------------- ------ mailto:/stefan@weideneder.de --- -------- http://www.weideneder.de -----

> > create table t1 ( whateveryouwant char(1000), f1 serial ); > create unique index t1idx01 on t1( f1 ); > update systables set flags=8 where tabname = "t1";> > Shutdown the IDS and bring it up again !!! > > load from "averylargefile" insert into t1; > update systables set flags=0 where tabname = "t1";> > Shutdown the IDS and bring it up again !!! > > Don't tell it anybody if it works ;-))) Also, don't expect too much help from tech support when you call in for help after having done this. I really doubt that anyone in support would be able to figure out why your rowids and or CRCOLS suddenly became a visible column. Nor would they be able to figure out why you suddenly lost all of your table hierarchies. All they would be able to say would be somthing like "Well, I guess you'll need to restore from a backup."

Stefan, You see, I *had* read the manuals and I knew the implications of what I was attempting. My biggest mistake was forgetting to set LBU-PRESERVE = 1. The main point to my story was: is there any way to recover from this situation without having to restore from backup, or rebuild entirely? Oh, and as to the fastest way to get this load done.... Set up the DB with "No logging", run the already created load scripts ("Load from file"), create indexes, and then archive and turn Unbuffered Logging on (one 'ontape' command). Michael In <3A7677EE.1368E379@weideneder.de> article, Stefan Weideneder mentioned that: : Hi Michael, : it`s a good idea to look into the server and : to play with it. But before you make a change : to a parameter you should read the manuals. : LTXEHWM shouldn't be a parameter or in other : words, there shouldn't be the possibility, to : increase the percentage value. : If you want to play with the system: : create table t1 ( whateveryouwant char(1000), f1 serial ); : create unique index t1idx01 on t1( f1 ); : update systables set flags=8 where tabname = "t1"; : Shutdown the IDS and bring it up again !!! : load from "averylargefile" insert into t1; : update systables set flags=0 where tabname = "t1"; : Shutdown the IDS and bring it up again !!! : Don't tell it anybody if it works ;-))) : FOR EVERYBODY, DON'T TRY IT IF YOU HAVE : 7.30 OR 9.20 SERVERS. : Regards : -- : Stefan Weideneder : Phone: +49 89/3565478-2 --------------- : --- Fax: +49 89/3565478-3 ------------- : ------ mailto:/stefan@weideneder.de --- : -------- http://www.weideneder.de -----

In <3A774EE1.6C84F322@weideneder.de> article, Stefan Weideneder mentioned that: : Hi Michael, :> :> Stefan, :> You see, I *had* read the manuals and I knew the implications of what :> I was attempting. My biggest mistake was forgetting to set LBU-PRESERVE = 1. : help me if I'm wrong. The long transaction you : tried wouldn't be a long transaction if you`d : set LBU_PRESERVE=1 ? Why ? No... it would still have been a long transaction, but I wouldn't have been locked out of the DB. LBU-Preserve would have left one more Logical Log available so I could have run a log archive (ontape -a). I also would have been able to kill the transaction. As to using raw tables (mentioned below), I'll test that. Having this box all to myself means I can break it as often as I please :-). I'll let you know what I discover using raw tables and then converting them to standard. My constraints and indexes, of which there are several, can always be applied *after* converting. Thanks, Michael :> :> The main point to my story was: is there any way to recover from this :> situation without having to restore from backup, or rebuild entirely? : If all logical logs became full, and the BEGIN WORK : record resides rigth behind the current logcial log ? No, : I guess you must recover from your backup. :> Oh, and as to the fastest way to get this load done.... Set up the DB :> with "No logging", run the already created load scripts ("Load from file"), :> create indexes, and then archive and turn Unbuffered Logging on (one 'ontape' :> command). : If you can easily change the mode of your database, : just do it. But keep in mind that you have a : new feature called "raw" tables. These tables do : not use "logging" and there is no longer a need : to change the whole database ( you cannot switch : the logging mode of a database as long as other : sessions are using the database ). That's what : I wanted to tell you with my "update" statement. : The problem with those raw tables is that they : cannot have constraints or indexes. I`m still : looking for a workaround for this disadvantage : and the one I found was mentioned in my previous : reply. : But if you always want to change the database : logging mode I think you don't want to make use of : the new feature in version 9.21. : Best regards, : Stefan :> :> Michael :> :> In <3A7677EE.1368E379@weideneder.de> article, Stefan Weideneder mentioned that: :> : Hi Michael, :> :> : it`s a good idea to look into the server and :> : to play with it. But before you make a change :> : to a parameter you should read the manuals. :> :> : LTXEHWM shouldn't be a parameter or in other :> : words, there shouldn't be the possibility, to :> : increase the percentage value. :> :> : If you want to play with the system: :> :> : create table t1 ( whateveryouwant char(1000), f1 serial ); :> : create unique index t1idx01 on t1( f1 ); :> : update systables set flags=8 where tabname = "t1"; :> :> : Shutdown the IDS and bring it up again !!! :> :> : load from "averylargefile" insert into t1; :> : update systables set flags=0 where tabname = "t1"; :> :> : Shutdown the IDS and bring it up again !!! :> :> : Don't tell it anybody if it works ;-))) :> :> : FOR EVERYBODY, DON'T TRY IT IF YOU HAVE :> : 7.30 OR 9.20 SERVERS. :> :> : Regards :> :> : -- :> : Stefan Weideneder :> :> : Phone: +49 89/3565478-2 --------------- :> : --- Fax: +49 89/3565478-3 ------------- :> : ------ mailto:/stefan@weideneder.de --- :> : -------- http://www.weideneder.de ----- : -- : Stefan Weideneder : Phone: +49 89/3565478-2 --------------- : --- Fax: +49 89/3565478-3 ------------- : ------ mailto:/stefan@weideneder.de --- : -------- http://www.weideneder.de -----

Hi, okay, okay. The better way would be: create raw table t1 ( f1 int ); load from "whateveryouwant" insert into t1; alter table t1 type(standard);create index ...; Everyone who wants to see the benefits of this load operation should use it. I will check it out tomorrow if the other way ( never update a system table ) would end in a disaster. Best regards, Stefan Madison Pruet wrote: > > > > > create table t1 ( whateveryouwant char(1000), f1 serial ); > > create unique index t1idx01 on t1( f1 ); > > update systables set flags=8 where tabname = "t1";> > > > Shutdown the IDS and bring it up again !!! > > > > load from "averylargefile" insert into t1; > > update systables set flags=0 where tabname = "t1";> > > > Shutdown the IDS and bring it up again !!! > > > > Don't tell it anybody if it works ;-))) > > Also, don't expect too much help from tech support when you call in for > help after having done this. I really doubt that anyone in support would > be able to figure out why your rowids and or CRCOLS suddenly became a > visible column. Nor would they be able to figure out why you suddenly > lost all of your table hierarchies. All they would be able to say would > be somthing like "Well, I guess you'll need to restore from a backup." -- Stefan Weideneder Phone: +49 89/3565478-2 --------------- --- Fax: +49 89/3565478-3 ------------- ------ mailto:/stefan@weideneder.de --- -------- http://www.weideneder.de -----

Hi Michael, > > Stefan, > You see, I *had* read the manuals and I knew the implications of what > I was attempting. My biggest mistake was forgetting to set LBU-PRESERVE = 1. help me if I'm wrong. The long transaction you tried wouldn't be a long transaction if you`d set LBU_PRESERVE=1 ? Why ? > > The main point to my story was: is there any way to recover from this > situation without having to restore from backup, or rebuild entirely? If all logical logs became full, and the BEGIN WORK record resides rigth behind the current logcial log ? No, I guess you must recover from your backup. > Oh, and as to the fastest way to get this load done.... Set up the DB > with "No logging", run the already created load scripts ("Load from file"), > create indexes, and then archive and turn Unbuffered Logging on (one 'ontape' > command). If you can easily change the mode of your database, just do it. But keep in mind that you have a new feature called "raw" tables. These tables do not use "logging" and there is no longer a need to change the whole database ( you cannot switch the logging mode of a database as long as other sessions are using the database ). That's what I wanted to tell you with my "update" statement. The problem with those raw tables is that they cannot have constraints or indexes. I`m still looking for a workaround for this disadvantage and the one I found was mentioned in my previous reply. But if you always want to change the database logging mode I think you don't want to make use of the new feature in version 9.21. Best regards, Stefan > > Michael > > In <3A7677EE.1368E379@weideneder.de> article, Stefan Weideneder mentioned that: > : Hi Michael, > > : it`s a good idea to look into the server and > : to play with it. But before you make a change > : to a parameter you should read the manuals. > > : LTXEHWM shouldn't be a parameter or in other > : words, there shouldn't be the possibility, to > : increase the percentage value. > > : If you want to play with the system: > > : create table t1 ( whateveryouwant char(1000), f1 serial ); > : create unique index t1idx01 on t1( f1 ); > : update systables set flags=8 where tabname = "t1"; > > : Shutdown the IDS and bring it up again !!! > > : load from "averylargefile" insert into t1; > : update systables set flags=0 where tabname = "t1"; > > : Shutdown the IDS and bring it up again !!! > > : Don't tell it anybody if it works ;-))) > > : FOR EVERYBODY, DON'T TRY IT IF YOU HAVE > : 7.30 OR 9.20 SERVERS. > > : Regards > > : -- > : Stefan Weideneder > > : Phone: +49 89/3565478-2 --------------- > : --- Fax: +49 89/3565478-3 ------------- > : ------ mailto:/stefan@weideneder.de --- > : -------- http://www.weideneder.de ----- -- Stefan Weideneder Phone: +49 89/3565478-2 --------------- --- Fax: +49 89/3565478-3 ------------- ------ mailto:/stefan@weideneder.de --- -------- http://www.weideneder.de -----

In article <956nqj$rne$1@news.panix.com>, Michael Hoffman <mrh@panix.com> writes >Stefan, > You see, I *had* read the manuals and I knew the implications of what >I was attempting. My biggest mistake was forgetting to set LBU-PRESERVE = 1. > > The main point to my story was: is there any way to recover from this >situation without having to restore from backup, or rebuild entirely? > Yes! But not without getting an inconsistent database. Informix have tools tools ("tbone"??) to add a logical log WITHOUT logging the fact that the logical log has been added. But a) You have to log a system down problem with informix technical support b) You have to sign a disclaimer that you don't care if your database becomes inconsistent. c) You cannot rollforward the database across the point of failure. i.e. you need a valid level 0 archive taken immediately after the long transaction finishes rolling back. -- David Williams

On 29 Jan 2001 18:19:33 GMT, Michael Hoffman <mrh@panix.com> wrote: >Hi All, > So, I have a brand new system from my own "learning experience": a >single CPU box with 2 4GB Hard drives and SUN OS 5.7 I'm running IDS 2000 >9.21.UC1 in this test environment while preparing to migrate the "real" >servers to IDS 2000 within the next few months. > > As I was copying the current database from the live servers to this >test environment, I ran into a Long Transaction problem ("Load from ...." on >a large table). Instead of using 'hpl' or even breaking this file into parts, >I upped the LTXHWM and LTXEHWM to 70 and 80, respectively. Of course, I also >forgot to set LBU-Preserve to 1. > > Needless to say, this time when the Long Transaction hit, it didn't >bomb the load, but instead hung the DB. The online.log message read "Waiting >for Next logical Log". Trouble with a Capital T! I couldn't force a >checkpoint because the logs were totally full. I tried 'ontape -a' but got an >error "LogBackup to /dev/null not allowed". I'm assuming that this is also >because there were no free logical logs to write out the fact that an archive >was being performed. > > Since this is "my" play server and the DB was just being built, I took >the easy way out and ran an 'oninit -i' and started over from scratch. > > For my future knowledge and further education, should I ever decide to >listen to the little voices in my head saying "why not try *this*?" and end >up in this situation again, is there ANY way to bounce the database and >*** BYPASS *** the Fast Recovery step? Or was my "easy way out" really my >only choice anyway? Hi, Mochael ! ************ BYPASS ************* We had recently the same problem. (IDS 7.31, HP-UX 10.20) I was surprised nobody hadn't recommended to apply to Informix Service. There is a BYPASS to avoid "Fast Recovery". It is possible of course loosing last transactions. But this way could help. They realize that using "onstat -S …" command with a special options, which they say on the phone. After that you can remove a flag "Fast Recovery " and skip it. Sorry for my English. Traube Yuri. Trau@uso-ars.ru > >Thanks, >Michael Hoffman

Traube, This is not a good idea if fuzzy checkpoints are being used. Traube Yuri wrote: > On 29 Jan 2001 18:19:33 GMT, Michael Hoffman <mrh@panix.com> wrote: > > >Hi All, > > So, I have a brand new system from my own "learning experience": a > >single CPU box with 2 4GB Hard drives and SUN OS 5.7 I'm running IDS 2000 > >9.21.UC1 in this test environment while preparing to migrate the "real" > >servers to IDS 2000 within the next few months. > > > > As I was copying the current database from the live servers to this > >test environment, I ran into a Long Transaction problem ("Load from ...." on > >a large table). Instead of using 'hpl' or even breaking this file into parts, > >I upped the LTXHWM and LTXEHWM to 70 and 80, respectively. Of course, I also > >forgot to set LBU-Preserve to 1. > > > > Needless to say, this time when the Long Transaction hit, it didn't > >bomb the load, but instead hung the DB. The online.log message read "Waiting > >for Next logical Log". Trouble with a Capital T! I couldn't force a > >checkpoint because the logs were totally full. I tried 'ontape -a' but got an > >error "LogBackup to /dev/null not allowed". I'm assuming that this is also > >because there were no free logical logs to write out the fact that an archive > >was being performed. > > > > Since this is "my" play server and the DB was just being built, I took > >the easy way out and ran an 'oninit -i' and started over from scratch. > > > > For my future knowledge and further education, should I ever decide to > >listen to the little voices in my head saying "why not try *this*?" and end > >up in this situation again, is there ANY way to bounce the database and > >*** BYPASS *** the Fast Recovery step? Or was my "easy way out" really my > >only choice anyway? > > Hi, Mochael ! > > ************ BYPASS ************* > > We had recently the same problem. (IDS 7.31, HP-UX 10.20) > I was surprised nobody hadn't recommended to apply to Informix > Service. > There is a BYPASS to avoid "Fast Recovery". It is possible of course > loosing last transactions. But this way could help. > They realize that using "onstat -S …" command with a special options, > which they say on the phone. > After that you can remove a flag "Fast Recovery " and skip it. > > Sorry for my English. > > Traube Yuri. > Trau@uso-ars.ru > > > > >Thanks, > >Michael Hoffman