COPY CLPPlus command

The COPY CLPPlus command copies data from a source database and table to a target database and table.

Invocation

You must run this command from the CLPPlus interface or from within a CLPPlus script file.

Authorization

None

Required connection

None

Command Syntax

srcdb, destdb

connection_identifier

Command parameters

FROM srcdb

Defines the connection details and database name from which the data is copied.

Note: Either one, or both, of the FROM or TO parameters must be specified in the COPY command. If FROM is not specified, and TO is specified, the database you are currently connected to, if a connection exists, is used for the source database.

TO destdb

Defines the connection details and database name, which the data is copied into.

Note: Either one, or both, of the FROM or TO parameters must be specified in the COPY command. If TO is not specified, and FROM is specified, the database you are currently connected to, if a connection exists, is used for the target database.

APPEND

Inserts data into the dest_table. If dest_table does not exist, you must specify the destination table definition with the dest_table and [(col1, col2, ...)] variables.

CREATE

Creates dest_table and inserts the data. You must specify the destination table definition with the dest_table and [(col1, col2, ...)] variables. If dest_table exists, an error is returned.

INSERT

Inserts data into the dest_table. If dest_table does not exist, an error is returned.

REPLACE

You must specify the destination table definition with the dest_table and [(col1, col2, ...)] variables. The dest_table is dropped, re-created, and then data is inserted.

dest_table

Target database table into which data is inserted.

query

The SQL query that is used to get the data from the source database.

user

Specifies the user ID to connect to the database.

password

Specifies the password that corresponds to the user ID.

hostname

Specifies the name of the computer on which the database is located. For example, for a computer that is named ascender, specify @ascender.

port

Specifies the port number that receives connections on the computer where the database server is installed. The default is 50000.

database

Specifies the database name to which the connection is made. The default is SAMPLE.

dsn_alias

Specifies that the database connection information is read from the IBM® data server driver configuration file (db2dsdriver.cfg) from the dsn with alias name dsn_alias. If the specified dsn_alias is not found in the IBM data server driver configuration file, the string dsn_alias is used as a database name and all other connection parameters are obtained interactively.

Examples

The following command copies the rows in the emp table in the db1 database and appends them into the emp table in the db2 database.

COPY FROM u1@db1 TO u2@db2 APPEND emp USING SELECT * FROM emp;

The following command copies the rows in the emp table in the db1 database and appends them into the emp table in the db2 database. Since the target table does not exist in the database that is named db2, you must specify the table definition in the command.

COPY FROM u1@db1 TO u2@db2 APPEND emp (EmpId integer, name varchar(20)) USING SELECT * FROM emp;

The following command copies the rows in the emp table in the db1 database, creates the emp table in the db2 database, and inserts the rows into the newly defined table in db2.

COPY FROM u1@db1 TO u2@db2 CREATE emp (EmpId integer, name varchar(20)) USING SELECT * FROM emp;

The following command copies the rows in the emp table in the db1 database and inserts them into the emp table in the db2 database since the target table exists.

COPY FROM u1@db1 TO u2@db2 INSERT emp USING SELECT * FROM emp;

The following command copies the rows in the emp table in the db1 database, re-creates the emp table in the db2 database, and replaces the rows.

COPY FROM u1@db1 TO u2@db2 REPLACE emp (EmpId integer, name varchar(20)) USING SELECT * FROM emp;