 |
IDS Forum
Re: Informix platform migration advice needed
Posted By: Art Kagel
Date: Monday, 12 January 2009, at 5:25 p.m.
In Response To: RE: Informix platform migration advice needed (Jonathan Smaby)
If you want to use myexport or myimport you need the following:
- utils2_ak package (for myschema)
- myexport package (for myexport and myimport)
- sqlcmd package (Jonathan Leffler's package of SQL tools - myexport uses
sqlunload and myimport uses sqlreload by default [both are links to the
sqlcmd executable] for moveing data)
- myonpload package (optional - if you want myexport/myimport to use the
hploader instead of sqlcmd to move data - this can be faster, especially for
loading).
Art
On Mon, Jan 12, 2009 at 4:51 PM, Jonathan Smaby
<Jonathan.Smaby@pomona.edu>wrote:
> Thanks again Art.
>
> Last question.. with myexport, "sqlunload" is missing when 'nohup
> sqlunload' tries to execute. Is that available in another utility? My
> system doesn't seem to have sqlunload installed.
>
> Thanks Art for all the help.
>
> Jonathan Smaby
> Pomona College
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art Kagel
> Sent: Monday, January 12, 2009 12:42 PM
> To: ids@iiug.org
> Subject: Re: Informix platform migration advice needed [14499]
>
> No, even simpler. Use:
>
> # First get split SQL schema files using myschema for each database
> myschema -d database_number_1 -l database_number_1.tables.sql
> database_number_1.otherobjects.sql
> myschema -d database_number_2 -l database_number_2.tables.sql
> database_number_2.otherobjects.sql
> myschema -d database_number_3 -l database_number_3.tables.sql
> database_number_3.otherobjects.sql
> .....
> # Next reload each database creating the tables and loading the data
> dbimport -l -f ./database_number_1.tables.sql -i
> /dir/where/dbexport/put/the/files database_number_1
> dbimport -l -f ./database_number_2.tables.sql -i
> /dir/where/dbexport/put/the/files database_number_2
> dbimport -l -f ./database_number_3.tables.sql -i
> /dir/where/dbexport/put/the/files database_number_3
> .....
> # Finally go back to each database and create the indexes, views,
> synonyms,
> procedures/functions, constraints
> # and other objects dependent on the tables existence.
> dbaccess database_number_1 - <database_number_1.otherobjects.sql
> dbaccess database_number_2 - <database_number_2.otherobjects.sql
> dbaccess database_number_3 - <database_number_3.otherobjects.sql
> .....
>
> Art
>
> On Mon, Jan 12, 2009 at 2:41 PM, Jonathan Smaby
> <jonathan.smaby@pomona.edu>wrote:
>
> > 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 <art.kagel@gmail.com>
> > Reply-To: <ids@iiug.org>
> > Date: Mon, 12 Jan 2009 14:20:19 -0500 (EST)
> > To: <ids@iiug.org>
> > 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 <erowell@gmail.com>
> 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 <<UNLOAD_TBL_LST
> > >=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<<UNLOAD_TBL >$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<<LOAD_TBL >$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
> > > <Jonathan.Smaby@pomona.edu>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
> > > > <jonathan.smaby@pomona.edu>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.
> >
> >
>
> --
> 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.
>
> ************************************************************************
> *******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> -------------------------------------------------------------
> This message has been scanned by Postini anti-virus software.
>
>
>
> *******************************************************************************
> 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
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
