Copy Operation

The copy database operation enables a DBA or non-DBA to copy the following:

• Selected tables and views in a database
• All of the user objects, including tables, views, and procedures, that you own in a database

Ways to Perform the Copy Database Operation

To perform the copy database operation in VDBA, you use the Generate copy.in and copy.out dialog. This is invoked by selecting a database and choosing the Database menu, Generate Scripts, and Copydb.For the detailed steps for performing this procedure, see online help.

You can accomplish this task at the command line using the copydb command. For more information, see the Command Reference Guide.

Options on the Copy Database Operation

Some of the options that are available for copying a database are:

• Specify tables
• Create printable data files
• Directory name
• Source directory
• Destination directory

Objects that Are Copied

The database objects copied in the copy database operation depend on whether tables are specified for the operation.

The following table shows what is copied in each situation:

For further flexibility in the statements written to the copy.in script, you can use additional flags so that the generated scripts contain statements to manipulate only certain database objects. The flags can be used with the specified tables or views to print statements for any particular table or view.

For example, use the –with_index flag to print statements only related to index.

For more information on these flags, see the copydb command description in the Command Reference Guide.

Scripts Produced by the Copy Database Operation

When you perform the copy database operation, the following two scripts are produced:

• copy.out

  The copy.out script contains query language statements to copy your tables to operating system files. The script contains a copy statement for each table being copied

• copy.in

  The copy.in script contains query language statements to recreate your tables, views, procedures, and associated indexes, permissions, and integrities, and copy the table's data from the operating system files into a database.

To copy the tables out of the database, you run the copy.out script. To copy them into the same or another database, you run the copy.in script.

If you specify a particular table or view, the copy.in script contains statements to recreate the specified table or view only (along with applicable permissions and so on). The script does not contain statements to create all tables, views, and procedures.

Ingres tables can also be copied into XML format, as described in Generate and Import XML Operations.

Reloading Order

When using the copy.in script, database objects are reloaded in the following order:

• Users, groups, and roles (only when recreating the iidbdb)Tables

  Alter table statements are used as needed for deferred creation of referential integrities.Data

  The unload database operation (using the unloaddb command) handles all data types, including decimal data, large objects, and User Data Types (UDTs). More information can be found in Considerations When Loading Large Objects and Column Name and Format Specifications.

• Table permissions

  Permissions are recreated to the original time stamp order, and can or cannot be those of the table owner (depending on the grant options for the table).

• Indexes, index modifications, and integrities
• Views and related permissions

  Like tables, these are recreated to the original time stamp order.

• Synonyms
• Database procedures and related permissions

  Procedures depend on tables, views, events, and synonyms. Procedures can also see other procedures. To handle reloading of procedures, two passes are made during the unload database process through the iiprocedures catalog.

• Comments

Copy in ASCII or Binary Format

When copying a database, you should copy the files in ASCII format unless you are copying the database to another instance on the same machine or to a binary-compatible machine. In these cases, use binary format.

If you are not sure, use ASCII format.

Copying the files in ASCII format allows you to:

• Move the tables you own to an instance with a different machine architecture
• Edit the data files before copying them into a database

To copy the database files in ASCII format, specify the -c option (create printable data files) on the copydb command. (In VDBA, use the Create Printable Data Files option in the Generate copy.in and copy.out dialog.)

To copy the database files in binary format, do not specify the -c option.

Note: The -c option can affect the value of floating point numbers, as described in Floating Point Specification for Copy Database.

Note: Copying between releases of Ingres with different major release identifiers can cause problems if new columns were added to a later release to support new features. If you have made use of these new features in the later release and attempt to copy out and copy in to an earlier release that did not support the new feature, the copy in operation produces an error. Additionally, new reserved words can have been added and can require renaming tables and/or columns. To avoid this problem, simply edit the copy.in script to avoid loading the non-existent columns, or renamed tables or columns.

Caution! If you copy the files in binary format, do not edit them; doing so causes problems.

Floating Point Specification for Copy Database

When you execute the sql command to run the copy.out and copy.in scripts, the floating point specification defaults to 10 positions with 3 to the right of the decimal. If your data requires more precision, change the precision mask by using the f flag with the sql command when you run the copy.out and copy.in scripts.

For a description of the floating point (-f) flag parameters that is used with the sql command, see the Command Reference Guide.

Copy a Database to Another Instance

When copying a database, you can direct where the data is copied to and from by specifying a destination directory and a source directory. The directories can be on the same machine or different machines.

When you run the copy.out script, the data files are generated in the destination directory. If you have specified a source directory, you must move the copy.in script and the data files to this directory. When you run the copy.in script, the user objects are created and the tables are populated with the data from the source directory.

In VDBA, use the Destination Directory and/or Source Directory options specified in the Generate copy.in and copy.out dialog.

Locking While Copying a Database

When you create the copy database scripts or execute the copy.out script, the locking system takes shared locks on the tables being copied.

When you execute the copy.in script, the locking system takes exclusive locks on the tables being copied in.

Inconsistent Database During Copy Operation

There are two major ways that the database can become inconsistent during the creation of copy database scripts or the execution of the scripts:

• Because shared locks are taken on the tables being copied while the copy scripts are being created or copy.out is being executed, a user can alter the tables that are not locked during this time.
• A user can alter the tables being copied after you run the copy.out script, but before you have run the copy.in script.

  If a user drops a table in this interval, it generates an error message. However, if a user makes either of the following changes during this time, no error message is generated, and you do not know about the change:

  • Adds or deletes rows from a table
  • Adds a table

To ensure the consistency of the tables being copied, lock them exclusively while they are being copied.

Lock Database Exclusively When Copying

Locking ensures the consistency of the tables being copied.

To lock tables exclusively when copying them, use the sql command with -l flag when you run the copy.out script, as follows:

sql -l dbname <copy.out