Hello all. I sure hope someone here can help me because informix support did not seem very interested in my question. I have two systems running informix, one system A has much more storage space on it than the other (system B!). I have two databases in the same instance on system A and I want to restore only one of those databases to system B. I should be able to do this by identifying all of the dbspaces on system A that make up the database in question and then use ontape -D to restore the three critical dbspaces and plus the dbspaces that make up this database. When I ran this by support, they were not at all helpful and told me I might be able to use oncheck -PE to identify all of the dbspaces I need. They also seemed very smug and told me that most people would just have two systems that are the same size in order to accomplish this...that sort of rubbed me the wrong way, but oh well. I need to do it and I know there must be an easier way to identify the dbspaces I need to restore and it burns me up that I cant get more help than this from IBM. The oncheck -PE is such a mess from a ease of use standpoint that I cant imagine how it would be useful other than going through it by hand with a highlighter and ruler and even then the possiblity for human error would be huge!

Can someone here please help me? I am guessing this info is available via SQL if only I knew where and how to look.

Hi,
If you pass a single base from data of a server to another, the reasonable thing is to do a dbexport and dbimport on the other server.
If the operating systems and versions of Informix are equals, then can
you use the onload-onunload or HPL.

Gustavo.

You can do someting like

database sysmaster;
select dbsname, partdbsnum(partnum) from systabnames
group by 1

to know the id's for the tables in that database (mix this info with the sysdbspaces content)

Just in case the procedure doesn't exist in your system:

create procedure partdbsnum (partnum int ) returning int;
return trunc(partnum/1048576);
end procedure;


But I think it won't work as easy as you believe: You need the OS to be the same in both machines (I'd bet), and the restore process will ask for the logical logs tapes, and if you don't provide them, the dbspaces will be marked as 'down' (or at least that was the behaviour in old versions). I'd use onunload-onload in your case. It's fast and reliable enough.

Hope it helps.

It may be rather cumbersome and time consuming, but wouldn't a dbexport/dbimport be the easiest way to accomplish this task.

Restoring critical dbspaces from systemA into systemB would tend to futz-up anything that already exists in systemB, wouldn't it?

There's also the issue of restoring logical logs, systemA & systemB will have different "current logical log" numbers.

Informix IDS likes doing cold restores (restore everything) because restoring logical logs is optional. Doing a warm restore will require (demand) that all logical logs be restored to make everything current.

Thanks matute, I know it sounds like a hassle, but the product should not make type of operation so difficult. Our problem with high performance loader really is that its just IMHO stupidly complicated and a simple unload on a very large database is just not efficient enough. This is one area where informix needs some work I think, it would be nice if you could back up at a table level with indexes the way you can with DB2 and then restore those to another system without all the fuss. On an AS400 running DB2 Universal, you can save and restore a file and all of its indexes from one system to another very easily and the database takes care of consistency issues transparently and I guess I have been spoiled by it.

Based on the product documentation ontape has been designed with this exact functionality in mind (ontape -r -d) and theres no reason why restoring specific dbspaces should NOT work after I get the chunks worked out. I am going to have a serious problem with IBM to the tune of a liability issue if it doesnt. Having some tech support person telling me I should have two half million dollar RS6000's available if I want to do this is just simply the most retarded thing I have ever heard - its kind of like going into a music store and having some 16 year old clerk smirk and say "you really listen to that?" when I go to buy a cd and its made me a lot less inclined to be forgiving over this issue.

Restoring individual dbspaces is possible with later versions of Informix but I don't think you could use that feature to accomplish what you are trying to do. Keep in mind that sysmaster has tables that point to each database and the location (partition) of the tables within the dbspace. You might be overlaying the dbspace but you won't be pointing to the correct contents within sysmaster.

I agree that a database (or even table) level restore would be nice but given the architecture doesn't support this. In pre-9.x a couple of third party vendors tried to write backlup/restore product for this but gave up when Informix looked like they were pulling the plug.

dbexport/dbimport works for the small, simple stuff but our big db's took a customized solution where we multi-streamed both the unload and the load forcing all the cpu's to help in the process.