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.
- 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.
- 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 "/+.".
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)