The DATALOAD command allows you to load rows into existing DB2 Server for VSE & VM tables from data contained in a sequential input file that was created by processing external to the database manager or by the Database Services Utility's DATAUNLOAD processing. You can load data into DB2 Server for VSE & VM application servers, as well as into other application servers that support DRDA flow.
In general, each input data record used for DATALOAD processing contains data for a row of a table. Input data records can reside in a sequential file or can be embedded within the (input) control file. The DB2 Server for VM input data file is typically a CMS file, but it can be a virtual reader file or any tape or DASD file supported by CMS OS/QSAM.
| Note: | You should not use a sequential access method (SAM) file produced by Database Services Utility UNLOAD processing as input to DATALOAD processing. An error condition can result. Use the RELOAD command instead to load a file produced by the UNLOAD command. |
The DATALOAD command and its subcommands can:
You must complete the DATALOAD command on a single record; do not continue it on a second input record. The record immediately following a DATALOAD command must contain a Table Column Identification (TCI) subcommand. If, for example, you want to load data into 10 columns of a table, the first input record would contain the DATALOAD command, and the next 10 input records would contain TCI subcommands.
The other subcommands used with the DATALOAD command are INFILE and ENDDATA. The INFILE subcommand identifies the input data file or, when followed by an asterisk (*), identifies that the data is in the (input) control file and immediately follows the subcommand. You use the ENDDATA command to signal the end of user-supplied data; you do not need it if the input data is in a separate file.
Figure 15 illustrates a DATALOAD command followed by three TCI subcommands and an INFILE subcommand. Because the input data is contained in the file NEWACT, the ENDDATA command is not used in this example.
Figure 15. DATALOAD Command with Subcommands
*------------------.
DATALOAD TABLE (ACTIVITY) <-----* DATALOAD Command |
ACTNO 1-3 *------------------'*---------------.
ACTKWD 7-12 <-------------------------* TCI Subcommand|
ACTDESC 18-37 *-------------------* *---------------* + @
INFILE (NEWACT) <---------------* INFILE Subcommand |
*-------------------*
|
The DATALOAD command identifies the table that you want to load the data into (ACTIVITY). This table is sometimes referred to as the target table.
The next three records identify the names of the columns in the ACTIVITY table into which you want to insert data. These records are TCI subcommands. The numbers in the subcommands represent the positions where the data exists on the input records (that is, they identify the input data fields).
| Note: | If you are not loading data into the column to the extreme right of the table, add a TCI subcommand for that column and set it to null. By identifying the last table column, you avoid space problems in the future when you update the rows loaded into the table. Enough space is allocated in the table to include the column farthest to the right that you specified with the TCI subcommands. |
INFILE identifies the file where the input data is located (NEWACT). Figure 16 and Figure 17 illustrate how the above DATALOAD command sequence relates to the target table and the input file.
Figure 16. Schematic Representation of the DB2 Server for VSE DATALOAD Command
Figure 17. Schematic Representation of the DB2 Server for VM DATALOAD Command
As with SQL INSERT statements, all columns of a table do not have to be specified for DATALOAD processing. Specifying the last column of a table is recommended to avoid problems when updating the rows in the future. If a table column is omitted, however, the column must be defined to permit nulls. If this rule is violated, SQL and Database Services Utility error messages are generated, and DATALOAD processing is not performed.
| DB2 Server for VSE |
|---|
|
NEWACT is a tape file, because that is the default device type. You must specify INFILE (NEWACT PDEV(DASD) BLKSZ(2048)) if NEWACT is located on a direct access storage device (DASD). |