copydb Command--Copy and Restore a Database
The copydb command creates command files containing the SQL statements required to copy and restore a database. The command creates the following two command files in the current directory:
• copy.out contains SQL commands to copy all tables, views, and procedures owned by the user into files in the specified directory.
• copy.in contains SQL commands to copy the files into tables, recreate views, procedures, and indexes, and perform modifications.
To copy the database, you must execute the SQL commands in the copy.in and copy.out command files.
The name of a file created by copy.out consists of the name of the table followed by an extension made up of the owner’s login name. Any non-standard characters are mapped to underscores, for example, $ingres becomes _ingres. If such modified file names collide, a unique digit is appended to all but one of the filenames. For example, both foo#table and foo$table would generate the filename foo_table; these are resolved by being renamed foo_table and foo_table0.
Copydb can be used to change ownership of tables. For details, see the Database Administrator Guide.
System catalogs cannot be copied using copydb. Use unloaddb to copy a complete database, including system catalogs.
Notes:
• It is important that the database be recreated with copy.in before doing any work (for example, creating tables, forms, applications, or reports) in the new database. After recreating a database, be sure to run sysmod to optimize storage structures.
• Copydb may generate a copy.in or copy.out file that contains syntax that is not supported in prior releases. You may need to manually remove that syntax if you want to use the command file against an earlier release.
• When copydb is run from an Ingres 9.1 or later installation against an older version of Ingres, the copy.in script generated will contain the data type INGRESDATE or ANSIDATE instead of DATE for any date columns in CREATE TABLE statements.
• Copydb output is not encrypted. To protect sensitive data, encrypt the copydb output files as needed.
The copydb command has the following format:
copydb [-param_file=filename] | [dbname|vnode::dbname[/server_class] [-charset=charset]
[-c] [+user=[authuser]] [-uusername] [-Ggroupid] [-group_tab_idx] [-parallel]]
[-journal] [-P] [-source=dirname] [-dest=dirname] [-ddirname] [-with_tables]
[-with_modify] [-nodependency_check] [-with_data] [-all] [-order_ccm]
[-with_index] [-with_constr] [-with_views] [-with_synonyms] [-with_events]
[-with_proc] [-with_reg] [-with_rules] [-with_alarms] [-with_comments]
[-with_roles] [-with_sequences] [-no_seq] [-with_permits] [‑with_blankline]
[-with_csv] [-with_ssv] [-add_drop] [-infile=filename] [-outfile=filename]
[-relpath] [-no_loc] [-no_perm] [-noint] [-no_persist] [-no_repmod] [-no_rep]
[-nologging] [-no_warn] [-online] [‑on_logfull=commit|abort|notify]
[‑nvarchar] [‑include | ‑exclude] [‑compress] [‑filesize=size] {tablename|viewname}] [-help]
-param_file=filename
Reads filename for all other command line flags, database names, and any other command line arguments. This file must contain only one flag per line (see the examples that follow). If this flag is specified, no other flags or arguments can appear on the command line; they must, however, appear in the specified file.
dbname
-charset=characterset
Creates a printable data file. The option -charset=UTF8 is recommended for transporting databases between computer systems whose internal representations of non-ASCII data differ or where character sets may differ. Use of this option potentially engages the transliteration support in the COPY function. Unlike the -c option, -charset can be used with -with_csv and ‑with_ssv.
-c
Creates a printable data file encoded with the UTF8 character set. This is useful for transporting databases between computer systems whose internal representations of non-ASCII data differ. (When you restore a database from a file created using the –c flag, the copy command automatically converts data stored in this format back to the appropriate type.)
Copydb cannot represent the following types of data using printable characters: (1) binary data stored in varchar columns, and (2) user-maintained logical keys.
+user=[authuser]
-uusername
-Ggroupid
-group_tab_idx
Builds indexes in the command file immediately after the respective table creation. Without this flag, all indexes are created for all tables toward the end of the command file. The usermod command uses this flag to limit the loss of non-persistent indexes if it encounters a failure.
-parallel
Creates indexes using the parallel index creation syntax (to build multiple indexes concurrently).
-journal
Replaces the SET NOJOURNALING statement in the copy.in scripts with the SET JOURNALING statement, and disables specifying the WITH NOJOURNALING option on each CREATE TABLE statement in copy.in script. When using the –journal flag, fastload is not possible when loading the tables.
-P
Prompts for password if the session requires one.
-source=dirname
Specifies the directory that contains the data files and from which copy.in will be run. An empty dirname specification (“”) denotes the current directory. The –source specification overrides a –d specification for the copy in file.
If a source is specified without a destination (no –d or –dest), the default copy out directory is used.
The source directory specification is not checked for validity or existence. This allows the scripts to be moved to another machine.
-dest=dirname
Specifies the directory where the data files created by copy.out will be stored. An empty dirname specification (“.”) denotes the current directory. The –dest specification overrides a –d specification for the copy out file.
If a destination is specified without a source (no –source) then the default copy in directory is used.
The destination directory specification is not checked for validity or existence. This allows the scripts to be moved to another machine. The destination directory must be different from the database directory, $II_DATABASE/ingres/data/default/dbname, because the files have the same names as the table files.
-ddirname
Stores the copy.in and copy.out files in the specified directory instead of the default current directory. The file name must be fully specified.
-with_tables
Prints only the CREATE statements.
-with_modify
Prints only the MODIFY statements.
-nodependency_check
Adds the WITH NODEPENDENCY_CHECK option to any MODIFY commands generated. This option forces a table modify operation and destroys indexes needed for constraints.
IMPORTANT! If you use this option, you must preserve or recreate the table structure necessary to enforce the constraints.
-with_data
Prints only the COPY statements.
-all
Prints all the statements related to the database.
-order_ccm
Determines the order in which the COPY and MODIFY statements are written for the table. The default is to modify and then copy. If –CCM is specified, the order is to copy and then modify.
-with_index
Prints statements only related to index.
-with_constr
Prints statements only related to constraints, such as ALTER TABLE statements.
-with_views
Prints statements only related to views.
-with_synonyms
Prints statements only related to synonyms.
-with_ events
Prints statements only related to events.
-with_proc
Prints statements only related to procedures.
-with_reg
Print statements only related to registration.
-with_rules
Prints statements only related to rules.
-with_alarms
Prints statements only related to security alarms.
-with_comments
Prints statements only related to comments.
-with_roles
Prints statements only related to roles.
-with_sequences
Prints statements only related to sequences.
-no_seq
Does not print sequence related statements.
-with_permits
Prints statements only related to permits.
‑with_blankline
Generates redundant blank lines after every row in each data file generated. By default, redundant blank lines are not generated. This option applies only when using the –c flag to generate UTF8 portable data files.
-with_csv
Generates a comma-separated data file.
-with_ssv
Generates a semicolon-separated data file.
-add_drop
Writes a DROP statement also, before writing the CREATE statements. This is useful when the scripts are run repeatedly in case of errors, and tables are already created.
-infile=filename
Specifies an input file name for the copy.in file, so user can run copydb with different options and give different names for infile.
-outfile=filename
Specifies an output file name for the copy.out file.
-relpath
Removes the paths from the file names; the files will thus be created and copied from the current directory.
-noint
Runs copydb uninterrupted for all the tables.
-no_loc
Does not write the LOCATION clause for CREATE TABLE, CREATE INDEX, or MODIFY statements.
-no_perm
Does not print GRANT statements.
-no_persist
Does not write CREATE INDEX statements for indexes that have been created with the WITH PERSISTENCE clause.
-no_repmod
Does not write MODIFY table statements for Ingres Replicator system tables of a replicated database.
-no_rep
Does not write Ingres Replicator objects (tables, indexes, events, procedures) of a replicated database to the copy.in file.
-nologging
Writes a SET NOLOGGING statement to the copy.in file to bypass the logging and recovery system. Use with caution.
-no_warn
Suppresses the following message when copydb processing ends: "COPYDB has created the scripts copy.out and copy.in. From an Ingres prompt, run sql databasename < copy.out to copy the data."
-online
Adds the WITH CONCURRENT UPDATES option to the MODIFY statement, if specified.
-on_logfull=commit|abort|notify
Adds a SET SESSION WITH ON_LOGFULL=value statement to the generated copy.in file.
-nvarchar
Exports strings in UTF-8 encoding so that the data files are portable. On CREATE TABLE statements, column data types char, varchar, and long varchar are changed to nchar, nvarchar, and long nvarchar, respectively. The ‑nvarchar flag changes the default copy specification for character types from varchar to nvarchar. You do not need to specify the ‑c flag.
Note: The generated copy.in script cannot be used to reload data into a non-Unicode database.
For more information, see the Database Administrator Guide.
-include= | -exclude=
Includes or excludes the specified object types in the generated copy scripts. The flags are mutually exclusive of each other and of other flags that specify objects that should [not] be generated.
For example, generate a copy.in that contains only the CREATE and MODIFY statements for each table in the mydb database:
copydb -include=table,modify mydb
-compress
Includes WITH COMPRESSION clause in the generated copy scripts.
‑filesize=size
Splits the output file into multiple parts, where size is the number of megabytes per part.
tablename|viewname
Specifies the tables to be copied. If omitted, all tables are copied. This could also be a list of views; in that case only the given views are copied.
Note: No more than 100 objects can be specified. This limit can be raised by modifying the utexe.def file. For more information, see the Database Administrator Guide.
-help
Displays command syntax online.