IDS Forum

Re: Informix platform migration advice needed

Posted By: Art Kagel
Date: Monday, 12 January 2009, at 3:54 p.m.

In Response To: Re: Informix platform migration advice needed (rroussey@comcast.net)

The relevant options are:

-a - output actual allocated pages for extent size and calculate next size
as %
-m - use minimum number of pages (based on nrows) rather than current pages
-M - enter strategy for allocating pages to table fragments (min, max, avg)
-n - % of extent size to use for next size (after applying -e to extent
size)
-e - Adjust extent size up by n% over calculated size (affected by -a & -m)

The default is to output the currently recorded extent and next sizes from
systables/sysptnhdr.

Art

On Mon, Jan 12, 2009 at 3:20 PM, rroussey@comcast.net
<rroussey@comcast.net>wrote:

> Don't remember which flag/option it is, but myschema can get you a schema
> with
> current extent usage; *very* nice for creating a new copy of a database and
> creating 1 extent / table!
>
> --
> Bob
>
> -------------- Original message --------------
> From: "Jonathan Smaby" <jonathan.smaby@pomona.edu>
>
> > Thanks Art. That makes sense. I=B9ll create empty databases using
> myschema,
> > then re-import each databases using myimport.
> >
> > Jonathan Smaby
> > Pomona College
> >
> > From: Art Kagel
> > Reply-To:
> > Date: Mon, 12 Jan 2009 14:20:19 -0500 (EST)
> > To:
> > Subject: Re: Informix platform migration advice needed [14496]
> >
> > OOOO, I missed that point. Jonathan, if the problem is views that relate
> to
> > multiple databases in all of the databases that you need to migrate, you
> ca=
> > n
> > solve that one with myschema also. If you give myschema two filenames it
> > will write all of the CREATE INDEX, CREATE VIEW, CREATE SYNONYM, and
> ALTER
> > TABLE....ADD CONSTRAINT commands to the second file. You'll have to run
> all
> > of those secondary schema files manually after the database is created
> (and
> > preferably after the data has been loaded to speed the index builds) but
> > that will solve the problem with cross referenced VIEW definitions.
> >
> > There's an option to myexport and myimport (-m) that automatically does
> thi=
> > s
> > split during the export and automatically runs the secondary schema after
> > the data is loaded during the import.
> >
> > Art=20
> >
> > On Mon, Jan 12, 2009 at 1:47 PM, Eric Rowell wrote:
> >
> > > Jonathan,=20
> > >=20
> > > I Love when Views Accross Databases get in the way ( have them and
> > > always have a script to recreate them after). I am sure that Art or
> someo=
> > ne
> > > will have a more elegent way of doing this one of their tools but I
> will
> > > try=20
> > > to give you a shot in the dark...
> > >=20
> > > The following is not a best practice (not even close) as I don't think
> yo=
> > u
> > > want to perpare all of the scripts to do so. 40G is not much in the way
> o=
> > f
> > > data... I would hope the following doesn't take long.
> > >=20
> > > One way in short is to:
> > >=20
> > > # unload all tables using a script which would take in a table list to
> > > start unloads=20
> > > ie:=20
> > >=20
> > > ksh=20
> > >=20
> > > mkdir database_name_1
> > >=20
> > > cd database_name_1
> > >=20
> > > dbaccess database_name_1 <> >=20
> > > UNLOAD TO database_name_1_table_list
> > >=20
> > > SELECT tabname=20
> > >=20
> > > FROM systables=20
> > >=20
> > > WHERE tabtype =3D "T"
> > >=20
> > > and tabid > 99;=20
> > > UNLOAD_TBL_LST=20
> > >=20
> > > cat database_name_1_table_list | while read table_name
> > > do=20
> > >=20
> > > dbaccess database_name_1<$table_name_unload.out 2>&1&
> > >=20
> > > UNLOAD TO database_name_1_$table_name.unl
> > >=20
> > > SELECT *=20
> > >=20
> > > FROM $table_name;
> > > UNLOAD_TBL=20
> > > done=20
> > >=20
> > > # Create DBSchema on all databases
> > > # Run All of the Scripts (Don't really care about errors for things not
> > > created since a second run should create them).
> > > # Run All of the Scripts Again (Don't really care about errors for
> things
> > > not created since a second run should create them).
> > >=20
> > > # Load all tables using a script which would take in a table list to
> star=
> > t
> > > unloads=20
> > > ie:=20
> > >=20
> > > ksh=20
> > >=20
> > > cd database_name_1
> > >=20
> > > cat database_name_1_table_list | while read table_name
> > > do=20
> > >=20
> > > dbaccess database_name_1<$table_name_load.out 2>&1 &
> > >=20
> > > LOAD FROM database_name_1_$table_name.unl
> > >=20
> > > INSERT INTO $table_name;
> > > LOAD_TBL=20
> > > done=20
> > >=20
> > > #####################################################
> > >=20
> > > I would have to say the best practice would to be to have scripts which
> > > sepeately create at least the following;
> > >=20
> > > Script 1:=20
> > >=20
> > > Sequences * (If required for Tables)
> > >=20
> > > Tables=20
> > >=20
> > > Script 2:=20
> > >=20
> > > Indexes, Relationships
> > >=20
> > > UDR=20
> > >=20
> > > Views=20
> > >=20
> > > Permissions * (Could be included in both.)
> > >=20
> > > Some of this is still hard to place since you could require a UDR in an
> > > Index or Require a View in a UDR. So as you have already stated it is
> har=
> > d
> > > to script this... But if you start with the basic database foundation
> for
> > > each database you can build from there.
> > >=20
> > > Eric B. Rowell=20
> > >=20
> > > On Mon, Jan 12, 2009 at 12:50 PM, Jonathan Smaby
> > > wrote:
> > >=20
> > > > Thanks Eric.=20
> > > >=20
> > > > I have over 900 tables in each database, in 6 databases. Most of my
> > > > DB's are between 3-5 Gigabytes each. The databases are exactly alike
> > > > because we have 5 Undergraduate colleges and 1 Graduate college with
> > > > their own ERP database, and we share the ERP application between the
> 6
> > > > institutions. The one database has about 40Gb because of all the
> > > > meticulous transaction auditing done by one of the colleges. The
> > > > problem that I'm running into is all of the views that point to each
> of
> > > > the databases, dbimport bombs because the SQL statement creating the
> > > > view to a table in a database that hasn't yet been imported. So, the
> > > > import dies before completing.
> > > >=20
> > > > I though of editing the dbimport SQL script and plucking out all the
> > > > create view commands, and putting them in a separate SQL script after
> > > > each of the 6 databases have loaded their table data. But that's a
> lot
> > > > of work.=20
> > > >=20
> > > > Thanks again Eric for any best practice advice.
> > > >=20
> > > > Sincerely,=20
> > > >=20
> > > > Jonathan Smaby=20
> > > > Pomona College=20
> > > > ---=20
> > > > "traditions-like people-should be judged on their merits, not on the
> > > > basis of historical associations unconnected to their actual
> character.
> > > > There is the troubling idea that all things associated with an
> imperfec=
> > t
> > > > past should be considered tainted even if there is nothing inherently
> > > > objectionable about them. And finally, there is the false sense of
> > > > closure provided by getting rid of something so that we no longer
> need
> > > > to talk about the issue that it calls to mind."
> > > >=20
> > > > ~ David Oxtoby,Ph.D., President - Pomona College
> > > >=20
> > > > -----Original Message-----
> > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> Of
> > > > Eric Rowell=20
> > > > Sent: Monday, January 12, 2009 9:28 AM
> > > > To: ids@iiug.org
> > > > Subject: Re: Informix platform migration advice needed [14487]
> > > >=20
> > > > I'm not sure why you had so much trouble with dbexport/dbimport since
> > > > the=20
> > > > files are text. How where the files transfered between the two
> systems?
> > > > Are there pk/fk relationships that could be messing with the
> dbexport?
> > > >=20
> > > > I have heard great things about Art's Programs but haven't used them.
> > > >=20
> > > > How big is this database? Is it worth just scripting it out using
> > > > "unload"=20
> > > > for the first test?
> > > >=20
> > > > As for the backup tools being used... Sorry out of luck there.
> > > >=20
> > > > Good Luck,=20
> > > >=20
> > > > Eric B. Rowell=20
> > > >=20
> > > > On Mon, Jan 12, 2009 at 12:17 PM, Jonathan Smaby
> > > > wrote:
> > > >=20
> > > > > Good morning,
> > > > >=20
> > > > > My school is currently an HPUX shop and we use IDS 10.00.FC5 64bit
> > > > version=20
> > > > > of the Informix binary. I am looking to test my HPUX University
> > > > management=20
> > > > > ERP solution against an IDS engine running on a RedHat or Suse
> Linux
> > > > on an=20
> > > > > Intel x86_64 box. I=3DB9ve ran into too many problems using
> > > > > dbimport/dbexport.
> > > > > I haven=3DB9t tried Art=3DB9s myexport/myimport yet. However, I do
> have
> > > > > EMC/Legato=20
> > > > > networker for my backup solution. Is it possible to migrate data
> > > > > cross-platform using onbar/legato? I believe an imported restore
> > > > appears t=3D=20
> > > > > o=20
> > > > > be binary specific, but if anyone has done that with onbar in a
> cross
> > > > > platform use. I have tried using HPL, but it=3DB9s not practical
> due
> to
> > > > the=20
> > > > > fact that I cannot export and import and entire database.
> > > > >=20
> > > > > If you have any advice and best practice recommendations for this
> > > > > challenge=3D=20
> > > > > ,=20
> > > > > I would be very grateful to hear your experience.
> > > > >=20
> > > > > Thanks in advance.
> > > > >=20
> > > > > Sincerely,=20
> > > > >=20
> > > > > Jonathan Smaby
> > > > > Pomona College
> > > > >=20
> > > > > -------------------------------------------------------------
> > > > > This message has been scanned by Postini anti-virus software.
> > > > > =3D0D=20
> > > > >=20
> > > > >=20
> > > > >=20
> > > > >=20
> > > >
> ***********************************************************************=
> > *
> > > > *******=20
> > > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > > >=20
> > > > >=20
> > > >=20
> > > > --=20
> > > > Eric B. Rowell=20
> > > >=20
> > > >
> ***********************************************************************=
> > *
> > > > *******=20
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >=20
> > > > -------------------------------------------------------------
> > > > This message has been scanned by Postini anti-virus software.
> > > >=20
> > > >=20
> > > >=20
> > > >=20
> > >=20
> > >=20
> >
> ***************************************************************************=
> > *
> > ***=20
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >=20
> > > >=20
> > >=20
> > > --=20
> > > Eric B. Rowell=20
> > >=20
> > >=20
> > >=20
> > >=20
> >
> ***************************************************************************=
> > *
> > ***=20
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >=20
> > >=20
> >
> > --=20
> > Art S. Kagel=20
> > Oninit (www.oninit.com)
> > IIUG Board of Directors (art@iiug.org)
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> an=
> > d
> > do not reflect on my employer, Oninit, the IIUG, nor any other
> organization
> > with which I am associated either explicitly or implicitly. Neither do
> > those opinions reflect those of other individuals affiliated with any
> entit=
> > y
> > with which I am affiliated nor those of the entities themselves.
> >
> >
> ***************************************************************************=
> > *
> > ***=20
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> > -------------------------------------------------------------
> > This message has been scanned by Postini anti-virus software.
> > =0D
> >
> >
> >
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)

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

Messages In This Thread

• Informix platform migration advice needed
  Jonathan Smaby -- Monday, 12 January 2009, at 12:17 p.m.
  • Re: Informix platform migration advice needed
    Obnoxio The Clown -- Monday, 12 January 2009, at 12:22 p.m.
  • Re: Informix platform migration advice needed
    Eric Rowell -- Monday, 12 January 2009, at 12:28 p.m.
    • RE: Informix platform migration advice needed
      Jonathan Smaby -- Monday, 12 January 2009, at 12:50 p.m.
      • Re: Informix platform migration advice needed
        Art Kagel -- Monday, 12 January 2009, at 1:14 p.m.
        • RE: Informix platform migration advice needed
          Jonathan Smaby -- Monday, 12 January 2009, at 1:45 p.m.
          • Re: Informix platform migration advice needed
            Art Kagel -- Monday, 12 January 2009, at 2:17 p.m.
      • Re: Informix platform migration advice needed
        Eric Rowell -- Monday, 12 January 2009, at 1:47 p.m.
        • Re: Informix platform migration advice needed
          Art Kagel -- Monday, 12 January 2009, at 2:20 p.m.
          • Re: Informix platform migration advice needed
            Jonathan Smaby -- Monday, 12 January 2009, at 2:41 p.m.
            • Re: Informix platform migration advice needed
              rroussey@comcast.net -- Monday, 12 January 2009, at 3:20 p.m.
              • Re: Informix platform migration advice needed
                Art Kagel -- Monday, 12 January 2009, at 3:54 p.m.
            • Re: Informix platform migration advice needed
              Art Kagel -- Monday, 12 January 2009, at 3:42 p.m.
              • RE: Informix platform migration advice needed
                Jonathan Smaby -- Monday, 12 January 2009, at 4:51 p.m.
                • Re: Informix platform migration advice needed
                  Art Kagel -- Monday, 12 January 2009, at 5:25 p.m.
  • Re: Informix platform migration advice needed
    Art Kagel -- Monday, 12 January 2009, at 1:00 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.