IDS Forum

RE: Onunload and onload

Posted By: ANDREW PISTOCCHI
Date: Wednesday, 5 June 2013, at 3:53 p.m.

In Response To: Re: Onunload and onload (Art Kagel)

Thanks for the input everyone. I will try the ontape to disk tonight to see how long it takes. This looks like the best option going from one machine to another and will be great to test all our databases on our R&D server.

Art....I would definitely like to try the myexport and myimport utilities for copying databases on our production server. On our production blade HPUX Integrity server we have 3 databases; Live, train and dev all in separate dbspaces. The dev database is exclusively for the developers and programmers. Our users have the ability to test in the train database. We have an increasing need for the train and dev databases to be updated frequently from live. Twice a year, I would use our scheduled downtime to copy live to train and dev using onunload / onload.

Now, developers and users want the train and dev databases on our production server refreshed from live a few times a year and possibly every month. Each database is about 33 GB. Our university runs Jenzabar CX on Informix. Our databases are larger than most Jenzabar CX schools but probably small from Informix standards. It sounds like the myexport / myimport utilities would work great for this process.

Andy P.

-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel
Sent: Wednesday, June 05, 2013 2:18 PM
To: ids@iiug.org
Subject: Re: Onunload and onload [30435]

If the disks for dbspaces are the same, then if you want to copy the entire server instance, using ontape to disk is easiest. If you just need to copy a single database, then yes, you can use dbexport/dbimport just note that dbexport will require an exclusive lock on the source database so no other users can be connected to the database while dbexport is running. If you need to make an export while the server is being accessed, you can download my package, myexport, which contains compatible replacements for dbexport and dbimport (myexport and myimport respectively). Myexport does not lock the database, however, in order to avoid constraint violations problems at import time, you should use the myexport -p option to export all tables concurrently in parallel.

Myexport/myimport also requires my dbschema replacement utility myschema which is in the package utils2_ak. Myexport/myimport can use any or three different methods for moving the data out of and back into the database.
In order of increasing speed these are:

1. Use Jonathan Leffler's sqlcmd (default, -U option). To use this

method you will also need the latest sqlcmd package.

2. Use the HPLoader (-D, -C, -S options). This requires Ravi Krishna's

myonpload package.

3. Use external tables (-E option). This does not require any other

packages.

With exceptions discussed in the README.1st file, the exported files and schema myexport creates can be reloaded with dbimport and dbexport files can be imported using myimport.

Another alternative would be to create an empty database on the target machine, and use my dbcopy utility (in the utils2_ak package) to copy the data directly from one server to the other. Dbcopy is very fast between servers and if you can separate ranges of rows by key ranges you can run multiple copies of dbcopy to move larger tables VERY quickly (dbschema -hd <table> is the easiest way to determine a good set of key ranges). You can use the mkdbcopy.awk script in my utils4_ak package to postprocess dbschema (or myschema) output into a shell script that will copy every table using dbcopy (you may have to reorder some of the tables in the resulting script to avoid constraint violations if the constraints and indexes are already in place at the time you copy the data).

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

On Wed, Jun 5, 2013 at 1:35 PM, ANDREW PISTOCCHI <APISTOCCHI@ut.edu> wrote:

> I have an older HP Integrity server that I would like to use for R&D
> testing for our Informix databases. I need assistance in copying a
> database from one server to another. Both servers are Integrity
> servers running HPUX 11i v3 and Informix 11.70.FC4. On the production
> server, I've used the onunload and onload utilities to copy a live
> database to a train or dev database. This works fine. Now I would like
> to copy a database from one server to the other without using any tape
> devices. Both servers are on the same network. Both servers have the
> same size dbspaces. The R&D server was restored from a HP Ignite
> make_tape_recovery bootable tape so all the file systems, logical
> volumes and dbspaces are the same on both servers.
>
> I've also seen older posts on using dbexport / dbimport and other
> utilities.
> What would be the best utility to do this on a regular basis? Thanks.
>
> Andy Pistocchi
> DBA / Systems Administrator
> The University of Tampa
> apistocchi@ut.edu<mailto:apistocchi@ut.edu>
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--047d7b34330e84ba5a04de6c336c

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• Onunload and onload
  ANDREW PISTOCCHI -- Wednesday, 5 June 2013, at 1:35 p.m.
  • Re: Onunload and onload
    John Miller iii -- Wednesday, 5 June 2013, at 2:02 p.m.
    • Re: Onunload and onload
      Nagaraju Inturi -- Wednesday, 5 June 2013, at 2:16 p.m.
    • RE: Onunload and onload
      Paul Watson -- Wednesday, 5 June 2013, at 2:25 p.m.
  • Re: Onunload and onload
    Art Kagel -- Wednesday, 5 June 2013, at 2:17 p.m.
    • RE: Onunload and onload
      ANDREW PISTOCCHI -- Wednesday, 5 June 2013, at 3:53 p.m.
      • Re: Onunload and onload
        Art Kagel -- Wednesday, 5 June 2013, at 4:14 p.m.
        • Re: Onunload and onload
          Marcus Haarmann -- Wednesday, 5 June 2013, at 4:29 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.