Re: Table unloads without logging [762]

> Alexey Sonkin > Senior Database Administrator > Grand Virtual, Inc. > (1-617) 547-6323 x245 > > > > -----Original Message----- > > From: Ferry, Craig [mailto:crferry@wescodist.com] > > Sent: Tuesday, January 20, 2004 4:28 PM > > To: classics@iiug.org > > Subject: Table unloads without logging [762] > > > > We unload our informix tables nightly as part of our backup procedure. > > We also do nightly processing on the database. Due to the length that > > both of these processes take, we have to perform them simultaneously. > > Occasionally we receive errors as part of our nightly processing similar > > to the following message. I am guessing this may be because the table > > unloads are running. Is there a way to unload the tables (via dbaccess) > > without logging? > > > > 01:22:49 Aborting Long Transaction: TX 0xc000000078acd800 username:> > wispier aid: 137 > > > > TEA > > > > Craig > > sending to informix-list I don't think this is related to unloading the tables unless the isolation level is repeatable read. So, you could unload with dirty read, but the data would be unloaded in whatever state the table is in when the database gets around to reading it. But if it is related to the unloads create more logs to fix the problem. But to really solve your problem I would take a step back and ask yourself why you are unloading the tables as a form of backup, because this seems like a strange way to do a backup. Have you thought of using onbar or ontape to backup the database? You can say that doesn't give you table level restore but you can restore the database to another machine and pull the data out of whichever table you want. You can even restore it to another machine and then unload all of the tables so as not to interfere with the nightly processing. This would give you multiprocessing ;-) and would speed up the nightly processing. And if you only need to restore the tables occasionally you would only restore to the other machine when you needed the table, saving you even more time unloading tables. Viability of this method depends on a couple of things: having another machine to restore to (duh), size of the database and length of time to do the backup and restore, whether you need the table data preprocessing or postprocessing or both or it doesn't matter. It seems like it doesn't matter since you are taking the current unloads while the processing is going on. Of course this wouldn't fix the long transactions because you don't have enough logs. If you get a chance could you explain why you are using table unloads as a backup method. If I knew your reasoning I might be able to solve your problem mo' betta. 'cause I can think of several other ways to solve this problem but they involve changes to your process. ====== "It is always the last place you look." Well of course it is, if you continue looking after you find it you're quite insane. Of course there is a useful corollary: If you haven't found it, you haven't looked in the right place, yet.