copydb Command—Copy and Restore a Database
This section contains the following topics:
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 first three letters of the owner's login name. If file names collide, a unique digit replaces the last character of the table name segment.
Note: 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.
System catalogs cannot be copied using copydb. Use unloaddb to copy a complete database, including system catalogs.
Copydb can be used to change ownership of tables. For details, see the Database Administrator Guide.
Note: When copydb is run from an Ingres 2006 Release 2 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.
The copydb command has the following format:
copydb [-param_file=filename] | [dbname|vnode::dbname[/server_class] [-c]
[-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] [-add_drop] [-infile=filename]
[-outfile=filename] [-relpath] [-no_loc] [-no_perm] [-noint] [-no_persist]
[-no_repmod] [-no_rep][-online] {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
Specifies the name of the database and, if required, the vnode and server_class, as described in Standard Flags and Parameters.
-c
Creates a printable data file. 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.
-uusername
Specifies the effective user for the session, as described in Standard Flags and Parameters and Schema Qualifier.
-Ggroupid
Specifies a group identifier, as described in Standard Flags and Parameters. You must enclose this parameter in double quotation marks ("-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.
-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.
-online
Adds the WITH CONCURRENT UPDATES option to the MODIFY statement, if specified.
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.
The table name can be qualified with a valid schema name in the format schema.tablename, as described in Schema Qualifier.
- 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.
User Comments
You must be logged in to leave a comment.
