Copy Individual Database Objects

Tables, forms, and other user objects can be moved or copied from one database to another by using various copying techniques.

Note: Make sure that there is a current backup of the database before performing any of the procedures. If there is a problem in moving the object, restore the original. For details, see the chapter "Backing Up and Recovering Databases."

To transfer a database object from one database to another, use the appropriate copy method, as described.

Command Scripts

The copy command creates scripts that do the following:

• Copy out the object from the current database
• Recreate the object and copy back the saved data into the new database

Prepare to Copy a Database Object

Before you copy a database object, check whether the user already owns an object by the same name in the new database.

In the case of a table, it must be destroyed before proceeding.

Caution! If you fail to do this, the new table cannot be successfully created, and you can potentially populate the existing table with unwanted data.

For other user objects (forms, reports, and applications) be aware that the object being moved can replace an object with the same name in the new database.

Copy a Database Object

Follow these basic steps to copy a database object:

1. Check for duplicate objects, as described in Prepare to Copy a Database Object.
2. Log in as the DBA of the old database or as a privileged user who can impersonate the DBA (by using the -u flag for commands or by using the Users branch in the Virtual Nodes window in VDBA).
3. Use the relevant copy method to copy the object out of the database into an intermediate file.
4. Set the protections on the file copy.in or intermediate file and the data files so that you can access them after you log in as the DBA of the new database.
5. Log in as the DBA of the new database.
6. Using the relevant copy method in input mode, copy the intermediate file into the new database.

   There are now two copies of the object, one in each database.

7. To remove the original object, the DBA or privileged user must use the applicable Ingres tool as the original owner (by using the -u flag for commands or by using the Users branch in the Virtual Nodes window in VDBA) and delete the copy of the object.

Copy Tables

To copy or move data between databases, copy the relevant tables from the current database into another database.

In VDBA, use the Generate copy.in and copy.out dialog, invoked from the Copydb command from the Database Generate Scripts submenu. The detailed steps for performing these procedures can be found in online help.

To accomplish these tasks using system commands, use the copydb and sql commands.

Example: Move a Table to Another Database

In this example, the DBA moves the customers table, owned by John, from the accounts database to the orders database:

1. Enter the following command at the operating system prompt:

   copydb -ujohn accounts customers

   The copy.in and copy.out scripts are generated.

2. Enter the following commands in sequence:

   sql –ujohn accounts <copy.out
   sql –ujohn orders <copy.in

   There are now two copies of the customers table: one in the accounts database and one in the orders database.

3. The DBA removes the old table by logging into the Terminal Monitor (sql) as -ujohn and issues the following statement:

   DROP TABLE customers

Copy Forms

Copy or move one or more forms from one database to another using the copyform command.

There are two forms of syntax, one without the –i flag, which copies the forms from a database to a file. The next form, using the –i flag, copies the forms from the text file into a database.

If you are copying a form that already exists in the new database and you do not use the -r flag, you are prompted as to whether you want to overwrite the existing form. To do so, you select Yes. If the -r flag is specified with copyform, the form is automatically overwritten.

The copyform command can also be used with -q and -j flags, for copying QBFNames and JoinDefs. For a complete description of the flags and parameters for this command, see the Command Reference Guide.

Example: Move Forms to Another Database

Assume the DBA wants to move two forms owned by the DBA, customers and parts, from the accounts database to the orders database. The name forms.txt is selected as the intermediate file name. The following commands perform the move:

copyform accounts forms.txt customers parts
copyform -i orders forms.txt

At this point, there are two copies of the forms, one in accounts and one in orders. To remove the old forms, enter the Visual Forms Editor and delete them.

Copy Applications

Copying or moving an application from one database to another can be done using the copyapp command.

The copyapp command syntax has two forms: copyapp out and copyapp in.

The copyapp out command copies database objects associated with a specific application from the database to a text file. These objects are entities such as forms, reports, and join definitions.

The copyapp in command copies these database objects into the desired database.

Copyapp does copy all of the following:

• Forms referenced in 4GL, Query-By-Forms, and report frames
• Reports referenced in report frames
• JoinDefs referenced in Query-By-Forms frames

Copyapp does not copy any of the following:

• Forms (compiled or non-compiled) referenced by the 4GL-call qbf command or used in embedded query language procedures
• Reports referenced by the call report, call sreport, or call rbf 4GL command
• Graphs referenced by the call graph 4GL command

For a complete description of the flags and parameters for this command, see the Command Reference Guide.

Copy Reports

Move single or multiple reports from one database to another by using the copyrep command to copy the reports out and the sreport command to copy them into the second database.

The sreport command overwrites existing reports with the same names as those copied. You must check whether you have any reports with the same names as the ones being copied. If you do not want to overwrite them, edit filename.rw and change the report names.

For a complete description of the flags and parameters for copyrep and sreport, see the Forms-based Application Development Tools User Guide. Also, see the copyapp command in the Command Reference Guide.

Example: Copy Reports to Another Database

Assume the DBA wants to copy two reports she owns, parts_restock and parts_on_order, from the inventory database to the orders database. The file name textfile.rw is selected as the temporary file name. The following command at the operating system prompt copies the reports out to a file:

copyrep inventory textfile.rw parts_restock parts_on_order

The following command at the operating system prompt copies the reports into the orders database:

sreport orders textfile.rw

Increase Object Limit on Commands

Certain utilities like copydb, copyform, repcfg, genxml and convtouni limit to 100 the number of tables or views that can be specified on the command line. If this limit is insufficient for your application, the utexe.def file found in $II_SYSTEM/ingres/files can be modified.

To increase the 100 object limit in utexe.def file

1. Back up the utexe.def file in case you want to revert to the original file.
2. Open $II_SYSTEM/ingres/files/utexe.def for editing.
3. Search for the string %100S in the parameter description list directly under the command you want to modify.
4. Change the string to %nS where n is an integer defining the new limit.
5. Save the file and test.