DBI (Import) batch command

Purpose

To import data, in a variety of formats, from a QSAM or VSAM file, into a Db2® object. The Db2® object must be accessible from the currently connected Db2® system.

Usage

The FM/Db2 import utility can:

Optionally delete all rows from the target table prior to importing any data.
Limit the number of records to be imported.
Select the records to be imported.
Map fields (using template mapping) in the import data set to columns in the target table, with appropriate data type and length conversions.
Optionally update existing rows, when the values in an imported row's unique index columns match an existing table row.

A REXX procedure can be used during the import to modify mapped and converted data, prior to inserting it into the target Db2® table. Any REXX functions can be used at this point, and records can be dropped, or the import stopped via the REXX proc.

At the end of the import process the following statistics are shown:

The count of rows successfully imported.
The number of duplicate row errors.
Optionally, the number of duplicate rows updated.
Optionally, the number of duplicate row updates that failed.
The number of rows in error.
The number of rows skipped via template selection.
The number of rows dropped by the REXX proc.

CSV import options

DSNIN=data_set_name

The data set name containing the data to be imported. If the data set is partitioned, a member name is required.

VOLSER=volume_serial_number

The volume serial number for the input data set. This is only required for uncataloged data sets.

TINPUT=ddname

Defines a reference to a DD statement for the data sets which contains the template member that describes the record structure of your input data. The default is TDDIN.

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 TCIN parameter is specified.

TCIN=template/copybook_data_set_name(member)

The PDS (template/copybook_data_set_name) and member name (member) of the template or copybook that describes the record structure of the data to be imported. The JCL generated by FM/Db2 specifies TCIN. See Specifying the template/copybook for a data set.

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) for the import. See Specifying a Db2 object name.

OBJONAME=name

The object name (name) for the target object for the import. 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.

UPDATE

Specifies whether FM/Db2 should attempt to update any rows that generate SQLCODE-803 (duplicate key) errors:

YES: FM/Db2 attempts to update the existing row. All columns of the table that are part of at least 1 unique index are used to identify the row. No comparison is made between the imported row and the existing row prior to the update operation (that is, the update is done regardless of whether the imported row and existing row are the same). When using this option it is recommended that a Db2® table name, rather than a view name, be specified. FM/Db2 does not attempt to update existing rows in a view defined on a view.
NO: No rows are updated. This is the default.

AUTOCOMMIT=value

Specifies a numeric value indicating how often FM/Db2 should issue a Db2® commit during the import. The default is zero, meaning that no Db2® commits are issued during the import operation. Any positive value results in FM/Db2 issuing a Db2® commit after value successful changes are made during the import. A successful change occurs when either a record is successfully inserted, or when a row is successfully updated after the attempted insert of the record resulted in a duplicate key error. The counter is reset after each Db2® commit is issued, therefore multiple Db2® commits may be issued. If an error occurs during the import and one or more Db2® commits have been issued, only the changes made subsequent to the last Db2® commit point are backed out.

STARTPOS=position

Specifies a numeric value indicating the starting record for the import. The default is 1, meaning FM/Db2 starts the import with the first record of the import file. Any value greater than 1 causes FM/Db2 to skip position-1 records before commencing the import.

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.

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 records to be imported:

ALL: All records from the import data set are inserted into the target object.
num: The maximum number of records to be imported. Valid range: 1-99999999.

PROC

REXX procedure:

procname: The name of the REXX procedure to be used when processing import records.
*: An inline procedure. The * is followed by one or more REXX procedure statements, and the terminating "/+.".

CSV Import Options

CSVIMP

Default NO. Specify CSVIMP=YES when importing CSV data with a template.

CSVIHDR

Default NO. Specify YES if the CSV data set to be imported includes headings.

CSVIDBCS

Default NO. Specify YES if the CSV data set to be imported includes double-byte data (DBCS) characters wrapped in shift-in (x'0E') and shift-out (x'0F') characters.

CSVISNGL

Default NO. Specify YES option if the CSV data set to be imported has single quote encapsulation.

CSVIDLM=delimiter

Default is the comma "," character. Use this keyword to specify an alternate delimiter value to the comma "," character.

NULLIND=character

Use this keyword to specify the null indicator value that was used to represent a null value in the CSV input data set. A nullable column will be set to null when the corresponding CSV value has:

A non-blank null indicator value.
A null CSV value. For example, two successive delimiters ',,'.

Examples

Example 1: Import data from a sequential file into DSN8810.EMP. Delete all rows in DSN8810.EMP before importing the data

//DBI JOB (acct),'name'
//* Import data from a sequential file into DSN8810.EMP.
//* Delete all the rows from the table before importing the data.
//*
//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 DBI DSNIN=ID1.EXPORT.EMP.EXAMPLE1,
$$FILEM TCIN=ID1.FM.TEMPLATE(EMP),
$$FILEM OBJOUT="DSN8810"."EMP",
$$FILEM UPDATE=NO,
$$FILEM DUPMAX=ALL,
$$FILEM DELROWS=YES,
$$FILEM ROWS=ALL
/*

Example 2: Import data from a sequential file into DSN8810.EMP. Use REXX proc PROC1 to process each record in the sequential file before inserting the record. Update any rows that cause duplicate key errors, to a maximum of 50 duplicate key errors.

//DBI JOB (acct),'name'
//* Import data from a sequential file into DSN8810.EMP.
//*
//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=*
//FMNEXEC  DD DSN=ID1.EXEC,DISP=SHR
//SYSIN    DD *
$$FILEM DBI DSNIN=ID1.EXPORT.EMP.EXAMPLE1,
$$FILEM TCIN=ID1.FM.TEMPLATE(EMP),
$$FILEM OBJOUT="DSN8810"."EMP",
$$FILEM UPDATE=YES,
$$FILEM DUPMAX=50,
$$FILEM DELROWS=NO,
$$FILEM ROWS=ALL,
$$FILEM PROC=PROC1
/*

Return codes

The return codes from the DBI function:

0: The function completed successfully
16: A serious error was encountered

Related functions

DBC: Copy data from one Db2® object to another (DBC (Copy) 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)