Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004

    Unanswered: how to ID all dbspaces that make a database for restore purposes

    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.

  2. #2
    Join Date
    Aug 2003
    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.


  3. #3
    Join Date
    Jun 2004
    Madrid, Spain
    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.
    José Luis Matute.

    Regards from Spain.

  4. #4
    Join Date
    Dec 2003
    North America
    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.

  5. #5
    Join Date
    Jan 2004

    thanks matute

    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.

  6. #6
    Join Date
    Apr 2003
    Phoenix, AZ
    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.
    Fred Prose

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts