DBC (Copy) batch command

Purpose

To copy data from one Db2® object to another Db2® object. Both objects need to be accessible from the currently connected Db2® system.

Usage

The FM/Db2 copy utility can:

  • Optionally delete all rows from the target table prior to copying any data.
  • Select the rows and columns to be copied.
  • Limit the total number of rows to be copied.
  • Reformat data into one or more new columns.
  • Initialize columns with a new value or pattern.

The FM/Db2 copy utility takes no account of any referential integrity relationships defined on the source Db2® object.


1  DBC
1  OBJIN=? ? location. owner. name
1 ? ? OBJILOCN=location OBJIOWNR=owner OBJINAME=name
1! NATIVE=NO
1 NATIVE=YES
1! TINPUT=TDDIN
1 TINPUT=ddname?TINMEM=member
1 TMIN=template_data_set_name(member)
1  OBJOUT=? ? location. owner. name
1 ? ? OBJOLOCN=location OBJOOWNR=owner OBJONAME=name
1! TOUTPUT=TDDOUT
1 TOUTPUT=ddname?TOUTMEM=member
1 TMOUT=template_data_set_name(member)
1! UCREAD=NO
1 UCREAD=YES
1! LOCK=NONE
1 LOCK=
2.1 SHARE
2.1 EXCL
1! DUPKEY=IGNORE
1 DUPKEY=UPDATE
9 DUPMAX=
10.1 ALL
10.1 num
1! REFI=FAIL
1 REFI=IGNORE
1! AUDIT=YES
1 AUDIT=NO
1! DELROWS=NO
1 DELROWS=YES
12 ROWS=
13.1 ALL
13.1 num
OBJIN=location.owner.name
The optional name of the Db2® remote server (location) where the source object is located; the optional name of the owner of the source object (owner) and the source object name (name) to be copied.

When location is not specified the current (local) Db2® server is used. When the owner is not specified the object name is qualified using the current SQLID. When FM/Db2 generates the utility control statements, the owner value is non-blank.

OBJIN should be used when the fully qualified name fits on a single line in the JCL deck. The last usable column is column 71. When the fully qualified name does not fit on a single line in the JCL deck, use one or more of the OBJILOCN, OBJIOWNR, OBJINAME keywords to specify the object.

OBJILOCN=location
The optional name of the Db2® remote server (location) where the source object is located. See Specifying a Db2 object name.
OBJIOWNR=owner
The optional name of the owner of the source object (owner) to be copied. See Specifying a Db2 object name.
OBJINAME=name
The object name (name) for the source object to be copied. See Specifying a Db2 object name.
NATIVE
Specifies whether FM/Db2 should process Unicode encoded data natively when copying data between two Unicode encoded tables. This option can be ignored if the default processing of Unicode data is desired; that is, Unicode data is potentially substituted with error characters when copying data between Unicode tables.
YES
Process Unicode data natively.
NO
Do not Process Unicode data natively. This is the default.
TINPUT=ddname
Defines a reference to a DD statement for the data sets which contain the Db2® template that describes the source Db2® object to be copied. If you specify a concatenated DD, then you must provide the member name, member, via the TINMEM keyword. See Specifying the template for a Db2 object.
TINMEM=member
The name of the template member in the dataset(s) identified by the TINPUT parameter, if it has not been specified on the DD statement. This parameter must not be specified if the TMIN parameter is specified. See Specifying the template for a Db2 object.
TMIN=template_data_set_name(member)
The PDS (template_data_set_name) and member name (member) of the File Manager Db2® template that describes the source Db2® object to be copied. See Specifying the template for a Db2 object.
OBJOUT=location.owner.name
The optional name of the Db2® remote server (location) where the target object is located; the optional name of the owner of the target object (owner) and the target object name (name) to be copied.

When location is not specified the current (local) Db2® server is used. When the owner is not specified the object name is qualified using the current SQLID. When FM/Db2 generates the utility control statements, the owner value is non-blank.

OBJOUT should be used when the fully qualified name fits on a single line in the JCL deck. The last usable column is column 71. When the fully qualified name does not fit on a single line in the JCL deck, use one or more of the OBJOLOCN, OBJOOWNR, OBJONAME keywords to specify the object.

OBJOLOCN=location
The optional name of the Db2® remote server (location) where the target object is located. See Specifying a Db2 object name.
OBJOOWNR=owner
The optional name of the owner of the target object (owner) to be copied. See Specifying a Db2 object name.
OBJONAME=name
The object name (name) for the target object to be copied. See Specifying a Db2 object name.
TOUTPUT=ddname
Defines a reference to a DD statement for the data sets which contain the Db2® template that describes the target Db2® object. If you specify a concatenated DD, then you must provide the member name, member, via the TOUTMEM keyword. See Specifying the template for a Db2 object.
TOUTMEM=member
The name of the template member in the dataset(s) identified by the TOUTPUT parameter, if it has not been specified on the DD statement. This parameter must not be specified if the TMOUT parameter is specified. See Specifying the template for a Db2 object.
TMOUT=template_data_set_name(member)
The PDS (template_data_set_name) and member name (member) of the FM/Db2 template that describes the target Db2® object. See Specifying the template for a Db2 object.
UCREAD
Specifies whether FM/Db2 should use uncommitted read, when accessing the source object:
YES
Uncommitted read is used. In this case, Db2® takes no locks when accessing the data in the source table, and it is possible that the data for a row changes after being read by FM/Db2, resulting in data inconsistencies.
NO
Uncommitted read is not used. This is the default.
LOCK
Specifies whether the source table should be locked, prior to copying data. The possible options are:
NONE
The default and recommended value. Any locks taken by Db2® depend on Db2® installation options, and options specified when the source object was created, unless negated by UCREAD=YES.
SHARE
FM/Db2 locks the source object in SHARE mode before copying data. This option is only effective for tables, it has no effect if the source object is a view. When an object is locked in SHARE mode, other users may read the object, but no updates by other users are possible.
EXCL
FM/Db2 locks the source object in EXCLUSIVE mode before copying the data. This option is only effective for tables, it has no effect if the source object is a view. When an object is locked in EXCLUSIVE mode, no other users may access the object at all, unless using uncommitted read.
DUPKEY
Specifies how FM/Db2 should respond to any duplicate key errors:
IGNORE
FM/Db2 ignores the error and continues processing. The row that caused the duplicate key error is not copied to the target Db2® object.
UPDATE
FM/Db2 attempts to update the existing row. The columns of the table that are part of the unique index that caused the SQLCODE-803 error are used to identify the row. No comparison is made between the copied row and the existing row prior to the update operation (that is, the update is done regardless of whether the copied row and existing row are the same). If there is more than one unique index defined on the target table, it is possible for an SQLCODE-803 to occur when the update is attempted. This is considered to be an update error.
DUPMAX
The number of duplicate key errors that are allowed before FM/Db2 terminates import processing:
ALL or 0
There is no limit.
num
The number of duplicate key errors allowed, up to a maximum of 2147483647.
REFI
Specifies whether to ignore errors arising from the violation of a check constraint (SQLCODE-545). The default is REFI=FAIL, meaning any check constraint error terminates copy processing. Otherwise (REFI=IGNORE) check constraint errors are ignored.
AUDIT
Specifies whether FM/Db2 is to write records to the FM/Db2 audit log:
YES
Write records to the FM/Db2 audit log. This is the default.
NO
Do not write records to the FM/Db2 audit log.
This option may be ignored if an installation audit option has been set that prevents a user modifying the installation setting.
DELROWS
Specifies whether FM/Db2 should attempt to delete all rows from the target table, prior to starting the copy operation:
YES
Attempt to delete all rows.
NO
Do not delete any rows. This is the default.
The deletion of rows from the target table is done using DELETE * FROM <object name> and fails if there are any referential integrity related errors arising from the deletion of rows.
ROWS
The maximum number of rows to be copied:
ALL
All rows from the source object are written to the target object.
num
The maximum number of rows to be copied. Valid range: 1-99999999.

Examples

Example 1: Copy the DSN8810.EMP table to a remote location. Delete all rows in the target table before copying the data. Use uncommitted read when accessing DSN8810.EMP. Audit the access to DSN8810.EMP.

//DBC JOB (acct),'name'
//* Copy DSN8810.EMP table to the same table at a remote location
//*
//FMNDB2   EXEC PGM=FMNDB2,PARM=('SSID=DSN1,SQID=ID1')
//STEPLIB  DD DSN=FMN.SFMNMOD1,DISP=SHR
//         DD DSN=DB2V810.DSN1.SDSNEXIT,DISP=SHR
//         DD DSN=DB2.V810.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//FMNTSPRT DD SYSOUT=*
//SYSTERM  DD SYSOUT=*
//SYSIN    DD *
$$FILEM DBC OBJIN="DSN8810"."EMP",
$$FILEM OBJOUT=REMLOC."DSN8810"."EMP",
$$FILEM     UCREAD=YES,
$$FILEM     LOCK=NONE,
$$FILEM     DUPKEY=IGNORE,
$$FILEM     DUPMAX=ALL,
$$FILEM     REFI=FAIL,
$$FILEM     AUDIT=YES,
$$FILEM     DELROWS=YES,
$$FILEM     ROWS=ALL
/*

Example 2: Copy the DSN8810.EMP table to a DSN8COPY.EMP. Update any rows in the target table that generate duplicate key errors, to a maximum of 100 duplicate key errors.

//DBC JOB (acct),'name'
//* Copy DSN8810.EMP table to a copy of the same table.
//*
//FMNDB2   EXEC PGM=FMNDB2,PARM=('SSID=DSN1,SQID=ID1')
//STEPLIB  DD DSN=FMN.SFMNMOD1,DISP=SHR
//         DD DSN=DB2V810.DSN1.SDSNEXIT,DISP=SHR
//         DD DSN=DB2.V810.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//FMNTSPRT DD SYSOUT=*
//SYSTERM  DD SYSOUT=*
//SYSIN    DD *
$$FILEM DBC OBJIN="DSN8810"."EMP",
$$FILEM OBJOUT="DSN8COPY"."EMP",
$$FILEM     LOCK=NONE,
$$FILEM     DUPKEY=UPDATE,
$$FILEM     DUPMAX=100,
$$FILEM     REFI=FAIL,
$$FILEM     ROWS=ALL
/*

Return codes

The return codes from the DBC function:

0
The function completed successfully
16
A serious error was encountered

Related functions

DBI
Import data from a sequential or VSAM file into a Db2® table (DBI (Import) batch command)
DBX
Export data from a Db2® object to a sequential or VSAM file (DBX (Export) batch command)
D2G
Create (generate) Db2® data (D2G (Data Generate) batch command)