Informix platform migration advice needed

Good morning, My school is currently an HPUX shop and we use IDS 10.00.FC5 64bit version of the Informix binary. I am looking to test my HPUX University management ERP solution against an IDS engine running on a RedHat or Suse Linux on an Intel x86_64 box. I=B9ve ran into too many problems using dbimport/dbexport. I haven=B9t tried Art=B9s myexport/myimport yet. However, I do have EMC/Legato networker for my backup solution. Is it possible to migrate data cross-platform using onbar/legato? I believe an imported restore appears t= o be binary specific, but if anyone has done that with onbar in a cross platform use. I have tried using HPL, but it=B9s not practical due to the fact that I cannot export and import and entire database. If you have any advice and best practice recommendations for this challenge= , I would be very grateful to hear your experience. Thanks in advance. Sincerely, Jonathan Smaby Pomona College ------------------------------------------------------------- This message has been scanned by Postini anti-virus software. =0D

Jonathan Smaby wrote: > Good morning, > > My school is currently an HPUX shop and we use IDS 10.00.FC5 64bit version > of the Informix binary. I am looking to test my HPUX University management > ERP solution against an IDS engine running on a RedHat or Suse Linux on an > Intel x86_64 box. I=B9ve ran into too many problems using dbimport/dbexport. > I haven=B9t tried Art=B9s myexport/myimport yet. However, I do have EMC/Legato > networker for my backup solution. Is it possible to migrate data > cross-platform using onbar/legato? No. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com

I'm not sure why you had so much trouble with dbexport/dbimport since the files are text. How where the files transfered between the two systems? Are there pk/fk relationships that could be messing with the dbexport? I have heard great things about Art's Programs but haven't used them. How big is this database? Is it worth just scripting it out using "unload" for the first test? As for the backup tools being used... Sorry out of luck there. Good Luck, Eric B. Rowell On Mon, Jan 12, 2009 at 12:17 PM, Jonathan Smaby <jonathan.smaby@pomona.edu>wrote: > Good morning, > > My school is currently an HPUX shop and we use IDS 10.00.FC5 64bit version > of the Informix binary. I am looking to test my HPUX University management > ERP solution against an IDS engine running on a RedHat or Suse Linux on an > Intel x86_64 box. I=B9ve ran into too many problems using > dbimport/dbexport. > I haven=B9t tried Art=B9s myexport/myimport yet. However, I do have > EMC/Legato > networker for my backup solution. Is it possible to migrate data > cross-platform using onbar/legato? I believe an imported restore appears t= > o > be binary specific, but if anyone has done that with onbar in a cross > platform use. I have tried using HPL, but it=B9s not practical due to the > fact that I cannot export and import and entire database. > > If you have any advice and best practice recommendations for this > challenge= > , > I would be very grateful to hear your experience. > > Thanks in advance. > > Sincerely, > > Jonathan Smaby > Pomona College > > ------------------------------------------------------------- > This message has been scanned by Postini anti-virus software. > =0D > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Eric B. Rowell

Thanks Eric. 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. I though of editing the dbimport SQL script and plucking out all the create view commands, and putting them in a separate SQL script aftereach of the 6 databases have loaded their table data. But that's a lot of work. Thanks again Eric for any best practice advice. Sincerely, Jonathan Smaby Pomona College --- "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 imperfect 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." ~ David Oxtoby,Ph.D., President - Pomona College -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Eric Rowell Sent: Monday, January 12, 2009 9:28 AM To: ids@iiug.org Subject: Re: Informix platform migration advice needed [14487] I'm not sure why you had so much trouble with dbexport/dbimport since the files are text. How where the files transfered between the two systems? Are there pk/fk relationships that could be messing with the dbexport? I have heard great things about Art's Programs but haven't used them. How big is this database? Is it worth just scripting it out using "unload" for the first test? As for the backup tools being used... Sorry out of luck there. Good Luck, Eric B. Rowell On Mon, Jan 12, 2009 at 12:17 PM, Jonathan Smaby <jonathan.smaby@pomona.edu>wrote: > Good morning, > > My school is currently an HPUX shop and we use IDS 10.00.FC5 64bit version > of the Informix binary. I am looking to test my HPUX University management > ERP solution against an IDS engine running on a RedHat or Suse Linux on an > Intel x86_64 box. I=B9ve ran into too many problems using > dbimport/dbexport. > I haven=B9t tried Art=B9s myexport/myimport yet. However, I do have > EMC/Legato > networker for my backup solution. Is it possible to migrate data > cross-platform using onbar/legato? I believe an imported restore appears t= > o > be binary specific, but if anyone has done that with onbar in a cross > platform use. I have tried using HPL, but it=B9s not practical due to the > fact that I cannot export and import and entire database. > > If you have any advice and best practice recommendations for this > challenge= > , > I would be very grateful to hear your experience. > > Thanks in advance. > > Sincerely, > > Jonathan Smaby > Pomona College > > ------------------------------------------------------------- > This message has been scanned by Postini anti-virus software. > =0D > > > > ************************************************************************ ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Eric B. Rowell ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ------------------------------------------------------------- This message has been scanned by Postini anti-virus software.

No, you cannot use either ontape or onbar for cross platform restores, even if the platforms are binary compatible. What problems did you have using dbexport and dbimport? Art On Mon, Jan 12, 2009 at 12:17 PM, Jonathan Smaby <jonathan.smaby@pomona.edu>wrote: > Good morning, > > My school is currently an HPUX shop and we use IDS 10.00.FC5 64bit version > of the Informix binary. I am looking to test my HPUX University management > ERP solution against an IDS engine running on a RedHat or Suse Linux on an > Intel x86_64 box. I=B9ve ran into too many problems using > dbimport/dbexport. > I haven=B9t tried Art=B9s myexport/myimport yet. However, I do have > EMC/Legato > networker for my backup solution. Is it possible to migrate data > cross-platform using onbar/legato? I believe an imported restore appears t= > o > be binary specific, but if anyone has done that with onbar in a cross > platform use. I have tried using HPL, but it=B9s not practical due to the > fact that I cannot export and import and entire database. > > If you have any advice and best practice recommendations for this > challenge= > , > I would be very grateful to hear your experience. > > Thanks in advance. > > Sincerely, > > Jonathan Smaby > Pomona College > > ------------------------------------------------------------- > 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.

No problem, Jonathan. If this is the only problem, you don't need the full myexport/myimport package, just myschema. Get my utils2_ak package and build myschema. Using myschema with the -l option you can create a dbimport compatible schema file and myschema always outputs VIEWS after all actual TABLE definitions, unlike dbexport which processes all systables entries in tabid order causing SYNONYMS, VIEWS, and TABLES to be interleaved. If you have performed a non-in-place alter on a table it's tabid will have been changed and any previously existing views referencing those tables will have a lower tabid resulting in the problem you've seen. Another one of the quirks in dbschema and dbexport that cause me to have to keep maintaining and updating myschema. Just double check the generated data file names embedded in the schema. While I have tried to make the name generation compatible with dbexport, I never know when IBM will change the algorithm and my own algorithm is necessarily a set of educated guesses. It's not hard to write an awk or perl script to extract the filenames from the schema and determine if the file is there. BTW, I uploaded an new release of utils2_ak last night. It may take a few days for that version to be available for download. No major changes to myschema, but there were improvements to dbcopy, ul and dostats included in the package version with the README.1st file dated 2009-01-01. When you run myschema for use with dbimport, it's a good idea to enable the features that will automatically size each table's extents which will tend to improve load speed. Art On Mon, Jan 12, 2009 at 12:50 PM, Jonathan Smaby <Jonathan.Smaby@pomona.edu>wrote: > Thanks Eric. > > 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. > > 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. > > Thanks again Eric for any best practice advice. > > Sincerely, > > Jonathan Smaby > Pomona College > --- > "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 imperfect > 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." > > ~ David Oxtoby,Ph.D., President - Pomona College > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Eric Rowell > Sent: Monday, January 12, 2009 9:28 AM > To: ids@iiug.org > Subject: Re: Informix platform migration advice needed [14487] > > I'm not sure why you had so much trouble with dbexport/dbimport since > the > files are text. How where the files transfered between the two systems? > Are there pk/fk relationships that could be messing with the dbexport? > > I have heard great things about Art's Programs but haven't used them. > > How big is this database? Is it worth just scripting it out using > "unload" > for the first test? > > As for the backup tools being used... Sorry out of luck there. > > Good Luck, > > Eric B. Rowell > > On Mon, Jan 12, 2009 at 12:17 PM, Jonathan Smaby > <jonathan.smaby@pomona.edu>wrote: > > > Good morning, > > > > My school is currently an HPUX shop and we use IDS 10.00.FC5 64bit > version > > of the Informix binary. I am looking to test my HPUX University > management > > ERP solution against an IDS engine running on a RedHat or Suse Linux > on an > > Intel x86_64 box. I=B9ve ran into too many problems using > > dbimport/dbexport. > > I haven=B9t tried Art=B9s myexport/myimport yet. However, I do have > > EMC/Legato > > networker for my backup solution. Is it possible to migrate data > > cross-platform using onbar/legato? I believe an imported restore > appears t= > > o > > be binary specific, but if anyone has done that with onbar in a cross > > platform use. I have tried using HPL, but it=B9s not practical due to > the > > fact that I cannot export and import and entire database. > > > > If you have any advice and best practice recommendations for this > > challenge= > > , > > I would be very grateful to hear your experience. > > > > Thanks in advance. > > > > Sincerely, > > > > Jonathan Smaby > > Pomona College > > > > ------------------------------------------------------------- > > This message has been scanned by Postini anti-virus software. > > =0D > > > > > > > > > ************************************************************************ > ******* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Eric B. Rowell > > ************************************************************************ > ******* > 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.

Thanks Art. I assume myshema only generates a files to import only schema without actual table data. If that is the case, I need to import table data and schema, should I use myexport/myimport? Thanks again Art for your help. Sincerely, Jonathan Smaby Pomona College -- "Experience is something you don't get until just after you need it." ~Steven Wright -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Monday, January 12, 2009 10:15 AM To: ids@iiug.org Subject: Re: Informix platform migration advice needed [14490] No problem, Jonathan. If this is the only problem, you don't need the full myexport/myimport package, just myschema. Get my utils2_ak package and build myschema. Using myschema with the -l option you can create a dbimport compatible schema file and myschema always outputs VIEWS after all actual TABLE definitions, unlike dbexport which processes all systables entries in tabid order causing SYNONYMS, VIEWS, and TABLES to be interleaved. If you have performed a non-in-place alter on a table it's tabid will have been changed and any previously existing views referencing those tables will have a lower tabid resulting in the problem you've seen. Another one of the quirks in dbschema and dbexport that cause me to have to keep maintaining and updating myschema. Just double check the generated data file names embedded in the schema. While I have tried to make the name generation compatible with dbexport, I never know when IBM will change the algorithm and my own algorithm is necessarily a set of educated guesses. It's not hard to write an awk or perl script to extract the filenames from the schema and determine if the file is there. BTW, I uploaded an new release of utils2_ak last night. It may take a few days for that version to be available for download. No major changes to myschema, but there were improvements to dbcopy, ul and dostats included in the package version with the README.1st file dated 2009-01-01. When you run myschema for use with dbimport, it's a good idea to enable the features that will automatically size each table's extents which will tend to improve load speed. Art On Mon, Jan 12, 2009 at 12:50 PM, Jonathan Smaby <Jonathan.Smaby@pomona.edu>wrote: > Thanks Eric. > > 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. > > 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. > > Thanks again Eric for any best practice advice. > > Sincerely, > > Jonathan Smaby > Pomona College > --- > "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 imperfect > 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." > > ~ David Oxtoby,Ph.D., President - Pomona College > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Eric Rowell > Sent: Monday, January 12, 2009 9:28 AM > To: ids@iiug.org > Subject: Re: Informix platform migration advice needed [14487] > > I'm not sure why you had so much trouble with dbexport/dbimport since > the > files are text. How where the files transfered between the two systems? > Are there pk/fk relationships that could be messing with the dbexport? > > I have heard great things about Art's Programs but haven't used them. > > How big is this database? Is it worth just scripting it out using > "unload" > for the first test? > > As for the backup tools being used... Sorry out of luck there. > > Good Luck, > > Eric B. Rowell > > On Mon, Jan 12, 2009 at 12:17 PM, Jonathan Smaby > <jonathan.smaby@pomona.edu>wrote: > > > Good morning, > > > > My school is currently an HPUX shop and we use IDS 10.00.FC5 64bit > version > > of the Informix binary. I am looking to test my HPUX University > management > > ERP solution against an IDS engine running on a RedHat or Suse Linux > on an > > Intel x86_64 box. I=B9ve ran into too many problems using > > dbimport/dbexport. > > I haven=B9t tried Art=B9s myexport/myimport yet. However, I do have > > EMC/Legato > > networker for my backup solution. Is it possible to migrate data > > cross-platform using onbar/legato? I believe an imported restore > appears t= > > o > > be binary specific, but if anyone has done that with onbar in a cross > > platform use. I have tried using HPL, but it=B9s not practical due to > the > > fact that I cannot export and import and entire database. > > > > If you have any advice and best practice recommendations for this > > challenge= > > , > > I would be very grateful to hear your experience. > > > > Thanks in advance. > > > > Sincerely, > > > > Jonathan Smaby > > Pomona College > > > > ------------------------------------------------------------- > > This message has been scanned by Postini anti-virus software. > > =0D > > > > > > > > > ************************************************************************ > ******* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Eric B. Rowell > > ************************************************************************ > ******* > 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

Jonathan, 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 someone will have a more elegent way of doing this one of their tools but I will try to give you a shot in the dark... The following is not a best practice (not even close) as I don't think you want to perpare all of the scripts to do so. 40G is not much in the way of data... I would hope the following doesn't take long. One way in short is to: # unload all tables using a script which would take in a table list to start unloads ie: ksh mkdir database_name_1 cd database_name_1 dbaccess database_name_1 <<UNLOAD_TBL_LST UNLOAD TO database_name_1_table_list SELECT tabname FROM systables WHERE tabtype = "T" and tabid > 99; UNLOAD_TBL_LST cat database_name_1_table_list | while read table_name do dbaccess database_name_1<<UNLOAD_TBL >$table_name_unload.out 2>&1& UNLOAD TO database_name_1_$table_name.unl SELECT * FROM $table_name; UNLOAD_TBL done # 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). # Load all tables using a script which would take in a table list to start unloads ie: ksh cd database_name_1 cat database_name_1_table_list | while read table_name do dbaccess database_name_1<<LOAD_TBL >$table_name_load.out 2>&1 & LOAD FROM database_name_1_$table_name.unl INSERT INTO $table_name; LOAD_TBL done ##################################################### I would have to say the best practice would to be to have scripts which sepeately create at least the following; Script 1: Sequences * (If required for Tables) Tables Script 2: Indexes, Relationships UDR Views Permissions * (Could be included in both.) 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 hard to script this... But if you start with the basic database foundation for each database you can build from there. Eric B. Rowell On Mon, Jan 12, 2009 at 12:50 PM, Jonathan Smaby <Jonathan.Smaby@pomona.edu>wrote: > Thanks Eric. > > 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. > > 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. > > Thanks again Eric for any best practice advice. > > Sincerely, > > Jonathan Smaby > Pomona College > --- > "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 imperfect > 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." > > ~ David Oxtoby,Ph.D., President - Pomona College > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Eric Rowell > Sent: Monday, January 12, 2009 9:28 AM > To: ids@iiug.org > Subject: Re: Informix platform migration advice needed [14487] > > I'm not sure why you had so much trouble with dbexport/dbimport since > the > files are text. How where the files transfered between the two systems? > Are there pk/fk relationships that could be messing with the dbexport? > > I have heard great things about Art's Programs but haven't used them. > > How big is this database? Is it worth just scripting it out using > "unload" > for the first test? > > As for the backup tools being used... Sorry out of luck there. > > Good Luck, > > Eric B. Rowell > > On Mon, Jan 12, 2009 at 12:17 PM, Jonathan Smaby > <jonathan.smaby@pomona.edu>wrote: > > > Good morning, > > > > My school is currently an HPUX shop and we use IDS 10.00.FC5 64bit > version > > of the Informix binary. I am looking to test my HPUX University > management > > ERP solution against an IDS engine running on a RedHat or Suse Linux > on an > > Intel x86_64 box. I=B9ve ran into too many problems using > > dbimport/dbexport. > > I haven=B9t tried Art=B9s myexport/myimport yet. However, I do have > > EMC/Legato > > networker for my backup solution. Is it possible to migrate data > > cross-platform using onbar/legato? I believe an imported restore > appears t= > > o > > be binary specific, but if anyone has done that with onbar in a cross > > platform use. I have tried using HPL, but it=B9s not practical due to > the > > fact that I cannot export and import and entire database. > > > > If you have any advice and best practice recommendations for this > > challenge= > > , > > I would be very grateful to hear your experience. > > > > Thanks in advance. > > > > Sincerely, > > > > Jonathan Smaby > > Pomona College > > > > ------------------------------------------------------------- > > This message has been scanned by Postini anti-virus software. > > =0D > > > > > > > > > ************************************************************************ > ******* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Eric B. Rowell > > ************************************************************************ > ******* > 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. > > -- Eric B. Rowell

Myschema is a clone of dbschema with extra features. One of those features, enabled with the '-l' flag, causes the generated schema to be compatible with dbimport, ie you can include the file in the '-f' option to dbimport and it will read the schema to use to create the tables and other objects. The file will also contain the comment line that dbexport includes which contains the row count and the name of the file from which to import the data for the table. As I said, the only problem MAY be that some of those data file names embedded in the myschema output may not be identical to the file name generated by dbexport due to differences in the algorithm I use and the one that IBM uses in dbexport, though I try to keep it compatible. Using myexport with myimport will always work as will using myimport to import a database exported with dbexport and using dbimport to import a database exported with myexport. Again, only if you export with dbexport and try to use a schema generated by myschema (which is what myexport uses for schema file generation) might you run into the file naming problem. As I said it's easy enough to write a script to read both the dbexport schema and the myschema schema files and make certain that the filename in the latter is the same as the one in the former. Here's one that reads the schema and writes out a list of tables and their filenames: awk '/{ TABLE /{table=$3;}/{ unload file name/{file=$6; printf "Table: \\\\t%s \\\\tFile: \\\\t%s\\ ", table, file; }' <mydb.sql If your run that against both schema files and write the output to separate files, you can then sort both files (in case dbexport and myschema process the tables in different orders) and diff the files to see if there are any differences. If there are any differences (this is rare BTW), either rename the files or edit the myschema output file to correct the file name contained in there. Art On Mon, Jan 12, 2009 at 1:45 PM, Jonathan Smaby <Jonathan.Smaby@pomona.edu>wrote: > Thanks Art. > > I assume myshema only generates a files to import only schema without > actual table data. If that is the case, I need to import table data and > schema, should I use myexport/myimport? > > Thanks again Art for your help. > > Sincerely, > > Jonathan Smaby > Pomona College > -- > "Experience is something you don't get until just after you need it." > > ~Steven Wright > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Art Kagel > Sent: Monday, January 12, 2009 10:15 AM > To: ids@iiug.org > Subject: Re: Informix platform migration advice needed [14490] > > No problem, Jonathan. If this is the only problem, you don't need the > full > myexport/myimport package, just myschema. > > Get my utils2_ak package and build myschema. Using myschema with the -l > option you can create a dbimport compatible schema file and myschema > always > outputs VIEWS after all actual TABLE definitions, unlike dbexport which > processes all systables entries in tabid order causing SYNONYMS, VIEWS, > and > TABLES to be interleaved. If you have performed a non-in-place alter on > a > table it's tabid will have been changed and any previously existing > views > referencing those tables will have a lower tabid resulting in the > problem > you've seen. Another one of the quirks in dbschema and dbexport that > cause > me to have to keep maintaining and updating myschema. > > Just double check the generated data file names embedded in the schema. > While I have tried to make the name generation compatible with dbexport, > I > never know when IBM will change the algorithm and my own algorithm is > necessarily a set of educated guesses. It's not hard to write an awk or > perl script to extract the filenames from the schema and determine if > the > file is there. > > BTW, I uploaded an new release of utils2_ak last night. It may take a > few > days for that version to be available for download. No major changes to > myschema, but there were improvements to dbcopy, ul and dostats included > in > the package version with the README.1st file dated 2009-01-01. > > When you run myschema for use with dbimport, it's a good idea to enable > the > features that will automatically size each table's extents which will > tend > to improve load speed. > > Art > > On Mon, Jan 12, 2009 at 12:50 PM, Jonathan Smaby > <Jonathan.Smaby@pomona.edu>wrote: > > > Thanks Eric. > > > > 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. > > > > 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. > > > > Thanks again Eric for any best practice advice. > > > > Sincerely, > > > > Jonathan Smaby > > Pomona College > > --- > > "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 > imperfect > > 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." > > > > ~ David Oxtoby,Ph.D., President - Pomona College > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > Eric Rowell > > Sent: Monday, January 12, 2009 9:28 AM > > To: ids@iiug.org > > Subject: Re: Informix platform migration advice needed [14487] > > > > I'm not sure why you had so much trouble with dbexport/dbimport since > > the > > files are text. How where the files transfered between the two > systems? > > Are there pk/fk relationships that could be messing with the dbexport? > > > > > I have heard great things about Art's Programs but haven't used them. > > > > How big is this database? Is it worth just scripting it out using > > "unload" > > for the first test? > > > > As for the backup tools being used... Sorry out of luck there. > > > > Good Luck, > > > > Eric B. Rowell > > > > On Mon, Jan 12, 2009 at 12:17 PM, Jonathan Smaby > > <jonathan.smaby@pomona.edu>wrote: > > > > > Good mornin

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 can 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 this split during the export and automatically runs the secondary schema after the data is loaded during the import. Art On Mon, Jan 12, 2009 at 1:47 PM, Eric Rowell <erowell@gmail.com> wrote: > Jonathan, > > 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 someone > will have a more elegent way of doing this one of their tools but I will > try > to give you a shot in the dark... > > The following is not a best practice (not even close) as I don't think you > want to perpare all of the scripts to do so. 40G is not much in the way of > data... I would hope the following doesn't take long. > > One way in short is to: > > # unload all tables using a script which would take in a table list to > start unloads > ie: > > ksh > > mkdir database_name_1 > > cd database_name_1 > > dbaccess database_name_1 <<UNLOAD_TBL_LST > > UNLOAD TO database_name_1_table_list> > SELECT tabname> > FROM systables > > WHERE tabtype = "T" > > and tabid > 99; > UNLOAD_TBL_LST > > cat database_name_1_table_list | while read table_name > do > > dbaccess database_name_1<<UNLOAD_TBL >$table_name_unload.out 2>&1& > > UNLOAD TO database_name_1_$table_name.unl> > SELECT *> > FROM $table_name; > UNLOAD_TBL > done > > # 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). > > # Load all tables using a script which would take in a table list to start > unloads > ie: > > ksh > > cd database_name_1 > > cat database_name_1_table_list | while read table_name > do > > dbaccess database_name_1<<LOAD_TBL >$table_name_load.out 2>&1 & > > LOAD FROM database_name_1_$table_name.unl> > INSERT INTO $table_name; > LOAD_TBL > done > > ##################################################### > > I would have to say the best practice would to be to have scripts which > sepeately create at least the following; > > Script 1: > > Sequences * (If required for Tables) > > Tables > > Script 2: > > Indexes, Relationships > > UDR > > Views > > Permissions * (Could be included in both.) > > 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 hard > to script this... But if you start with the basic database foundation for > each database you can build from there. > > Eric B. Rowell > > On Mon, Jan 12, 2009 at 12:50 PM, Jonathan Smaby > <Jonathan.Smaby@pomona.edu>wrote: > > > Thanks Eric. > > > > 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. > > > > 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. > > > > Thanks again Eric for any best practice advice. > > > > Sincerely, > > > > Jonathan Smaby > > Pomona College > > --- > > "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 imperfect > > 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." > > > > ~ David Oxtoby,Ph.D., President - Pomona College > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > Eric Rowell > > Sent: Monday, January 12, 2009 9:28 AM > > To: ids@iiug.org > > Subject: Re: Informix platform migration advice needed [14487] > > > > I'm not sure why you had so much trouble with dbexport/dbimport since > > the > > files are text. How where the files transfered between the two systems? > > Are there pk/fk relationships that could be messing with the dbexport? > > > > I have heard great things about Art's Programs but haven't used them. > > > > How big is this database? Is it worth just scripting it out using > > "unload" > > for the first test? > > > > As for the backup tools being used... Sorry out of luck there. > > > > Good Luck, > > > > Eric B. Rowell > > > > On Mon, Jan 12, 2009 at 12:17 PM, Jonathan Smaby > > <jonathan.smaby@pomona.edu>wrote: > > > > > Good morning, > > > > > > My school is currently an HPUX shop and we use IDS 10.00.FC5 64bit > > version > > > of the Informix binary. I am looking to test my HPUX University > > management > > > ERP solution against an IDS engine running on a RedHat or Suse Linux > > on an > > > Intel x86_64 box. I=B9ve ran into too many problems using > > > dbimport/dbexport. > > > I haven=B9t tried Art=B9s myexport/myimport yet. However, I do have > > > EMC/Legato > > > networker for my backup solution. Is it possible to migrate data > > > cross-platform using onbar/legato? I believe an imported restore > > appears t= > > > o > > > be binary specific, but if anyone has done that with onbar in a cross > > > platform use. I have tried using HPL, but it=B9s not practical due to > > the > > > fact that I cannot export and import and entire database. > > > > > > If you have any advice and best practice recommendations for this > > > challenge= > > > , > > > I would be ve

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 bo

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 bein

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

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 inher

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 dbi

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@@