Contents of shell archive myexport, submitted by Art S. Kagel: Version dated: July 12, 2006 ---------------------------------------------------------------------------- Utility: myexport/myimport Synopsis: Replacements for dbexport & dbimport which do not suffer from many of the problems from which those utilities suffer. Most importantly myexport/myimport does not lock the database. Note the Caviat section on this subject. Myexport and myimport also extend dbexport/dbimport with some new and useful features. There are options to allow myexport to use the myonpload utility by S. R. Krishna for the actual data unload instead of sqlunload. Future versions may allow myimport to also use myonpload. These are ksh scripts which use myschema (from my own utils2_ak package), ksh, awk, and sqlunload or sqlreload and sqlcmd (from Jonathan Leffler's sqlcmd package) to get the job done. The output from myexport should be compatible with dbimport and likewise the output from dbexport will load using myimport. The only caviats to compatibility are the -m & -u options. If you myexport with -m and use dbimport to reload the data you will have to manually execute the index/constraint creation script which is named 2.sql and is found in the .exp directory. Similarly the data distributions script created with the myexport -u option, named 3.sql, will not be executed by dbimport. Inversely if you dbexport you cannot use the myimport -m option unless you use myschema's schema split feature (with the -l dbimport compatility flag) to create the two separate scripts or manually break out the indexes and constraints using an editor and place and name the scripts appropriately. And likewise you will have to use either the myexport -u (or -U filename) option or dostats -f filename to create the data distributions script to be able to use the myexport -u flag if you are importing a dbexport unload. Author(s): Art S. Kagel Revision: 2.4 Features Version: 2.4 Version(s) supported: IDS (Modifications would be needed to support SE) File(s): README.1st -- This file myexport -- Myexport script, perms should be 550 or better myimport -- Myimport script, perms should be 550 or better simple.dbspace.file -- Simple dbspace map file for myimport -T complex.dbspace.file -- More complex dbspace map file for myimport -T Comments: Supports most of the commandline options of dbexport/dbimport with the notable exception of writing and reading tapes. Release Notes and Requirements: Version 2.4 add myimport support for loading with myonpload. Previous releases only supported unloading using myonpload. Deluxe and Express modes are supported. Also new option -F for myexport to enable filtering in the schema file so that parallel exports will result in a consistent database after dbimport/myimport loads the data. Use of -F requires myschema Revision 2.165 (utils2_ak dated July 12, 2006) or later. Version 2.3 adds support for secure connections to the database using myschema Revision 2.162 or later and sqlcmd (onpload does not support secure connections). Thanks to Mark Montalvo for alerting me to the need. Version 2.2 fixes a problem passing relative paths to the -i option to myimport. Added a '-l nolog' option to myimport. Version 2.1 fixes some awk problems running on systems, notably HPUX without gawk, with particularly pedantic versions of nawk. Substituted sqlcmd for dbaccess in myimport to create the database and tables so myimport can be run on a pure client machine. Version 2.0 includes the new -m & -u options and the myexport script also includes support for using the myonpload utility with the -D, -C, or -S flag. Version 1.4 improves myimport making it more forgiving of the location of the dbspace map file which defaults to current directory or the directory specified in the -i option. The -f option has been removed as it is really an adjunct to the tape option which is not supported. The SQL file is not restored to its unmodified condition when the script completes. The -q option has been implemented in myimport. Problems with parsing some combinations of options in myimport have been. Version 1.3 adds several extension features NOT found in dbexport/dbimport. See the Extensions section for details. Version 1.2 fixes some confusion if the path supplied to -o was not an absolute path. Now works correctly with relative paths. Version 1.1 adds the -p options to myexport which causes the unloads to occur in parallel reducing data integrity difficulties from not locking the database as dbexport does and improving total throughput. No known problems. You will need myschema and sqlcmd V5.2 or later and a compatible ksh version. Conversion to use bash or perl should not be difficult, I just like ksh. Also the version of awk I use is GNU Awk (GAWK) so you may need that or NAWK if you only have an older awk, I really have not checked if I am using anything version specific beyond the "-v variable=value" commandline option which most awk versions now support. Extensions: myexport: -F This flag enables filtering during load to trap constraint violations and force database consistency when used with -p. -p This flag causes myexport to export all tables in parallel in a single batch. Myexport waits for all unloads to complete. -D Use onpload with Dirty Read isolation level for exporting. -C Use onpload with Committed Read isolation level for exporting. -S Use onpload with Cursor Stability isolation level for exporting. -U Use sqlunload for exporting (default mode). -m Use myschema's second script feature to separate index and constraint creation commands from table creation commands. -u Use myschmea's -U option to create a SQL script to recreate the database's current level of data distributions. myimport: -p This flag causes myimport to import all tables in parallel in a single batch. Myimport waits for all loads to complete. -e Flag causes myimport to assume that the database and tables already exist and only need the data reloaded. Great for syncing databases after deleting rows from the target server and exporting them from a sister server. -p Load all tables in parallel. (Best used with -m! See caviats.) -l Specify logging mode for the new database. (Default: unbuffered if no -l, nolog if -l with no argument.) -T will cause myimport to open the named file path, read pairs of dbspace names from the lines of the file (one pair per line), convert the pairs to sed commands, and apply these to the schema file to map every incidence of the first dbspace of a pair to the second. Great for moving production databases to test where the available dbspaces may differ or for reorganizations. Two sample mapping files are provided a simple one and a complex one which implements a spacename swap and contains comments. Note that sed applies the dbspace mappings sequentially so unexpected results are possible if you do not keep that in mind. Note the temp mapping used in the complex sample file to swap two names. Caviats: Note, since dbexport and dbimport syntax permits it, myimport/myexport will accept the database name either as the first argument or following ALL flags and their optional arguments even though this violates the UNIX command syntax rules. However, the database name may not be intermixed with the optional flags and arguments. Because myexport does not lock the database when exporting one should be extremely careful about using it for more than emergency archiving and/or checkpointing if referential integrity is to be maintained upon reloading the data. It is ideal for data migration and syncing sister servers especially if other mechanisms are used to maintain the referential integrity of the database during the export such as stopping or blocking update tasks. Unlike dbexport myexport can be safely used while there are open FETCH cursors on the database and its tables. The parallel option to myimport is LIKELY to cause data dependency problems in any database with relational integrity. In addition since myschema is used by dbexport to generate the schema file and since that utility does not yet analyse these dependencies it may be necessary to reorder the tables in the schema or myexport and myimport with the -m flag so that indexes and constraints are created after the data has been loaded completely. If the myexport was run on an active server you may want to edit the index/constraints script adding START VIOLATIONS statements for tables that may experience consistency problems during the reload and setting the mode of constraints to FILTERING. Using the -p option to myexport for unloading an active database can reduce the possibility of experiencing integrity problems during reloads.