How do I restore a single table?

Hello, Does anyone know of an easy way to restore a single table from an Informix ontape backup? I'm using IDS 7.31 on Digital Unix 4.0 I know that I can restore the whole instance using ontape -r, but I that would mean that I would lose the work done in other tables. I have to resort to restoring the whole instance on another server since I don't want to interrupt work being done by users on the production system. Then I unload the data from the table from the restored instance and load this back into the production system. Please, tell me that there is an easier way to do this!!?? I can't believe that Informix has overlooked this very important task. How have others been coping with this shortcoming (by Informix)? Are there any 3rd party utilities that can provide this functionality? Thanks very much for your input. Regards, Samuel Lee. Systems Analyst. Trinidad & Tobago Electricity Commission. sleesing@ttec.co.tt Sent via Deja.com http://www.deja.com/ Before you buy.

sleesingchan@my-deja.com wrote: > > Hello, > > Does anyone know of an easy way to restore a single table from an > Informix ontape backup? I'm using IDS 7.31 on Digital Unix 4.0 > > I know that I can restore the whole instance using ontape -r, but I > that would mean that I would lose the work done in other tables. There is a utility, in the IIUG Software Repository, in the Special Software section, written a while ago by an Informix employee which is no longer maintained, it may not work with IDS 7.3x archives. Arcunload can read an IDS7.2x archive and extract a single non-fragmented table to a onunload format file which you can reload using Informix onload. Arcunload has trouble with tape sizes that are not an even multiple of the blocksize and with multiple volume archives and is ONLY available for some specific platform/version combinations. > I have to resort to restoring the whole instance on another server > since I don't want to interrupt work being done by users on the > production system. Then I unload the data from the table from the > restored instance and load this back into the production system. So do the rest of us. > Please, tell me that there is an easier way to do this!!?? Dbexport critical tables periodically and keep an audit trail. Also shooting fumble fingered programmers helps to reduce the frequency with which one is required to save someone's butt this way. > I can't believe that Informix has overlooked this very important task. They did not. They provide dbexport/dbimport, onunload/onload, dbload and other tools for saving and restoring individual tables mucked up by poor SQL, brain dead users, and other annoyances of the DBAs life. No to mention dbspace level restores if you are willing to create a dbspace for each table/database that is critical to your operation. > How have others been coping with this shortcoming (by Informix)? Remember the purpose of ontape/onarchive/onbar archives, as far as Informix us concerned, is to restore the engine to the state in which it was at the point of a crash of other catastrophic system failure. Not to replace rows deleted by an overzealous temp clerk with too much privelege in the database or to restore the transactions table to its pristene condition prior to someone running the new mass update loader without proper testing. For this purpose only a good database and application design with sufficient safeguards and an active and reversible audit trail will do! In a pinch dbexport several times a day is a fair stop gap until the new code is in. > Are there any 3rd party utilities that can provide this functionality? > Thanks very much for your input. Art S. Kagel

In article <392AB873.55B57228@bloomberg.net>, Art S. Kagel <kagel@bloomberg.net> writes > >There is a utility, in the IIUG Software Repository, in the Special >Software section, written a while ago by an Informix employee which is >no longer maintained, it may not work with IDS 7.3x archives. Arcunload >can read an IDS7.2x archive and extract a single non-fragmented table to I tried it recently on Solaris 2.6 against 7.31.UC4-1 and it kept failing. I tried the Sun Solaris 2.4 version for OnLine Dynamic Server version 7.20 (Untested on 7.21) OnLine Dynamic Server version 7.22, 7.23 (Untested on 7.21) both failed with an error writing to /tmp/arcucl..<something> file. Using truss showed that it open the file for writing (O_CREAT + O_TRUNC + writing), wrote something to it (I assume the table) and then errored trying to read from the file descriptor which was only opened for writing! Art, I'd like to write an new version but need the tape format for ontape, I'd be happy to sign an NDA. Any ideas? >a onunload format file which you can reload using Informix onload. >Arcunload has trouble with tape sizes that are not an even multiple of the >blocksize and with multiple volume archives and is ONLY available for >some specific platform/version combinations. > > >Art S. Kagel -- David Williams

Hi and thanks for the replies... Art, I understand and agree on your point about maintaining database integrity but I still think that Informix should have given us some sort of option/utility where we can do a single table restore, at our own risk of course. Since someone within Informix did take the time and effort to write an unsupported utility (which unluckily doesn't support my current version) means that it's a real problem that others seem to encounter often enough and have to deal with. From David's experience it also seems that the utility doesn't seem to to work for all versions of Informix. I took a look at running the dbexport regularly, but I realise that it requires the database to be "offline", meaning that no users must be connected while it is being run. This is not possible for us, even though we don't have a 24 X 7 system 'coz some users schedule jobs which may run overnight. So maybe the only solution for now, is to write a routine that unloads all the data from the database and write it out to tape. Someone also mentioned a utility from BMC called SQL-Backtrack that will allow partial restores so I'll check this out. Thank you. Samuel Lee. In article <cXVZqFAB4vK5Ew+g@smooth1.demon.co.uk>, David Williams <djw@smooth1.demon.co.uk> wrote: > In article <392AB873.55B57228@bloomberg.net>, Art S. Kagel > <kagel@bloomberg.net> writes > > > >There is a utility, in the IIUG Software Repository, in the Special > >Software section, written a while ago by an Informix employee which is > >no longer maintained, it may not work with IDS 7.3x archives. Arcunload > >can read an IDS7.2x archive and extract a single non-fragmented table to > > I tried it recently on Solaris 2.6 against 7.31.UC4-1 and it kept > failing. > > I tried the Sun Solaris 2.4 version for > > OnLine Dynamic Server version 7.20 (Untested on 7.21) > > OnLine Dynamic Server version 7.22, 7.23 (Untested on 7.21) > > both failed with an error writing to /tmp/arcucl..<something> file. > > Using truss showed that it open the file for writing > (O_CREAT + O_TRUNC + writing), wrote something to it > > (I assume the table) and then errored trying to read from the file > descriptor which was only opened for writing! > > Art, I'd like to write an new version but need the tape format for > ontape, I'd be happy to sign an NDA. Any ideas? > > >a onunload format file which you can reload using Informix onload. > >Arcunload has trouble with tape sizes that are not an even multiple of the > >blocksize and with multiple volume archives and is ONLY available for > >some specific platform/version combinations. > > > > > >Art S. Kagel > > -- > David Williams > Sent via Deja.com http://www.deja.com/ Before you buy.

sleesingchan@my-deja.com wrote: > > Hi and thanks for the replies... > > Art, I understand and agree on your point about maintaining database > integrity but I still think that Informix should have given us some > sort of option/utility where we can do a single table restore, at our > own risk of course. > Since someone within Informix did take the time and effort to write an > unsupported utility (which unluckily doesn't support my current > version) means that it's a real problem that others seem to encounter > often enough and have to deal with. > From David's experience it also seems that the utility doesn't seem to > to work for all versions of Informix. It is buggy and the author is no longer with Informix and while someone was assigned to hold the source internally word is no further support is to be forthcoming for arcunload. > I took a look at running the dbexport regularly, but I realise that it > requires the database to be "offline", meaning that no users must be > connected while it is being run. This is not possible for us, even Get my new package, myexport (which also required my utils2_ak and sqlcmd from Jonathan Leffler) which is a dbexport/dbimport replacement utility. Myexport/myimport do not lock the database during exporting. (If you try it and hit any problems let me know I use it here but do not know if there are any localisms in the scripts that may cause others problems.) > though we don't have a 24 X 7 system 'coz some users schedule jobs > which may run overnight. > So maybe the only solution for now, is to write a routine that unloads > all the data from the database and write it out to tape. Essentially I've done that for you in myexport. Then you can tar the export directory to tape. Art S. Kagel > Someone also mentioned a utility from BMC called SQL-Backtrack that > will allow partial restores so I'll check this out. > > Thank you. > Samuel Lee. > > In article <cXVZqFAB4vK5Ew+g@smooth1.demon.co.uk>, > David Williams <djw@smooth1.demon.co.uk> wrote: > > In article <392AB873.55B57228@bloomberg.net>, Art S. Kagel > > <kagel@bloomberg.net> writes > > > > > >There is a utility, in the IIUG Software Repository, in the Special > > >Software section, written a while ago by an Informix employee which > is > > >no longer maintained, it may not work with IDS 7.3x archives. > Arcunload > > >can read an IDS7.2x archive and extract a single non-fragmented > table to > > > > I tried it recently on Solaris 2.6 against 7.31.UC4-1 and it kept > > failing. > > > > I tried the Sun Solaris 2.4 version for > > > > OnLine Dynamic Server version 7.20 (Untested on 7.21) > > > > OnLine Dynamic Server version 7.22, 7.23 (Untested on 7.21) > > > > both failed with an error writing to /tmp/arcucl..<something> file. > > > > Using truss showed that it open the file for writing > > (O_CREAT + O_TRUNC + writing), wrote something to it > > > > (I assume the table) and then errored trying to read from the file > > descriptor which was only opened for writing! > > > > Art, I'd like to write an new version but need the tape format for > > ontape, I'd be happy to sign an NDA. Any ideas? > > > > >a onunload format file which you can reload using Informix onload. > > >Arcunload has trouble with tape sizes that are not an even multiple > of the > > >blocksize and with multiple volume archives and is ONLY available > for > > >some specific platform/version combinations. > > > > > > > > >Art S. Kagel > > > > -- > > David Williams > > > > Sent via Deja.com http://www.deja.com/ > Before you buy.