DB2 Version 10.1 for Linux, UNIX, and Windows

Load sessions - CLP examples

Example 1

TABLE1 has 5 columns:

COL1 VARCHAR 20 NOT NULL WITH DEFAULT
COL2 SMALLINT
COL3 CHAR 4
COL4 CHAR 2 NOT NULL WITH DEFAULT
COL5 CHAR 2 NOT NULL

ASCFILE1 has 6 elements:

ELE1 positions 01 to 20
ELE2 positions 21 to 22
ELE3 positions 23 to 23
ELE4 positions 24 to 27
ELE5 positions 28 to 31
ELE6 positions 32 to 32
ELE7 positions 33 to 40

Data Records:

   1...5...10...15...20...25...30...35...40
   Test data 1         XXN 123abcdN
   Test data 2 and 3   QQY    XXN
   Test data 4,5 and 6 WWN6789    Y

The following command loads the table from the file:

   db2 load from ascfile1 of asc modified by striptblanks reclen=40
      method L (1 20, 21 22, 24 27, 28 31)
      null indicators (0,0,23,32)
      insert into table1 (col1, col5, col2, col3)

Note:

The specification of striptblanks in the MODIFIED BY parameter forces the truncation of blanks in VARCHAR columns (COL1, for example, which is 11, 17 and 19 bytes long, in rows 1, 2 and 3, respectively).
The specification of reclen=40 in the MODIFIED BY parameter indicates that there is no newline character at the end of each input record, and that each record is 40 bytes long. The last 8 bytes are not use to load the table.
Since COL4 is not provided in the input file, it will be inserted into TABLE1 with its default value (it is defined NOT NULL WITH DEFAULT).
Positions 23 and 32 are used to indicate whether COL2 and COL3 of TABLE1 will be loaded NULL for a given row. If there is a Y in the column's null indicator position for a given record, the column will be NULL. If there is an N, the data values in the column's data positions of the input record (as defined in L(........)) are used as the source of column data for the row. In this example, neither column in row 1 is NULL; COL2 in row 2 is NULL; and COL3 in row 3 is NULL.
In this example, the NULL INDICATORS for COL1 and COL5 are specified as 0 (zero), indicating that the data is not nullable.
The NULL INDICATOR for a given column can be anywhere in the input record, but the position must be specified, and the Y or N values must be supplied.

Example 2 (using dump files)

Table FRIENDS is defined as:

   table friends "( c1 INT NOT NULL, c2 INT, c3 CHAR(8) )"

If an attempt is made to load the following data records into this table,

   23, 24, bobby
   , 45, john
   4,, mary

the second row is rejected because the first INT is NULL, and the column definition specifies NOT NULL. Columns which contain initial characters that are not consistent with the DEL format will generate an error, and the record will be rejected. Such records can be written to a dump file.

DEL data appearing in a column outside of character delimiters is ignored, but does generate a warning. For example:

   22,34,"bob"
   24,55,"sam" sdf

The utility will load "sam" in the third column of the table, and the characters "sdf" will be flagged in a warning. The record is not rejected. Another example:

   22 3, 34,"bob"

The utility will load 22,34,"bob", and generate a warning that some data in column one following the 22 was ignored. The record is not rejected.

Example 3 (Loading a table with an identity column)

TABLE1 has 4 columns:

C1 VARCHAR(30)
C2 INT GENERATED BY DEFAULT AS IDENTITY
C3 DECIMAL(7,2)
C4 CHAR(1)

TABLE2 is the same as TABLE1, except that C2 is a GENERATED ALWAYS identity column.

Data records in DATAFILE1 (DEL format):

   "Liszt"
   "Hummel",,187.43, H
   "Grieg",100, 66.34, G
   "Satie",101, 818.23, I

Data records in DATAFILE2 (DEL format):

   "Liszt", 74.49, A
   "Hummel", 0.01, H
   "Grieg", 66.34, G
   "Satie", 818.23, I

Note:

The following command generates identity values for rows 1 and 2, since no identity values are supplied in DATAFILE1 for those rows. Rows 3 and 4, however, are assigned the user-supplied identity values of 100 and 101, respectively.
```
   db2 load from datafile1.del of del replace into table1
```

To load DATAFILE1 into TABLE1 so that identity values are generated for all rows, issue one of the following commands:

   db2 load from datafile1.del of del method P(1, 3, 4)
      replace into table1 (c1, c3, c4)
   db2load from datafile1.del of del modified by identityignore
      replace into table1

To load DATAFILE2 into TABLE1 so that identity values are generated for each row, issue one of the following commands:

   db2 load from datafile2.del of del replace into table1 (c1, c3, c4)
   db2 load from datafile2.del of del modified by identitymissing
      replace into table1

To load DATAFILE1 into TABLE2 so that the identity values of 100 and 101 are assigned to rows 3 and 4, issue the following command:
```
   db2 load from datafile1.del of del modified by identityoverride
      replace into table2
```
In this case, rows 1 and 2 will be rejected, because the utility has been instructed to override system-generated identity values in favor of user-supplied values. If user-supplied values are not present, however, the row must be rejected, because identity columns are implicitly not NULL.
If DATAFILE1 is loaded into TABLE2 without using any of the identity-related file type modifiers, rows 1 and 2 will be loaded, but rows 3 and 4 will be rejected, because they supply their own non-NULL values, and the identity column is GENERATED ALWAYS.

Example 3 (loading from CURSOR)

MY.TABLE1 has 3 columns:

ONE INT
TWO CHAR(10)
THREE DATE

MY.TABLE2 has 3 columns:

ONE INT
TWO CHAR(10)
THREE DATE

Cursor MYCURSOR is defined as follows:

   declare mycursor cursor for select * from my.table1

The following command loads all the data from MY.TABLE1 into MY.TABLE2:

   load from mycursor of cursor method P(1,2,3) insert into 
     my.table2(one,two,three)

Note:

Only one cursor name can be specified in a single LOAD command. That is, load from mycurs1, mycurs2 of cursor... is not allowed.
P and N are the only valid METHOD values for loading from a cursor.
In this example, METHOD P and the insert column list (one,two,three) could have been omitted since they represent default values.
MY.TABLE1 can be a table, view, alias, or nickname.